BigQuery解説 ~テーブルの複製方法あれこれ~

次世代システム研究室の Y.I です。 Google Cloud の BigQuery についてまとめます。今回は BigQuery のテーブルの複製を作る方法をご紹介します。 BigQuery は高頻度で新しい機能がリリースされるため、ユースケースにあった上手な方法でテーブルコピーを作りましょう。

おすすめ

ユースケースにあったおすすめのテーブル複製方法を先にまとめます。

　　=> CREATE TABLE AS SELECT (CTAS)

　　=> snapshot テーブル

　　=> Materialized view

はじめに

複製元のベーステーブルとして以下のテーブルをさまざまな方法で複製していきます。

-- テーブル作成
CREATE TABLE IF NOT EXISTS `yi_dataset02.base_table`
(
  name STRING NOT NULL
  ,quantity INT64 NOT NULL OPTIONS(description="NOT NULL FIELD")
  ,f_value FLOAT64 OPTIONS(description="NULLABLE FIELD")
  ,n_value NUMERIC(10,5)
  ,st_value STRUCT<
    st01 STRING
    ,st02 INT64
    ,st03 NUMERIC(10,5)
  >
)

-- レコード登録
INSERT INTO `yi_dataset02.base_table` (name, st_value)
VALUES
  ("record01", ("struct01", 1, 99999.99999))
  ,("record02", ("struct02", 2, 99999.99999))
  ,("record03", ("struct03", 3, 99999.99999))

CREATE TABLE AS SELECT （CTAS）

SELECT 結果を元にテーブルを複製することができます。おすすめのユースケースは、必要なカラムやレコードのみに絞った形でテーブルを複製することです。物理コピーなのでデータ保存料金が別途かかりますが、集計などで使う情報を絞った中間テーブルを作成する際に向いています。 SELECT 結果が0件ならばテーブル定義のみ複製されます。

CREATE TABLE yi_dataset02.base_cras 
AS SELECT name, quantity, st_value FROM yi_dataset02.base_table WHERE name = 'struct01'
;

SELECT name, quantity, st_value FROM yi_dataset02.base_ctas
+----------+----------+-----------------------------------------------------+
|   name   | quantity |                      st_value                       |
+----------+----------+-----------------------------------------------------+
| record01 |        1 | {"st01":"struct01","st02":"1","st03":"99999.99999"} |
+----------+----------+-----------------------------------------------------+

CREATE TABLE LIKE

ベーステーブルを元にテーブル及びレコードを複製することができます。テーブル定義のみの複製やレコードも含めて複製可能です。おすすめのユースケースは、テーブル定義を複製することです。 CTAS の違いはかテーブル定義を変更して複製できるかどうかです。LIKE は完全に一致したテーブルのみ複製可能です。

-- メタ情報のみ（レコードなし）
CREATE TABLE `yi_dataset02.base_like` LIKE `yi_dataset02.base_table`;

-- メタ/レコードコピー
CREATE TABLE yi_dataset02.base_like02 LIKE yi_dataset02.base_table 
AS SELECT * FROM `yi_dataset02.base_table`


-- メタ情報のみ（レコードなし）
SELECT * FROM yi_dataset02.base_like;
（　レコードなし）

-- 確認(レコードあり)
SELECT * FROM yi_dataset02.base_like02;
+----------+----------+---------+-------------+-----------------------------------------------------+
|   name   | quantity | f_value |   n_value   |                      st_value                       |
+----------+----------+---------+-------------+-----------------------------------------------------+
| record01 |        1 |     0.1 | 99999.99999 | {"st01":"struct01","st02":"1","st03":"99999.99999"} |
| record03 |        3 |     0.1 | 99999.99999 | {"st01":"struct03","st02":"3","st03":"99999.99999"} |
| record02 |        2 |     0.1 | 99999.99999 | {"st01":"struct02","st02":"2","st03":"99999.99999"} |
+----------+----------+---------+-------------+-----------------------------------------------------+

clone テーブル

ベーステーブルを元にテーブル及びレコードを複製することができます。おすすめのユースケースは、テーブルを複製してレコードを変更するユースです。cloneテーブルは別途データ保存コストがかかりません。cloneテーブルの変更分やベーステーブルを削除しない限り別途コストが不要なのでユースケースが合えば利用を検討すべきテーブルです。

-- 作成
CREATE TABLE `yi_dataset02.base_clone`
CLONE `yi_dataset02.base_table`;

snapshot テーブル

ベーステーブルを元にテーブル及びレコードを複製することができます。snapshot テーブルはレコードの変更ができません。おすすめのユースケースは、テーブルをバックアップするユースです。snapshot テーブルは別途データ保存コストがかかりません。cloneテーブルとの違いは、read only であることです。

CREATE SNAPSHOT TABLE `yi_dataset02.base_snapshot01`
CLONE `yi_dataset02.base_table`
  OPTIONS (
    expiration_timestamp = TIMESTAMP'2023-12-31 23:59:59' );

-- タイムテーブルから snapshot
CREATE SNAPSHOT TABLE `yi_dataset02.base_snapshot02`
CLONE `yi_dataset02.base_table`
FOR SYSTEM_TIME AS OF
  TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 HOUR);

-- snapshot 一覧
SELECT  *
FROM  yi_dataset02.INFORMATION_SCHEMA.TABLE_SNAPSHOTS
WHERE  base_table_name = 'base_table';

materialized view

マテリアライズドビューの利用も可能です。おすすめユースケースは、自動で集計、整形するなどの ETL 処理です。ベーステーブルの変更を自動で取り込んでくれるので、更新や新規登録されるベーステーブルの中間テーブルに向いています。

-- 作成
CREATE MATERIALIZED VIEW `yi_dataset02.base_mateview` AS 
SELECT name, sum(quantity) as amount from `yi_dataset02.base_table` GROUP BY name;

temp table

同一トランザクションで利用できる temp テーブルを作り参照します。 作成と参照を別トランザクションで実行することはできず、作成と参照を同一トランザクションで行うと利用できます。

-- 
CREATE OR REPLACE TEMP TABLE base_temp AS SELECT * FROM `yi_dataset02.base_table`;
SELECT * FROM base_temp LIMIT 2;
--

SELECT * FROM PROJECT_ID._script9e3e393a505a25fa3acc137cd87ac2d11d77d15d.base_temp LIMIT 2;
END

bq cp コマンド

terminal で利用できる bq cp コマンドにより物理テーブル、clone テーブルや snapshot テーブルを作ることができます。

bq cp table

baseテーブルと同じ通常テーブルの作成

bq cp PROJECT_ID:yi_dataset02.base_table PROJECT_ID:yi_dataset02.base_cp11

bq cp clone

clone テーブルの作成

bq cp --clone=true -n PROJECT_ID:yi_dataset02.base_table PROJECT_ID:yi_dataset02.base_cp12

bq cp snapshot

snapshot テーブルの作成

bq cp --snapshot=true -n PROJECT_ID:yi_dataset02.base_table PROJECT_ID:yi_dataset02.base_cp13

最後に

グループ研究開発本部では、グループ全体のインテグレーションを支援してくれるアーキテクトを募集しています。アプリケーション開発の方、次世代システム研究室にご興味を持って頂ける方がいらっしゃいましたら、ぜひ募集職種一覧からご応募をお願いします。