CREATE EXTERNAL TABLE

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

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

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

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

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

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

所需權限

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

使用須知具有有關外部資料表特定權限的其他資訊。

語法

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 個資料表的限制，包括使用者定義的臨時資料表，以及 Amazon Redshift 在查詢處理或系統維護期間建立的臨時資料表。您也可以選擇使用資料庫名稱來限定資料表名稱。在以下範例中，資料庫名稱為 spectrum_db，外部結構描述名稱為 spectrum_schema，而資料表名稱為 test。


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

如果指定的資料庫或結構描述不存在，則不會建立資料表，而且陳述式會傳回錯誤。您無法在系統資料庫 template0、template1、padb_harvest 和 sys:internal 中建立資料表或檢視。

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

如需有效名稱的相關資訊，請參閱名稱與識別碼。

( 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)
VARBYTE (CHARACTER VARYING) – 可以與 Parquet 和 ORC 資料檔案一起使用，並且只能與未分割資料表一起使用。
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。例如，使用 mm-dd-yyyy 格式的日期 05-01-17 會轉換成 05-01-2017。
如果年份小於 100 且大於 69，則該年份的計算方式為年份加上 1900。例如，使用 mm-dd-yyyy 格式的日期 05-01-89 會轉換成 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...ADD PARTITION。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 的名稱。您可以指定下列格式：

或者. 阿帕奇. 哈多普. RegexSerDe
com. 亞馬遜. 葡萄糖. GrokSerDe
org.apache.hadoop.hive.serde2.OpenCSVSerde

這個參數支援下列 SerDe 屬性：
```
'wholeFile' = 'true' 
```
將 wholeFile 屬性設定為 true，以正確剖析 OpenCSV 請求中引號字串內的新行字元 (\n)。
組織. 開發. 數據. JsonSer德
- JSON SERDE 也支援 Ion 檔案。
- JSON 必須格式正確。
- 採用 Ion 和 JSON 的時間戳記必須使用 ISO8601 格式。
- 此參數支援下列 SerDe 屬性 JsonSerDe：
```
'strip.outer.array'='true' 
```
  處理包含含括在外部方括弧 ( [ … ] ) 中的一個非常大型陣列的 Ion/JSON 檔案，就好像在陣列內包含多個 JSON 記錄。
am. 亞馬遜。離子組織. IonHiveSerDe

除了資料類型外，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 (適用於文字檔案，包括 JSON 檔案)。
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 物件路徑清單的資訊清單檔案。儲存貯體必須與 Amazon 紅移叢集位於相同的 AWS 區域。如需支援的 AWS 區域清單，請參閱Amazon Redshift Spectrum 考量事項。

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

如果路徑指定的是資訊清單檔案，則 's3://bucket/manifest_file' 引數必須明確參考單一檔案，例如 's3://mybucket/manifest.txt'。無法參考金鑰前綴。

資訊清單是 JSON 格式的文字檔案，其中列出要從 Amazon S3 載入之每個檔案的 URL 以及檔案的大小 (單位為位元組)。URL 包含檔案的儲存貯體名稱和完整物件路徑。資訊清單中指定的檔案可以位於不同的值區中，但所有值區都必須與 Amazon Redshift 叢集位於相同的 AWS 區域中。如果某個檔案列出兩次，則該檔案會載入兩次。下列範例顯示資訊清單的 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 進行 COPY 的資訊清單檔案相容，但使用不同的索引鍵。未使用的索引鍵會加以忽略。

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

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

注意

資料表屬性區分大小寫。

'compression_type'='value'

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

bzip2
gzip
無
snappy

'data_cleansing_enabled'='true / false’

此屬性會設定資料表的資料處理是否開啟。當 'data_cleansing_enabled' 設定為 true 時，資料表的資料處理為開啟狀態。當 'data_cleansing_enabled' 設定為 false 時，資料表的資料處理為關閉狀態。以下是此屬性所控制的資料表層級資料處理屬性清單：

column_count_mismatch_handling
invalid_char_handling
numeric_overflow_handling
replacement_char
surplus_char_handling

如需範例，請參閱資料處理範例。

'invalid_char_handling'='value'

指定當查詢結果包含無效的 UTF-8 字元值時要執行的動作。您可以指定下列動作：

DISABLED: 不執行無效的字元處理。
FAIL: 取消傳回的資料包含無效 UTF-8 值的查詢。
SET_TO_NULL: 以 null 取代無效的 UTF-8 值。
DROP_ROW: 將資料列中的每個值取代為 null。
REPLACE: 以您使用 replacement_char 指定的取代字元取代無效字元。

'replacement_char'='character’

指定當您將 invalid_char_handling 設定為 REPLACE 時要使用的取代字元。

'numeric_overflow_handling'='value’

指定當 ORC 資料包含大於資料欄定義 (例如 SMALLINT 或 int16) 的整數 (例如，BIGINT 或 int64) 時，要執行的動作。您可以指定下列動作：

DISABLED: 無效字元處理已關閉。
FAIL: 當資料包含無效字元時，取消查詢。
SET_TO_NULL: 將無效字符設定為 null。
DROP_ROW: 將資料列中的每個值設定為 null。

'surplus_bytes_handling'='value'

針對包含 VARBYTE 資料的資料欄，指定當載入的資料超過所定義的資料類型長度時應如何處理。根據預設，Redshift Spectrum 會針對超出欄寬度的資料，將值設定為 null。

您可以指定當查詢傳回超過資料類型長度的資料時，執行下列動作：

SET_TO_NULL: 以 null 取代超過欄寬的資料。
DISABLED: 不執行多餘位元組處理。
FAIL: 取消傳回資料超出欄寬的查詢。
DROP_ROW: 捨棄包含超出欄寬之資料的所有資料列。
TRUNCATE: 如果字元超過為欄定義的字元數目上限，則移除字元。

'surplus_char_handling'='value'

針對包含 VARCHAR、CHAR 或字串資料的資料欄，指定當載入的資料超過所定義的資料類型長度時應如何處理。根據預設，Redshift Spectrum 會針對超出欄寬度的資料，將值設定為 null。

您可以指定當查詢傳回超過欄寬的資料時，執行下列動作：

SET_TO_NULL: 以 null 取代超過欄寬的資料。
DISABLED: 不執行多餘字元處理。
FAIL: 取消傳回資料超出欄寬的查詢。
DROP_ROW: 將資料列中的每個值取代為 null。
TRUNCATE: 如果字元超過為欄定義的字元數目上限，則移除字元。

'column_count_mismatch_handling'='value’

識別檔案包含的資料列值是否少於或多於外部資料表定義中指定的欄數。此屬性僅適用於未壓縮的文字檔案格式。您可以指定下列動作：

DISABLED: 欄計數不相符處理已關閉。
FAIL: 如果偵測到資料欄計數不相符，則查詢失敗。
SET_TO_NULL: 使用 NULL 填入遺漏值，並忽略每一列中的其他值。
DROP_ROW: 從掃描中捨棄包含欄計數不相符錯誤的所有資料列。

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

‘write.kms.key.id’=‘value’

您可以指定 AWS Key Management Service 金鑰來為 Amazon S3 物件啟用伺服器端加密 (SSE)，其中值為下列其中一項：

auto使用存放在 Amazon S3 儲存貯體中的預設 AWS KMS 金鑰。
您指定用來加密資料的 kms-key。

select_statement

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

範例

您可在範例取得範例集合。

您的瀏覽器已停用或無法使用 Javascript。

您必須啟用 Javascript，才能使用 AWS 文件。請參閱您的瀏覽器說明頁以取得說明。

文件慣用形式

CREATE EXTERNAL SCHEMA

使用須知