👻

BigQueryの便利な機能＆コンソールのショートカット集

2022/01/08に公開

Google Cloud

BigQuery

tech

前の記事では、BigQueryのざっくり概要を書きました。

今回はBigQueryを使う上でこれ知っておくと便利ですよ！という機能やTipsを書きたいと思います。

知っておくと便利な機能

メタデータを取得する

ある程度データベースを触る人なら、スキーマやテーブルの情報を持っているメタデータがだいたい存在していて活用している人も多いと思います。

BigQueryでももちろん存在するので私がよく使うものを紹介していきます。

-- testdatasetデータセットのテーブル一覧を取得する
SELECT * FROM testdataset.INFORMATION_SCHEMA.TABLES;

-- testdataset.testのテーブルのカラム一覧を取得する
SELECT　*　FROM testdataset.INFORMATION_SCHEMA.COLUMNS WHERE table_name="test" order by ordinal_position

-- testdataset.testのテーブルのカラム名をカンマ区切りで取得する
SELECT STRING_AGG(column_name ORDER BY ordinal_position) AS column_name 　FROM testdataset.INFORMATION_SCHEMA.COLUMNS WHERE table_name="test" 

-- testdataset.testのテーブルのDDLを取得する
SELECT　ddl　FROM testdataset.INFORMATION_SCHEMA.TABLES WHERE table_name="test"

他にもINFORMATION_SCHEMAには色々な情報がありますので一度見てみるといいと思います！

削除したor昔のデータにアクセスする

データベースを管理したことある人なら、dmp（データベースの内容を丸ごと複製したファイル）取っておけば良かった〜〜！とかテーブルバックアップ取っておけば良かった〜〜！って思うことありませんか？
BigQueryでは、時間指定でその時点のデータを参照することができます。すごい。
ただ過去ずっと見れるかというとそうではなく、過去7日間のみ保存されます。

-- testdataset.testのテーブルの１２時間前のデータを取得する
SELECT *
FROM `testdataset.test`
  FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 12 HOUR);

コスト削減のために有効期限を設定する

古いデータがストレージを圧迫して費用がかさんでいる気がする・・・いらないデータだから定期的に掃除しないと・・・と思っている方へ、BigQueryは古いデータを削除できる有効期限を設定する機能があります。

設定できる有効期限は以下のとおりです。

テーブルの有効期限
テーブルのパーテション有効期限

また、データセット（テーブルのまとまり、スキーマのようなもの）でデフォルトのテーブル有効期限・パーテションの有効期限を設定できます。

BigQueryでは、プロジェクト＞データセット＞テーブルという構成になっています。
データセットにデフォルトの設定することで、そのデータセット以下で新しく作られたテーブルはデータセットのデフォルトが採用されます。（既に存在しているテーブルには適用されません、またテーブルに有効期限の設定がある場合はそちらを優先します）

コンソールからでもSQLでもクライアントライブラリでも設定できますが、ここではSQLでの設定方法を記載します。

テーブルの有効期限

一定期間後にテーブルが削除されます。
テーブルごとに設定でき、有効期限切れになったテーブルは削除されます。

-- 2022年1月1日の０時に削除される設定を`test`テーブルに入れるSQLの例
 ALTER TABLE testdataset.test
 SET OPTIONS (
   expiration_timestamp=TIMESTAMP "2022-01-01 00:00:00"
 )

テーブルのパーテション有効期限

パーテーションとは、取り込み時間または時間単位の列で分割されたテーブルのことです。
そのパーティションの有効期限を設定することで、取り込み時間が古いものは削除していくといった作業が自動化できます。
なので、テーブルよりもっと細かい単位で削除することができます。

最新データだけあればいいという要件の場合に使える便利な設定だと思います。
ちなみに両方の設定がある場合は、テーブルの有効期限が優先されます。

-- 5日過ぎたパーテションのデータが削除される設定を`test`テーブルに入れるSQLの例
ALTER TABLE testdataset.test
SET OPTIONS (
 partition_expiration_days=5
)

データセットのデフォルト有効期限

テーブルごとに有効期限やパーテションの有効期限を設定するより、似た目的のテーブルを同一のデータセットに入れておき、デフォルト有効期限を設定するのが良い方法かなと思います。

※CLIツールとAPIでは設定する時間の単位が違うようです。この例では7.5日とかでも設定可能です

-- ７日の有効期限をデフォルトにする設定を`testdataset`データセットに追加するSQLの例
 ALTER SCHEMA testdataset
 SET OPTIONS(
     default_table_expiration_days=7
 )

同様にパーテションは、 default_partition_expiration_daysで設定できます。

コンソールのショートカット集

BigQueryコンソールに「ショートカット」というボタンがあり、使えるショートカットが表示されますが実はそれだけではありません。
ショートカットの表示方法

VSCode使っている人ならお馴染みのショートカットが実はたくさん使えるのです！（ちょっと見にくいですがF1で何が使えるか確認できます）
個人的にぜひ使いこなした方がいいものに◎、これもおすすめだよというものに◯をつけていまとめましたので参考にしてみてください。

ショートカット	機能	おすすめ度
F1	ショートカット一覧を表示	◯
⌘X	選択なしで実行すると1行カット	◎
⌘C	選択なしで実行すると1行コピー	◯
⌥↓ / ⌥↑	行を上/下移動	◎
⇧⌥↓ / ⇧⌥↑	行を上/下に複製
⇧⌘K	行を削除	◎
⌘ delete	左側を削除	◯
⇧K	右側を削除	◯
⌘ J	下の行と結合
⌘] / ⌘[	インデント/アウトデント
⌘↑ / ⌘↓	編集エリアの上部/下部に移動	◎
⌘K ⌘C	コメントアウト
⌘K ⌘U	コメントアウトを外す
⌘/	コメントアウト切り替え	◎
⇧⌥A	コメントアウトブロック（/* */）を切り替え
≈	カーソル挿入	◎
⌥⌘↑ / ⌥⌘↓	上/下にカーソル挿入	◎
⌘U	最後のカーソル操作のUndo
⌘L	現在の行を選択（続けて押すと下の行を追加選択していく）	◎
⌘F2	現在選択してるワードと一致するものを全て選択	◎
⌘F	検索	◎
⌘F3	検索にて次に移動
⌘Enter	検索にて前に移動
⌥⌘F	置換	◎
⌘G / ⇧⌘G	検索（次へ/ 前へ）
⌥Enter	一致する文字列を全て選択	◎
⌘D	選択なしだと現在のワードを選択。選択中だと次に選択した文字列に一致する文字列を選択	◎
⌘K ⌘D	次に一致する文字列に移動	◯
⌘K ⌘[ / ⌘K ⌘]	折り畳み/展開
⌘K ⌘0 / ⌘K ⌘J	折り畳み/展開（全部）
⌘K ⌘/	ブロックコメントの折り畳み
⌘K ⌘1	１つネストされた部分の折り畳み	◯
⌘K ⌘2	２つネストされた部分の折り畳み	◯
⌘K ⌘3	３つネストされた部分の折り畳み	◯
⌘K ⌘4	４つネストされた部分の折り畳み	◯
⌘K ⌘5	５つネストされた部分の折り畳み
⌘K ⌘6	６つネストされた部分の折り畳み
⌘K ⌘7	７つネストされた部分の折り畳み
F8 / ⌥ F8	次のクエリのエラー部分に移動	◯
⇧⌥ F8	前のクエリのエラー部分に移動
F7 / ⌥ F7	次のシンボルハイライトに移動
⇧⌥ F7	前のシンボルハイライトに移動
⌘K ⌘B	アンカーをセットする	◯
⌘K ⌘K	アンカーから現在のカーソルまでを選択する	◯

個人的に「⌘/|」（コメントアウト切り替え）と「⇧⌘K」（行を削除）と「⌘D」（選択中の文字列に一致する次の文字列を追加選択）は息をするように使うのでBigQueryでも使えて嬉しいです。

さいごに

BigQueryは頻繁にアップデートがあるので、使える機能も今後増えていくと思いますが、２０２２年１月の現在時点で自分が「おっ便利」とか「よく使うな」と思うものをまとめてみました。

どなたかのお役に立てば幸いです！
ここまで読んでいただいてありがとうございました！