BigQuery 使い方(7) 〜レコードの更新、新規登録、削除、マージ

BigQueryは多種多様なビッグデータの格納やインポート処理、分析が可能なGoogleのデータウェアハウスサービスです。

本ページでは、SQLのデータ操作言語(DML : Data Manipulation Language)を利用して、BigQueryのテーブルに対してレコードを更新(UPDATE)、新規登録(INSERT)、削除(DELETE)、マージ(MERGE)する方法を紹介します。

スポンサーリンク

更新(UPDATE)

レコードを更新するUPDATEの構文は、以下のように記述します。

UPDATE table_name
SET set_clause
WHERE condition

テーブルtable_nameの条件conditionを満たす行に対して、set_clauseで列の値を変更します。

以下のような店舗における注文のテーブルの一部を更新する場合を考えます。
▼注文テーブル(daily_sales)

例えば、商品タイプが「Office Supplies」の注文件数を100倍に変更するクエリを作成します。

実行すると該当レコードが更新されていることが分かります。

テーブルの一部を、別のテーブルを参照して置き換えることもできます。
例えば、上記注文テーブル(daily_sales)を、以下のような最新の注文レコードが含まれるテーブルnew_orderを参照して更新する場合を考えます。
▼新規注文テーブル(new_order)

2つのテーブルの注文IDが一致する場合に、注文テーブルの注文数を新規注文テーブルの注文数に更新するクエリを作成します。

実行すると該当するレコードが更新され、それ以外のレコードは変更ないことが分かります。

新規登録(INSERT)

レコードを新たに追加登録するINSERTの構文は、以下のように記述します。

INSERT table_name [(column_1, …, column_n)]
VALUES (value1_c1, …, value1_cn),
(value2_c1, …, value2_cn),

テーブルtable_nameに、指定した列column_1, …, column_nの順にVALUES以下で指定したレコードを挿入します。

例えば、以下のような担当者テーブル(operator)に新規に3件のレコードを追加する場合を考えます。
▼担当者テーブル(operator)

BigQueryのクエリは以下のようになります。

実行後再度テーブルを見てみると、指定のレコードが新規登録されていることが分かります。

別のテーブルから抽出したレコードや、WITH句を利用して抽出したレコードを新規登録することもできます。
例えば注文テーブルから担当者に関する列を抽出して新規登録する場合には、以下のようなクエリになります。

削除(DELETE)

レコードを削除するDELETEの構文は、以下のように記述します。

DELETE table_name
WHERE condition

テーブルtable_nameから、条件conditionに該当するレコードを削除します。

例えば、以下のような担当者テーブルから、operator が ‘Susan’ のレコードを削除する場合を考えます。
▼担当者テーブル(operator)

クエリは以下のようになります。

実行後再度テーブルを見てみると、指定のレコードが削除されていることが分かります。

コメント

テーブルからすべてのレコードを削除するには、条件部分を
WHERE true
のようにします。

マージ(MERGE)

マージ(MERGE)は、レコードの更新(UPDATE)や新規登録(INSERT)、削除(DELETE)を利用してテーブルを結合(JOIN)することができます。毎日の売上を追加更新したり、商品コードを管理したりするのに便利な非常に強力な操作コマンドです。

構文は以下のように記述します。

MERGE target_table_name
USING source_table_name
ON condition
WHEN MATCHED [AND search_condition] THEN statement
WHEN NOT MATCHED [AND search_condition] THEN statement
WHEN NOT MATCHED BY SOURCE [AND search_condition] THEN statement

テーブルtarget_table_namesource_table_nameに対して、条件conditionに従い、WHEN句の操作を実施します。
WHEN句にはWHEN MATCHEDかWHEN NOT MATCHED、WHEN NOT MATCHED BY SOURCEのいずれか少なくとも1つが必要になります。

  • WHEN MATCHEDには、target_table_namesource_table_name条件が一致する場合に、target_table_nameのレコードを更新(UPDATE)または削除(DELETE)する処理を定義します。
  • WHEN NOT MATCHEDには、target_table_namesource_table_name条件が一致しない場合に、target_table_nameに新規登録(INSERT)する処理を定義します。
  • WHEN NOT MATCHED BY SOURCEには、target_table_namesource_table_name条件が一致しない場合に、target_table_nameのレコードを更新(UPDATE)または削除(DELETE)する処理を定義します。

以下のような注文テーブルと最新の注文テーブルをマージする場合に、実際によくある例を考えます。
▼注文テーブル(daily_sales)

▼新規注文テーブル(new_order)

【例1】
両テーブルの注文IDが一致する場合には注文数を合算して、一致しない場合にはnew_orderのレコードを新規に登録するようにしてみます。つまり、既存のデータであれば一部の列の値を更新(UPDATE)して、新規のデータであればレコードを新規登録(INSERT)することになります。

BigQueryのクエリは以下のようになります。

実行すると、該当箇所が更新および追加されていることが分かります。

【例2】
次は両テーブルの注文IDが一致する場合には注文数を合算して、一致しない場合にはdaily_salesの注文数が少ないレコードを削除するようにしてみます。つまり、ターゲットテーブルで結合条件が一致するレコードは列の値を更新(UPDATE)して、一致しない一部のレコードは削除(DELETE)します。

BigQueryのクエリは以下のようになります。

実行すると、該当箇所が更新され、かつ注文数が100以下のレコード1件が削除されていることが分かります。

【例3】
今度は両テーブルの注文IDが一致する場合にはdaily_salesのレコードを削除、一致しない場合にはdaily_salesのレコードの日付を新しくして、new_orderのレコードを新規に登録するようにします。
つまり、既存のデータで条件に一致すれば削除(DELETE)して、条件に一致しなければ列の値を更新(UPDATE)し、新規のデータであればレコードを新規登録(INSERT)します。

BigQueryのクエリは以下のようになります。

実行すると、削除・新規登録・更新がさていることが分かります。

本ページでは、BigQueryのテーブルのレコードを更新(UPDATE)、新規登録(INSERT)、削除(DELETE)、マージ(MERGE)する方法を紹介しました。

タイトルとURLをコピーしました