BigQuery: удаление дубликатов в секционированной таблице

У меня есть таблица BQ, которая разделена по времени вставки. Я пытаюсь удалить дубликаты из таблицы. Это истинные дубликаты: для 2-х одинаковых строк все столбцы равны - конечно, полезно иметь уникальный ключ:-(

Сначала я попытался выполнить запрос SELECT, чтобы перечислить дубликаты и удалить их:

SELECT
    * EXCEPT(row_number)
FROM (
    SELECT
    *,
    ROW_NUMBER() OVER (PARTITION BY id_column) row_number
    FROM
    `mytable`)
WHERE
    row_number = 1

Это приводит к уникальным строкам, но создает новую таблицу, которая не включает данные раздела - так что это не хорошо.

Я видел этот ответ, в котором говорится, что единственный способ сохранить разделы состоит в том, чтобы просмотреть их один за другим с помощью вышеуказанного запроса и сохранить в определенный целевой раздел таблицы.

Что я действительно хочу сделать, это использовать DML DELETE удалить дубликаты строк на месте. Я попробовал нечто похожее на то, что предложил этот ответ:

DELETE
FROM `mytable` AS d
WHERE (SELECT ROW_NUMBER() OVER (PARTITION BY id_column)
   FROM `mytable ` AS d2
   WHERE d.id = d2.id) > 1;

Но принятый ответ не работает и приводит к ошибке BQ:

Error: Correlated subqueries that reference other tables are not supported unless they can be de-correlated, such as by transforming them into an efficient JOIN

Было бы замечательно, если бы кто-нибудь мог предложить более простой (DML или другой) способ решения этой проблемы, поэтому мне не нужно будет перебирать все разделы по отдельности.

Источник

1 ответ

Решение

Вид взлома, но вы можете использовать MERGE Оператор для удаления всего содержимого таблицы и повторной вставки только отдельных строк атомарно. Вот пример:

-- Create a table with some duplicate rows
CREATE TABLE dataset.PartitionedTable
PARTITION BY date AS
SELECT x, CONCAT('foo', CAST(x AS STRING)) AS y, DATE_SUB(CURRENT_DATE(), INTERVAL x DAY) AS date
FROM UNNEST(GENERATE_ARRAY(1, 10)) AS x, UNNEST(GENERATE_ARRAY(1, 10));

Теперь для MERGE часть:

-- Execute a MERGE statement where all original rows are deleted,
-- then replaced with new, deduplicated rows:
MERGE dataset.PartitionedTable AS t1
USING (SELECT DISTINCT * FROM dataset.PartitionedTable) AS t2
ON FALSE
WHEN NOT MATCHED BY TARGET THEN INSERT (x, y, date) VALUES (t2.x, t2.y, t2.date)
WHEN NOT MATCHED BY SOURCE THEN DELETE
Источник

Вы можете сделать это с помощью одного оператора SQL MERGE без создания дополнительных таблиц.

-- WARNING: back up the table before this operation
-- FOR large size timestamp partitioned table 
-- -------------------------------------------
-- -- To de-duplicate rows of a given range of a partition table, using surrage_key as unique id
-- -------------------------------------------

DECLARE dt_start DEFAULT TIMESTAMP("2019-09-17T00:00:00", "America/Los_Angeles") ;
DECLARE dt_end DEFAULT TIMESTAMP("2019-09-22T00:00:00", "America/Los_Angeles");

MERGE INTO `gcp_project`.`data_set`.`the_table` AS INTERNAL_DEST
USING (
  SELECT k.*
  FROM (
    SELECT ARRAY_AGG(original_data LIMIT 1)[OFFSET(0)] k 
    FROM `gcp_project`.`data_set`.`the_table` AS original_data
    WHERE stamp BETWEEN dt_start AND dt_end
    GROUP BY surrogate_key
  )

) AS INTERNAL_SOURCE
ON FALSE

WHEN NOT MATCHED BY SOURCE
  AND INTERNAL_DEST.stamp BETWEEN dt_start AND dt_end -- remove all data in partiion range
    THEN DELETE

WHEN NOT MATCHED THEN INSERT ROW

кредит: https://gist.github.com/hui-zheng/f7e972bcbe9cde0c6cb6318f7270b67a

Источник
Другие вопросы по тегам