google-bigquery - bigquery 数据集中所有表的行数

Question

我有一个包含许多表的数据集。有没有一种简单的方法来查询显示 table_name 和该表中的行数的输出，而不必对数据集中的每个表执行 count(*) ？

Answer 1

是的，您可以做到这一点，查询元数据:

SELECT
  dataset_id,
  table_id,
  # Convert size in bytes to GB
  ROUND(size_bytes/POW(10,9),2) AS size_gb,
  # Convert creation_time and last_modified_time from UNIX EPOCH format to a timestamp
  TIMESTAMP_MILLIS(creation_time) AS creation_time,
  TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
  row_count,
  # Convert table type from numerical value to description
  CASE
    WHEN type = 1 THEN 'table'
    WHEN type = 2 THEN 'view'
  ELSE
  NULL
END
  AS type
FROM
  project_id.dataset_id.__TABLES__
ORDER BY
  size_gb DESC;