Athena でのテーブルの作成（Hive DDL）

初めに

Athena のテーブル作成には、次の方法がある。

• ①AWS Glue クローラ
• ②Athena のテーブル作成フォーム
• ③Hive DDL

ここでは、主に③で作成する場合の項目について理解を深めるために、要点を纏めてみようと思います。

CREATE EXTERNAL

パラメータ	説明
EXTERNAL	常に EXTERNAL キーワードが使用される。 EXTERNAL キーワードを指定せずに CREATE TABLE を使用すると、Athena でエラーが発生する。
[IF NOT EXISTS]	既に同じ名前のテーブルが存在している場合はテーブルの作成を行わないようにする。
[db_name.]table_name	Athena のテーブル名では大文字と小文字は区別されない。 Apache Spark を使用する場合、Spark ではテーブル名を小文字にする必要がある。
[PARTITIONED BY (col_name data_type)]	col_name、data_type が指定された 1 つ以上の列を持つ、パーティション分割されたテーブルを作成する。 データのパーティション分割
[CLUSTERED BY (col_name) INTO num_buckets BUCKETS]	パーティション分割の有無にかかわらず、指定された col_name 列のデータをバケットと呼ばれるデータサブセットに分割する。 num_buckets パラメータは、作成するバケットの数を指定する。 バケット化は、大規模なデータセットでの一部のクエリのパフォーマンスを向上させることができる。
[ROW FORMAT row_format]	テーブルの行形式と基になるソースデータを指定する。 DELIMITED 句で 1 つ以上の区切り記号を指定できる。 SERDE 句を使用できる。 ROW FORMAT を省略するか、ROW FORMAT DELIMITED を指定すると、ネイティブ SerDe が使用されます。
[STORED AS file_format]	テーブルデータのファイル形式を指定する。 省略すると、デフォルトの TEXTFILE が使用される。
[LOCATION 's3://bucket_name/[folder]/']	S3 の場所を指定する。 globなどは使用しない。 フォルダやバケットの後にはスラッシュを使用する。 パーティションを使用する場合は、パーティション分割されたデータのルートを指定する。
[TBLPROPERTIES ( ['has_encrypted_data'='true false',] ['classification'='aws_glue_classification',] property_name=property_value [, ...] ) ]	定義済みのテーブルプロパティに加えて、テーブル定義のカスタムメタデータとしてキーと値のペアを指定する。

参考

---

PARTITIONED BY と TBLPROPERTIES

CloudTrail のログを例に見てみます。

手動

CloudTrail のログは、
s3://{bucket}/AWSLogs/{account-id}/CloudTrail/{region}/{year}/{month}/{day}
に保存される。

次は、
s3://{bucket}/AWSLogs/{account-id}/CloudTrail/
までの、S3の場所を指定し、
{region}/{year}/{month}/{day}
は、パーティションにします。

PARTITIONED BY (region string, year string, month string, day string)
LOCATION 's3://{bucket}/AWSLogs/{account-id}/CloudTrail/'

手動の場合、CloudTrail は、Hive標準では無いため、ALTER TABLE ADD PARTITION でパーティーションを読み込ませます。

ALTER TABLE table_name ADD 
   PARTITION (region='ap-northeast-1',
              year='2023',
              month='01',
              day='01')
   LOCATION 's3://{bucket}/AWSLogs/{account-id}/CloudTrail/ap-northeast-1/2023/01/01/'

WHERE句で必要なデータのみに絞り込んで、データをロードすることができます。

SELECT *
FROM cloudtrail_logs_table 
WHERE region='ap-northeast-1' AND year='2023' AND month='01' AND day='01'

パーティション射影

• パーティション射影を使用することで、クエリの実行時間を短縮し、パーティション管理を自動化できる。
• 新しいデータが追加されると、パーティション投影は新しいパーティションを自動で追加する。このため、ALTER TABLE ADD PARTITION を使用してパーティションを手動で追加する必要がなくなる。

次の例ではPARTITIONED BYでパーティションを指定し、設定はTBLPROPERTIES句で行い、パーティション射影を自動で追加します。

PARTITIONED BY (`timestamp` string)
LOCATION 's3://{bucket}/AWSLogs/{account-id}/CloudTrail/{region}'
TBLPROPERTIES (
  'projection.enabled'='true', 
  'projection.timestamp.format'='yyyy/MM/dd', 
  'projection.timestamp.interval'='1', 
  'projection.timestamp.interval.unit'='DAYS', 
  'projection.timestamp.range'='2020/01/01,NOW', 
  'projection.timestamp.type'='date',
  'storage.location.template'='s3://{bucket}/AWSLogs/{account-id}/CloudTrail/{region}/${timestamp}')

パーティション射影にサポートされている型は次の通りです。