How can I get a BigQuery table's meta data (record counts or last update date or creation date) via BigQuery

Question

I want to write a BigQuery command line command that will retrieve the last modification time of a BigQuery table. How can I do that?

I will use the BigQuery table only if its last modified datetime is greater than some datetime.

Answer 1

Here's a simple query that shows metadata about all the tables in your dataset:

SELECT * FROM <dataset>.__TABLES__;

You can add

WHERE table_id='<table_name>'

if you want to restrict it to a certain table.

These are the columns returned:

project_id, dataset_id, table_id, creation_time, last_modified_time, row_count, size_bytes, type

The web UI says this query processes 0 bytes, so I think it's free to run.

Answer 2

Use the following command line to show a table's metadata :

bq show project_id:dataset_id.table_id

You will get a result like below. Use grep to obtain the date if you want to automate the process.

  Last modified           Schema           Total Rows   Total Bytes   Expiration
 ----------------- ----------------------- ------------ ------------- ------------
  24 Apr 16:29:28   |- state: string        5365794      165658304
                    |- gender: string
                    |- year: integer
                    |- name: string
                    |- occurence: integer