S3にあげたCSVファイルからAthenaのテーブルを作成

1 min read読了の目安(約1500字

事前準備

AWSの公式などを参考に、S3バケットの作成、S3バケットへのCSVファイルのアップロードはしておいてください。

https://docs.aws.amazon.com/ja_jp/AmazonS3/latest/user-guide/create-bucket.html

今回扱うもの

plotlyのAPIから入手できるgapminderのデータフレームをto_csv('~.csv')関数でCSVファイルにして、それをS3にアップロード

Athenaでテーブル作成

CREATE EXTERNAL TABLE IF NOT EXISTS gapminder (
         country STRING,
         continent STRING,
         year INT,
         lifeExp FLOAT,
         pop BIGINT,
         gdpPercap DOUBLE,
         iso_alpha STRING,
         iso_num INT 
) 
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  "separatorChar" = ","
)
LOCATION 's3://athena20200616/' 
TBLPROPERTIES ( 'has_encrypted_data'='false', 'skip.header.line.count'='1' )

修正 2020/12/13:登録データに誤りがあったため、クエリが書き換わっています

解説

テーブルのカラム名は、CSVファイルのヘッダーをあわせます。(順番も)。
CSVファイルで、文字列等がシングルクオーテーションで括られている場合は、以下の2行をLOCATIONの上に記述します

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerDe'
WITH SERDEPROPERTIES (
         'serialization.format' = '1' ) 

詳しくはこちらを

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/csv-serde.html

なにもSERDEの指定がないと、AthenaはLazySimpleSerdeを使用しますが、エスケープしたい文字列など、Serde意外で指定したい場合は、以下のように記述します。

ROW FORMAT SerDe 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' 
WITH SerDeProperties ("field.delim" = ",", "escapeChar"="\\",  "quoteChar"="\"")

LOCATION

LOCATIONでは、S3のバケット名を指定します。

TBLPROPERTIES

テーブル作成の際の補足情報を記入

has_encrypted_data

true or false
S3の暗号化がされているかどうか

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

CSVファイルの1行目はヘッダーなのでスキップ

'serialization.encoding'

文字コード指定。Shift-JISは、'SJIS'とする