CREATE EXTERNAL TABLE - Amazon Redshift

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

CREATE EXTERNAL TABLE

在指定的結構描述中建立新的外部資料表。所有外部資料表都必須經由外部結構描述建立。外部結構描述和外部資料表不支援搜尋路徑。如需詳細資訊,請參閱 CREATE EXTERNAL SCHEMA

您必須為外部結構描述的擁有者或超級使用者,始可建立外部資料表。若要轉移外部結構描述的所有權,請使用 ALTER SCHEMA 來變更擁有者。外部資料表的存取權是由外部結構描述的存取權所控制。您無法對外部資料表上的許可執行 GRANTREVOKE。但可改為在外部結構描述授予和撤銷 USAGE。

除了使用 CREATE EXTERNAL TABLE 命令建立的外部資料表之外,Amazon Redshift 還可參考AWS Glue或者AWS Lake Formation目錄或 Apache Hive 中繼存放區。使用CREATE EXTERNAL SCHEMA命令註冊外部目錄中定義的外部資料庫,並且在 Amazon Redshift 中將外部資料表提供使用。若外部資料表存在 AWS Glue 或 AWS Lake Formation 目錄或 Hive 中繼存放區中,您就不需要使用 CREATE EXTERNAL TABLE 建立該資料表。若要檢視外部資料表,請查詢 SVV_EXTERNAL_TABLES 系統畫面。

您可以執行 CREATE EXTERNAL TABLE AS 命令,您可以建立依據從查詢欄定義的外部資料表,並將該查詢的結果寫入 Amazon S3。結果為 Apache Parquet 或分隔文字格式。如果外部資料表有一個或多個分割區索引鍵,Amazon Redshift 會根據這些分割區索引鍵來分割新檔案,並自動將新的分割區註冊到外部目錄中。如需「建立外部資料表 AS」的詳細資訊,請參使用須知

您可以用與其他 Amazon Redshift 資料表一起使用的 SELECT 語法來查詢外部資料表。您也可以使用 INSERT 語法將新檔案寫入 Amazon S3 上外部資料表的位置。如需詳細資訊,請參閱 INSERT (外部資料表)

若要建立外部資料表的檢視,請在 CREATE VIEW 陳述式中包含 WITH NO SCHEMA BINDING 子句。

您無法在交易內 (BEGIN … END) 執行 CREATE EXTERNAL TABLE。如需交易的詳細資訊,請參閱 可序列化隔離

Syntax (語法)

CREATE EXTERNAL TABLE
external_schema.table_name  
(column_name data_type [, …] )
[ PARTITIONED BY (col_name data_type [, … ] )] 
[ { ROW FORMAT DELIMITED row_format |
  ROW FORMAT SERDE 'serde_name' 
  [ WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ] } ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]

以下是 CREATE EXTERNAL TABLE AS 的語法。

CREATE EXTERNAL TABLE
external_schema.table_name  
[ PARTITIONED BY (col_name [, … ] ) ] 
[ ROW FORMAT DELIMITED row_format ]
STORED AS file_format
LOCATION { 's3://bucket/folder/' }
[ TABLE PROPERTIES ( 'property_name'='property_value' [, ...] ) ]
 AS
 { select_statement }
         

參數

external_schema.table_name

要建立的資料表名稱,以外部結構描述名稱限定。外部資料表必須建立在外部結構描述中。如需詳細資訊,請參閱 CREATE EXTERNAL SCHEMA

資料表名稱的長度上限為 127 個位元組;超過此長度的名稱會截斷至 127 個位元組。您可以使用 UTF-8 多位元組字元,最長可達 4 個位元組。Amazon Redshift 會強制執行每個叢集 9,900 個資料表的限制,包括使用者定義的暫時資料表,以及在查詢處理或系統維護期間建立的暫時資料表。您也可以選擇使用資料庫名稱來限定資料表名稱。在以下範例中,資料庫名稱為 spectrum_db,外部結構描述名稱為 spectrum_schema,而資料表名稱為 test

create external table spectrum_db.spectrum_schema.test (c1 int) stored as parquet location 's3://mybucket/myfolder/';

如果指定的資料庫或結構描述不存在,則不會建立資料表,而且陳述式會傳回錯誤。您無法在系統資料庫 template0template1padb_harvest 中建立資料表或檢視。

資料表名稱對於指定的結構描述來說必須是唯一的。

如需有效名稱的詳細資訊,請參閱 名稱與識別符

( column_name data_type )

要建立的每個資料欄的名稱和資料類型。

資料欄名稱的長度上限為 127 個位元組;超過此長度的名稱會截斷至 127 個位元組。您可以使用 UTF-8 多位元組字元,最長可達 4 個位元組。您無法指定資料欄名稱 "$path""$size"。如需有效名稱的詳細資訊,請參閱 名稱與識別符

Amazon Redshift 會以虛擬資料欄建立外部資料表$path$size。您可以藉由將 spectrum_enable_pseudo_columns 組態參數設定為 false,以停用工作階段的虛擬資料欄建立。如需詳細資訊,請參閱 虛擬資料欄

若已啟用虛擬資料欄,則單一資料表中可定義的資料欄數目上限為 1,598 個。若未啟用虛擬資料欄,則單一資料表中可定義的資料欄數目上限為 1,600 個。

若您要建立「寬資料表」,則務必確定在載入和查詢處理期間,您的資料欄清單未超過中繼結果的資料列寬度界限。如需詳細資訊,請參閱 使用須知

針對 CREATE EXTERNAL TABLE AS 命令,您不需要欄位清單,因為欄是從查詢中衍生的。

data_type

支援以下 資料類型

  • SMALLINT (INT2)

  • INTEGER (INT、INT4)

  • BIGINT (INT8)

  • DECIMAL (NUMERIC)

  • REAL (FLOAT4)

  • DOUBLE PRECISION (FLOAT8)

  • BOOLEAN (BOOL)

  • CHAR (CHARACTER)

  • VARCHAR (CHARACTER VARYING)

  • DATE (DATE 資料類型只可搭配文字、Parquet 或 ORC 資料檔案使用,或做為分割區資料欄使用)

  • TIMESTAMP

對於 DATE,您可以使用格式,如下所述。使用數字表示的月份資料表示,支援下列格式:

  • mm-dd-yyyy 例如:05-01-2017。此為預設值。

  • yyyy-mm-dd,其中年份由 2 位以上的數字表示。例如:2017-05-01

對於使用三個字母縮寫表示的月份值,支持以下格式:

  • mmm-dd-yyyy 例如:may-01-2017。此為預設值。

  • dd-mmm-yyyy,其中年份由 2 位以上的數字表示。例如:01-may-2017

  • yyyy-mmm-dd,其中年份由 2 位以上的數字表示。例如:2017-may-01

對於始終小於 100 的年份值,將按以下方式計算年份:

  • 如果年份小於 70,則該年的計算方式為年份加 2000 年。例如,日期 05-01-17 在mm-dd-yyyy格式轉換為05-01-2017

  • 如果年度小於 100 且大於 69,則年份計算為年加 1900 年。例如,日期 05-01-89 在mm-dd-yyyy格式轉換為05-01-1989

  • 對於由兩位數表示的年份值,添加前導零以 4 位數表示年份。

文字檔案中的時間戳記值格式必須為 yyyy-mm-dd HH:mm:ss.SSSSSS,如以下的時間戳記值所示:2017-05-01 11:30:59.000000

VARCHAR 資料欄的長度是以字元組而非字元來定義。例如,VARCHAR(12) 資料欄可包含 12 個單位元組的字元或 6 個 2 位元組的字元。查詢外部資料表時,結果會截斷以配合定義的資料欄大小,而不會傳回錯誤。如需詳細資訊,請參閱 儲存與範圍

為獲得最佳效能,建議您指定可配合您的資料的最小資料欄大小。若要尋找資料欄中值位元組的大小上限,請使用 OCTET_LENGTH 函數。下列範例會傳回電子郵件資料欄中值的大小上限。

select max(octet_length(email)) from users; max --- 62
PARTITIONED BY (col_name data_type [, … ] )

此子句會定義包含一個或多個分割區資料欄的分割資料表。每種指定的組合都會另外使用一個資料目錄,這樣可在某些情況下改善查詢效能。資料表資料內並未包含分割資料欄。如果您對 col_name 使用的值與資料表資料欄相同,則會發生錯誤。

建立分割資料表後,使用 ALTER TABLE … ADD PARTITION 陳述式以將新增分割區註冊到外部目錄。當您新增分割區時,會定義 Amazon S3 上包含分割區資料的子資料夾位置。

例如,若資料表 spectrum.lineitem_part 是以 PARTITIONED BY (l_shipdate date) 定義,執行下列 ALTER TABLE 命令來新增分割區。

ALTER TABLE spectrum.lineitem_part ADD PARTITION (l_shipdate='1992-01-29') LOCATION 's3://spectrum-public/lineitem_partition/l_shipdate=1992-01-29';

如果您使用 CREATE EXTERNAL TABLE AS,則不需要執行 ALTER TABLE ... 添加分區。Amazon Redshift 會自動在外部目錄中註冊新的分割區。Amazon Redshift 還會根據資料表中定義的分割區索引鍵,自動將相應的資料寫入 Amazon S3 中的分割區。

若要檢視分割區,請查詢 SVV_EXTERNAL_PARTITIONS 系統畫面。

注意

針對 CREATE EXTERNAL TABLE AS 命令,您不需要指定分割區欄位的資料類型,因為此欄位是從查詢衍生的。

ROW FORMAT DELIMITED rowformat

此子句會指定基礎資料的格式。rowformat 可能的值如下:

  • LINES TERMINATED BY 'delimiter'

  • FIELDS TERMINATED BY 'delimiter'

指定單一 ASCII 字元做為 'delimiter'。您可以使用八進位指定非印刷 ASCII 字元,格式為 '\ddd',其中 d 是八進位數字 (0 – 7),最大為 ‘\177’。以下範例使用八進位指定 BEL (鐘形) 字元。

ROW FORMAT DELIMITED FIELDS TERMINATED BY '\007'

如果省略 ROW FORMAT,預設格式會是 DELIMITED FIELDS TERMINATED BY '\A' (標題開頭) 和 LINES TERMINATED BY '\n' (換行符號)。

ROW FORMAT SERDE 'serde_name'
[WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ]

此子句會指定基礎資料的 SERDE 格式。

'serde_name'

SerDe 的名稱。您可以指定下列格式:

  • org.apache.hadoop.hive.serde2.RegexSerDe

  • com.amazonaws.glue.serde.GrokSerDe

  • org.apache.hadoop.hive.serde2.OpenCSVSerde

    此參數支援下列選項: SerDe 屬性:

    'wholeFile' = 'true'

    wholeFile屬性設置為true來正確解析 OpenCSV 請求的引號字符串中的新行字符 (\ n)。

  • org.openx.data.jsonserde.JsonSerDe

    • JSON SERDE 也支援 Ion 檔案。

    • JSON 必須格式正確。

    • 採用 Ion 和 JSON 的時間戳記必須使用 ISO8601 格式。

    • 此參數支援下列選項: SerDe 屬 JsonSerDe:

      'strip.outer.array'='true'

      處理包含含括在外部方括弧 ( [ … ] ) 中的一個非常大型陣列的 Ion/JSON 檔案,就好像在陣列內包含多個 JSON 記錄。

  • 亞馬遜分離器

    除了數據類型之外,Amazon ION 格式還提供文本和二進制格式。對於參考 ION 格式資料的外部資料表,您可以將外部資料表中的每個資料欄映射到 ION 格式資料中的相應元素。如需詳細資訊,請參閱「」Amazon Ion。您還需要指定輸入和輸出格式。

WITH SERDEPROPERTIES ( 'property_name' = 'property_value' [, ...] ) ]

選擇性地指定屬性名稱和值,並以逗號分隔。

如果省略 ROW FORMAT,預設格式會是 DELIMITED FIELDS TERMINATED BY '\A' (標題開頭) 和 LINES TERMINATED BY '\n' (換行符號)。

STORED AS file_format

資料檔案的檔案格式。

有效格式如下:

  • PARQUET

  • RCFILE(如需使用 ColumnarSerDe 只有, 不適用 LazyBinaryColumnarSerDe)

  • SEQUENCEFILE

  • TEXTFILE

  • ORC

  • AVRO

  • INPUTFORMAT 'input_format_classname' OUTPUTFORMAT 'output_format_classname'

CREATE EXTERNAL TABLE AS 命令只支援兩種檔案格式,TEXTFILE 和 PARQUET。

若是 INPUTFORMAT 和 OUTPUTFORMAT,請指定類別名稱,如以下範例所示。

'org.apache.hadoop.mapred.TextInputFormat'
LOCATION { 's3://bucket/folder/' | 's3://bucket/manifest_file'}

包含資料檔案的 Amazon S3 存儲段或資料夾路徑,或包含 Amazon S3 物件路徑清單的資訊清單檔案。儲存貯體必須位於相同AWS區域為 Amazon Redshift 叢集。如需支援的清單AWS區域,請參閲Amazon Redshift Spectrum 注意事項

如果路徑指定了存儲桶或文件夾,例如's3://mybucket/custdata/',則 Redshift Spectrum 會掃描指定儲存貯體或資料夾以及任何子資料夾中的檔案。Redshift Spectrum 會忽略隱藏檔案以及開頭為句號或底線的檔案。

如果路徑指定的是資訊清單檔案,'s3://bucket/manifest_file'參數必須顯式引用單個文件-例如's3://mybucket/manifest.txt'。無法參考金鑰前綴。

資訊清單是 JSON 格式的文字檔案,其中列出要從 Amazon S3 載入之每個檔案的 URL 以及檔案的大小 (單位為位元組)。URL 包含檔案的儲存貯體名稱和完整物件路徑。資訊清單中指定的檔案可以位於不同儲存貯體,但所有儲存貯體必須位於相同的AWS區域為 Amazon Redshift 叢集。如果某個檔案列出兩次,則該檔案會載入兩次。下列範例顯示資訊清單的 JSON,此資訊清單會載入三個檔案。

{ "entries": [ {"url":"s3://mybucket-alpha/custdata.1", "meta": { "content_length": 5956875 } }, {"url":"s3://mybucket-alpha/custdata.2", "meta": { "content_length": 5997091 } }, {"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } } ] }

您可以讓包含特定檔案變為強制性。若要這麼做,請在資訊清單中的檔案層級包含 mandatory 選項。當您查詢遺失強制性檔案的外部資料表時,SELECT 陳述式會失敗。確定外部資料表定義中包含的所有檔案均存在。如果並非全部存在,則會出現錯誤,顯示找不到第一個強制性檔案。下列範例顯示資訊清單的 JSON,其 mandatory 設定為 true

{ "entries": [ {"url":"s3://mybucket-alpha/custdata.1", "mandatory":true, "meta": { "content_length": 5956875 } }, {"url":"s3://mybucket-alpha/custdata.2", "mandatory":false, "meta": { "content_length": 5997091 } }, {"url":"s3://mybucket-beta/custdata.1", "meta": { "content_length": 5978675 } } ] }

若要參考使用 UNLOAD 建立的檔案,您可以使用以 UNLOAD 搭配 MANIFEST 參數所建立的資訊清單。資訊清單檔案與 從 Amazon S3 中複製 的資訊清單檔案相容,但使用不同的索引鍵。未使用的索引鍵會加以忽略。

TABLE PROPERTIES ( 'property_name'='property_value' [, ...] )

此子句會設定資料表屬性的資料表定義。

注意

資料表屬性區分大小寫。

'compression_type'='value'

如果檔案名稱未包含副檔名,此屬性會設定要使用的壓縮類型。如果您設定此屬性,但是有副檔名,則會忽略副檔名並使用屬性所設定的值。壓縮類型的有效值如下所示:

  • bzip2

  • gzip

  • snappy

'數據清理啟用 =' 真/假 '

此屬性設置表的數據處理是否打開。如果「啟用了數據清理」設置為 true,則表的數據處理處於啟用狀態。如果「啟用了數據清理」設置為假,則表的數據處理處於關閉狀態。以下是由此屬性控制的表級數據處理方向的列表:

  • 無效處理

  • 替換_char

  • 盈餘處理

  • 數值流量處理

如需範例,請參閱「資料處理示例」。

'無效 _ 查詢處理' = ''

指定查詢結果包含無效 UTF-8 字符值時要執行的操作。您可以指定下列操作:

DISABLED

不執行無效字符處理。

失敗

取消返回包含無效 UTF-8 值的數據的查詢。

設置為空

將無效的 UTF-8 值替換為空值。

刪除行

將行中的每個值替換為 null。

REPLACE

將無效字符替換為使用replacement_char

'替換 _char'='character'

指定在設置invalid_char_handlingREPLACE

'數字溢出處理' = '值'

指定當 ORC 數據包含大於列定義(例如,BIGINT 或 int64)的整數(例如,SLOMINT 或 int16)時要執行的操作。您可以指定下列操作:

DISABLED

關閉無效字符處理。

失敗

當數據包含無效字符時,取消查詢。

設置為空

將無效字符設置為空。

刪除行

將行中的每個值設置為空。

'盈餘處理' = ''

指定如何處理正在加載的數據超出為包含 VARCHAR、CHAR 或字符串數據的列定義的數據類型長度的數據。默認情況下,對於超出列寬度的數據,Redshift 頻譜將值設置為空。

當查詢返回超過列寬的數據時,您可以指定要執行的以下操作:

設置為空

將超過列寬的數據替換為 null。

DISABLED

不執行剩餘字符處理。

失敗

取消返回超過列寬的數據的查詢。

刪除行

將行中的每個值替換為 null。

TRUNCATE

刪除超過為列定義的最大字符數的字符。

'numRows'='row_count'

此屬性會設定資料表定義的 numRows 值。若要明確更新外部資料表的統計資訊,請設定 numRows 屬性以指出資料表的大小。Amazon Redshift 不會分析外部資料表來產生資料表統計資訊 (該統計資訊可供查詢最佳化工具用來產生查詢計畫)。如果未設定外部資料表的資料表統計資訊,Amazon Redshift 會根據「外部資料表較大而本機資料表較小」的假設來產生查詢執行計畫。

'skip.header.line.count'='line_count'

此屬性會設定每個來源檔案開頭要略過的資料列數。

'serialization.null.format'=' '

此屬性會指定,欄位中提供的文字有完全相符項目時,Spectrum 應傳回 NULL 值。

'orc.schema.resolution'='mapping_type'

此屬性會為使用 ORC 日期格式的資料表設定資料欄映射類型。將針對其他所有日期格式忽略此屬性。

資料欄映射類型的有效值如下所示:

  • name

  • position

如果忽略 orc.schema.resolution 屬性,則預設會依名稱映射資料欄。如果 orc.schema.resolution 設為 'name''position' 以外的任何值,則會依位置映射資料欄。如需資料欄映射的詳細資訊,請參閱將外部資料表資料欄映射到 ORC 資料欄

注意

COPY 命令只會依位置映射至 ORC 資料檔案。orc.schema.resolution 資料表屬性對於 COPY 命令行為沒有影響。

'write.parallel'='on/off’

設定是否將 CREATE EXTERNAL TABLE AS 平行寫入資料的屬性。依預設,CREATE EXTERNAL TABLE AS 會根據叢集中的分割數,將資料平行寫入多個檔案。預設選項為開啟。當 'write.parallel' 設為關閉時,CREATE EXTERNAL TABLE AS 會連續寫入一個或多個資料檔案到 Amazon S3。此表格屬性也適用於相同外部資料表的任何後續 INSERT 陳述式。

‘write.maxfilesize.mb’=‘size’

設定由 CREATE EXTERNAL TABLE AS 寫入 Amazon S3 的每個檔案之大小上限 (以 MB 為單位) 屬性。大小必須是介於 5 到 6200 之間的有效整數。預設的檔案大小上限為 6,200 MB。此表格屬性也適用於相同外部資料表的任何後續 INSERT 陳述式。

'寫入 .km.key .id' = ''

您可以指定AWS Key Management Service密鑰為 Amazon S3 對象啟用服務器端加密 (SSE),其中是下列其中一項:

  • auto使用預設AWS KMS存放在 Amazon S3 儲存貯體中的金鑰。

  • KMS 金鑰來加密數據。

select_statement

透過定義任何查詢,將一或多個列插入外部資料表的陳述式。查詢產生的所有列都會根據資料表定義,以文字或 Parquet 格式寫入 Amazon S3。