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

问题描述

如何比较两个表（ Table1 和 Table2 ）并找到所有新条目或 Table2 。

使用SQL Server我可以使用

 从Table1中选择* b $ b除
外*从Table2中选择* b $ b  

这里是我想要的样本

Table1

  A | 1 
 B | 2 
 C | 3 
  

表2

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

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

  C | 2 
 D | 4 
  

我尝试了几条没有运气的陈述。

解决方案

现在我有了您的实际样本数据集，我可以编写一个查询，查找一个表中没有的每个域另一张表：

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行。

让我们看看1025中不在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）

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

Using SQL Server I can use

Select * from Table1
Except
Select * from Table2

Here a sample of what I want

Table1

 A   |  1
 B   |  2
 C   |  3

Table2

 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 has 24,729,816 rows. https://bigquery.cloud.google.com/table/inbound-acolyte-377:demo.1025 has 24,732,640 rows.

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

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

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