サクッとJSON形式のschema情報(bigquery用)を抽出するクエリ

SELECT  
  '[' || ARRAY_JOIN(
            ARRAY_AGG('{"name":"' || column_name || '", "type":"' || data_type || '", "mode": "' || is_nullable || '"}') 
            , ','
            ) 
  || ']'
FROM  
  (
  SELECT  
    column_name
    , CASE WHEN data_type = 'varchar' 
              THEN 'string' 
          WHEN data_type = 'double'
              THEN 'float'
          WHEN data_type = 'bigint'
              THEN 'integer'
              ELSE data_type 
      END AS data_type
    , CASE WHEN is_nullable = 'YES' 
              THEN 'nullable' 
              ELSE 'required' 
      END AS is_nullable
  FROM  
    information_schema.columns 
  WHERE 
    -- 以下にDB名とテーブル名を入れてクエリ実行すればJSON形式のレイアウトを取得可能
    table_schema || '.' || table_name = 'DB.table'
  )