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

问题描述

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

pre $ SELECT $ _ $ $ $ $ $ $ TABLE_QUERY（database_main，'table_id CONTAINSTitle_AND length（table_id）> = 4'））
GROUP BY标识符，table_id按AppAnalyticCount排序DESC LIMIT 10
pre>

当然，这实际上不起作用，因为table_id在TABLE_QUERY函数产生的表聚合中不可见。有什么办法可以做到这一点？某些类型的表元数据可能会被连接？

解决方案

通过 _TABLE_SUFFIX 伪列。完整的文档位于 https://cloud.google.com/bigquery/docs/querying -wildcard桌。
请注意几件事：

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



• 您必须重命名 _TABLE_SUFFIX 放入您的 SELECT 列表中的其他内容中，即下面的示例说明它
  
  

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

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 

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?

解决方案

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:

• 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屋！