はじめに
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がうまくいかないことが多かったのでその一助になればと思います。