はじめに
Amazon Athena の文字データ型 CHAR, VARCHAR, STRINGにおいて、空白とNULLを区別できないことがあります。
実際にNULLと空白がテーブル内やテーブル間で混在していると、WHERE句やJOIN句で意図しない結果になってしまうことがあるので、混在させないようNULLと空白それぞれの発生方法を調べました。
結果
検証環境
- Amazon Athena v3
- WSL2 Ubuntu 20.04
- Python 3.11.4
- awswrangler 3.8.0
- Python 3.11.4
検証
以下のようなシンプルなテキストファイルを使います。
nameはカラム名です。JaneとAnnaの間に何も記入しない行を作ります。
name
John
Jane
Anna
csv,tsv等テキストデータの場合
AWS S3にtest.csvを配置し、Athenaでテーブルとして読ませるためには、CREATE TABLEクエリを実行します。
その際、パラメータとしてROW FORMAT row_formatを選択する必要がありますが、この部分について調査しました。
LazySimpleSerDe
まずはLazySimpleSerdeを用いてテキストデータをAthenaでクエリする場合の結果を見てみます。
ROW FORMAT row_formatにおいて、ROW FORMAT DELIMITEDを指定するとLazySimpleSerDeになります。
テーブルの行形式と基になるソースデータ (該当する場合) を指定します。row_format として、DELIMITED 句で 1 つ以上の区切り記号を指定できます。または、以下に説明するように、SERDE 句を使用できます。ROW FORMAT を省略するか、ROW FORMAT DELIMITED を指定すると、ネイティブ SerDe が使用されます。
また、ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'のように明示も可能です。
テーブルを作ります。
CREATE EXTERNAL TABLE IF NOT EXISTS `awsdatacatalog`.`default`.`lazy_simple_serde` (
name VARCHAR(255)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION 's3://xxxx/serde_test/'
TBLPROPERTIES (
"skip.header.line.count"="1"
)
空白('')が無いかを調べます。
SELECT * FROM "default"."lazy_simple_serde"
WHERE name = ''
NULLが無いかを調べます
SELECT * FROM "default"."lazy_simple_serde"
WHERE name is NULL
LazySimpleSerDeでは、データの記入無しの行は空白になるようです。
OpenCSVSerDe
OpenCSVSerDeではどうなるか調べます。
データに二重引用符 (") で囲まれた値が含まれる場合は、Athena で OpenCSV SerDe を使用して値を逆シリアル化できます。データに二重引用符 (") で囲まれた値が含まれていない場合は、SerDe の指定を省略できます。この場合、Athena はデフォルトの LazySimpleSerDe を使用します。
テーブルを作ります。
CREATE EXTERNAL TABLE IF NOT EXISTS `awsdatacatalog`.`default`.`open_csv_serde` (
name VARCHAR(255)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ',',
'quoteChar' = '"',
'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://xxxx/serde_test/'
TBLPROPERTIES (
"skip.header.line.count"="1"
)
空白('')が無いかを調べます。
SELECT * FROM "default"."open_csv_serde"
WHERE name = ''
NULLが無いかを調べます
SELECT * FROM "default"."open_csv_serde"
WHERE name is NULL
OpenCSVSerDeでは、データの記入無しの行はNULLになるようです。
LazySimpleSerDeとは結果が異なっていました。
また、今回はvarchar(255)で定義しているので関係ありませんが、OpenCSVSerDeでは数値データ型の場合記入無しの行があるとエラーになるようです。
数値データ型として定義された列の空値または null 値を認識せず、string として残します。回避策の 1 つは、null 値を string とした列を作成してから、CAST を使用してクエリのフィールドを数値データ型に変換して、null の場合にデフォルト値の 0 を指定することです。詳細については、AWS ナレッジセンターの「Athena で CSV データをクエリすると、『HIVE_BAD_DATA: フィールド値の解析エラー』というエラーが表示されます」を参照してください。
SerDe確認方法
確認したい対象のテーブルに対して、SHOW CREATE TABLEもしくはコンソールでテーブルDDLを生成として結果を表示すれば分かります
-- LazySimpleSerDeの場合
CREATE EXTERNAL TABLE `lazy_simple_serde`(
`name` varchar(255))
ROW FORMAT DELIMITED -- 明示しなければデフォルトLazySimpleSerDe
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://quark-sandbox/serde_test'
TBLPROPERTIES (
'skip.header.line.count'='1',
'transient_lastDdlTime'='1717594167')
-- OpenCSVSerDeの場合
CREATE EXTERNAL TABLE `open_csv_serde`(
`name` string COMMENT 'from deserializer')
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.OpenCSVSerde' -- OpenCSVSerdeと書いてある
WITH SERDEPROPERTIES (
'escapeChar'='\\',
'quoteChar'='\"',
'separatorChar'=',')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://quark-sandbox/serde_test'
TBLPROPERTIES (
'skip.header.line.count'='1',
'transient_lastDdlTime'='1717595029')
parquetの場合
Apache Parquetは列指向のデータファイル形式で、分析環境のデータベースにおいて高いパフォーマンスを発揮します。ビッグデータ分析のために主に使われる形式です。
Parquetで書きこむためのPythonライブラリとしてAWS SDK for pandasを使います。
その中のawswrangler.s3.to_parquet関数を使ってparquetデータをS3に配置しつつ、Athenaへテーブルを作ります。
内部的にはpyarrowのparquet.write_tableを使っているようです。
前処理なし書き込み
csvファイルを読み取ってそのままs3.to_parquet関数でAthenaにテーブルを作ります。
import pandas as pd
import awswrangler as wr
import boto3
session = boto3.session.Session()
df = pd.read_csv('./serde_test_csv.csv', skip_blank_lines=False)
Pandas DataFrameでの表示はこんな感じ
テーブルを作ります。
wr.s3.to_parquet(
df = df
path = 's3://quark-sandbox/to_parquet_no_option/',
index = False,
compression = 'snappy',
boto3_session = session,
dataset = True,
mode ='overwrite',
database = 'default',
table='to_parquet_no_option',
dtype={
"name" : "VARCHAR(255)"
}
)
空白('')が無いかを調べます。
SELECT * FROM "default"."to_parquet_no_option"
WHERE name = ''
NULLが無いかを調べます
SELECT * FROM "default"."to_parquet_no_option"
WHERE name is NULL
データの記入無しの行はNULLになるようです。
fillnaによる前処理
pandas.DataFrame.fillna関数を使って以下のような前処理を行います。
df.fillna({"name":""})
NaN表記が無くなりました。
この状態でテーブルを作ります。
wr.s3.to_parquet(
df = df.fillna({"name":""}),
path = 's3://quark-sandbox/to_parquet_fill_na/',
index = False,
compression = 'snappy',
boto3_session = session,
dataset = True,
mode ='overwrite',
database = 'default',
table='to_parquet_fill_na',
dtype={
"name" : "VARCHAR(255)"
}
)
空白('')が無いかを調べます。
SELECT * FROM "default"."to_parquet_fill_na"
WHERE name = ''
NULLが無いかを調べます
SELECT * FROM "default"."to_parquet_fill_na"
WHERE name is NULL
上記のようにfillnaするとデータの記入無しの行は空白になるようです。
さいごに
ここまで閲覧いただきありがとうございました。同じ問題で悩んでいる方の一助になれば嬉しいです。
間違っている部分や不足している部分などありましたらご指摘いただけますと幸いです。