比较 BigQuery 中的表 [英] Compare Tables in BigQuery

问题描述

如何比较两个表(Table1 和 Table2)并找到 Table2 中的所有新条目或更改.

How would I compare two tables (Table1 and Table2) and find all the new entries or changes in Table2.

使用我可以使用的 SQL Server

Using SQL Server I can use

Select * from Table1
Except
Select * from Table2

这是我想要的样本

表 1

 A   |  1
 B   |  2
 C   |  3

表2

 A   |  1
 B   |  2
 C   |  2
 D   |  4

所以，如果我比较这两个表，我希望我的结果显示以下内容

So, if I comparing the two tables I want my results to show me the following

C   |   2
D   |   4

我尝试了一些没有运气的语句.

I tried a few statements with no luck.

推荐答案

现在我有了你的实际示例数据集，我可以编写一个查询来查找一个表中的每个域，而不是另一个表:

Now that I have your actual sample dataset, I can write a query that finds every domain in one table that is not on the other table:

https://bigquery.cloud.google.com/table/inbound-acolyte-377:demo.1024 有 24,729,816 行.https://bigquery.cloud.google.com/table/inbound-acolyte-377:demo.1025 有 24,732,640 行.

https://bigquery.cloud.google.com/table/inbound-acolyte-377:demo.1024 has 24,729,816 rows. https://bigquery.cloud.google.com/table/inbound-acolyte-377:demo.1025 has 24,732,640 rows.

让我们看看 1025 中没有 1024 的所有内容:

Let's look at everything in 1025 that is not in 1024:

SELECT a.domain
FROM [inbound-acolyte-377:demo.1025] a
LEFT OUTER JOIN EACH [inbound-acolyte-377:demo.1024] b
ON a.domain = b.domain
WHERE b.domain IS NULL

结果:39,629 行.(经过 8.1 秒，处理了 2.04 GB)

Result: 39,629 rows. (8.1s elapsed, 2.04 GB processed)

这篇关于比较 BigQuery 中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！