開啟 CSV SerDe 以處理 CSV - Amazon Athena
序列化程式庫名稱使用開啟 CSV SerDe範例

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

開啟 CSV SerDe 以處理 CSV

使用開啟 CSV SerDe 從逗號分隔資料 (CSV) 資料建立 Athena 資料表。

序列化程式庫名稱

Open CSV SerDe 的序列化程式庫名稱為 org.apache.hadoop.hive.serde2.OpenCSVSerde。如需來源碼資訊，請參閱 Apache 文件中的 CSV 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 陳述式。

針對無效資料使用 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」。

  • Open CSV SerDe 不支援 CSV 檔案中的內嵌換行符號。

非字串資料的考量事項

對於 以外的資料類型STRING，Open CSV SerDe 的行為如下：

  • 識別 BOOLEAN、BIGINT、INT，以及 DOUBLE 資料類型。

  • 無法識別資料欄中定義為數值資料類型的空值或 null 值，而會將其保留為 string。一種解決方法是建立帶有 null 值的資料欄作為 string，然後使用 CAST 將查詢中的欄位轉換為數字資料類型，並為 null 提供 0 預設值。如需詳細資訊，請參閱 AWS 知識中心中的當我在 Athena 中查詢 CSV 資料時，會收到錯誤 HIVE_BAD_DATA：剖析欄位值時發生錯誤。

  • 對於使用 CREATE TABLE 陳述式中 timestamp 資料類型指定的資料欄，如果其是以 UNIX 數字格式指定 (以毫秒為單位)，例如 1579059880000，請識別 TIMESTAMP 資料。如需範例，請參閱「Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format」。

    • Open CSV SerDe 不支援 TIMESTAMP JDBC 相容java.sql.Timestamp格式，例如 "YYYY-MM-DD HH:MM:SS.fffffffff"(9 位小數精確度）。

  • 對於使用 CREATE TABLE 陳述式中 DATE 資料類型指定的資料欄，如果這些值代表 1970 年 1 月 1 日以來經過的天數，則請將這些值識別為日期。例如，資料欄中具有 date 資料類型的值 18276 會在查詢時呈現為 2020-01-15。在此 UNIX 格式中，每一天都會被認為有 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 資料表。參考 OpenCSVSerde（請注意小寫的 "d")，ROW FORMAT SERDE並在 中指定字元分隔符號、引號字元和逸出字元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 類型

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

"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