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

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

開放CSV SerDe 進行處理 CSV

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

序列化程式庫名稱

O CSV SerDe pen 的序列化程式庫名稱為。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。

  • 「開啟」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資料類型指定的TIMESTAMP資料行，如果資料是以毫秒為單位指定的UNIX數值格式，例如1579059880000。如需範例，請參閱Example: Using the TIMESTAMP type and DATE type specified in the UNIX numeric format。

    • Open CSV SerDe 不支援TIMESTAMPJDBC相容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 查詢中傳回所需結果。如需詳細資訊，請參閱文章當我在 Amazon Athena 查詢資料表時，AWS 知識中心中的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
範例：使用以TIMESTAMPUNIX數字格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