開啟 CSV SerDe 以處理 CSV - Amazon Athena

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

開啟 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 陳述式。

字串資料的考量

Open CSV SerDe 具有下列字串資料的特性:

  • 使用雙引號 (") 做為預設的引號字元,並且可讓您指定分隔符號、引號和逸出字元,例如:

    WITH SERDEPROPERTIES ("separatorChar" = ",", "quoteChar" = "`", "escapeChar" = "\\" )
  • 您無法逸出\t\n直接逸出。若要將它們逸出,請使用 "escapeChar" = "\\"。如需範例,請參閱「Example: Escaping \t or \n」。

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

非字串資料的考量事項

對於 以外的資料類型STRING,開啟 CSV SerDe 的行為如下:

  • 識別 BOOLEANBIGINTINT,以及 DOUBLE 資料類型。

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

  • 對於使用 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