用於處理 CSV 的 OpenCSVSerDe - Amazon Athena

用於處理 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 會有以下表現:

  • 識別 BOOLEANBIGINTINT,以及 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