こんにちは。香田です。
AWS Athenaで参照しているS3データをBigQueryで実行する場合、BigQuery Data Transfer ServiceのS3転送を利用することで、定期的にBigQueryに取り込みクエリ実行が可能になりますが、
逆のパターンとしてBigQueryのデータをAthenaで動かす機会があったので、データ転送の流れを紹介していきます。
はじめに
サンプルデータとして利用するBigQueryテーブルは、下記の一般公開データセットを利用しています。
対象テーブルはDATEタイプを利用した分割テーブルになっています。
bigquery-public-data:catalonian_mobile_coverage.mobile_data_2015_2017一般公開データセットのコピー
はじめに一般公開データセットのコピーを実施します。
一般公開データセットに対して直接エクスポートはできないので、自身のプロジェクトへテーブルをコピーします。
bq --location=US cp \
bigquery-public-data:catalonian_mobile_coverage.mobile_data_2015_2017 \
<project_id>:<dataset_name>.mobile_data_2015_2017BigQueryのデータをGCSへエクスポート
次にコピーしたテーブルをGCSへエクスポートします。
GCSからS3へコピーした際、Athenaでパーティション分割できるように事前にフォルダ構成をdt=<yyyy-mm-dd>で構成しておきます。Athenaのパーティション分割については下記を参照してください。
下記でGCSへエクスポートします。例として3日分のデータをエクスポートしています。
# 20150105
bq --location=US extract \
--destination_format CSV \
--compression GZIP \
'<project_id>:<dataset_name>.mobile_data_2015_2017$20150105' \
gs://<bucket_name>/mobile_data_2015_2017/dt=2015-01-05/data-*.gz
# 20150106
bq --location=US extract \
--destination_format CSV \
--compression GZIP \
'<project_id>:<dataset_name>.mobile_data_2015_2017$20150106' \
gs://<bucket_name>//mobile_data_2015_2017/dt=2015-01-06/data-*.gz
# 20150107
bq --location=US extract \
--destination_format CSV \
--compression GZIP \
'<project_id>:<dataset_name>.mobile_data_2015_2017$20150107' \
gs://<bucket_name>/mobile_data_2015_2017/dt=2015-01-07/data-*.gzエクスポート完了後、GCSに下記のようなフォルダ構成が確認できるはずです。
GCSからS3へコピー
次にgsutilコマンドを利用してGCSからS3へコピーします。
S3コピー用の認証設定として~/.botoファイルを作成します。
[Credentials]
aws_access_key_id = <aws_access_key_id>
aws_secret_access_key = <aws_secret_access_key>
[s3]
use-sigv4=True
host=s3.<region>.amazonaws.comコピーするS3バケットへアクセスできるか確認します。
gsutil ls s3://<bucket_name>/GCSからS3へコピーします。
gsutil -m rsync -r \
gs://<bucket_name>/mobile_data_2015_2017 \
s3://<bucket_name>/mobile_data_2015_2017Athenaのテーブルを作成する
次にAthenaコンソールへアクセスし、テーブルを作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS default.mobile_data_2015_2017 (
date STRING,
hour STRING,
lat FLOAT,
long FLOAT,
signal INT,
network STRING,
operator STRING,
status INT,
description STRING,
net STRING,
speed FLOAT,
satellites FLOAT,
precission FLOAT,
provider STRING,
activity STRING,
downloadSpeed STRING,
uploadSpeed STRING,
postal_code STRING,
town_name STRING,
position_geom STRING
) PARTITIONED BY (
dt STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ',', 'quoteChar' = '"', 'escapeChar' = '\\' ) LOCATION 's3://<bucket_name>/mobile_data_2015_2017'
TBLPROPERTIES ( 'skip.header.line.count'='1' );パーティション設定をAthenaへ認識させます。
MSCK REPAIR TABLE default.mobile_data_2015_2017;Athenaでクエリ実行する
パーティション設定することで下記のように日にちを指定し実行した際、クエリで読み込むデータ量が制限可能となります。
SELECT * FROM default.mobile_data_2015_2017
WHERE dt >= '2015-01-05'
AND dt < '2015-01-07'下記のような実行結果が確認できるはずです。
さいごに
BigQueryのデータをS3へ転送しAthenaで実行する流れはいかがでしたでしょうか。
BigQueryとAthenaで比較検証する際、本記事が参考になれば幸いです。
最後までご覧頂きありがとうございます。