bigquery table details

bigquery table details

#!/bin/bash
project_name="Project-ID"
echo -e "project_id,dataset_id,table_id,creation_time,last_modified_time,row_count,size_mb,size_gb,type,partiton,partition_expiration_days,cluster_key" > /tmp/bq_out.csv
for dataset in $(bq ls|tail -n +3); do
bq query --format=csv --use_legacy_sql=false '
SELECT
  t1.project_id as project_id,
  t1.dataset_id as dataset_id ,
  t1.table_id as table_id,
  TIMESTAMP_MILLIS(t1.creation_time) AS creation_time,
  TIMESTAMP_MILLIS(t1.last_modified_time) AS last_modified_time,
  t1.row_count as row_count,
  round(safe_divide(t1.size_bytes, (1000*1000)),1) as size_mb,
  round(safe_divide(t1.size_bytes, (1000*1000*1000)),2) as size_gb,
  case
        when t1.type = 1 then "table"
        when t1.type = 2 then "view"
        when t1.type = 3 then "external"
        else "?"
     END AS type,
  case 
    when t2.ddl like "%PARTITION BY%" then "Yes" 
    else "No" 
  end as partiton,
  REGEXP_EXTRACT(t2.ddl, r".*partition_expiration_days=([0-9-].*)") as partition_expiration_days,
  REGEXP_EXTRACT(t2.ddl, r"CLUSTER BY(.*)") as cluster_key,
FROM `'"${project_name}"'.'"${dataset}"'.__TABLES__` as t1,`'"${project_name}"'.'"${dataset}"'.INFORMATION_SCHEMA.TABLES` as t2
where t1.table_id=t2.table_name' | sed "1d" >> /tmp/bq_out.csv
done
  t1.row_count as row_count,
  round(safe_divide(t1.size_bytes, (1000*1000)),1) as size_mb,
  round(safe_divide(t1.size_bytes, (1000*1000*1000)),2) as size_gb,
  case
        when t1.type = 1 then "table"
        when t1.type = 2 then "view"
        when t1.type = 3 then "external"
        else "?"
     END AS type,
  case 
    when t2.ddl like "%PARTITION BY%" then "Yes" 
    else "No" 
  end as partiton,
  REGEXP_EXTRACT(t2.ddl, r".*partition_expiration_days=([0-9-].*)") as partition_expiration_days,
  REGEXP_EXTRACT(t2.ddl, r"CLUSTER BY(.*)") as cluster_key,
FROM `'"${project_name}"'.'"${dataset}"'.__TABLES__` as t1,`'"${project_name}"'.'"${dataset}"'.INFORMATION_SCHEMA.TABLES` as t2
where t1.table_id=t2.table_name' | sed "1d" >> /tmp/bq_out.csv
done