作为唯一键的两列上的BigQuery DeDuplication

Question

我们虔诚地使用BigQuery，并且有两个表，本质上是通过不同的过程并行更新的。我遇到的问题是，我们没有一个表的唯一标识符，如果可能的话，目标是将两个表组合为零重复。唯一的标识符是两列的组合。

我尝试过各种基于MySQL的查询，但似乎没有一个在BigQuery中工作。所以我在这里发帖寻求一些帮助。:)

步骤1.将“干净”表复制到新的合并表中。

步骤2.查询“脏”(旧)表并插入任何缺失的条目。

查询尝试1:

SELECT
  COUNT(c.*)
FROM
  [flash-student-96619:device_data.device_datav3_20160530] AS old
WHERE NOT EXISTS (
  SELECT
    1
  FROM
    [flash-student-96619:device_data_v7_merged.20160530] AS new
  WHERE
    new.dsn = old.dsn
    AND new.timestamp = old.timestamp 
)

错误:错误在:6.1-10.65。一次只能执行一个查询。

查询尝试2:

SELECT
  *
FROM
  [flash-student-96619:device_data.device_datav3_20160530]
WHERE
  (dsn, timestamp) NOT IN (
  SELECT
    dsn,
    timestamp
  FROM
    [flash-student-96619:device_data_v7_merged.20160530] 
  )

错误:在第7列第6行遇到"“、”“、”"，期望：")“.

老实说，如果我能在一个查询中做到这一点，我会很高兴的。我需要从两个表中提取，并使用独特的数据创建一个新的表。

有什么帮助吗？

Answer 1

像下面这样的东西应该能工作

SELECT * 
FROM (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY dsn, timestamp) AS dup
  FROM
    [flash-student-96619:device_data.device_datav3_20160530],
    [flash-student-96619:device_data_v7_merged.20160530] 
) 
WHERE dup = 1  

我建议使用明确的字段列表，而不是在外部选择中使用*，这样就可以从实际输出中省略dup。

Answer 2

有点晚了，但我想指出的是，您的原始查询使用标准SQL进行了一些小的修改(取消选中"Show“下的”uncheck“框)。我只需将new更改为其他东西，因为这是一个保留关键字。例如，此查询是有效的：

WITH OldData AS (
  SELECT
    x AS dsn,
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x HOUR) AS timestamp
  FROM UNNEST([1, 2, 3, 4]) AS x),
NewData AS (
  SELECT
    x AS dsn,
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x HOUR) AS timestamp
  FROM UNNEST([5, 2, 1, 6]) AS x)
SELECT
  COUNT(*)
FROM OldData oldData
WHERE NOT EXISTS (
  SELECT 1
  FROM NewData newData
  WHERE
    newData.dsn = oldData.dsn
    AND newData.timestamp = oldData.timestamp
);
+-----+
| f0_ |
+-----+
|   2 |
+-----+

关于你的第二次尝试，你可以：

WITH OldData AS (
  SELECT
    x AS dsn,
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x HOUR) AS timestamp
  FROM UNNEST([1, 2, 3, 4]) AS x),
NewData AS (
  SELECT
    x AS dsn,
    TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL x HOUR) AS timestamp
  FROM UNNEST([5, 2, 1, 6]) AS x)
SELECT
  *
FROM OldData
WHERE
  STRUCT(dsn, timestamp) NOT IN (
  SELECT AS STRUCT
    dsn,
    timestamp
  FROM NewData);
+-----+---------------------+
| dsn |      timestamp      |
+-----+---------------------+
|   3 | 2016-07-21 11:54:08 |
|   4 | 2016-07-21 10:54:08 |
+-----+---------------------+