はじめに

AWS AthenaでCREATE TABLEを実行するやり方を紹介したいと思います。
CTAS(CREATE TABLE AS SELECT)は少し毛色が違うので、本記事では紹介しておりません。

AWS GlueのCrawlerを実行してメタデータカタログを作成、編集するのが一般的ですが、Crawlerの推論だとなかなかうまくいかないこともあり、カラム数やプロパティが単純な場合はAthenaでデータカタログを作る方が楽なケースが多いように感じます。

S3に配置されたデータに対してテーブルを作成する

CREATE EXTERNAL TABLE 構文を用いてテーブルを作成いたします。

例として、今回は日本の人口推移データをs3://inu-is-dog/athena/c01.csvに配置しました。
(都合上、csvのヘッダーを英語表記に変更いたしました。)
出典：政府統計の総合窓口(e-Stat)（https://www.e-stat.go.jp ）

配置されるデータは構造体が同じならば同じ階層に複数存在しても問題ありません。例えば、同じ構造体のc02.csvがs3://inu-is-dog/athena/c02.csvのように配置されていても大丈夫です。

csvの中身をエクセルで開くとこんな感じになります。

では、テーブルの作成を行なっていきます。今回はデフォルトで配置されている"default"のデータベースに"population_table"というテーブルを作成するためには以下のクエリを実行します。

CREATE EXTERNAL TABLE IF NOT EXISTS default.population_table (
  code string,
  prefecture string,
  era_name string,
  japanese_calender int,
  year int,
  comment string,
  all_population bigint,
  man_population bigint,
  woman_population bigint
)
ROW FORMAT SerDe 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SerDeProperties ("field.delim" = ",", "escapeChar"="\\",  "quoteChar"="\"")
STORED AS TEXTFILE
LOCATION 's3://inu-is-dog/athena/' 
TBLPROPERTIES ('has_encrypted_data'='false',  'skip.header.line.count'='1', 'serialization.encoding'='SJIS')

このクエリを例に1つずつ構文の解説をしていきます。(わかりやすいものから紹介していきます)

STORED AS

データ形式を指定します。省略した場合はTEXTFILEが適用されます。
主要なオプションは以下の通りです。

SEQUENCEFILE
TEXTFILE
RCFILE
ORC
PARQUET
AVRO
INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

LOCATION

データの配置場所を意味します。フォルダ名またはバケット名を指定します。
ファイル名を指定しないように注意しましょう。
今回は 's3://inu-is-dog/athena/' を指定しております。

TBLPROPERTIES

テーブルのプロパティを設定します。今回の場合は以下のように設定されております。

'has_encrypted_data'='false'

=> S3の暗号化がされていない

'skip.header.line.count'='1'

=> csvファイルの１行目はヘッダーなのでスキップする

'serialization.encoding'='SJIS'

=> 文字コードはSJIS

余談ですが、TBLPROPERTIESはCREATE TABLE後、ALTER TABLEで変更できます。

ALTER TABLE table_name SET TBLPROPERTIES ('property_name' = 'property_value' [ , ... ])

ROW FORMAT

SerDe(シリアライザー/デシリアライザー)を設定です。
設定方法は2通りで

ROW FORMATを省略するか、ROW FORMAT DELIMITEDを指定しネイティブSerDeを使用する
SerDe句を使用する

今回は後者のSerDe句を使用しております。

SerDe句を設定する場合、推奨設定は以下になります。

データ形式がCSV(or TSV)の場合

データに引用符(シングルクォーテーションやダブルクォーテーション)が含まれないならば、LazySimpleSerDeを使用します。(TSVの場合、区切り文字を FIELDS TERMINATED BY '\t' として指定)

データに引用符(シングルクォーテーションやダブルクォーテーション)が含まれるならば、OpenCSVSerDeを使用します。OpenCSVSerDeでは、区切り文字、引用符、およびエスケープ文字をROW FORMAT SERDEの後に記述するWITH 部分で指定できます。

今回はOpenCSVSerDeのorg.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe ライブラリを使用し、

区切り文字を,エスケープ文字を\\に、引用符を\"に設定しております。

WITH SERDEPROPERTIES ("field.delim" = ",", "escapeChar"="\\",  "quoteChar"="\"")

他にもSerDeのプロパティを設定することができます。

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = ',',
'field.delim' = ',',
'collection.delim' = '|',
'mapkey.delim' = ':',
'escape.delim' = '\\'
)

データ形式がJSONの場合

Hive JSON SerDe (ライブラリ名：org.apache.hive.hcatalog.data.JsonSerDe) または OpenX JSON SerDe (ライブラリ名：org.openx.data.jsonserde.JsonSerDe) を使用します。

Hive JSON SerDeに対して、OpenX JSON SerDeは以下のオプションを使用できるのが特徴です。

ignore.malformed.json
不正なJSONフォーマットを無視できる
dots.in.keys
キーに含まれるドットをアンダーバーに置換する(Athenaではカラム名にドットがあるとエラーになる)
case.insensitive
キーの文字列内の大文字、小文字を区別する
ColumnToJsonKeyMappings
カラム名をJSONキーにマップする

データ形式がApache Avroの場合

Avro SerDe の org.apache.hadoop.hive.serde2.avro.AvroSerDe ライブラリを使用する

データ形式がORC(Optimized Row Columnar)の場合

ORC SerDe の org.apache.hadoop.hive.ql.io.orc.OrcSerde ライブラリを使用する

または、 ZLIB圧縮を使用する。

STORED AS ORC
LOCATION 's3://inu-is-dog/athena/'
tblproperties ("orc.compress"="ZLIB");

データ形式がParquetの場合

Parquet SerDe の org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe ライブラリを使用する

または、SNAPPY圧縮を使用する。

STORED AS PARQUET
LOCATION 's3://inu-is-dog/athena/'
tblproperties ("parquet.compress"="SNAPPY");

Apache WebServerの場合

RegexSerDe の org.apache.hadoop.hive.serde2.RegexSerDe ライブラリを使用する

CloudTrailログの場合

CloudTrail SerDe の com.amazon.emr.hive.serde.CloudTrailSerde ライブラリを使用する

ALTER TABLE table_name SET TBLPROPERTIES ('property_name' = 'property_value' [ , ... ])

PARTITION BYでパーティションテーブルを作成する

パーティション分割されたテーブルを作成するにはPATITION BY句を使用する必要があります。

例えば、csvを s3://inu-is-dog/athena/c01.csv 、s3://inu-is-dog/athena/c02.csvではなく、

s3://inu-is-dog/athena/vol1/c01.csv 、s3://inu-is-dog/athena/vol2/c02.csvと配置したとしましょう。

vol1とvol2がパーティションであることを認識させるためには、先ほどのCREATE TABLE文に PARTITIONED BY (vol string) を以下のように追記する必要があります。

CREATE EXTERNAL TABLE IF NOT EXISTS default.population_table (
  code string,
  prefecture string,
  era_name string,
  japanese_calender int,
  year int,
  comment string,
  all_population bigint,
  man_population bigint,
  woman_population bigint
)
PARTITIONED BY (vol string)
ROW FORMAT SerDe 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SerDeProperties ("field.delim" = ",", "escapeChar"="\\",  "quoteChar"="\"")
STORE AS TEXTFILE
LOCATION 's3://inu-is-dog/athena/' 
TBLPROPERTIES ('has_encrypted_data'='false',  'skip.header.line.count'='1', 'serialization.encoding'='SJIS')

これでパーティション分割されたテーブルが出来上がります。

Athenaは読み込まれたデータの分だけ課金されるのでWHERE句でパーティション指定することは料金の節約にもつながるので重要です。

例えば、↓の場合、vol2のパーティションは読み込まれません、

SELECT *
FROM default.population_table
WHERE vol = 'vol1'

また、パーティションテーブルはS3のプレフィックス2パターンあります。

パーティションの値そのまま s3://inu-is-dog/athena/vol1/, s3://inu-is-dog/athena/vol2/ など
パーティション名と値を=で指定する s3://inu-is-dog/athena/vol=1/, s3://inu-is-dog/athena/vol=2/ など

これでどのように変わるのかというと、新しいパーティションテーブルを認識させる方法が変わります。

例えば、CREATE TABLEで新しくテーブルを作成した後、新しくvol3のプレフィックスを追加したくなったとしましょう。

プレフィックスをs3://inu-is-dog/athena/vol3/とした場合、このパーティションテーブルを認識させるには、以下のクエリを実行必要があります。

ALTER TABLE default.population_table ADD PARTITION (vol='vol3') location 's3://inu-is-dog/athena/vol3/'

それに対して、プレフィックスをs3://inu-is-dog/athena/vol=3/とした場合、

ALTER TABLE default.population_table ADD PARTITION (vol='3') location 's3://inu-is-dog/athena/vol=3/'

だけでなく、

MSCK REPAIR TABLE default.population_table

でも新しいパーティションテーブルを認識させることができます。

新しいパーティションテーブルが複数ある場合、前者だとADD PARTITONをひたすら実行しなければいけないのに対して後者は1つのクエリで完結するのでスマートですね。

おわりに

今回はAthenaのCREATE TABLEについて紹介させていただきました。
経験上、SerDeがうまくいかないことが多かったのでその一助になればと思います。

AWS Athena で CREATE TABLE する