BigQueryでテーブルのDDL(CreateTable文)をINFORMATION_SCHEMAから若干無理やり(？)作成する

ざっと調べたのですが、BQで掲題のように任意のテーブルからCreateTable文を取得する簡単な方法(show create table等)が見つからなかったのでINFORMATION_SCHEMAを利用して作ってみました。（バッドノウハウ感がありますが・・・ ）

※カラム数が数百とかあったりする場合便利かもしれません。
　もっとスマートな取得方法がありましたら、どなたか教えて下さい。

CREATE TABLE文を生成するクエリ(SQLを生成するSQL)

以下、DDL（CREATE TABLE文）を生成するクエリですが、生成されるDDLの形式としては**「CREATE TABLE XXX AS SELECT」構文**にしています。
前半のCREATE部が欲しい場合や、逆にSELECTだけが欲しいケースの場合、適宜withブロックの「ddl」のところを編集すれば都合の良い形式に編集できると思います。

with
target as (
  select
    -- 利用対象のテーブル名になるようここを編集してください
    'my-project' as project,
    'my-dataset' as dataset,  -- データセットだけは下記クエリの方にも複数箇所埋め込まれていることに注意(全部置換必要)
    'my-table' as table
),
table_info as (
  SELECT
    tables.table_catalog,
    tables.table_schema,
    tables.table_name,
    option.option_value,
  FROM
    my-dataset.INFORMATION_SCHEMA.TABLES tables
    cross join target
    left join my-dataset.INFORMATION_SCHEMA.TABLE_OPTIONS option
      on tables.table_catalog = option.table_catalog
      and tables.table_schema = option.table_schema
      and tables.table_name = option.table_name
      and option_name = 'description'
  where
    tables.table_catalog = target.project
    and tables.table_schema = target.dataset
    and tables.table_name = target.table
),
column_info as (
  SELECT
    column.ordinal_position,
    column.column_name,
    column.data_type,
    path.description,
  FROM
    my-dataset.INFORMATION_SCHEMA.COLUMNS column
      cross join target
      left join my-dataset.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS path
        on column.table_name = path.table_name
        and column.column_name = path.column_name
  WHERE
    column.table_catalog = target.project
    and column.table_schema = target.dataset
    and column.table_name = target.table
),
ddl as (
  -- はじめの「create table xxx (」までを作る部分
  select
    0 as ordinal_position,
    'create table ' || table_info.table_catalog || '.' || table_info.table_schema || '.' || table_info.table_name ||  ' (' as text,
  from
    table_info
  union all
  -- 「カラム名　データ型　カラム説明」のリストを作る部分
  SELECT
    column_info.ordinal_position,
    '  ' || column_info.column_name || ' ' || column_info.data_type || ' options(description="' || ifnull(column_info.description, '') || '"),' as text,
  FROM
    column_info
  union all
  -- CREATE TABLE文の最後、テーブルの説明などのオプションを指定する部分(パーティションテーブルも対応したいなどはこの辺をいじればOK)
  select
    1000000 as ordinal_position,
    ') options(description = "' || ifnull(trim(table_info.option_value, '"'), '') || '") as select' as text,
  from
    table_info
  union all
  -- SELECTのカラムリスト
  SELECT
    1000000 + column_info.ordinal_position as ordinal_position,
    '  ' || column_info.column_name || ','  as text,
  FROM
    column_info
  union all
  -- SELECTのFrom部
  select
    10000000 as ordinal_position,
    'from ' || table_info.table_catalog || '.' || table_info.table_schema || '.' || table_info.table_name as text,
  from
    table_info
)
select ddl.text from ddl order by ordinal_position

使い方

• 以下3つのキーワードを自分の環境に合わせて修正する
  • my-project
  • my-dataset (※他は修正1箇所ですが、datasetだけ修正が複数箇所に必要であることにご注意を)
  • my-table

注意点

• 以下の情報しか取得していませんので、必要に応じてもう少し編集必要です。(例えばパーティション情報などは対象外)
  • テーブル名物理
  • カラム名物理
  • テーブルDESCRIPTION
  • カラムDESCRIPTION

結果の紹介

対象のテーブル

create table my-dataset.test_table_01 (
  col1 NUMERIC options(description="カラム1つ目"),
  col2 STRING options(description="カラム2つ目"),
  col3 DATETIME options(description="カラム3つ目"),
) options(description = "テストのテーブル")

SQL実行結果(SQLを生成するクエリ実行結果)

プロジェクトIDは隠しています。データセットはtest_terui_01です。

DDL利用方法

BQのWEBコンソールに出力されたものを複数行に渡るDDL形式にするために、ちょっとめんどくさい加工が必要になりました。
→Pythonとかで軽いツール作りたくなりますが、面倒なのでそこまではやってません。

DDLのSQLテキストを取得する手順

• 「SAVE RESULTS」で「Google Sheets」を選択して結果を取得する
• スプレッドシートに書かれたA列をコピーする

なぜわざわざ「Google Sheets」としたか

• 「Copy to Clipboard」や「CSV」の場合、クエリ中にあるダブルコートやカンマなどが影響していて、エスケープ用のダブルコートが付加されるため、エスケープをクリアする処理が必要になる
• JSONの場合も、クエリにそのまま転用するには文字列加工が必要

おしまい。