google-bigquery - 在 google-bigquery 中，如何获取按大小排序的表列表？

Question

我需要按大小按数据集顺序列出所有 BQ 表，以找出占用更多空间的表。如何实现这一目标？

谢谢!

Answer 1

#standardSQL
SELECT table_id,
    DATE(TIMESTAMP_MILLIS(creation_time)) AS creation_date,
    DATE(TIMESTAMP_MILLIS(last_modified_time)) AS last_modified_date,
    row_count,
    size_bytes,
    CASE
        WHEN type = 1 THEN 'table'
        WHEN type = 2 THEN 'view'
        WHEN type = 3 THEN 'external'
        ELSE '?'
    END AS type,
    TIMESTAMP_MILLIS(creation_time) AS creation_time,
    TIMESTAMP_MILLIS(last_modified_time) AS last_modified_time,
    dataset_id,
    project_id
FROM `yourProject.yourDataset.__TABLES__`
ORDER BY size_bytes DESC  

以上查询为您提供的不仅仅是尺寸 - 享受 :o)