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_nameとsource_table_nameに対して、条件conditionに従い、WHEN句の操作を実施します。
WHEN句にはWHEN MATCHEDかWHEN NOT MATCHED、WHEN NOT MATCHED BY SOURCEのいずれか少なくとも1つが必要になります。
- WHEN MATCHEDには、target_table_nameとsource_table_nameで条件が一致する場合に、target_table_nameのレコードを更新(UPDATE)または削除(DELETE)する処理を定義します。
- WHEN NOT MATCHEDには、target_table_nameとsource_table_nameで条件が一致しない場合に、target_table_nameに新規登録(INSERT)する処理を定義します。
- WHEN NOT MATCHED BY SOURCEには、target_table_nameとsource_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)する方法を紹介しました。
