Redshift Spectrum 的外部資料表

本主題說明如何透過 Redshift Spectrum 建立和使用外部資料表。外部資料表是您用來做為參考的資料表，以存取 Amazon Redshift 叢集外部的資料。這些資料表包含有關 Redshift Spectrum 讀取之外部資料的中繼資料。

您會在外部結構描述中建立外部資料表。您必須為外部結構描述的擁有者或超級使用者，始可建立外部資料表。若要轉移外部結構描述的所有權，請使用 ALTER SCHEMA 來變更擁有者。下列範例會將 spectrum_schema 結構描述的擁有者變更為 newowner。

alter schema spectrum_schema owner to newowner;

若要執行 Redshift Spectrum 查詢，您需要以下許可：

下列範例可在結構描述 spectrum_schema 上授予使用許可至 spectrumusers 使用者群組。

grant usage on schema spectrum_schema to group spectrumusers;

下列範例可在資料庫 spectrumdb 上授予臨時許可至 spectrumusers 使用者群組。

grant temp on database spectrumdb to group spectrumusers;

您可以在 Amazon Redshift AWS Glue、Amazon Athena 或 Apache Hive 中繼存放區中建立外部資料表。如需詳細資訊，請參閱《AWS Glue 開發人員指南》中的 AWS Glue使用入門、《Amazon Athena 使用者指南》中的 入門，或《Amazon EMR 開發人員指南》中的 Apache Hive。

如果您的外部資料表是在 AWS Glue、Athena 或 Hive 中繼存放區中定義，您首先會建立參考外部資料庫的外部結構描述。然後，您可以透過在資料表名稱前加上結構描述名稱來參考 SELECT 陳述句中的外部資料表，且無需在 Amazon Redshift 中建立資料表。如需詳細資訊，請參閱Amazon Redshift Spectrum 中的外部結構描述。

若要允許 Amazon Redshift 在 中檢視資料表 AWS Glue Data Catalog，請將 glue:GetTable新增至 Amazon Redshift IAM 角色。否則，您可能會得到如下的錯誤：

RedshiftIamRoleSession is not authorized to perform: glue:GetTable on resource: *;

例如，假設您在 Athena 外部目錄中定義了名為 lineitem_athena 的外部資料表。在此狀況中，您可定義一個名為 athena_schema 的外部結構描述，然後使用下列 SELECT 陳述式來查詢資料表。

select count(*) from athena_schema.lineitem_athena;

若要在 Amazon Redshift 中定義外部資料表，請使用 CREATE EXTERNAL TABLE 命令。外部資料表陳述句定義了資料表欄、資料檔案格式，以及 Amazon S3 中的資料位置。Redshift Spectrum 會掃描指定資料夾以及任何子資料夾裡的檔案。Redshift Spectrum 會忽略以句號、底線或井號 (.、_ 或 #) 開頭，或以波狀符號 (~) 結尾的檔案和隱藏檔案。

以下範例在名為 spectrum 的 Amazon Redshift 外部結構描述中建立名為 SALES 的資料表。該資料位於 Tab 鍵分隔的文字檔案中。

create external table spectrum.sales(
salesid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
row format delimited
fields terminated by '\t'
stored as textfile
location 's3://redshift-downloads/tickit/spectrum/sales/'
table properties ('numRows'='172000');

若要檢視外部資料表，請查詢 SVV_EXTERNAL_TABLES 系統畫面。

虛擬資料欄

Amazon Redshift 預設會以虛擬資料欄 $path、$size 和 $spectrum_oid 建立外部資料表。選擇 $path 欄可檢視 Amazon S3 上資料檔案的路徑，選擇 $size 欄可檢視查詢傳回的每列資料檔案大小。$spectrum_oid 欄提供使用 Redshift Spectrum 執行關聯查詢的能力。如需範例，請參閱「範例：在 Redshift Spectrum 中執行相互關聯子查詢」。您必須以雙引號分隔 $path、$size 和 $spectrum_oid 欄名稱。SELECT * 子句不會傳回虛擬資料欄。您必須在查詢中明確包含 $path、$size 和 $spectrum_oid 欄名稱，如以下範例所示。

select "$path", "$size", "$spectrum_oid"
from spectrum.sales_part where saledate = '2008-12-01';

您可以藉由將 spectrum_enable_pseudo_columns 組態參數設定為 false，以停用工作階段的虛擬資料欄建立。如需詳細資訊，請參閱spectrum_enable_pseudo_columns。您也可以將 enable_spectrum_oid 設定為 false，僅停用 $spectrum_oid 虛擬資料欄。如需詳細資訊，請參閱enable_spectrum_oid。不過，停用 $spectrum_oid 虛擬資料欄也會停用對 Redshift Spectrum 相關查詢的支援。

虛擬資料欄範例

下列範例會傳回外部資料表相關的資料檔案大小總和。

select distinct "$path", "$size"
from spectrum.sales_part;

 $path                                                                    | $size
--------------------------------------------------------------------------+-------
s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01/ |  1616
s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-02/ |  1444
s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03/ |  1644

分割 Redshift Spectrum 外部資料表

當您分割資料時，可以在分割區索引鍵上進行篩選來限制 Redshift Spectrum 掃描的資料量。您可透過任何索引鍵來分割您的資料。

常見做法是根據時間對資料進行分割。例如，您可以選擇依年、月、日和小時來進行分割。如果您有來自多個來源的資料，則可以按資料來源識別碼和日期進行分割。

下列程序說明如何分割您的資料。

分割資料範例

在此範例中，您將建立一個由單一分割區索引鍵值分割的外部資料表，以及由兩個索引鍵值所分割的外部資料表。

此範例的範例資料位於 Amazon S3 儲存貯體中，該儲存貯體提供所有已驗證 AWS 使用者的讀取存取權。您的叢集與外部資料檔案必須在相同的 AWS 區域。範例資料儲存貯體位於美國東部 (維吉尼亞北部) 區域 (us-east-1)。若要使用 Redshift Spectrum 存取資料，您的叢集必須也在 us-east-1 中。若要列以列出 Amazon S3 中的資料夾，請執行下列命令。

aws s3 ls s3://redshift-downloads/tickit/spectrum/sales_partition/

PRE saledate=2008-01/
PRE saledate=2008-03/
PRE saledate=2008-04/
PRE saledate=2008-05/
PRE saledate=2008-06/
PRE saledate=2008-12/

如果您沒有外部結構描述，請執行下列命令。將 Amazon Resource Name (ARN) 替換為您的 AWS Identity and Access Management (IAM) 角色。

create external schema spectrum
from data catalog
database 'spectrumdb'
iam_role 'arn:aws:iam::123456789012:role/myspectrumrole'
create external database if not exists;

範例 1：透過單一分割區索引鍵進行分割

在下列範例中，您會建立以月份進行分割的外部資料表。

若要建立以月份分割的外部資料表，請執行以下命令。

create external table spectrum.sales_part(
salesid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
partitioned by (saledate char(10))
row format delimited
fields terminated by '|'
stored as textfile
location 's3://redshift-downloads/tickit/spectrum/sales_partition/'
table properties ('numRows'='172000');

請執行下列 ALTER TABLE 命令以新增分割區。

alter table spectrum.sales_part add
partition(saledate='2008-01') 
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01/'

partition(saledate='2008-03') 
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03/'

partition(saledate='2008-04') 
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-04/';

若要從分割資料表選取資料，請執行下列查詢。

select top 5 spectrum.sales_part.eventid, sum(spectrum.sales_part.pricepaid) 
from spectrum.sales_part, event
where spectrum.sales_part.eventid = event.eventid
  and spectrum.sales_part.pricepaid > 30
  and saledate = '2008-01'
group by spectrum.sales_part.eventid
order by 2 desc;

eventid | sum     
--------+---------
   4124 | 21179.00
   1924 | 20569.00
   2294 | 18830.00
   2260 | 17669.00
   6032 | 17265.00

若要檢視外部資料表分割區，請查詢 SVV_EXTERNAL_PARTITIONS 系統畫面。

select schemaname, tablename, values, location from svv_external_partitions
where tablename = 'sales_part';

schemaname | tablename  | values      | location                                                                
-----------+------------+-------------+-------------------------------------------------------------------------
spectrum   | sales_part | ["2008-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01
spectrum   | sales_part | ["2008-03"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03
spectrum   | sales_part | ["2008-04"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-04

範例 2：透過多個分割區索引鍵進行分割

若要建立以 date 與 eventid 分割的外部資料表，請執行以下命令。

create external table spectrum.sales_event(
salesid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
partitioned by (salesmonth char(10), event integer)
row format delimited
fields terminated by '|'
stored as textfile
location 's3://redshift-downloads/tickit/spectrum/salesevent/'
table properties ('numRows'='172000');

請執行下列 ALTER TABLE 命令以新增分割區。

alter table spectrum.sales_event add
partition(salesmonth='2008-01', event='101') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-01/event=101/'

partition(salesmonth='2008-01', event='102') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-01/event=102/'

partition(salesmonth='2008-01', event='103') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-01/event=103/'

partition(salesmonth='2008-02', event='101') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-02/event=101/'

partition(salesmonth='2008-02', event='102') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-02/event=102/'

partition(salesmonth='2008-02', event='103') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-02/event=103/'

partition(salesmonth='2008-03', event='101') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-03/event=101/'

partition(salesmonth='2008-03', event='102') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-03/event=102/'

partition(salesmonth='2008-03', event='103') 
location 's3://redshift-downloads/tickit/spectrum/salesevent/salesmonth=2008-03/event=103/';

執行下列查詢以從分割的資料表選擇資料。

select spectrum.sales_event.salesmonth, event.eventname, sum(spectrum.sales_event.pricepaid) 
from spectrum.sales_event, event
where spectrum.sales_event.eventid = event.eventid
  and salesmonth = '2008-02'
	and (event = '101'
	or event = '102'
	or event = '103')
group by event.eventname, spectrum.sales_event.salesmonth
order by 3 desc;

salesmonth | eventname       | sum    
-----------+-----------------+--------
2008-02    | The Magic Flute | 5062.00
2008-02    | La Sonnambula   | 3498.00
2008-02    | Die Walkure     |  534.00

將外部資料表資料欄映射到 ORC 資料欄

您會使用 Amazon Redshift Spectrum 外部資料表來查詢採用 ORC 格式的檔案中的資料。最佳化列單欄式 (ORC) 格式為單欄式儲存檔案格式，支援巢狀資料結構。如需關於查詢巢狀資料的相關資訊，請參閱使用 Amazon Redshift Spectrum 查詢巢狀資料。

當您建立參考 ORC 檔案中資料的外部資料表時，您會將外部資料表中的每個資料欄映射到 ORC 資料中的資料欄。若要這麼做，您會使用下列其中一個方法：

依資料欄名稱映射為預設值。

依位置映射

利用位置映射，外部資料表中定義的第一個資料欄會映射到 ORC 資料檔案中的第一個資料欄，第二個映射到第二個，依此類推。依位置映射要求外部資料表中資料欄的順序與 ORC 檔案中的相符。如果資料欄的順序不相符，則可以依名稱映射資料欄。

alter table spectrum.orc_example 
set table properties('orc.schema.resolution'='position'); 

例如，資料表 SPECTRUM.ORC_EXAMPLE 的定義如下。

create external table spectrum.orc_example(
int_col int,
float_col float,
nested_col struct<
  "int_col" : int,
  "map_col" : map<int, array<float >>
   >
) stored as orc
location 's3://example/orc/files/';

資料表結構的摘要如下。

• 'int_col' : int
• 'float_col' : float
• 'nested_col' : struct
   o 'int_col' : int
   o 'map_col' : map
      - key : int
      - value : array
         - value : float

基礎 ORC 檔案具有下列檔案結構。

• ORC file root(id = 0)
   o 'int_col' : int (id = 1)
   o 'float_col' : float (id = 2)
   o 'nested_col' : struct (id = 3)
      - 'int_col' : int (id = 4)
      - 'map_col' : map (id = 5)
         - key : int (id = 6)
         - value : array (id = 7)
            - value : float (id = 8)

在此範例中，您可以嚴格依位置將外部資料表中的每個資料欄映射至 ORC 檔案中的資料欄。以下顯示映射。

依資料欄名稱映射

使用名稱映射，您可以將外部資料表中的資料欄映射至 ORC 檔案中相同層級、具有相同名稱的指定資料欄。

例如，假設您要將來自先前範例的資料表 SPECTRUM.ORC_EXAMPLE 與使用下列檔案結構的 ORC 檔案映射。

• ORC file root(id = 0)
   o 'nested_col' : struct (id = 1)
      - 'map_col' : map (id = 2)
         - key : int (id = 3)
         - value : array (id = 4)
            - value : float (id = 5)
      - 'int_col' : int (id = 6)
   o 'int_col' : int (id = 7)
   o 'float_col' : float (id = 8)

使用位置映射，Redshift Spectrum 會嘗試進行下列映射。

查詢具有前述位置映射的資料表時，SELECT 命令會在類型驗證時失敗，因為結構不同。

您可以使用資料欄名稱映射，將相同的外部資料表映射至先前範例中所示的檔案結構。資料表資料欄 int_col、float_col 和 nested_col 會依資料欄名稱映射至 ORC 檔案中具有相同名稱的資料欄。外部資料表中名為 nested_col 的資料欄為 struct 資料欄，具有的子資料欄名為 map_col 和 int_col。子資料欄也會依資料欄名稱正確映射至 ORC 檔案中的對應資料欄。

為在 Apache Hudi 中管理的資料建立外部資料表

若要查詢 Apache Hudi Copy On Write (CoW) 格式的資料，您可以使用 Amazon Redshift Spectrum 外部資料表。Hudi Copy On Write 資料表是儲存在 Amazon S3 中的 Apache Parquet 檔案的集合。您可以讀取 Apache Hudi 版本 0.5.2、0.6.0、0.7.0、0.8.0、0.9.0、0.10.0、0.10.1、0.11.0 和 0.11.1 中的 Copy On Write (CoW) 資料表，這些表是透過插入、刪除和更新插入寫入操作來建立和修改的。例如，不支援 Bootstrap 資料表。如需詳細資訊，請參閱開放原始碼 Apache Hudi 文件中的 Copy On Write 資料表。

當您建立參考 Hudi CoW 格式之資料的外部資料表時，您會將外部資料表中的每個欄映射到 Hudi 資料中的欄。映射是透過欄完成的。

分割和未分割 Hudi 資料表的資料定義語言 (DDL) 陳述式類似於其他 Apache Parquet 檔案格式的 DDL 陳述式。對於 Hudi 資料表，您可以將 INPUTFORMAT 定義為 org.apache.hudi.hadoop.HoodieParquetInputFormat。LOCATION 參數必須指向包含 .hoodie 資料夾的 Hudi 資料表基底資料夾，這是建立 Hudi 提交時間表所必需的。在某些情況下，Hudi 資料表上的 SELECT 操作可能會失敗，並顯示找不到有效的 Hudi 提交時間表訊息。若是如此，請檢查 .hoodie 資料夾是否位於正確的位置並包含有效的 Hudi 提交時間表。

定義未分割資料表的 DDL 具有下列格式。

CREATE EXTERNAL TABLE tbl_name (columns)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS
INPUTFORMAT 'org.apache.hudi.hadoop.HoodieParquetInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 's3://s3-bucket/prefix'

定義分割資料表的 DDL 具有下列格式。

CREATE EXTERNAL TABLE tbl_name (columns)
PARTITIONED BY(pcolumn1 pcolumn1-type[,...])
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS
INPUTFORMAT 'org.apache.hudi.hadoop.HoodieParquetInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 's3://s3-bucket/prefix'

若要將分割區新增至分割的 Hudi 資料表，請執行 ALTER TABLE ADD PARTITION 命令，其中 LOCATION 參數指向 Amazon S3 子資料夾，其中包含屬於該分割區的檔案。

新增分割區的 DDL 具有下列格式。

ALTER TABLE tbl_name
ADD IF NOT EXISTS PARTITION(pcolumn1=pvalue1[,...])
LOCATION 's3://s3-bucket/prefix/partition-path'

為在 Delta Lake 中管理的資料建立外部資料表

若要查詢 Delta Lake 資料表中的資料，您可以使用 Amazon Redshift Spectrum 外部資料表。

若要從 Redshift Spectrum 存取 Delta Lake 資料表，請在查詢前產生清單檔案。Delta Lake 清單檔案包含構成 Delta Lake 資料表一致快照的檔案清單。在分割資料表中，每個分割區都有一個清單檔案。Delta Lake 資料表是儲存在 Amazon S3 中的 Apache Parquet 檔案的集合。如需詳細資訊，請參閱開放原始碼 Delta Lake 文件中的 Delta Lake。

當您建立參考 Delta Lake 資料表中資料的外部資料表時，您會將外部資料表中的每個欄映射到 Delta Lake 資料表中的欄。映射是透過欄名稱完成的。

分割和未分割 Delta Lake 資料表的 DDL 類似於其他 Apache Parquet 檔案格式的 DDL。對於 Delta Lake 資料表，您可以將 INPUTFORMAT 定義為 org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat，以及將 OUTPUTFORMAT 定義為 org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat。LOCATION 參數必須指向資料表基底資料夾中的清單檔案資料夾。如果 Delta Lake 資料表上的 SELECT 操作失敗，可能的原因請參閱Delta Lake 資料表的限制和疑難排解。

定義未分割資料表的 DDL 具有下列格式。

CREATE EXTERNAL TABLE tbl_name (columns)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS
INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://s3-bucket/prefix/_symlink_format_manifest'

定義分割資料表的 DDL 具有下列格式。

CREATE EXTERNAL TABLE tbl_name (columns)
PARTITIONED BY(pcolumn1 pcolumn1-type[,...])
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS
INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://s3-bucket>/prefix/_symlink_format_manifest'

若要將分割區新增至分割的 Delta Lake 資料表，請執行 ALTER TABLE ADD PARTITION 命令，其中 LOCATION 參數指向包含分割區清單檔案的 Amazon S3 子資料夾。

新增分割區的 DDL 具有下列格式。

ALTER TABLE tbl_name
ADD IF NOT EXISTS PARTITION(pcolumn1=pvalue1[,...])
LOCATION
's3://s3-bucket/prefix/_symlink_format_manifest/partition-path'

或者執行直接指向 Delta Lake 清單檔案的 DDL。

ALTER TABLE tbl_name
ADD IF NOT EXISTS PARTITION(pcolumn1=pvalue1[,...])
LOCATION
's3://s3-bucket/prefix/_symlink_format_manifest/partition-path/manifest'

Delta Lake 資料表的限制和疑難排解

從 Redshift Spectrum 查詢 Delta Lake 資料表時，請考慮下列事項：

下表說明查詢 Delta Lake 資料表時出現某些錯誤的一些潛在原因。