はじめに
こんにちは！ AI技術開発室のYTです。
Google BigQueryは、Google Cloud Storage (GCS) 上のファイルだけでなく、GoogleスプレッドシートやGoogle Drive上のファイルなど、様々なデータソースを外部テーブルとして参照できる非常に便利な機能を持っています。これにより、データをBigQueryにロードすることなく、直接SQLクエリを実行できます。
先日、この機能を使ってGoogleスプレッドシートを外部テーブルとして設定する際に、特定のシート範囲を指定しようとしてちょっとだけハマってしまいました。今回はその原因と解決策について共有します。
外部テーブルの作成手順
まずは、Googleスプレッドシートを外部テーブルとして作成する基本的な手順を説明します。
- BigQueryのSQLワークスペースで、対象のデータセットのケバブメニュー（︙）から「データを追加」を選択します。
- データソースとして「Google Sheets」を指定します。
- 外部データへのアクセスの「BigQueryフェデレーション」を選択します。
- 「テーブルの作成」画面で、以下の項目を設定します。
- テーブルの作成元:
Googleドライブ - ドライブのURI: 参照したいGoogleスプレッドシートのURLを入力します。
- ファイル形式:
Googleスプレッドシート - データセット: 外部テーブルを作成するデータセット名を入力します。
- テーブル: 任意のテーブル名を入力します。
- スキーマ: 「自動検出」にチェックを入れるか、手動で定義します。
- テーブルの作成元:
この設定の中で、特定の範囲のデータのみを読み込みたい場合に「シート範囲」を指定します。今回のハマりポイントはここにありました。
発生した問題
BigQueryのコンソールから外部テーブルを作成する際、ソースの「シート範囲」を指定する項目があります。こちらのヘルプアイコンにカーソルを合わせると、入力例として 例: "Sheet1!A1:B20" と表示されます(記事執筆時点)。
公式のヘルプに記載されている通りなので、私もこの形式に従って "Sheet1!A1:B20" のようにダブルクォーテーションで囲んで入力しました。しかし、この設定で「テーブルを作成」を押下すると、以下のようなエラーが発生してしまいました。
range のパースに失敗しているようです。
原因調査の過程
エラーメッセージから、範囲指定の書式が正しくないと推測しました。
スプレッドシートの関数で別シートのセルを参照する際は ='Sheet1'!A1 のようにクォーテーションで囲むことを思い出し、'Sheet1'!A1:B20 という形式で試してみました。しかし、結果は同じエラーとなりました。
公式ドキュメントの調査
次に、公式ドキュメントを調査しました。該当項は以下のページです。
記事執筆時点では、「コンソールを使用してテーブルを作成する」セクションは日本語版の記載がなく、英語版を閲覧すると、ヘルプアイコンと同様に、ダブルクォーテーションで囲まれた形の記載になっていました。
Geminiとのやりとり
エラーについてGeminiに聞いてみると、
- 範囲指定は構文としては正しい
- 指定した範囲がスプレッドシートの実際のサイズを超えているのではないか
- シート名が完全一致していないのではないか
といった答えが返ってきて、さらにハマりかけました...
解決策
結論から言うと、クォーテーションマークを含めずに範囲を指定するのが正解でした。
具体的には、以下のように入力します。
Sheet1!A1:B20
この形式で設定したところ、無事にテーブルの作成に成功しました。
コンソールのヘルプに表示されていた " は、入力値が文字列であることを示すための記号であり、入力そのものに含める必要はなかったようです。ドキュメントの例をコピー＆ペーストする際には少し注意が必要ですね。
SQLクエリでの外部テーブル作成
上記はコンソールから外部テーブルを作成する際の手順でしたが、以下のようにSQLクエリで外部テーブルを作成し、範囲指定を行うことも可能です。
CREATE EXTERNAL TABLE `my-project.my_dataset.my_table`
OPTIONS (
format = 'GOOGLE_SHEETS',
uris = ['https://docs.google.com/spreadsheets/d/xxxxxxx/'],
sheet_range = 'Sheet1!A1:B20',
skip_leading_rows = 1
);
まとめ
BigQueryの外部テーブルでGoogleスプレッドシートの範囲を指定する際は、Sheet1!A1:B20 のように、クォーテーションマークを含めずに指定する必要があります。また、SQLクエリで外部テーブルを作成することもできます。UI上のヘルプテキストが少し紛らわしい例でしたが、この記事がBigQueryやGoogleスプレッドシートのユーザーの皆さまの助けとなれば幸いです。
We are hiring!
AI技術開発室では、一緒に働く仲間を募集しています。詳しくは以下をご覧ください。
株式会社ケアネットのエンジニアブログです。CareNetサービスの技術情報を中心に記事を投稿しております。 各記事の内容は個人の意見であり、企業を代表するものではございません。
Discussion