BigQuery检查表格是否存在 [英] BigQuery check if table exists or not

问题描述

我正在使用C#根据来自bigquery api表终结点的响应动态构建查询.我正在尝试计算有效的活动用户，但是仅当我选择每个具有.*的表时.我的问题确实很简单，是否可以检查BigQuery SQL中是否存在表?

I'm using C# to dynamically construct a query based on a response from the bigquery api tables endpoint. I'm trying to calculate active users which works, but only if I select every table with .*. My question is quite simple really, is there a way to check if a table exists within BigQuery SQL?

推荐答案

有称为__TABLES__和__TABLES_SUMMARY__

您可以运行以下查询:

SELECT size_bytes FROM <dataset>.__TABLES__ WHERE table_id='mytablename'

该查询的__TABLES__部分可能看起来并不熟悉. __TABLES_SUMMARY__是一个元表，其中包含有关数据集中表的信息.您可以自己使用此元表.例如，查询SELECT * FROM publicdata:samples.__TABLES_SUMMARY__将返回有关publicdata:samples数据集中表的元数据.您也可以执行SELECT * FROM publicdata:samples.__TABLES__

The __TABLES__ portion of that query may look unfamiliar. __TABLES_SUMMARY__ is a meta-table containing information about tables in a dataset. You can use this meta-table yourself. For example, the query SELECT * FROM publicdata:samples.__TABLES_SUMMARY__ will return metadata about the tables in the publicdata:samples dataset. You can also do SELECT * FROM publicdata:samples.__TABLES__

可用字段:

__TABLES_SUMMARY__元表的字段(在TABLE_QUERY查询中都可用)包括:

The fields of the __TABLES_SUMMARY__ meta-table (that are all available in the TABLE_QUERY query) include:

• table_id:表的名称.
• creation_time:创建表的时间，自1970年1月1日UTC以来的毫秒数.这与表上的creation_time字段相同.
• type:是视图(2)还是常规表(1).

• table_id: name of the table.
• creation_time: time, in milliseconds since 1/1/1970 UTC, that the table was created. This is the same as the creation_time field on the table.
• type: whether it is a view (2) or regular table (1).

以下字段在TABLE_QUERY()中不可用 ，因为它们是__TABLES__而不是__TABLES_SUMMARY__的成员.出于历史考虑，将它们保留在此处，并部分记录__TABLES__元表:

The following fields are not available in TABLE_QUERY() since they are members of __TABLES__ but not __TABLES_SUMMARY__. They're kept here for historical interest and to partially document the __TABLES__ metatable:

• last_modified_time:自1970年1月1日UTC开始以毫秒为单位的表更新时间(元数据或表内容).请注意，如果您使用tabledata.insertAll()将记录流式传输到表中，则可能已过时了几分钟.
• row_count:表中的行数.
• size_bytes:表的总大小(以字节为单位).

• last_modified_time: time, in milliseconds since 1/1/1970 UTC, that the table was updated (either metadata or table contents). Note that if you use the tabledata.insertAll() to stream records to your table, this might be a few minutes out of date.
• row_count: number of rows in the table.
• size_bytes: total size in bytes of the table.

这篇关于BigQuery检查表格是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！