_TABLE_SUFFIX

  • pseudo column
  • in “standard SQL” dialect only
  • can be used only in WHERE clause for limiting partitions used in query with wildcard table
  • Example:
    • SELECT count(*) FROM dataset.tablemask* WHERE _TABLE_SUFFIX ……

 

  • Usage in WHERE:
    • _TABLE_SUFFIX = ‘…..’
    • _TABLE_SUFFIX LIKE ‘2017%’
    • _TABLE_SUFFIX BETWEEN ‘20170101’ AND ‘20170102’
    • _TABLE_SUFFIX IN ( subquery )

 

__TABLES_SUMMARY__

  • meta-table – shows basic info about tables in dataset
  • available in both “legacy SQL” and “standard SQL” dialects
  • usage:
    • SELECT * FROM `dataset.__TABLES_SUMMARY__`
  • columns:
    • project_id
    • dataset_id
    • table_id
    • creation_time (unix time)
    • type (1=regular table, 2=view)

 

__TABLES__

  • Meta-table – shows more informations about tables in dataset
  • available both in “legacy SQL” and “standard SQL” dialects
  • Usage:
    • SELECT * FROM `dataset.__TABLES__`
  • columns:
    • project_id
    • dataset_id
    • table_id
    • creation_time (unix time)
    • last_modified_time (unix time)
    • row_count
    • size_bytes
    • type (1=regular table, 2=view)
  • compare tables in 2 different datasets (standard SQL only due to union):
    select * from `dataset1.__TABLES__`
    union all
    select * from `dataset2.__TABLES__`
    order by table_id, dataset_id