大查询是否对标准SQL执行__TABLES_SUMMARY__ [英] Does big query executes __TABLES_SUMMARY__ for standard SQL

问题描述

我们在Big Query中有一个数据集，该数据集包含500000多个表，当我们使用旧版SQL对该数据集运行查询时，会抛出错误

根据Jordan Tigani，它执行SELECT table_id FROM. TABLES_SUMMARY 以获取要查询的相关表

其次，使用 __ TABLES_SUMMARY __ .

  SELECT *来自`bigquery-public-data.noaa_gsod .__ TABLES_SUMMARY__` 

输出表的一部分，

如您所见，每种方法的输出都有一个特定的值.即使两者都检索有关特定数据集中表的元数据.

注意:BigQuery的查询具有配额.此配额适用于某些情况，包括单个查询可以引用的表数，即每个查询1000个表，How do I use the TABLE_QUERY() function in BigQuery?

Does queries using _TABLE_SUFFIX(standard SQL) executes TABLES_SUMMARY to get relevant tables to query?

解决方案

According to the documentation TABLE_SUFFIX is a pseudo column that contains the values matched by the table wildcard and it is olny available in StandardSQL. Meanwhile, __TABLE_SUMMARY_ is a meta-table that contains information about the tables within a dataset and it is available in Standard and Legacy SQL. Therefore, they have two different concepts.

However, in StandardSQL, you can use INFORMATION_SCHEMA.TABLES to retrieve information about the tables within the chosen dataset, similarly to __TABLE_SUMMARY_. Here you can find examples of usage and also its limitations.

Below, I queried against a public dataset using both methods:

First, using INFORMATION_SCHEMA.TABLES.

SELECT * FROM `bigquery-public-data.noaa_gsod.INFORMATION_SCHEMA.TABLES` 

And part of the output:

Secondly, using __TABLES_SUMMARY__.

SELECT * FROM `bigquery-public-data.noaa_gsod.__TABLES_SUMMARY__`

And part of the output table,

As you can see, for each method the output has a particular. Even though, both retrieve metadata about the tables within a particular dataset.

NOTE: BigQuery's queries have quotas. This quotas applies for some situations, including for the number of tables a single query can reference, which is 1000 per query, here.

这篇关于大查询是否对标准SQL执行__TABLES_SUMMARY__的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！