AWS Athena で CREATE TABLE する

# はじめに

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

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

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

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

例として、今回は[日本の人口推移データ](https://www.e-stat.go.jp/stat-search/files?page=1&layout=datalist&cycle=0&toukei=00200521&tstat=000001011777&tclass1=000001094741&stat_infid=000031524010)を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の中身をエクセルで開くとこんな感じになります。

![https___qiita-image-store.s3.ap-northeast-1.amazonaws.com_0_451269_76c59b65-380a-99a7-3ef1-dc0da0628853.jpeg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/451269/f43883f4-d63c-8cc9-5aa4-1d2dfc299ccb.jpeg)


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

```sql
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で変更できます。

```sql
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** ライブラリを使用し、

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

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

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

```sql
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圧縮を使用する。

```SQL
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圧縮を使用する。

```sql
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** ライブラリを使用する



```sql
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)` を以下のように追記する必要があります。

```sql
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のパーティションは読み込まれません、

```sql
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/とした場合、このパーティションテーブルを認識させるには、以下のクエリを実行必要があります。

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



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

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

だけでなく、

```sql
MSCK REPAIR TABLE default.population_table
```

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

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



# おわりに

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