Amazon Athena 文字データ型のNULLと空白を混在させないための対処方法

## はじめに

[Amazon Athena の文字データ型](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/data-types.html) CHAR, VARCHAR, STRINGにおいて、空白とNULLを区別できないことがあります。


![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/e0b04368-ab7a-d7c0-5189-f23cea8d2c7d.png)

実際にNULLと空白がテーブル内やテーブル間で混在していると、**WHERE句やJOIN句で意図しない結果になってしまうことがある**ので、混在させないようNULLと空白それぞれの発生方法を調べました。


## 結果

- [SerDe（シリアライザー・デシリアライザー）の設定に気を付ける](#csv,tsv等テキストデータの場合)
    - [既にテーブルが存在する場合はSHOW CREATE TABLEでSerDeを確認](#SerDe確認方法)
- [AWS SDK Pandas(pyarrow)の場合はNaNに対してfillnaの対処を考える](#parquetの場合)

## 検証環境

- [Amazon Athena v3](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/engine-versions-reference-0003.html)
- WSL2 Ubuntu 20.04
    - Python 3.11.4
        - awswrangler 3.8.0

## 検証

以下のようなシンプルなテキストファイルを使います。
`name`はカラム名です。`Jane`と`Anna`の間に何も記入しない行を作ります。

```text:test.csv
name
John
Jane

Anna
```


### csv,tsv等テキストデータの場合

AWS S3にtest.csvを配置し、Athenaでテーブルとして読ませるためには、[CREATE TABLE](https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-table.html)クエリを実行します。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/create-table.html

その際、パラメータとして`ROW FORMAT row_format`を選択する必要がありますが、この部分について調査しました。


#### LazySimpleSerDe

まずはLazySimpleSerdeを用いてテキストデータをAthenaでクエリする場合の結果を見てみます。

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

`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'`のように明示も可能です。

テーブルを作ります。

```sql
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"
)
```

空白('')が無いかを調べます。

```sql
SELECT * FROM "default"."lazy_simple_serde"
WHERE name = ''
```

![20240608.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/42ae4947-4186-66f2-3fbd-e3df193365d4.png)

NULLが無いかを調べます

```sql

SELECT * FROM "default"."lazy_simple_serde"
WHERE name is NULL
```

![202406081.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/acb53038-eecd-ed86-c2f1-8724e4c645b6.png)


LazySimpleSerDeでは、[データ](#検証)の記入無しの行は`空白になる`ようです。


#### OpenCSVSerDe

OpenCSVSerDeではどうなるか調べます。

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

> データに二重引用符 (") で囲まれた値が含まれる場合は、Athena で OpenCSV SerDe を使用して値を逆シリアル化できます。データに二重引用符 (") で囲まれた値が含まれていない場合は、SerDe の指定を省略できます。この場合、Athena はデフォルトの LazySimpleSerDe を使用します。

テーブルを作ります。

```sql
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"
)
```

空白('')が無いかを調べます。

```sql
SELECT * FROM "default"."open_csv_serde"
WHERE name = ''
```

![202406082.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/72f83c1a-f45a-2014-6003-45ff7ef79c68.png)


NULLが無いかを調べます

```sql
SELECT * FROM "default"."open_csv_serde"
WHERE name is NULL
```

![202406083.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/aca36dd4-15d7-3b22-ea8b-0e9112ed79c5.png)


OpenCSVSerDeでは、[データ](#検証)の記入無しの行は`NULLになる`ようです。
[LazySimpleSerDe](#LazySimpleSerDe)とは結果が異なっていました。

また、今回は`varchar(255)`で定義しているので関係ありませんが、OpenCSVSerDeでは数値データ型の場合記入無しの行があるとエラーになるようです。

> 数値データ型として定義された列の空値または null 値を認識せず、string として残します。回避策の 1 つは、null 値を string とした列を作成してから、CAST を使用してクエリのフィールドを数値データ型に変換して、null の場合にデフォルト値の 0 を指定することです。詳細については、AWS ナレッジセンターの「Athena で CSV データをクエリすると、『HIVE_BAD_DATA: フィールド値の解析エラー』というエラーが表示されます」を参照してください。




### SerDe確認方法

確認したい対象のテーブルに対して、`SHOW CREATE TABLE`もしくはコンソールで`テーブルDDLを生成`として結果を表示すれば分かります

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/56f27adf-4c1c-2fdd-55f4-7979fb19827b.png)

```sql
-- 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の場合

https://parquet.apache.org/

Apache Parquetは列指向のデータファイル形式で、分析環境のデータベースにおいて高いパフォーマンスを発揮します。ビッグデータ分析のために主に使われる形式です。

Parquetで書きこむためのPythonライブラリとして[AWS SDK for pandas](https://aws-sdk-pandas.readthedocs.io/en/stable/about.html)を使います。

https://github.com/aws/aws-sdk-pandas/tree/main

その中の[awswrangler.s3.to_parquet関数](https://github.com/aws/aws-sdk-pandas/blob/main/awswrangler/s3/_write_parquet.py#L340)を使ってparquetデータをS3に配置しつつ、Athenaへテーブルを作ります。

内部的には[pyarrow](https://pypi.org/project/pyarrow/)の[parquet.write_table](https://arrow.apache.org/docs/python/generated/pyarrow.parquet.write_table.html)を使っているようです。

#### 前処理なし書き込み

csvファイルを読み取ってそのまま`s3.to_parquet`関数でAthenaにテーブルを作ります。

```python
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での表示はこんな感じ
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/589695e4-f951-3092-b486-93d75e066d7e.png)

テーブルを作ります。

```python
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)"
    }
)
```

空白('')が無いかを調べます。

```sql
SELECT * FROM "default"."to_parquet_no_option"
WHERE name = ''
```

![202406084.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/687d4f5a-57e6-9750-1b8c-411b48970c3e.png)


NULLが無いかを調べます

```sql
SELECT * FROM "default"."to_parquet_no_option"
WHERE name is NULL
```
![202406085.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/29420f20-6270-f76a-2c02-675ce72408a8.png)

[データ](#検証)の記入無しの行は`NULLになる`ようです。


#### fillnaによる前処理

[pandas.DataFrame.fillna](https://pandas.pydata.org/pandas-docs/version/2.1/reference/api/pandas.DataFrame.fillna.html)関数を使って以下のような前処理を行います。

```python
df.fillna({"name":""})
```

NaN表記が無くなりました。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/7a1050c7-5a1e-d88c-f4e3-c18a21a20dae.png)

この状態でテーブルを作ります。

```python
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)"
    }
)
```

空白('')が無いかを調べます。

```sql
SELECT * FROM "default"."to_parquet_fill_na"
WHERE name = ''
```

![202406086.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/45077a97-1e8d-3ed9-d621-fd77e6abcf49.png)



NULLが無いかを調べます

```sql
SELECT * FROM "default"."to_parquet_fill_na"
WHERE name is NULL
```

![202406087.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/635079/13e1e6b2-c399-da0b-fe5a-a66c4c040f10.png)


上記のようにfillnaすると[データ](#検証)の記入無しの行は`空白になる`ようです。

## さいごに

ここまで閲覧いただきありがとうございました。同じ問題で悩んでいる方の一助になれば嬉しいです。

間違っている部分や不足している部分などありましたらご指摘いただけますと幸いです。