AthenaからUNLOADにてORC形式で書き出した後にBigQuery Data Transfer Service for Amazon S3でBigQueryに取り込んでみた

今月始めにAmazon AthenaがUNLOADコマンドにてSELECTクエリによる結果をS3へ出力できるようになったのは記憶に新しいところです。

以前、BigQueryを通してGoogle DataStudioでの可視化にあたって、Athena上のTableをConnector経由で試みました。

Connector経由でのデメリットは取り込みのスケジューリングができないところです。今回はスケジューリングを試すための下地として、BigQuery Data Transfer Serviceにてコスト圧縮を目的にORC形式での取り込みをやってみました。

注意点

Transferサービスでの転送時エラーと、DataStudio上での集計エラーが発生しないように、カラム型指定が一番の肝となります。

データ型の取り扱いは同じタイプ名でもAthenaとBigQueryで同一とはなりません。まずはAthena側で値を適切な状態に保つ型で書き出し、BigQuery側でそれをエラーなく受け入れられるテーブル構成になるまで作り直す必要があります。

Athenaでの書き出し

まずは該当テーブルで「Generate Create Table DDL」を実行してみます。

出力されたSQL内で型指定が全て string の場合、大量にカラムがあるのならデータ型検証に時間を要することを覚悟しておきます。`

万が一、以下のエラーが出る場合はGlueでTableが存在しない、S3上のファイルを参照している状態になっています。この場合は手間ですがSQLを1から手作りします。

特記すべき点として、DATE型にしたい場合は from_iso8601_date() を使っておきましょう。

SELECT from_iso8601_date(COLUMN_NAME) as COLUMN_NAME from table;

ORCでの書き出しにて細かくオプションを指定しないのなら以下のようなSQLとなります。

UNLOAD (SELECT * FROM table) 
TO 's3://bucketname/'
WITH (format = 'ORC')

BigQuery Data Transfer Serviceで取り込む

事前にデータセットとテーブルの作成が必要です。BigQueryページを開き、データセットとテーブルを作成します。

まず、データ取り込み先のプロジェクトであることを確認して、次に画面右下方向にある「データセットを作成」を選択します。

データのロケーションを「東京(asia-northeast1)」に設定。後は要件に沿って入力し、「データセットを作成」を選択します。

次にテーブルの作成となりますが、これはSQL文を実行する必要があります。

CREATE TABLE test.test_table(
    id int,
    name string
) OPTIONS ();

実行後、左カラムメニューから「転送」を選択します。

「転送の作成」にて、フォームを埋めていきます。

項目名	内容
ソース	Amazon S3
転送構成名	要件があれば沿う。なければ適当に。
スケジュールオプション	後から変更可能。要件があっても、とりあえずは事故防止と手作業での確認優先で「オンデマンド」にしておくとよいでしょう。
データセットID	先程作成したデータセット名を指定。
Destination table	先程作成したテーブル名を指定。
Amazon S3 URI	ORCファイルを書き出したパスをいれます。末尾が「/*」となるようにします。
AccessKeyID	AWSアカウントのKeyをいれます。
SecretAccessKey	上に同じく。
File format	ORCを選択します。

他の項目は要件があれば沿って設定します。設定完了後は右上の「今すぐ転送を実行」を選択します。

転送が完了したら、概要に「転送実行が正常に完了しました。」と出ますが、データが転送されているとは限りません。必ず該当のスケジュールを選択して「実行の詳細」から「ログ」を確認しましょう。「No files found matching」とある場合はChromeならポップアップ設定にて「ポップアップの送信やリダイレクトの使用を許可するサイト」にBigQueryのURLを追加してから再実行します。

エラーが発生していた場合に「Field cost has changed type from XXXX to XXX」とあるなら、BigQuery側の型不一致となります。問題なければ「to XXX」の型に変更をいれますが、不安であればBigQueryのデータ型ドキュメントを見ながら一致する型に修正します。

データが入った場合は、テーブルのプレビューにて「このテーブルは空です。」と表示されなくなります。

あとがき

8月にAthenaで UNLOAD 機能が追加されたことで、ORCの書き出し処理をコードベースで実装する必要がなくなったのは好都合でした。ただ、それでもカラムの型一致だけは一手間かかることになりそうです。

必要に応じてGCP公式ドキュメントのAmazon S3 転送も参考にしてください。