查询 BigQuery 数据集中的最新表 [英] Query latest table in the BigQuery dataset

问题描述

我有一个包含类似表名以 yyyymmdd 结尾的表的数据集.例如:

I have a dataset containing tables with similar table names ending in yyyymmdd. For example:

myproject:mydataset.Sales20140815
myproject:mydataset.Sales20140816
myproject:mydataset.Sales20140817
myproject:mydataset.Sales20140818
...
myproject:mydataset.Sales20140903
myproject:mydataset.Sales20140904 

有没有办法编写BigQuery来查询数据集中的最新表(上面的例子是myproject:mydataset.Sales20140904)?

Is there any way to write the BigQuery to query the latest table in the dataset (for the above example it is myproject:mydataset.Sales20140904 )?

推荐答案

N.N.答案很好，但是如果重新导入旧数据集会错误地将其作为最新"数据集，则依赖修改日期是有问题的，因为 table_id 以正确的顺序明确列出日期，最好直接使用该值.

N.N. answer is good, but relying on the modification date is problematic if an old set of data is reimported that would erroneously be pulled as the "latest" Since the table_id explicitly lists the dates in the correct order it is best to use that value directly.

SELECT 
  *
FROM 
TABLE_QUERY(MyDATASET, 
      'table_id CONTAINS "MyTable" 
      AND table_id= (Select MAX(table_id) 
                              FROM MyDATASET.__TABLES__
                              where table_id contains "MyTable")'
            )

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