こんにちは、CX事業本部の若槻です。
Amazon Athenaでは、Glueのテーブルタイプの一つであるView
を使用することにより、実行するクエリを単純化したり、動的に変わるクエリを固定のクエリで実行するようにすることができます。
このViewテーブルはAthenaでCREATE VIEWクエリを使用することにより作成できますが、AWS CLIのglue create-table
コマンドを使用して作成することも可能です。
今回は、AWS GlueのViewテーブルをAWS CLIのglue create-table
コマンドで作成してみました。
やってみた
まず最初に以降のコマンド実行で使用する変数を定義しておきます。
% AWS_REGION=ap-northeast-1
% ACCOUNT_ID=$(aws sts get-caller-identity | jq -r ".Account")
% RAW_DATA_BUCKET=s3://devices-raw-data-${ACCOUNT_ID}-${AWS_REGION}
% GLUE_DATABASE_NAME=devices_data_analystics
% RAW_DATA_GLUE_TABLE_NAME=devices_raw_data
% ATHENA_WORK_GROUP_NAME=devices-data-analytics
環境作成
CloudFormationスタック
CloudFormationスタックのテンプレートです。
template.yaml
AWSTemplateFormatVersion: '2010-09-09'
Resources:
DevicesRawDataBucket:
Type: AWS::S3::Bucket
Properties:
BucketName: !Sub devices-raw-data-${AWS::AccountId}-${AWS::Region}
DevicesDataAnalyticsAthenaWorkGroup:
Type: AWS::Athena::WorkGroup
Properties:
Name: devices-data-analytics
WorkGroupConfiguration:
ResultConfiguration:
OutputLocation: !Sub s3://${DevicesRawDataBucket}/query-result
EnforceWorkGroupConfiguration: true
PublishCloudWatchMetricsEnabled: true
DevicesDataAnalyticsGlueDatabase:
Type: AWS::Glue::Database
Properties:
CatalogId: !Ref AWS::AccountId
DatabaseInput:
Name: devices_data_analystics
DevicesRawDataGlueTable:
Type: AWS::Glue::Table
Properties:
CatalogId: !Ref AWS::AccountId
DatabaseName: !Ref DevicesDataAnalyticsGlueDatabase
TableInput:
Name: devices_raw_data
TableType: EXTERNAL_TABLE
Parameters:
has_encrypted_data: false
serialization.encoding: utf-8
EXTERNAL: true
StorageDescriptor:
OutputFormat: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
Columns:
- Name: device_id
Type: string
- Name: timestamp
Type: bigint
- Name: state
Type: boolean
InputFormat: org.apache.hadoop.mapred.TextInputFormat
Location: !Sub s3://${DevicesRawDataBucket}/raw-data
SerdeInfo:
Parameters:
paths: "device_id, timestamp, state"
SerializationLibrary: org.apache.hive.hcatalog.data.JsonSerDe
Viewが参照するテーブルをリソースDevicesRawDataGlueTable
で定義しています。
スタックをデプロイします。
% aws cloudformation deploy \
--template-file template.yaml \
--stack-name devices-data-analytics-stack \
--capabilities CAPABILITY_NAMED_IAM \
--no-fail-on-empty-changeset
データ作成
DevicesRawDataGlueTable
のLocationとなるS3バケットのパスにデータとしてオブジェクトraw-data.json
を作成します。
raw-data.json
{"device_id": "device1", "timestamp": 1609348014, "state": true}
{"device_id": "device2", "timestamp": 1609348014, "state": false}
{"device_id": "device3", "timestamp": 1609348014, "state": true}
% aws s3 cp raw-data.json \
${RAW_DATA_BUCKET}/raw-data/raw-data.json
Viewの作成
AWS CLIのglue create-table
コマンドを使用してdevices_raw_data_view
という名前のViewテーブルを作成します。
まずcreate-table
のTableInputパラメータで指定する値を作成します。
Viewが実行するSQLクエリをファイルで定義します。
originalsql.sql
SELECT * FROM ${RAW_DATA_GLUE_TABLE_NAME}
eval "echo \"$(cat originalsql.sql)\""
によりSQLクエリを変数に格納します。(eval
によりファイルの記述内の変数を展開しています)
% original_sql=$(eval "echo \"$(cat originalsql.sql)\"")
% echo $original_sql
SELECT * FROM devices_raw_data
TableInputのViewOriginalText
パラメータで指定する値をファイルで定義します。(先程作成したSQLクエリをoriginalSql
パラメータに指定しています)
vieworiginaltext.json
{
\"originalSql\":\"${original_sql}\",
\"catalog\":\"awsdatacatalog\",
\"schema\":\"${GLUE_DATABASE_NAME}\",
\"columns\":[
{
\"name\":\"device_id\",
\"type\":\"varchar\"
},
{
\"name\":\"timestamp\",
\"type\":\"bigint\"
},
{
\"name\":\"state\",
\"type\":\"boolean\"
}
]
}
※ここでViewOriginalTextの値の作成時の注意点として、columns
でのtype
で文字列型を指定したい場合は、参照元のテーブルでの型の指定のされ方に関わらず、上記のハイライト部分のようにvarchar
を指定する必要があります。string
を指定した場合はViewの作成自体はできますが、作成されたViewに対してクエリを実行すると下記のようなエラーが出て失敗します。
INVALID_VIEW: Invalid view JSON: { "originalSql":"SELECT * FROM devices_raw_data", "catalog":"awsdatacatalog", "schema":"devices_data_analystics", "columns":[ { "name":"device_id", "type":"string" }, { "name":"timestamp", "type":"bigint" }, { "name":"state", "type":"boolean" } ] }
eval "echo \"$(cat vieworiginaltext.json)\""
によりViewOriginalTextの値を変数に格納します。(eval
によりファイルの記述内の変数を展開しています)
% view_original_text=$(eval "echo \"$(cat vieworiginaltext.json)\"")
$ echo $view_original_text
{
"originalSql":"SELECT * FROM devices_raw_data",
"catalog":"awsdatacatalog",
"schema":"devices_data_analystics",
"columns":[
{
"name":"device_id",
"type":"varchar"
},
{
"name":"timestamp",
"type":"bigint"
},
{
"name":"state",
"type":"boolean"
}
]
}
ViewOriginalTextの値をBase64エンコードして変数に格納します。
% view_original_text_b64encoded=$(echo -n $view_original_text | base64)
TableInputパラメータで指定する値をファイルで定義します。ここまでで作成したViewOriginalTextの値のBase64エンコードをViewOriginalText
パラメータで使用します。
tableinput.json
{
\"Name\": \"devices_raw_data_view\",
\"TableType\": \"VIRTUAL_VIEW\",
\"Parameters\": {
\"presto_view\": \"true\"
},
\"ViewOriginalText\": \"/* Presto View: ${view_original_text_b64encoded} */\",
\"ViewExpandedText\": \"/* Presto View */\",
\"StorageDescriptor\": {
\"SerdeInfo\": {},
\"Columns\": [
{
\"Name\":\"device_id\",
\"Type\":\"string\"
},
{
\"Name\":\"timestamp\",
\"Type\":\"bigint\"
},
{
\"Name\":\"state\",
\"Type\":\"boolean\"
}
]
}
}
eval "echo \"$(cat tableinput.json)\""
により変数を展開したTableInputの値を--table-input
に指定し、glue create-table
によりViewを作成します。
% tableinput=$(eval "echo \"$(cat tableinput.json)\"")
% aws glue create-table \
--database-name ${GLUE_DATABASE_NAME} \
--table-input ${tableinput}
作成したViewテーブルdevices_raw_data_view
に対してSELECTクエリを実行すると、オリジナルのSQLによりGlueテーブルのデータが取得できました。
SELECT * FROM "devices_data_analystics"."devices_raw_data_view"
おわりに
AWS GlueのViewテーブルをAWS CLIのglue create-table
コマンドで作成してみました。
TableInputのViewOriginalTextパラメータでBase64エンコードしての指定が必要となるなど少々面倒な部分はありましたが、Athenaでのクエリ実行でない方法を今回確立出来て良かったです。
参考
- [AWS Glue]データカタログのパーティションをAWS CLIで更新してみた | Developers.IO
- Shellでもbase64 - Qiita
- AWS Glueデータカタログへのテーブルの作成とAthenaでのクエリ実行をAWS CLIでやってみた | Developers.IO
- amazon web services - External View query works in athena console but fails to work when using it in aws quicksight - Stack Overflow
- Create Virtual Views with AWS Glue and Query them Using Athena — Ujjwal Bhardwaj
- 整形済みJSON文字列を1行JSONに変換(圧縮)する - Qiita
以上