bigquery

Google BigQuery のテーブル (パブリックデータセットを含む) に対して、SELECT および INSERT クエリを実行できます。テーブル構造は、BigQuery のテーブルスキーマから自動的に推論されます。

読み取りには BigQuery REST API (tabledata.list) を使用するため、読み取り可能なのはネイティブテーブルのみです (ビュー、materialized view、外部テーブルは読み取れません) 。書き込みにはストリーミング挿入 (tabledata.insertAll) を使用します。この機能を使用するには、プロジェクトで課金を有効にする必要があります。

bigquery(project, dataset, table[, access_token][, key = value, ...])
bigquery(named_collection[, key = value, ...])

project、dataset、table、access_token 引数は、key = value 形式でも指定できます。位置引数はこの順序でこれらのスロットを埋めます。引数を位置指定とキー指定の両方で指定した場合、または同じキーを 2 回指定した場合はエラーになります。

次の引数は、key = value 形式 (または名前付きコレクションのキー) で指定できます。

認証方法は必ず1つだけ指定する必要があります。BigQuery では匿名アクセスは許可されないため、パブリックデータセットであっても認証情報が必要です。

1. アクセストークン。gcloud auth print-access-token などで取得した有効な OAuth 2.0 アクセストークンを使用します。トークンは短時間で失効する (通常は1時間後) ため、この方法は対話的な利用に最適です。
2. サービスアカウントキー (サーバーでの利用に推奨) 。Google Cloud IAM で作成したキーファイルの内容を、service_account_key 引数で渡します。ClickHouse はこのキーで JWT に署名し、アクセストークンと交換して自動的に更新します。
3. リフレッシュトークン。client_id、client_secret、refresh_token を渡します。たとえば、gcloud auth application-default login の実行後、~/.config/gcloud/application_default_credentials.json から取得できます。

クエリごとに認証情報を指定する必要がないよう、認証情報は 名前付きコレクション に保存します。名前付きコレクションから作成された永続テーブル (BigQuery テーブルエンジンまたは CREATE TABLE ... AS bigquery(...) を使用) は、そのコレクションの依存先として登録されるため、テーブルが存在する間は DROP NAMED COLLECTION を実行できません。

注:

• BigQuery の DATETIME にはタイムゾーンがありません。表示値がサーバーのタイムゾーンに依存しないよう、DateTime64(6, 'UTC') にマッピングされます。
• NULLABLE の RECORD は Nullable(Tuple(...)) にマッピングされるため、レコード全体の NULL はデフォルト値からなる Tuple に折りたたまれず、NULL として保持されます。NULL の配列 (または空の配列) は空の配列になります。これは ClickHouse では Array を Nullable 内に含められないためです。BigQuery の配列に NULL 要素を含めることはできません (ARRAY<T> は ARRAY<T NOT NULL> と同等です) 。そのため、REPEATED フィールドの要素型は Nullable ではありません (Array(T)、または RECORD 要素の場合は Array(Tuple(...))) 。tabledata.list レスポンス内の NULL 要素は不正な入力として拒否されます。
• bigquery テーブル関数を介した Nullable(Tuple(...)) カラムの読み書きは、追加設定なしで行えます。このようなカラムを含む永続的な BigQuery エンジンテーブルを作成するには、構造を推論する場合も明示的に宣言する場合も、他の Nullable(Tuple) カラムと同様に enable_nullable_tuple_type 設定が必要です。カラムを明示的に宣言する場合は、設定を不要にするため、RECORD フィールドを通常の Tuple(...) として宣言することもできますが、その場合レコード全体の NULL はデフォルトのタプルに強制変換されます。推論された型との差異として許容されるのは、RECORD の Tuple をラップする Nullable を削除することだけであり、同じレコードに限られます。null 許容性を別の (内側または外側の) レコードに移すことはできません。
• GEOGRAPHY は Geometry にマッピングされます。BigQuery は GEOGRAPHY 値を WKT テキストとして転送します。読み取り時には、これが Geometry の対応する代替型 (Point、MultiPoint、Ring、LineString、MultiLineString、Polygon、MultiPolygon の Variant) に解析され、書き込み時には WKT にシリアル化されます。GEOMETRYCOLLECTION および空のジオメトリ (POINT EMPTY など) に対応する Geometry はないため、このような値を含む行を読み取るとエラーが発生します。Variant は単体で NULL を保持できるため、NULLABLE の GEOGRAPHY フィールドは Nullable(Geometry) ではなく Geometry にマッピングされ、NULL も往復変換されます。
• JSON は JSON データ型ではなく String にマッピングされます。これは、ClickHouse の JSON 型が最上位レベルではオブジェクト ({...}) のみを受け入れる一方、BigQuery の JSON 値はスカラー、配列、null など任意の JSON 値になり得るためであり、このような値を含むテーブルは読み取れなくなります。さらに、JSON は Nullable でラップできないため、NULLABLE カラムの SQL NULL は保持されません。String マッピングは情報を損なわず、最上位オブジェクトは CAST(value AS JSON) で変換できます。
• 整数部が 38 桁を超える BIGNUMERIC 値は Decimal(76, 38) に収まらず、エラーになります。
• DateTime64/Date32 の範囲 (1900～2299 年) 外の TIMESTAMP および DATE 値はサポートされません。
• RANGE カラムは読み取り専用です。tabledata.insertAll は RANGE<T> 値として構造化された {start, end} オブジェクトを想定しますが、これは String マッピングから再構築できないため、RANGE カラムへの挿入はエラーになります。
• INT64 値は、API が JSON 数値を double として解析するため、そうしなければ [-2^53 + 1, 2^53 - 1] の範囲外の値が破損することから、10 進数文字列として tabledata.insertAll に送信されます。

gcloud のトークンを使用して、公開データセットを読み取ります。

SELECT word, sum(word_count) AS c
FROM bigquery('bigquery-public-data', 'samples', 'shakespeare', '<access token>')
GROUP BY word
ORDER BY c DESC
LIMIT 5;

サービスアカウントキーファイルを使用してプライベートテーブルを読み取ります。

SELECT count()
FROM bigquery('my-project', 'my_dataset', 'my_table',
              service_account_key = '{"type": "service_account", "private_key": "...", "client_email": "...", ...}');

データを挿入 (ストリーミング挿入。billing を有効にする必要があります) :

INSERT INTO FUNCTION bigquery('my-project', 'my_dataset', 'my_table', '<access token>')
SELECT number AS id, toString(number) AS name FROM numbers(10);

名前付きコレクションを使用します。

<clickhouse>
    <named_collections>
        <my_bigquery>
            <project>my-project</project>
            <dataset>my_dataset</dataset>
            <service_account_key><![CDATA[{"type": "service_account", ...}]]></service_account_key>
        </my_bigquery>
    </named_collections>
</clickhouse>

SELECT * FROM bigquery(my_bigquery, table = 'my_table');

• 読み取れるのはネイティブ BigQuery テーブルのみです。ビューおよび外部テーブルを読み取るには BigQuery クエリジョブを実行する必要がありますが、この関数では実行しません。
• RANGE カラムは (String として) 読み取れますが、書き込むことはできません。RANGE カラムへの挿入はエラーになります。
• GEOMETRYCOLLECTION または空のジオメトリである GEOGRAPHY 値は、Geometry 型で表現できないため、それを含む行を読み取るとエラーになります。REQUIRED GEOGRAPHY フィールドに NULL の Geometry を書き込むこと、または REPEATED GEOGRAPHY フィールドの要素として書き込むことは、BigQuery ではその位置に NULL を許可しないため拒否されます。
• 述語はプッシュダウンされません。tabledata.list はテーブルの行を一覧するだけで、フィルタリングパラメータをまったく提供しません (ページネーション、カラム選択、フォーマットのオプションのみを受け取ります) 。フィルタリングには BigQuery クエリジョブの実行が必要ですが、この関数では実行しません。したがって、WHERE 条件は行のダウンロード後に ClickHouse で適用されます。転送データ量を削減するには、カラム選択を使用してください。
• 一方、LIMIT は読み取るデータ量を削減します。ページは maxResults を max_block_size に設定して遅延的にリクエストされ、クエリに十分な行数が得られると、それ以上のページはリクエストされません。単純な LIMIT n (WHERE、GROUP BY、ORDER BY がなく、n が max_block_size 未満) の場合、ClickHouse は max_block_size を n に下げるため、ちょうど n 行に対して 1 回だけリクエストが行われます。それ以外の場合、読み取りは制限を超えた最初のページ境界で停止し、超過分は 1 ページ未満です。
• 読み取りでは、明示的なカラムリストを tabledata.list に渡すことで、クエリ分析時に確認したスキーマに固定します。カラムリストがリクエスト URL の長さ制限を超える非常に多数のカラムを対象とする読み取り (たとえば、数千カラムあるテーブルに対する SELECT *) では、固定せずに読み取るのではなくクエリが拒否されます (固定されていない読み取りは、同時実行のスキーマ変更により不整合になる可能性があります) 。リストに収まるよう、選択するカラムを減らしてください。同じ URL 長制限は、ページネーションされた各リクエストの前にも確認されます (各ページには不透明な pageToken が含まれます) 。そのため、後続ページが制限に収まらない読み取りは、途中で失敗するのではなく同じエラーで拒否されます。
• BigQuery テーブルのスキーマを読み取った後にテーブルが変更された場合、不一致のデータを黙って返したり書き込んだりする代わりに、クエリは拒否されます。ライブスキーマは読み取りの直前に再取得され、分析時のスキーマと比較されます。また、INSERT が最初の行のストリーミングを開始する前にも再度比較されます。この確認から後続のリクエストまでの間にスキーマが変更される可能性は、スキーマとデータが別々の REST リクエストで取得されるため、排除できません。
• 比較対象は、クエリの分析に使用されたスキーマスナップショットです。これはテーブル関数がその構造を解決するときに取得されます。永続テーブル (BigQuery engine テーブル、または CREATE TABLE ... AS bigquery(...) で作成され、同様にカラムを永続化するテーブル) の場合は、CREATE、ATTACH、またはサーバー再起動後の最初の読み取りまたは書き込み時に取得されます。テーブルメタデータには BigQuery スキーマではなくマッピングされた ClickHouse カラムが永続化されるため、テーブルがデタッチされている間 (またはサーバー停止中) に行われたスキーマ変更は拒否されず、次のクエリで採用されます。宣言されたカラムは引き続きライブスキーマに対して検証され、行もそのスキーマに従ってデコードされるため、マッピングされた ClickHouse 型を維持する変更 (たとえば STRING から BYTES) は、同じカラム型のまま新しい型の規則に従って読み取られます。
• ストリーミング挿入で書き込まれた行は BigQuery のストリーミングバッファに格納され、後続の読み取りで表示されるまでに時間がかかる場合があります。
• 大きな INSERT はバッチに分割して tabledata.insertAll に送信されます。各リクエストは最大 500 行で、BigQuery の 10 MB のリクエストサイズ制限を超えないようにも分割されます (この制限を超える単一行は、明確なエラーで拒否されます) 。
• 書き込みはアトミックではなく、1 回の tabledata.insertAll リクエストでも一部のみ成功する可能性があります。BigQuery はリクエスト内の一部の行をコミットする一方、他の行を insertErrors として拒否する場合があります。また、各リクエストは互いに独立してコミットされるため、先行するバッチが受け入れられた後に後続のバッチが拒否されることもあります。どちらの場合もクエリはエラーを報告しますが、すでにコミットされた行は BigQuery に残ります。重複を抑えるため、各行にはクエリ ID とストリーム内での行の序数位置から生成した安定した insertId を付与して送信します。BigQuery はこれを使用して、ストリーミング挿入ウィンドウ内でベストエフォートによる重複排除を行います。BigQuery の insertId の 128 文字制限を超える query_id は、固定長のプレフィックスにハッシュ化され、その query_id に対して安定した値となります。insertId は序数位置に依存するため、重複排除が確実に行われるのは、再実行時に行が同じ順序で生成される場合に限られます。バッチのトランスポートレベルでの再試行は常に安全ですが、同じ query_id で同じ INSERT を再実行した場合に重複排除されるのは、行が同じ順序で提示される場合のみです (たとえば、単一スレッドの insert、またはそれ以外に決定論的な順序付けの場合です。試行間で chunk の順序が変わる可能性がある並列 INSERT ... SELECT では、max_threads = 1 および max_insert_threads = 1 を設定してください) 。

• BigQuery テーブルエンジン