CSV を処理するための Open CSV SerDe - Amazon Athena
シリアル化ライブラリ名Open CSV SerDe の使用例

CSV を処理するための Open CSV SerDe

Open CSV SerDe を使用して、カンマ区切りデータ (CSV) データから Athena テーブルを作成します。

シリアル化ライブラリ名

Open CSV SerDe のシリアル化ライブラリ名は org.apache.hadoop.hive.serde2.OpenCSVSerde です。ソースコード情報については、Apache ドキュメントの「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 フローログのクエリ」および「Amazon CloudFront ログのクエリ」の CREATE TABLE ステートメントを参照してください。

文字列データに関する考慮事項

Open CSV SerDe には、文字列データに関して次の特性があります。

  • デフルルトの引用文字は二重引用符 (") を使用し、区切り文字、引用符、およびエスケープ文字を指定できます。

    WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )

  • \t または \n を直接エスケープすることはできません。それらをエスケープするには、"escapeChar" = "\\" を使用します。例については、「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: フィールド値の解析エラー』というエラーが表示されます」を参照してください。

  • CREATE TABLE ステートメントで timestamp データ型で指定された列については、1579059880000 などミリ秒単位の UNIX 数値形式で指定されている場合は TIMESTAMP データを認識します。例については、「Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format」を参照してください。

    • 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 秒です。

  • テーブル内の列を希望の型にさらに変換するには、テーブル上にビューを作成し、CAST を使用して目的の型に変換します。

例

例: シンプルな 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
例: 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
例: \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