VSCode ＋ BigQuery ＋ SQLFluff の環境構築
はじめに
VSCode上で、Bigqueryのクエリを書きたい人向けの記事です。
クエリのGitHub管理したい気持ちがでてきた人、
dbtとかで環境構築するのは、ちょっとハードルあるなーって人向けかもしれません。
できること
- VSCode上で、Bigqueryのクエリ書いて実行できる
- VSCode上で、Bigquery上のデータセット一覧が確認できる
- VSCode上で、クエリを自動で綺麗にしてくれる、 など
できること（詳細）
下記のリンクから詳細確認できます
前提条件
- VSCodeインストール済み
- Pythonの環境構築済み
- Homebrewインストール済み
- OS : macOS
フォルダ構成
/* ディレクトリ構造を確認 */
$ tree -a ./
./
├── .venv
├── .vscode
│ └── settings.json
├── src
│ ├── example1.bqsql
│ └── example2.bqsql
├── .gitattributes
├── .gitignore
├── .sqlfluff
├── README.md
└── requirements.txt
環境構築
VSCodeの拡張機能のインストール
下記の拡張機能をVSCodeにインストールする
BigQuery Runner の設定
マーケットプレースページのAuthenticationの項目に記載してある
認証方法は、Gcloud SDKでGCPで使っている個々のアカウントで認証する方法とサービスアカウントキーで認証する方法の２種があるが、今回はGcloud SDKを記載する。
- Gcloud SDKインストールする
- リンク先に記載方法より、Homebrew経由でインストールする方が簡単
- 参考
2. gcloud auth application-default loginをターミナルで実行
3. setting.jsonのbigqueryRunner.projectIdにGCPで使用するプロジェクトIDを記載。
// .vscode/setting.json
{
// BigQuery Runner
"bigqueryRunner.projectId": "プロジェクトID",
}
SQL (BigQuery) の設定
下記項目をsetting.jsonに追記
// .vscode/setting.json
{
// 拡張子sqlのファイルの言語モードをSQL(Bigquery)に指定
"files.associations": {
"*.bqsql": "sql-bigquery"
},
}
SQLFluff の設定
sqlfluffって記載のrequirements.txtを作成しておく
VSCodeのターミナルで下記を実行
python -m venv .venv #.venvという仮想環境を作成
source .venv/bin/activate #仮想環境の有効化
pip install --upgrade pip # pipアップデート
pip install -r requirements.txt # モジュール（sqlfluff）インストール
.sqlfluffファイルを作成(チームのスタイルに応じていい感じにしてください)
サンプルsqlfluffファイル
[sqlfluff]
dialect = bigquery
max_line_length = 200
output_line_length = 200
sql_file_exts = .sql,.bqsql
# バイト制限解除
large_file_skip_byte_limit = 0
# ルールの中から取り除くものを選ぶ
exclude_rules =
# ç fixでカラムの順序を変えないようにするための設定
structure.column_order
,ambiguous.column_count
,structure.using
# Salesforceはキャメルケースでlowerにすると読みにくくなるため追加
,capitalisation.identifiers
[sqlfluff:indentation]
indent_unit = space
tab_space_size = 2
[sqlfluff:rules:layout.long_lines]
# コメント行は長くても可とする。URLなどが入るため
# 行の長さに関してコメント行は無視する
ignore_comment_lines = True
# 行の長さに関してコメント句は無視する
ignore_comment_clauses = True
[sqlfluff:layout:type:comma]
# 先頭のカンマの強制
line_position = leading
# 前カンマの場合スペース入れない
spacing_after = touch
[sqlfluff:rules:capitalisation.keywords]
# 予約語の大文字小文字
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.functions]
# 関数名の大文字小文字
extended_capitalisation_policy = upper
[sqlfluff:rules:capitalisation.literals]
# リテラル値 (null・true・false) の大文字小文字
capitalisation_policy = upper
[sqlfluff:rules:capitalisation.types]
# データ型の大文字小文字
extended_capitalisation_policy = upper
下記項目をsetting.jsonに追記
// .vscode/setting.json
{
// SQLFluff
"sqlfluff.config": "${workspaceFolder}/.sqlfluff",
"sqlfluff.executablePath": ".venv/bin/sqlfluff",
"sqlfluff.dialect": "bigquery"
}
使用方法
クエリ実行方法
-
マーケットプレースページの
Usageの項目に記載の通り、適当なクエリを書いて.bqsqlの拡張子で保存 - 再生ボタン(
BigQuery Runner: Run)からクエリ実行
クエリの整形
- 右クリックから「ドキュメントのフォーマットを選択」すると
.sqlfluffに記載のルールで整形されます。
クエリサンプル
- 下記のクエリで、Bigqueryのプロジェクト上にテーブルを作成したりしています。
CREATE OR REPLACE VIEW `PROJECT_ID.region-REGION.テーブル名` AS (
-- ビュー作成クエリ
)
CREATE OR REPLACE TABLE `PROJECT_ID.region-REGION.テーブル名` AS (
-- テーブル作成クエリ
)
- この環境だとテーブルを検索する機能がないので、下記のクエリで検索することも可能です。
-- テーブルリストを格納する配列を宣言
DECLARE table_list ARRAY<STRING>;
DECLARE research_table_id STRING;
-- 検索対象のテーブル
SET research_table_id = '検索対象のテーブル名';
-- テーブルリストを取得
SET table_list = (
SELECT ARRAY_AGG(CONCAT(catalog_name,'.',schema_name,'.__TABLES__'))
FROM PROJECT_ID.`region-REGION`.INFORMATION_SCHEMA.SCHEMATA
);
-- テーブルリストからテーブルを選択してクエリを実行
EXECUTE IMMEDIATE (
SELECT STRING_AGG(
CONCAT('SELECT CASE type WHEN 1 THEN "table" WHEN 2 THEN "view" ELSE "" END AS table_type,CONCAT("`",project_id,".",dataset_id,".",table_id,"`") AS dataset_id,FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_MILLIS(creation_time), "Asia/Tokyo") as creation_time,FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP_MILLIS(last_modified_time), "Asia/Tokyo") as update_time,row_count,size_bytes / 1024 / 1024 / 1024 as size_gb FROM `'
, table
, '` WHERE REGEXP_CONTAINS(table_id, "', research_table_id, '")' )
, ' UNION ALL ')
FROM UNNEST(table_list) AS table
);
ちょっとした設定
.bqsqlのファイルアイコン設定
使っているアイコンセットが下記の場合
下記項目をsetting.jsonに追記
// .vscode/setting.json
"material-icon-theme.files.associations": {
"*.bqsql": "database"
}
GitHub上で.bqsqlのシンタックスハイライトを有効にする
GitHubでは、.bqsql ファイルをSQLとして強調表示するために、.gitattributes ファイルをリポジトリのルートに作成し、その中に拡張子に対する言語ヒントを設定することができます。
以下は、.gitattributes ファイルの例です。この例では、.bqsql ファイルを SQL として扱うように指定しています。
# .gitattributes
*.bqsql linguist-language=sql
これにより、GitHubは .bqsql ファイルを SQL として解釈し、適切なシンタックスハイライトを提供します。
終わりに
あくまで簡易的に環境構築です。
結局、クエリをGitHub上で管理したとしても、BigQuery上にあるクエリが最新か、GitHub上のクエリが最新か分からないので、dbtなどを導入してクエリを管理していくことが必要になると思います。
あと、dbtを使うにしろBigQuery Runnerの導入は必須だと思います。
参考リンク
Discussion