有没有办法在 Bigquery 表通配符查询中选择 table_id [英] Is there a way to select table_id in a Bigquery Table Wildcard Query

问题描述

我有一组按天分片的数据，其中单个条目不包含日期.我想使用表通配符来选择所有可用数据并取回按我感兴趣的列和捕获它的日期分组的数据.换句话说，就像这样:

I have a set of day-sharded data where individual entries do not contain the day. I would like to use table wildcards to select all available data and get back data that is grouped by both the column I am interested in and the day that it was captured. Something, in other words, like this:

SELECT table_id, identifier, Sum(AppAnalytic) as AppAnalyticCount 
    FROM  (TABLE_QUERY(database_main,'table_id CONTAINS "Title_" AND length(table_id) >= 4')) 
    GROUP BY identifier, table_id order by AppAnalyticCount DESC LIMIT 10 

当然，这实际上不起作用，因为 table_id 在由 TABLE_QUERY 函数生成的表聚合中不可见.有什么办法可以做到这一点吗?也许是对表元数据的某种连接?

Of course, this does not actually work because table_id is not visible in the table aggregation resulting from the TABLE_QUERY function. Is there any way to accomplish this? Some sort of join on table metadata perhaps?

推荐答案

此功能现在可通过 _TABLE_SUFFIX 伪列在 BigQuery 中使用.完整文档位于 https://cloud.google.com/bigquery/docs/querying-通配符表.需要注意的几点:

This functionality is available now in BigQuery through _TABLE_SUFFIX pseudocolumn. Full documentation is at https://cloud.google.com/bigquery/docs/querying-wildcard-tables. Couple of things to note:

• 您需要使用标准 SQL 来启用表通配符

• 您必须将 _TABLE_SUFFIX 重命名为 SELECT 列表中的其他名称，即以下示例说明了它

• You will need to use Standard SQL to enable table wildcards

• You will have to rename _TABLE_SUFFIX into something else in your SELECT list, i.e. following example illustrates it

SELECT _TABLE_SUFFIX as table_id, ... FROM `MyDataset.MyTablePrefix_*`

这篇关于有没有办法在 Bigquery 表通配符查询中选择 table_id的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！