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

既存の他DBのスキーム情報からサクッとJSON形式のレイアウト情報を抽出するクエリです。

用途

bigqueryへのテーブル新規登録時のJson形式レイアウトをテーブルから取得したい時などに利用

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'
  )
  

bigquery触りたての頃は、既存のDB（mysqlとか）からデータ持ってくのに、Excelとかスプレッドシートをうまく使ってjsonレイアウト作ってたのですが、このクエリのおかげでかなり工数削減できるようになりました。
PRESTOで作ってあるので、ほかのDBでは試してないのですが、少し直せばいけると思われます。