比较 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屋!