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

翻訳は機械翻訳により提供されています。提供された翻訳内容と英語版の間で齟齬、不一致または矛盾がある場合、英語版が優先します。

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 が次のように動作します。

  • BOOLEAN、BIGINT、INT、および 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 ステートメントを参照してください。

例

次の例では、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