BigQueryのテーブル作成実践(データ定義言語(DDL))

2020.03.17

Google BigQueryでは、テーブル作成を行う術が複数提供されています。当エントリではその中から「データ定義言語(DDL)」による作成方法について、実践を交えて見ていきたいと思います。

目次

 

構文

DDLによるテーブル作成(CREATE TABLE)の構文は以下の通り。

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
table_name
[(
column_name column_schema[, ...]
)]
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]

 

要点

上記ドキュメントに記載の内容について、ザッと目を通した上で気になるところをまとめてみました。


テーブル作成の際に取れる"挙動"としては何パターンか存在。

  • 新規で作成(CREATE TABLE)
  • 既存テーブルから新しいテーブルを作成(CREATE TABLE ... AS SELECT)
  • テーブルが存在していなければ作成(CREATE TABLE IF NOT EXISTS)
  • テーブルが存在していればそれを上書きして作成(CREATE OR REPLACE TABLE)

データ型について:詳細は下記公式ドキュメントを参照。

BigQueryで利用出来るテーブルカラムの「データ型」は以下の通り。

  • STRING(文字列型)
  • BYTES(可変長文字バイナリデータ)
  • INTEGER(整数型)
  • FLOAT(浮動小数点型)
  • NUMERIC(10 進 38 桁の精度と 10 進 9 桁の尺度の正確な数値)
  • BOOLEAN(ブール型)
  • TIMESTAMP(タイムスタンプ型 ※マイクロ秒の精度で、絶対的な時刻)
  • DATE(日付型)
  • TIME(時刻型 ※特定の日付とは関係無し)
  • DATETIME(日時型 ※年、月、日、時、分、秒、およびサブ秒)
  • GEOGRAPHY(地理型)
  • RECORD(構造体型/STRUCT)

カラムの属性としてNULLABLE, REQUIRED(NOT NULL), REPEATEDが指定出来る。

BigQuery では、列に対して次のモードがサポートされます。
モードは省略可能です。モードが指定されていない場合、
列はデフォルトの NULLABLE に設定されます。

NULLABLE: 列で NULL 値が許可されます(デフォルト)。
REQUIRED(必須): NULL 値は許可されません。
REPEATED(反復): 列に指定された型の値の配列が含まれます

モード - スキーマの指定  |  BigQuery  |  Google Cloud

カラムのコメントはcolumn_schema内でdescriptionを使う事によって指定可能(テーブルのコメントは後述の table_option_listで指定可能)

文字列型に桁数制限は無い(!)、その代わり行の最大サイズに制限がある(100MB)のでこれを超えるとアウト。

行の最大サイズ - 100 MB
行のサイズは行データの内部表現に基づくことから、その最大サイズに対する上限は概算値です。
行の最大サイズに対する上限は、クエリジョブ実行の特定の段階で適用されます。

割り当てと上限  |  BigQuery  |  Google Cloud

パーティションという概念がある(PARTITION BYで指定)。指定無し/取り込み時間で指定/条件を満たすデータ型のカラムで指定、が選べる

クラスターという概念がある(CLUSTER BYで指定)

オプションリスト(table_option_list)を使用することで、ラベルや有効期限などのテーブル オプションを設定する事が可能となっている

  • expiration_timestamp(TIMESTAMP):テーブルの有効期限
  • partition_expiration_days(FLOAT64):パーティションの有効期限
  • require_partition_filter(BOOL):パーティションフィルタの設定有無
  • kms_key_name(STRING):暗号化に用いるKMS名称
  • friendly_name(STRING):テーブル名コメント?
  • description(STRING):テーブル名コメント
  • labels(ARRAY>)):ラベル情報

 

CREATE TABLE実践

 

コンソール経由で試す

まずは一番手っ取り早いコンソール経由から試してみます。任意のデータセットから「テーブルを作成」を選択。

テーブルの作成元「空のテーブル」を指定、テーブル名も任意の内容を入力。

必要な個数分、「フィールドを追加」を押下してカラムの設定行を追加していきます。ここで指定可能な内容は「名前」「型」「モード」の3種のみ。

パーティションとクラスタの設定も以下のように個別指定可能です。「テーブルを作成」を押下。

テーブルが作成出来ました。「スキーマを編集」ボタンでカラム定義の編集が行えます。

この編集画面では、モードの一部編集、及び説明の編集が可能となっています。

編集後の画面。

また、「詳細」タブについては以下のような表示内容となっています。「説明」と「ラベル」が付与可能なのはデータセットと同様です。

テーブルの属性として変更可能なのは「有効期限」のみとなっています。

テーブルのスキーマ編集については、プルダウンを用いた上記の手順とは異なり、テキストとして編集した内容を定義として用いる事も可能です。(※この場合、NULLABLE等の指定ってどうやるんだろう?)

 

CLI経由(bq query)で試す

上記で作成した内容を、同じ様な定義でCLI経由(SQLとしてのCREATE TABLE文を使用)して実行してみたものが以下となります。bq queryコマンドを使ってCREATE TABLE文を実行した形です。基本的には設定に倣う形でDDLは用意出来たのですが、一部REPEATEDを指定する方法が分からずでした(この辺そもそも使い勝手とかあまり把握していないのもあるので、別途エントリを改める形で深堀りしてみたいと思います)

$ bq query --nouse_legacy_sql '
CREATE TABLE cmbqdataset.bqtable_from_cli (

  /** 列毎の定義 */
  user_id INT64 NOT NULL OPTIONS(description="ユーザーID、一意のキー"),
  user_name STRING NOT NULL OPTIONS(description="ユーザー名"),
  item_bytes BYTES,
  value_float FLOAT64,
  value_numeric NUMERIC,
  value_boolean BOOLEAN,
  birthday DATE NOT NULL OPTIONS(description="誕生日"),
  create_timestamp TIMESTAMP,
  create_datetime DATETIME,
  create_time TIME,
  item_geography GEOGRAPHY,
  item_record STRUCT <
    key STRING
  >
)
/** パーティション指定 */
PARTITION BY DATE(create_timestamp)
/** クラスタ指定 */
CLUSTER BY user_id
/** その他オプション指定 */
OPTIONS(
  expiration_timestamp=TIMESTAMP "2020-12-31 23:59:59 UTC",
  partition_expiration_days=7,
  description="a table that expires at 2021, with each partition living for 7 days",
  labels=[("production_mode", "false")]
)
'

Waiting on bqjob_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx ... (0s) Current status: DONE   
Created cm-xxxxxxxxxxxxx.cmbqdataset.bqtable_from_cli

クエリ実行後のコンソールで確認した内容がこちら(カラム定義)。

テーブルに関する属性周りの設定。

 

まとめ

という訳で、BigQueryのテーブル作成をDDL経由で実践してみた内容のまとめでした。

基本的には従来のデータベースで実践するような構文と然程変わらないイメージでしたが、やはり細かいところで"BigQueryならでは"の部分が顔を出して来ていました。この辺、他のサービスとの『違い』を整理しつつ、使い分けて行きたいと思います。