How to get BigQuery table metadata (number of records or latest update date or creation date) through BigQuery

Question

I want to write a BigQuery command line command that will retrieve recent modifications to a BigQuery table. How can i do this?

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

Answer 1

Here is 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 limit it to a specific table.

These are the returned columns:

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

The web interface says this request processes 0 bytes, so I think it is free to run.

Answer 2

:

bq show project_id:dataset_id.table_id

, . grep , .

  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