CSV を処理するための OpenCSVSerDe - Amazon Athena

CSV を処理するための OpenCSVSerDe

CSV データ用の Athena テーブルを作成する場合、テーブルに含まれる値の形式に応じて SerDe を選択します。

  • データに二重引用符 (") で囲まれた値が含まれる場合は、Athena で OpenCSV SerDe を使用して値を逆シリアル化できます。データに二重引用符 (") で囲まれた値が含まれていない場合は、SerDe の指定を省略できます。この場合、Athena はデフォルトの LazySimpleSerDe を使用します。詳細については、「CSV、TSV、およびカスタム区切りファイルの LazySimpleSerDe」を参照してください。

  • データに UNIX の TIMESTAMP 数値 (1579059880000 など) がある場合は、OpenCSVSerDe を使用します。データが java.sql.Timestamp 形式を使用する場合は、LazySimpleSerDe を使用します。

CSV SerDe (OpenCSVSerDe)

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

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

    WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )
  • \t または \n を直接エスケープすることはできません。それらをエスケープするには、"escapeChar" = "\\" を使用します。このトピックの例を参照してください。

  • CSV ファイルの埋め込み改行はサポートしません。

STRING 以外のデータ型の場合、OpenCSVSerDe が次のように動作します。

  • BOOLEANBIGINTINT、および DOUBLE データ型を認識します。

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

  • CREATE TABLE ステートメントで timestamp データ型で指定された列については、1579059880000 などミリ秒単位の UNIX 数値形式で指定されている場合は TIMESTAMP データを認識します。

    • OpenCSVSerDe は、"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 を使用して目的の型に変換します。

例: 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://DOC-EXAMPLE-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://DOC-EXAMPLE-BUCKET/dataset/test1/'

次に、以下のクエリを実行します。

SELECT * FROM test1;

この結果は、\t または \n を正しくエスケープして返されます。

f1 s2 \t\t\n 123 \t\t\n abc 456 xyz

SerDe 名

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 ステートメントを参照してください。

Example

次の例では、CSV のデータが s3://DOC-EXAMPLE-BUCKET/mycsv/ に保存されており、コンテンツが以下のとおりであるとします。

"a1","a2","a3","a4" "1","2","abc","def" "a","a1","abc3","ab4"

CREATE TABLE ステートメントを使用して、このデータに基づいた Athena テーブルを作成します。以下の例にあるように、ROW FORMAT SERDE の後で OpenCSVSerDe クラスを参照し、文字の区切り文字、引用符文字、およびエスケープ文字を 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://DOC-EXAMPLE-BUCKET/mycsv/';

テーブル内のすべての値に対してクエリを実行します。

SELECT * FROM myopencsvtable;

クエリは次の値を返します。

col1 col2 col3 col4 ----------------------------- a1 a2 a3 a4 1 2 abc def a a1 abc3 ab4