本文為英文版的機器翻譯版本,如內容有任何歧義或不一致之處,概以英文版為準。
開啟 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 的行為如下:
-
識別
BOOLEAN
、BIGINT
、INT
,以及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 秒。-
Open CSV SerDe 不支援
DATE
任何其他直接格式。若要處理其他格式的時間戳記資料,您可以將資料欄定義為string
,然後使用時間轉換函數在SELECT
查詢中傳回所需結果。如需詳細資訊,請參閱 AWS 知識中心中的當我在 Amazon Athena 中查詢資料表時,TIMESTAMP 結果為空 。
-
-
若要進一步將資料欄轉換為資料表中所需的類型,您可以對資料表建立檢視,並使用
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