# CSV SerDe ライブラリ
Athena で CSV データのテーブルを作成する際には、Open CSV SerDe または Lazy Simple SerDe ライブラリのいずれかを使用できます。いずれを使用するかを決める際には、次のガイドラインを検討してください。
+ データに二重引用符 (`"`) で囲まれた値が含まれる場合は、Athena で [Open CSV SerDe](https://cwiki.apache.org/confluence/display/Hive/CSV+Serde) ライブラリを使用して値を逆シリアル化できます。データに二重引用符 (`"`) で囲まれた値が含まれていない場合は、SerDe の指定を省略できます。この場合、Athena はデフォルトの Lazy Simple SerDe を使用します。詳細については、[CSV、TSV、カスタム区切りファイル用の Lazy Simple SerDe](lazy-simple-serde.md) を参照してください。
+ データに UNIX の `TIMESTAMP` 数値 (`1579059880000` など) がある場合は、Open CSV SerDe を使用します。データが `java.sql.Timestamp` 形式を使用する場合は、Lazy Simple SerDe を使用します。
**Topics**
+ [CSV、TSV、カスタム区切りファイル用の Lazy Simple SerDe](lazy-simple-serde.md)
+ [CSV を処理するための Open CSV SerDe](csv-serde.md)
# CSV、TSV、カスタム区切りファイル用の Lazy Simple SerDe
これは CSV、TSV、およびカスタム区切り形式のデータ用の、Athena におけるデフォルトの SerDe であるため、指定はオプションです。`CREATE TABLE` ステートメントで SerDe を指定せず、`ROW FORMAT DELIMITED` のみを指定した場合、Athena はこの SerDe を使用します。データ内に引用符で囲まれた値がない場合は、この SerDe を使用します。
Lazy Simple SerDe に関するリファレンスドキュメントについては、「Apache Hive デベロッパーガイド」の「[Hive SerDe](https://cwiki.apache.org/confluence/display/Hive/DeveloperGuide#DeveloperGuide-HiveSerDe)」セクションを参照してください。
## シリアル化ライブラリ名
Lazy Simple SerDe のシリアル化ライブラリ名は `org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe` です。ソースコード情報については、GitHub.com の「[LazySimpleSerDe.java](https://github.com/apache/hive/blob/master/serde/src/java/org/apache/hadoop/hive/serde2/lazy/LazySimpleSerDe.java)」を参照してください。
## ヘッダ―の無視
テーブルを定義するときにデータ内のヘッダーを無視するには、以下の例にあるように、`skip.header.line.count` テーブルプロパティを使用できます。
```
TBLPROPERTIES ("skip.header.line.count"="1")
```
ヘッダーを無視する例については、「[Amazon VPC フローログをクエリする](vpc-flow-logs.md)」および「[Amazon CloudFront ログをクエリする](cloudfront-logs.md)」の `CREATE TABLE` ステートメントを参照してください。
## CSV の例
次の例は、Athena で CSV データからテーブルを作成するために `LazySimpleSerDe` ライブラリを使用する方法を示しています。この SerDe を使用してカスタム区切りファイルをデシリアライズするには、これらの例のパターンに従いますが、`FIELDS TERMINATED BY` 句を使用して異なる単一文字の区切り文字を指定します。Lazy Simple SerDe は、複数文字の区切り文字をサポートしていません。
**注記**
`s3://athena-examples-myregion/path/to/data/` の *myregion* を、Athena が実行されるリージョンの識別子 (`s3://athena-examples-us-west-1/path/to/data/` など) に置き換えます。
`CREATE TABLE` ステートメントを使用して、Amazon S3 に保存されている CSV 形式の基盤となるデータから Athena テーブルを作成します。
```
CREATE EXTERNAL TABLE flight_delays_csv (
yr INT,
quarter INT,
month INT,
dayofmonth INT,
dayofweek INT,
flightdate STRING,
uniquecarrier STRING,
airlineid INT,
carrier STRING,
tailnum STRING,
flightnum STRING,
originairportid INT,
originairportseqid INT,
origincitymarketid INT,
origin STRING,
origincityname STRING,
originstate STRING,
originstatefips STRING,
originstatename STRING,
originwac INT,
destairportid INT,
destairportseqid INT,
destcitymarketid INT,
dest STRING,
destcityname STRING,
deststate STRING,
deststatefips STRING,
deststatename STRING,
destwac INT,
crsdeptime STRING,
deptime STRING,
depdelay INT,
depdelayminutes INT,
depdel15 INT,
departuredelaygroups INT,
deptimeblk STRING,
taxiout INT,
wheelsoff STRING,
wheelson STRING,
taxiin INT,
crsarrtime INT,
arrtime STRING,
arrdelay INT,
arrdelayminutes INT,
arrdel15 INT,
arrivaldelaygroups INT,
arrtimeblk STRING,
cancelled INT,
cancellationcode STRING,
diverted INT,
crselapsedtime INT,
actualelapsedtime INT,
airtime INT,
flights INT,
distance INT,
distancegroup INT,
carrierdelay INT,
weatherdelay INT,
nasdelay INT,
securitydelay INT,
lateaircraftdelay INT,
firstdeptime STRING,
totaladdgtime INT,
longestaddgtime INT,
divairportlandings INT,
divreacheddest INT,
divactualelapsedtime INT,
divarrdelay INT,
divdistance INT,
div1airport STRING,
div1airportid INT,
div1airportseqid INT,
div1wheelson STRING,
div1totalgtime INT,
div1longestgtime INT,
div1wheelsoff STRING,
div1tailnum STRING,
div2airport STRING,
div2airportid INT,
div2airportseqid INT,
div2wheelson STRING,
div2totalgtime INT,
div2longestgtime INT,
div2wheelsoff STRING,
div2tailnum STRING,
div3airport STRING,
div3airportid INT,
div3airportseqid INT,
div3wheelson STRING,
div3totalgtime INT,
div3longestgtime INT,
div3wheelsoff STRING,
div3tailnum STRING,
div4airport STRING,
div4airportid INT,
div4airportseqid INT,
div4wheelson STRING,
div4totalgtime INT,
div4longestgtime INT,
div4wheelsoff STRING,
div4tailnum STRING,
div5airport STRING,
div5airportid INT,
div5airportseqid INT,
div5wheelson STRING,
div5totalgtime INT,
div5longestgtime INT,
div5wheelsoff STRING,
div5tailnum STRING
)
PARTITIONED BY (year STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
LOCATION 's3://athena-examples-myregion/flight/csv/';
```
このテーブルに新しいパーティションを追加するたびに、`MSCK REPAIR TABLE` を実行してパーティションメタデータを更新します。
```
MSCK REPAIR TABLE flight_delays_csv;
```
遅延が 1 時間を超えた上位 10 便をクエリします。
```
SELECT origin, dest, count(*) as delays
FROM flight_delays_csv
WHERE depdelayminutes > 60
GROUP BY origin, dest
ORDER BY 3 DESC
LIMIT 10;
```
**注記**
フライトテーブルデータは、米国運輸省の[運輸統計局](http://www.transtats.bts.gov/)が提供する [Flights](http://www.transtats.bts.gov/DL_SelectFields.asp?Table_ID=236&DB_Short_Name=On-Time) からのものです。オリジナルを白黒に変換しています。
## TSV の例
Amazon S3 に保存されている TSV データから Athena テーブルを作成するには、`ROW FORMAT DELIMITED` を使用して `\t` をタブフィールドの区切り文字、`\n` を区切り文字、および `\` をエスケープ文字として指定します。次の抜粋は構文例を示します。`athena-examples` の場所にはサンプルの TSV フライトデータはありませんが、CSV テーブルと同様に、`MSCK REPAIR TABLE` を実行して新しいパーティションが追加されるたびにパーティションメタデータを更新する必要があります。
```
...
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
...
```
# CSV を処理するための Open CSV SerDe
Open CSV SerDe を使用して、カンマ区切りデータ (CSV) データから Athena テーブルを作成します。
## シリアル化ライブラリ名
Open CSV SerDe のシリアル化ライブラリ名は `org.apache.hadoop.hive.serde2.OpenCSVSerde` です。ソースコード情報については、Apache ドキュメントの「[CSV SerDe](https://cwiki.apache.org/confluence/display/Hive/CSV+Serde)」を参照してください。
## Open CSV SerDe の使用
この SerDe を使用するには、その完全修飾されたクラス名を `ROW FORMAT SERDE` の後に指定します。次の例のように、`SERDEPROPERTIES` 内の区切り文字も指定します。
```
...
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = "`",
"escapeChar" = "\\"
)
```
### ヘッダーを無視する
テーブルを定義するときにデータ内のヘッダーを無視するには、以下の例にあるように、`skip.header.line.count` テーブルプロパティを使用できます。
```
TBLPROPERTIES ("skip.header.line.count"="1")
```
例については、「[Amazon VPC フローログをクエリする](vpc-flow-logs.md)」および「[Amazon CloudFront ログをクエリする](cloudfront-logs.md)」の `CREATE TABLE` ステートメントを参照してください。
### 無効なデータに NULL を使用する
列の定義済み型への逆シリアル化に失敗したデータに NULL 値を使用するには、次の例に示すように、 `use.null.for.invalid.data` テーブルプロパティを使用できます。
```
TBLPROPERTIES ("skip.header.line.count"="1")
```
**重要**
`use.null.for.invalid.data` を `TRUE` に設定すると、スキーマと一致しない列の無効なデータが、 `NULL` 値で置き換えられるため、誤った結果や予期しない結果が発生する可能性があります。このプロパティを有効にするのではなく、ファイルまたはテーブルスキーマ内のデータを修正することをお勧めします。このプロパティを有効にすると、無効なデータに対してクエリが失敗しないため、データ品質の問題を検出できなくなる可能性があります。
### 文字列データに関する考慮事項
Open CSV SerDe には、文字列データに関して次の特性があります。
+ デフルルトの引用文字は二重引用符 (`"`) を使用し、区切り文字、引用符、およびエスケープ文字を指定できます。
```
WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )
```
+ `\t` または `\n` を直接エスケープすることはできません。それらをエスケープするには、`"escapeChar" = "\\"` を使用します。例については、[Example: Escaping \t or \n](#csv-serde-opencsvserde-example-escaping-t-or-n)を参照してください。
+ Open CSV SerDe は、CSV ファイルの埋め込み改行をサポートしません。
### 文字列以外のデータに関する考慮事項
`STRING` 以外のデータ型の場合、Open CSV SerDe が次のように動作します。
+ `BOOLEAN`、`BIGINT`、`INT`、および `DOUBLE` データ型を認識します。
+ 数値データ型として定義された列の空値または null 値を認識せず、`string` として残します。回避策の 1 つは、null 値を `string` とした列を作成してから、`CAST` を使用してクエリのフィールドを数値データ型に変換して、null の場合にデフォルト値の `0` を指定することです。詳細については、AWS ナレッジセンターの「[Athena で CSV データをクエリすると、『HIVE\$1BAD\$1DATA: フィールド値の解析エラー』というエラーが表示されます](https://aws.amazon.com/premiumsupport/knowledge-center/athena-hive-bad-data-error-csv/)」を参照してください。
+ `CREATE TABLE` ステートメントで `timestamp` データ型で指定された列については、`1579059880000` などミリ秒単位の UNIX 数値形式で指定されている場合は `TIMESTAMP` データを認識します。例については、[Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format](#csv-serde-opencsvserde-example-timestamp-unix)を参照してください。
+ Open CSV SerDe は、`"YYYY-MM-DD HH:MM:SS.fffffffff"` (小数点以下 9 桁の精度) などの JDBC 準拠の `java.sql.Timestamp` 形式の `TIMESTAMP` をサポートしていません。
+ `CREATE TABLE` ステートメントで `DATE` データ型で指定された列については、値が 1970 年 1 月 1 日からの経過日数を表す場合、値を日付として認識します。例えば、`date` データ型の列の値 `18276` は、クエリを実行すると `2020-01-15` と出力されます。この UNIX 形式では、1 日は 86,400 秒です。
+ Open CSV SerDe では、これ以外の形式の `DATE` は直接サポートしていません。他の形式のタイムスタンプデータを処理するには、列を `string` として定義してから、時刻変換関数を使用して `SELECT` クエリで求める結果を返します。詳細については、[AWS ナレッジセンター](https://aws.amazon.com/premiumsupport/knowledge-center/)の「[Amazon Athena のテーブルにクエリを実行すると、TIMESTAMP の結果が空になる](https://aws.amazon.com/premiumsupport/knowledge-center/query-table-athena-timestamp-empty/)」を参照してください。
+ テーブル内の列を希望の型にさらに変換するには、テーブル上に[ビューを作成し](views.md)、`CAST` を使用して目的の型に変換します。
## 例
**Example 例: シンプルな CSV データをクエリする**
次の例では、CSV データが次の内容で場所 `s3://amzn-s3-demo-bucket/mycsv/` に保存されていることを前提としています。
```
"a1","a2","a3","a4"
"1","2","abc","def"
"a","a1","abc3","ab4"
```
`CREATE TABLE` ステートメントを使用して、このデータに基づいた Athena テーブルを作成します。`ROW FORMAT SERDE` の後に `OpenCSVSerde` を参照 (小文字の「d」に留意) し、次の例のように、`WITH SERDEPROPERTIES` で文字区切り文字、引用符、エスケープ文字を指定します。
```
CREATE EXTERNAL TABLE myopencsvtable (
col1 string,
col2 string,
col3 string,
col4 string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = ',',
'quoteChar' = '"',
'escapeChar' = '\\'
)
STORED AS TEXTFILE
LOCATION 's3://amzn-s3-demo-bucket/mycsv/';
```
テーブル内のすべての値に対してクエリを実行します。
```
SELECT * FROM myopencsvtable;
```
クエリは次の値を返します。
```
col1 col2 col3 col4
-----------------------------
a1 a2 a3 a4
1 2 abc def
a a1 abc3 ab4
```
**Example 例: UNIX 数値形式で指定された TIMESTAMP 型と DATE 型の使用**
次のカンマで区切ったデータから成る 3 つの列を検討してください。各列の値を二重引用符で囲みます。
```
"unixvalue creationdate 18276 creationdatetime 1579059880000","18276","1579059880000"
```
以下のステートメントは、指定された Amazon S3 バケットの場所から Athena にテーブルを作成します。
```
CREATE EXTERNAL TABLE IF NOT EXISTS testtimestamp1(
`profile_id` string,
`creationdate` date,
`creationdatetime` timestamp
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://amzn-s3-demo-bucket'
```
次に、以下のクエリを実行します。
```
SELECT * FROM testtimestamp1
```
クエリは、日付と時刻のデータを示す次の結果を返します。
```
profile_id creationdate creationdatetime
unixvalue creationdate 18276 creationdatetime 1579146280000 2020-01-15 2020-01-15 03:44:40.000
```
**Example 例: \$1t または \$1n のエスケープ**
以下のテストデータの場合を考えます。
```
" \\t\\t\\n 123 \\t\\t\\n ",abc
" 456 ",xyz
```
以下のステートメントは、Athena にテーブルを作成し、`"escapeChar" = "\\"` を指定します。
```
CREATE EXTERNAL TABLE test1 (
f1 string,
s2 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES ("separatorChar" = ",", "escapeChar" = "\\")
LOCATION 's3://amzn-s3-demo-bucket/dataset/test1/'
```
次に、以下のクエリを実行します。
```
SELECT * FROM test1;
```
この結果は、`\t` または `\n` を正しくエスケープして返されます。
```
f1 s2
\t\t\n 123 \t\t\n abc
456 xyz
```