用於處理 CSV 的 OpenCSVSerDe - Amazon Athena
CSV SerDe (OpenCSVSerDe)

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

用於處理 CSV 的 OpenCSVSerDe

建立 CSV 資料的 Athena 資料表時，請根據資料所含值的類型，判斷要使用哪個 SerDe：

  • 如果您資料包含的值含括在雙引號 (") 中，您可以使用 OpenCSV SerDe 在 Athena 中將值還原序列化。如果您資料不包含括在雙引號 (") 中的值，則可以省略指定任何 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。一種解決方法是建立帶有 null 值的資料欄作為 string，然後使用 CAST 將查詢中的欄位轉換為數字資料類型，並為 null 提供 0 預設值。如需詳細資訊，請參閱 AWS 知識中心中的在我查詢 Athena 中的 CSV 資料時，出現錯誤 HIVE_BAD_DATA：剖析欄位值時發生錯誤。

  • 對於使用 CREATE TABLE 陳述式中 timestamp 資料類型指定的資料欄，如果其是以 UNIX 數字格式指定 (以毫秒為單位)，例如 1579059880000，請識別 TIMESTAMP 資料。

    • OpenCSVSerDe 不支援使用 JDBC 相容 java.sql.Timestamp 格式的 TIMESTAMP，例如 "YYYY-MM-DD HH:MM:SS.fffffffff" (9 位小數精確度)。

  • 對於使用 CREATE TABLE 陳述式中 DATE 資料類型指定的資料欄，如果這些值代表 1970 年 1 月 1 日以來經過的天數，則請將這些值識別為日期。例如，資料欄中具有 date 資料類型的值 18276 會在查詢時呈現為 2020-01-15。在此 UNIX 格式中，每一天都會被認為有 86,400 秒。

  • 若要進一步將資料欄轉換為資料表中所需的類型，您可以對資料表建立檢視，並使用 CAST 來轉換為所需的類型。

範例：使用以 UNIX 數字格式指定的 TIMESTAMP (時間戳記) 類型和 DATE (日期) 類型。

請考慮以下以逗號分隔資料的三個資料欄。每個資料欄中的值皆以雙引號括住。

"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