建立 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 使用者指南, 或Apache Hive中的Amazon EMR 開發人員指南

如果您的外部表在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: *;

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

select count(*) from athena_schema.lineitem_athena;

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

下列範例在 Amazon Redshift 外部結構中建立名為 SALES 的資料表,名為spectrum。該資料位於 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://awssampledbuswest2/tickit/spectrum/sales/' table properties ('numRows'='172000');

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

虛擬資料欄

Amazon Redshift 會以虛擬資料欄建立外部資料表$path$size,以及$spectrum_oid。選擇$path欄位以檢視 Amazon S3 上資料檔案的路徑,然後選擇$size欄位以檢視由查詢返回的每列資料檔案大小。所以此$spectrum_oid列提供了使用 Redshift 頻譜執行相關查詢的功能。如需範例,請參閱 範例:在 Redshift 頻譜中執行相關子查詢。您必須將$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_oid偽列通過設置enable_spectrum_oid至假的。如需詳細資訊,請參閱 啟用頻譜號。但是,禁用$spectrum_oid偽列也禁用對 Redshift 頻譜相關查詢的支持。

重要

選取$size$path, 或$spectrum_oid會產生費用,因為 Redshift Spectrum 會掃描 Amazon S3 上的資料檔案以判斷結果集的大小。如需詳細資訊,請參閱「」Amazon Redshift 定價

虛擬資料欄範例

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

select distinct "$path", "$size" from spectrum.sales_part; $path | $size ---------------------------------------+------- s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/ | 1616 s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/ | 1444 s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03/ | 1644

分割 Redshift Spectrum 外部資料表

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

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

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

分割您的資料

  1. 根據您的分割區域鍵將資料存放在 Amazon S3 中的檔案夾中。

    為每個分割區值建立一個資料夾,並使用分割區索引鍵和值命名該資料夾。例如,若您要依日期分割資料,您可能有名為 saledate=2017-04-01saledate=2017-04-02 的資料夾,依此類推。Redshift Spectrum 會掃描分割區資料夾以及任何子資料夾裡的檔案。Redshift Spectrum 會忽略以句號、底線或井號 (.、_ 或 #) 開頭,或以波狀符號 (~) 結尾的檔案和隱藏檔案。

  2. 建立外部資料表,並在 PARTITIONED BY 子句中指定分割區索引鍵。

    分割區索引鍵不得為資料表欄位的名稱。資料類型可能是 SMALLINT、INTEGER、BIGINT、DECIMAL、REAL、DOUBLE PRECISION、BOOLEAN、CHAR、VARCHAR、DATE、TIMESTAMP。

  3. 新增分割區。

    使用ALTER TABLE... 添加分割區,添加每個分割區,指定分割區欄位和密鑰值,以及分割區資料夾在 Amazon S3 中的位置。您可以在單一 ALTER TABLE … ADD 陳述式中新增多個分割區。以下範例將為 '2008-01''2008-02' 新增分割區。

    alter table spectrum.sales_part add partition(saledate='2008-01-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/' partition(saledate='2008-02-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/';
    注意

    如果您使用 AWS Glue 目錄,則可以使用 ALTER TABLE 陳述式新增最多 100 個分割區。

分割資料範例

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

此範例的範例資料位於 Amazon S3 儲存貯體中,該儲存貯體可提供對所有經過身份驗證的讀取權限AWS用户。您的叢集與外部資料檔案必須在相同的AWS區域。範例資料儲存貯體會在美國西部 (奧勒岡) 區域 (us-west-2)。要使用 Redshift Spectrum 存取資料,您的叢集必須也在 us-west-2 中。若要在 Amazon S3 中列出資料夾,請執行以下命令。

aws s3 ls s3://awssampledbuswest2/tickit/spectrum/sales_partition/
PRE saledate=2008-01/
PRE saledate=2008-02/
PRE saledate=2008-03/

如果您沒有外部結構描述,請執行下列命令。將 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://awssampledbuswest2/tickit/spectrum/sales_partition/' table properties ('numRows'='172000');

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

alter table spectrum.sales_part add partition(saledate='2008-01') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01/' partition(saledate='2008-02') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02/' partition(saledate='2008-03') location 's3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03/';

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

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://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-01
spectrum   | sales_part | ["2008-02"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-02
spectrum   | sales_part | ["2008-03"] | s3://awssampledbuswest2/tickit/spectrum/sales_partition/saledate=2008-03

範例 2:使用多個分割區鍵進行分割

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

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://awssampledbuswest2/tickit/spectrum/salesevent/' table properties ('numRows'='172000');

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

alter table spectrum.sales_event add partition(salesmonth='2008-01', event='101') location 's3://awssampledbuswest2/tickit/spectrum/salesevent/salesmonth=2008-01/event=101/' partition(salesmonth='2008-01', event='102') location 's3://awssampledbuswest2/tickit/spectrum/salesevent/salesmonth=2008-01/event=102/' partition(salesmonth='2008-01', event='103') location 's3://awssampledbuswest2/tickit/spectrum/salesevent/salesmonth=2008-01/event=103/' partition(salesmonth='2008-02', event='101') location 's3://awssampledbuswest2/tickit/spectrum/salesevent/salesmonth=2008-02/event=101/' partition(salesmonth='2008-02', event='102') location 's3://awssampledbuswest2/tickit/spectrum/salesevent/salesmonth=2008-02/event=102/' partition(salesmonth='2008-02', event='103') location 's3://awssampledbuswest2/tickit/spectrum/salesevent/salesmonth=2008-02/event=103/' partition(salesmonth='2008-03', event='101') location 's3://awssampledbuswest2/tickit/spectrum/salesevent/salesmonth=2008-03/event=101/' partition(salesmonth='2008-03', event='102') location 's3://awssampledbuswest2/tickit/spectrum/salesevent/salesmonth=2008-03/event=102/' partition(salesmonth='2008-03', event='103') location 's3://awssampledbuswest2/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 檔案中的相符。如果資料欄的順序不相符,則可以依名稱映射資料欄。

重要

在舊版中,Redshift Spectrum 預設使用位置映射。如果您需要對現有資料表使用位置映射,請將資料表屬性 orc.schema.resolution 設為 position,如以下範例所示。

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 資料欄 ID ORC 資料欄名稱
int_col 1 int_col
float_col 2 float_col
nested_col 3 nested_col
nested_col.int_col 4 int_col
nested_col.map_col 5 map_col
nested_col.map_col.key 6 不適用
nested_col.map_col.value 7 不適用
nested_col.map_col.value.item 8 不適用

依資料欄名稱映射

使用名稱映射,您可以將外部資料表中的資料欄映射至 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 會嘗試進行下列映射。

外部資料表資料欄名稱 ORC 資料欄 ID ORC 資料欄名稱
int_col 1 struct
float_col 7 int_col
nested_col 8 float_col

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

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

為在 Apache Hudi 中管理的數據創建外部表

要以 Apache Hudi 寫入時複製 (CoW) 格式查詢數據,您可以使用 Amazon Redshift Spectrum 外部表。寫入時的 Hudi 複製表是存儲在 Amazon S3 中的 Apache 實木複製文件的集合。您可以在 Apache Hudi 版本 0.5.2、0.6.0 和 0.7.0 中讀取寫入時複製 (CoW) 表。如需詳細資訊,請參閱「」寫入時複製表在開源 Apache Hudi 文檔中。

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

分區和未分區 Hudi 表的數據定義語言 (DDL) 語句與其他 Apache 實木複合文件格式的語句類似。對於 Hudi 表,您可以定義INPUTFORMATorg.apache.hudi.hadoop.HoodieParquetInputFormat。所以此LOCATION參數必須指向包含.hoodie文件夾,該文件夾是建立 Hudi 提交時間線所需的。在某些情況下,Hudi 表上的 SELECT 操作可能會失敗,並顯示消息未找到有效的 Hudi 提交時間線。如果是這樣,請檢查.hoodie文件夾位於正確的位置,幷包含有效的 Hudi 提交時間軸。

注意

Apache Hudi 格式僅在您使用AWS Glue Data Catalog。當您使用 Apache Hive 元數據庫作為外部目錄時,它不受支持。

定義未分區資料表的 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 表添加分區命令,其中LOCATION參數指向帶有屬於該分區的文件的 Amazon S3 子文件夾。

添加分區的 DDL 採用下列格式。

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

為在三角洲湖中管理的數據創建外部表

要查詢三角洲湖表中的數據,您可以使用 Amazon Redshift Spectrum 外部表。

要從 Redshift 頻譜訪問三角洲湖表,請在查詢之前生成清單。A Delta Lake表現包含構成 Delta Lake 表的一致快照的文件列表。在分區表中,每個分區有一個清單。Delta Lake 表是存放在 Amazon S3 中的 Apache 複合體檔案的叢集。如需詳細資訊,請參閱「」Delta Lake在開源三角洲湖文檔中。

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

用於分區和未分區的三角洲湖表的 DDL 與其他 Apache 實木複合文件格式的 DDL 類似。對於三角洲湖表,您可以定義INPUTFORMATorg.apache.hadoop.hive.ql.io.SymlinkTextInputFormatOUTPUTFORMATorg.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat。所以此LOCATION參數必須指向表基文件夾中的清單文件夾。如果 Delta 湖表上的 SELECT 操作失敗,可能出於可能的原因,請參閲三角洲湖表的限制和故障排除

定義未分區資料表的 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'

若要將分區添加到已分區的三角洲湖表,請運行 ALTER 表添加分區命令,其中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'

或者運行直接指向三角洲湖清單文件的 DDL。

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

三角洲湖表的限制和故障排除

從 Redshift 頻譜查詢三角洲湖表時,請考慮以下事項:

  • 如果清單指向不再存在的快照或分區,則查詢將失敗,直到生成新的有效清單。例如,這可能是由於對基礎表執行的 VACUUM 操作,

  • 德爾塔湖清單僅提供分區級別的一致性。

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

錯誤訊息 可能的原因

水桶中的三角洲湖清單s3 桶 -1不能包含存儲桶中的條目s3 桶 -2

清單條目指向與指定存儲桶不同的 Amazon S3 存儲桶中的文件。

Delta Lake 檔案應在相同的資料夾中。

清單條目指向具有與指定前綴不同的 Amazon S3 前綴的文件。

File (檔案)檔案名稱列在三角洲湖清單清單路徑找不到。

在 Amazon S3 中未找到清單中列出的資料。

獲取三角洲湖清單時出錯。

在 Amazon S3 中未找到清單。

Invalid S3 路徑。

清單文件中的條目不是有效的 Amazon S3 路徑,或清單文件已損壞。