本記事では、BigQueryでよく使う操作である、テーブルの作成・削除、ビューの作成・削除の操作方法を中心に解説します。
熟練すれば複雑なクエリを書いて一発でデータを抽出することができますが、初心者の内は、単純なクエリを複数書いてテーブルやビューを活用することで回避できます。
複雑なクエリを書くことが無くても、よく使う操作ではあるので、ぜひ覚えておきましょう。
データセットの作成
まずはBigQueryにアクセスしましょう。
アクセス権限を有していればアクセスできます。
該当プロジェクト配下にあるデータセットを確認するためには、プロジェクト名の隣にある三角形をクリックします。
今はデータセットが無いため、「外部接続」という項目だけが表示されている状態です。
データセットを作るには、プロジェクト名の右側にある点々をクリックしましょう。
「データセットを作成」という項目が出てくるので、それをクリックします。
どんなデータセットを作るかを指定する画面が出てきます。
「データセットID」は作成するデータセットの名前です。
分かりやすい名前にしましょう。
ロケーションやリージョンを指定できますが、職場で決まっている値があればそれを適用しましょう。
ちなみに「リージョン」とは、どの地域のデータセンターを利用するかを意味していて、「US」はアメリカのことです。
クエリを実行したりするには同じリージョンで操作することになるため、職場で共通のルールに従えば問題無いのです。
「test_dataset」という名前でデータセットを作成しました。
データセットをクリックすると、データセット情報を見ることができます。
データセットの削除
いらなくなったデータセットを削除する方法も確認しておきましょう。
データセットの右側の点々をクリックするとメニューが出てきて、その中から「削除」を選択できます。
もう一つ、「削除」を選択する方法を紹介します。
まずは削除したいデータセットをクリックし、データセット情報を確認しましょう。
右上に「削除」というボタンが表示されるので、これをクリックすると進めます。
すると、「データセットを削除しますか？」と表示され、確認用の入力フォームと「削除」ボタンが表示されるので、メッセージ内容含め、よく確認しましょう。
本当に削除するのであれば、メッセージに従い、入力フォームに「削除」と入力します。
その上で、「削除」ボタンをクリックします。
ちなみに、BigQueryでは過去何度かこの画面のデザインが変わっており、以前は「delete」と入力するなどの仕様でした。
もしかしたら今後も変更があるかもしれません。
削除すると、プロジェクトの下に表示されていたデータセットが無くなりました。
テーブルの作成
データセットが操作できるようになったら、次はテーブルを作成できるようになりましょう。
該当データセット配下に格納されているテーブルやビューを確認するには、データセット名の左側の三角形をクリックします。
データセットを作成したばかりなので、テーブルやビューが無く、三角形が消えただけに見えます。
テーブルの作り方ですが、先に、初心者の内はあまり使わない方法を紹介します。
データセットの右側の点々をクリックします。
「テーブルを作成」を選択します。
どんなテーブルを作成するかを指定する画面が出てきます。
しかしこの画面は、初心者の内に使うことは少ないでしょうから、紹介は一旦ここまでとします。
代わりに、初心者の内からよく使う方法を紹介します。
クエリを実行した結果を保存します。
まずはクエリを用意します。
エディタが表示されていなければ表示させましょう。
試しに簡単なクエリを入力してみます。
SELECT 1 AS test_value「実行」ボタン、あるいは、Ctrl＋Enterで、クエリを実行しましょう。
クエリの実行結果が表示されます。
「結果を保存」という項目があるので、それをクリックしましょう。
「BigQueryテーブル」という項目があるので、これを選択します。
テーブルとして保存するための情報を入力する画面が出てきます。
必要情報を入力したら「エクスポート」ボタンを押しましょう。
ちなみに、データセットを選択する際には、新しくデータセットを作ってそこに保存するような操作もできます。
実際はあまり使わないでしょう。
テーブルが作成されました。
テーブル名をクリックしてみましょう。
テーブルのスキーマが表示されます。
このテーブルにはどんなカラムがあるのか分かります。
「詳細」のタブをクリックすると、テーブルの詳細情報を見ることもできます。
テーブルの削除
テーブルを作成した後、使い終わったら削除することになります。
BigQueryではテーブルの容量次第で、保存し続けることでお金がかかりますし、いらないテーブルが沢山あると管理が難しくなります。
テーブルの詳細画面に「削除」のボタンがあるので、そこをクリックしましょう。
または、削除したいテーブルの右側に点々があるので、そこをクリックすると「削除」を選択できます。
どちらから選択してもOKです。
すると、「テーブルを削除しますか？」という確認画面が出てきます。
まずは、メッセージをよく読み、間違って別のテーブルを削除しようとしていないか確かめます。
特に、テーブルを削除しようとしてデータセットを削除してしまい、沢山の大事なテーブルやビューを喪失してしまうミスが起こりやすいので注意しましょう。
確認が済んだら、画面表示に従い、「削除」と入力し、「削除」ボタンを押下します。
削除が行われました。
元々配置されていたデータセットから、削除したテーブルが無くなっていることを確認しましょう。
テーブルの使い方
実際にテーブルを使ってみましょう。
先ほどの手順で、テーブルを作成しておきます。
テーブルの詳細タブを開き、「テーブルID」のテーブル名の右側にカーソルを合わせてみましょう。
「クリップボードにコピー」というボタンが登場するので、これをクリックすると、テーブル名をコピーできます。
コピーしたテーブル名を使って、クエリを書いて実行してみましょう。
SELECT * FROM `テーブル名`
このクエリでは、テーブルに格納されたデータをそのまま抽出できました。
この方法を使えば、クエリを書いてデータ抽出、結果をテーブルとして保存、保存したテーブル使って新しいクエリでデータ抽出、と繰り返すことができるので、１回のクエリで書くことが難しくても、複数回に分けてクエリを実行し、目的のデータを抽出することができるようになります。
ビューの作成
ビューを使うと、クエリをテーブルのように取り扱うことができます。
テーブルの作成の時と同じような操作になるので、見比べながら覚えましょう。
試しにクエリを入力し、実行します。
SELECT 1 AS test_value思ったとおりの結果が返ってきていることを確認します。
クエリを実行する「実行」ボタンの隣に、「保存」ボタンがあります。
そこをクリックすると、「ビューを保存」を選択できます。
保存先のプロジェクト、データセット、テーブル名（ビュー名）を入力する画面が出てきます。
それぞれ入力し、「保存」をクリックしましょう。
ビューを作成できました。
作成されたビューをクリックして開いてみましょう。
このビューではどんなカラムが出力されるかを示すスキーマ情報が表示されます。
「詳細」タブをクリックすると、そのビューの詳細情報を表示できます。
元になったクエリも表示されます。
一度保存したビューの元のクエリを編集することもできます。
クエリの右側に「クエリを編集」と表示されているので、そこをクリックしてみましょう。
クエリの編集画面が開かれます。
ビューを作成した時と同様に、クエリを書き換えて、想定どおりの結果が返ってくるかを実行して確かめましょう。
SELECT 2 AS test_value
確認が終わったら、編集したクエリを保存します。
ビューを作った時と同じ場所には、「ビューを保存」と表示され、そこをクリックすると「ビューを保存」を選択できます。
ちなみに、「ビューに名前を付けて保存」を選択すると、編集中のビューとは別に新しいビューを作ることができます。
もう一度ビューを開いてみますが、先ほど開いたままのビューだったりすると、編集前のクエリが表示されていたりします。
画面右側に「更新」とあるので、そこをクリックしてみましょう。
編集後のクエリが表示されました。
最終更新も更新されています。
ビューの削除
不要になったテーブルを削除するように、不要になったビューも削除することがあります。
特にビューは、クエリが内包されているため、いかにも正しいビュー名で間違ったクエリや古いクエリを残してしまうと、偶然それを見かけたメンバーが参考にしてしまい、正しくない処理が蔓延してしまうリスクがあります。
それを防ぐためにも、いらなくなったビューはすぐに削除するようにしましょう。
テーブル同様、ビューの右側の点々から「削除」を選択できます。
ビューの詳細タブの中に「削除」ボタンがあるので、そこからでも操作できます。
「削除」と入力して「削除」ボタンを押下すると削除できるので、よく確認してから操作しましょう。
該当データセット配下から、ビューが削除されます。
ビューの使い方
ビューを作成した状態を使って、ビューを実際に使ってみましょう。
テーブルを使う時と同じ操作となります。
ビューの詳細タブで、ビューIDの右側のビュー名にカーソルを合わせると、「クリップボードにコピー」というボタンが表示されるので、それをコピーします。
コピーしたビュー名を使ってクエリを書きましょう。
SELECT * FROM `ビュー名`ビューを使って、結果が得られました。
テーブルやビューを使って初心者が複雑なクエリを書く方法
実演をしながら確認した方が分かりやすいでしょうから、例題をベースに進めましょう。
2023年の日付の中から、13日の金曜日を抽出してみるケースを考えます。
最初にテストデータを用意します。
SELECT * FROM UNNEST(GENERATE_DATE_ARRAY(DATE("2023-01-01"), DATE("2023-12-31"), INTERVAL 1 DAY)) AS target_date初心者の方にとっては未修得の領域になるでしょうから、クエリの詳細は一旦考えずに進めましょう。
このクエリを実行すると、2023/01/01～2023/12/31までの日付を取得できます。
このクエリを実行して、テーブルとして保存しましょう。
今回は「calendar」（カレンダー）というテーブル名にしました。
このカレンダーテーブルを基に、年月日と曜日を算出するビューを用意します。
SELECT
target_date,
EXTRACT(YEAR FROM target_date) AS y,
EXTRACT(MONTH FROM target_date) AS m,
EXTRACT(DAY FROM target_date) AS d,
CASE EXTRACT(DAYOFWEEK FROM target_date)
WHEN 1 THEN "日曜日"
WHEN 2 THEN "月曜日"
WHEN 3 THEN "火曜日"
WHEN 4 THEN "水曜日"
WHEN 5 THEN "木曜日"
WHEN 6 THEN "金曜日"
WHEN 7 THEN "土曜日"
ELSE NULL
END AS w
FROM カレンダーテーブルこのクエリを実行すると、年月日と曜日を抽出できます。
正しく実行できることを確認できてから、ビューとして保存します。
今回は「calendar_with_extract_info」という名前にしました。
試しに、ビューを正しく参照できるか試してみましょう。
SELECT * FROM カレンダーテーブルに抽出結果を加えたビュー同じ結果が得られるはずです。
さて、例題のとおり、13日の金曜日を抽出してみましょう。
SELECT * FROM カレンダーテーブルに抽出結果を加えたビュー
WHERE d = 13
AND w = "金曜日"
2023/01/13と2023/10/13が抽出されました。
ちなみに、例題を１回のクエリで実行しようとすると、下記のようなクエリになります。
SELECT * FROM UNNEST(GENERATE_DATE_ARRAY(DATE("2023-01-01"), DATE("2023-12-31"), INTERVAL 1 DAY)) AS target_date
WHERE EXTRACT(DAY FROM target_date) = 13
AND EXTRACT(DAYOFWEEK FROM target_date) = 6
今回の例題ではそこまで複雑ではないのですが、初心者の内は、いきなり書くには難しいところもあるでしょう。
一段階ずつ、クエリを書いて抽出して、テーブルやビューとして保存して、を繰り返すことによって、着実に前進できます。
振り返り
テーブル、ビューに関する基本操作は以上です。
BigQueryをビジネス利用しているとよく使う操作ですから、繰り返し操作することで慣れていきましょう。