CODEWITHSUNDEEP
c#sqlgoogle-cloud-platformgoogle-bigquerygoogle-cloud-storage
Joe Scotto
Joe Scotto

Reputation: 10877

BigQuery check if table exists or not

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?

Upvotes: 12

Views: 30687

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173028

#standardSQL
SELECT COUNT(1) AS cnt
FROM `project.dataset.__TABLES_SUMMARY__`
WHERE table_id = 'mytable'

Upvotes: 7

Pentium10
Pentium10

Reputation: 207952

There are metatables called __TABLES__ and __TABLES_SUMMARY__

You can run a query like:

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

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__

Available Fields:

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

  • 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).

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: 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.

Upvotes: 17

Related Questions