# 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\_BAD\_DATA: フィールド値の解析エラー』というエラーが表示されます](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 例: \\t または \\n のエスケープ**
以下のテストデータの場合を考えます。
```
" \\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
```