错误：TABLE_QUERY表达式无法查询BigQuery表 [英] Error: TABLE_QUERY expressions cannot query BigQuery tables

问题描述

这是关于Jordans的后续问题答案： BigQuery中的奇怪错误

我用来在Table_Query中查询参考表以退出一段时间。现在，在Joradan提到的最近的变化之后，我们的许多疑问都被打破了......我想问社区的建议，以寻找替代解决方案。

我有包含事件的表（MyTable_YYYYMMDD）。我想查询一段特定（或几个）广告系列的数据。该活动的时间段存储在包含所有活动数据（ID，StartCampaignDate，EndCampaignDate）的表格中。为了仅查询相关表格，我们使用Table_Query（），并在TableQuery（）中根据活动数据构建所有相关表格名称的列表。
这个查询以不同的参数多次以各种形式运行。使用通配符功能（而不是查询整个数据集）的原因是性能，执行成本和维护成本。因此，让它查询所有表并过滤结果不是一个选项，因为它会导致执行成本太高。

示例查询将如下所示：

  SELECT 
 * 
 FROM 
 TABLE_QUERY（[MyProject：MyDataSet]'table_id IN 
 SELECT CONCAT（MyTable _，STRING（Year * 100 + Month））TBL_NAME 
 FROM DWH.Dim_Periods P 
 CROSS JOIN DWH.Campaigns AS LC 
 WHERE ID IN（86254e5a-b856- ）
和日期（P.Date）BETWEEN DATE（StartCampaignDate）AND DATE（EndCampaignDate））''
  

现在已经坏了...
我的问题 - 信息，你应该查询哪些表存储在一个参考表中，你将如何查询相关的表（分区）当TableQuery不再被允许查询引用表时？

非常感谢

解决方案

div>

我看到的简单方式将其分成两步 -
步骤1 - 构建lis t将用于过滤table_id的

  SELECT GROUP_CONCAT_UNQUOTED（
 CONCAT（''，MyTable _，STRING （年* 100 +月），'''）
）TBL_NAME_LIST 
 FROM DWH.Dim_Periods P 
 CROSS JOIN DWH.Campaigns AS LC 
 WHERE ID IN（86254e5a-b856 -3b5a-85e1-0f5ab3ff20d6）
 AND DATE（P.Date）BETWEEN DATE（StartCampaignDate）AND DATE（EndCampaignDate）
  

请注意查询中的变化，将结果转换为您将在步骤2中使用的列表。

第2步 - 最终查询

  SELECT 
 * 
 FROM 
 TABLE_QUERY（[MyProject：MyDataSet]，
'table_id IN （< paste list（TBL_NAME_LIST）built in first query>）'）
  

以上步骤很容易在任何可能使用

的客户端中实现 - 如果您在BigQuery Web UI中使用它 - 这会让您做一些额外的手动移动，您可能不会appy about

我的回答很明显，你很可能已经把它作为一个选项，但是想要提及的是

This s a followup question regarding Jordans answer here: Weird error in BigQuery

I was using to query reference table within "Table_Query" for quit some time. Now, following the recent changes Joradan is referring to, many of our queries are broken... I would like to ask the community advice for alternative solution to what we are doing.

I have tables containing events ("MyTable_YYYYMMDD"). I want to query my data for a period of a specific (or several) campaign. The period of that campaign is stored in a table with all campaigns data (ID, StartCampaignDate, EndCampaignDate). In order to query only the relevant tables, we use Table_Query(), and within the TableQuery() we construct a list of all relevant table names based on the campaigns data. This query runs in various forms many times with different params. the reason for using wildcard function (rather than query the entire dataset), is performance, execution costs, and maintenance costs. So, having it query all tables and filter just the results is not an option as it drives execution costs too high.

a sample query will look like:

SELECT
  *
FROM
  TABLE_QUERY([MyProject:MyDataSet] 'table_id IN  
  (SELECT CONCAT("MyTable_",STRING(Year*100+Month)) TBL_NAME  
    FROM DWH.Dim_Periods P  
    CROSS JOIN DWH.Campaigns AS LC  
    WHERE ID IN ("86254e5a-b856-3b5a-85e1-0f5ab3ff20d6") 
    AND DATE(P.Date) BETWEEN DATE(StartCampaignDate) AND DATE(EndCampaignDate))')

This is now broken... My question - the info, which tables should you query is stored on a reference table, How would you query only the relevant tables (partitions) when "TableQuery" is no longer allowed to query reference tables?

Many thanks

解决方案

The "simple" way I see is split it to two steps
Step 1 - build list that will be used to filter table_id's

SELECT GROUP_CONCAT_UNQUOTED(
                   CONCAT('"',"MyTable_",STRING(Year*100+Month),'"')
       ) TBL_NAME_LIST 
FROM DWH.Dim_Periods P  
CROSS JOIN DWH.Campaigns AS LC  
WHERE ID IN ("86254e5a-b856-3b5a-85e1-0f5ab3ff20d6") 
AND DATE(P.Date) BETWEEN DATE(StartCampaignDate) AND DATE(EndCampaignDate)

Note the change in your query to transform result to list that you will use in step 2

Step 2 - final query

SELECT
  *
FROM
  TABLE_QUERY([MyProject:MyDataSet], 
              'table_id IN (<paste list (TBL_NAME_LIST) built in first query>)')

Above steps are easy to implement in any client you potentially using
If you use it from within BigQuery Web UI - this makes you do a little extra manual "moves" that you might not be happy about

My answer is obvious and you most likely have this already as an option, but wanted to mention

这篇关于错误：TABLE_QUERY表达式无法查询BigQuery表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！