Data Engineering
Querying All Tables in a Dataset Using a FOR Loop in BigQuery
→ 日本語版を読むThere are times when I want to compile the record counts of multiple tables into a single table.
In such cases, I use a FOR loop to run count(*) and store the results in a temporary table for review.
DECLARE dataset STRING DEFAULT "test_dataset";
CREATE OR REPLACE TEMP TABLE record_counts (
table_name STRING
,record_count INT64
);
FOR table IN (SELECT table_name FROM `region-us.INFORMATION_SCHEMA.TABLES` AS name WHERE table_schema = dataset)
DO
EXECUTE IMMEDIATE FORMAT(
"""
INSERT INTO record_counts
SELECT '%s', count(*)
FROM `%s.%s`
"""
,table.table_name
,dataset
,table.table_name
);
END FOR;
SELECT * FROM record_counts;