Amazon Redshift 將不再支援從修補程式 198 開始建立新的 Python UDFs。現有 Python UDF 將繼續正常運作至 2026 年 6 月 30 日。如需詳細資訊，請參閱[部落格文章](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/)。
本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。
# 範例
以下範例在名為 `spectrum` 的 Amazon Redshift 外部結構描述中建立名為 SALES 的資料表。該資料位於 Tab 鍵分隔的文字檔案中。TABLE PROPERTIES 子句會將 numRows 屬性設定為 170,000 個資料列。
視您用來執行 CREATE EXTERNAL TABLE 的身分而定，您可能需要設定 IAM 許可。最佳做法是，建議您將許可政策附加到 IAM 角色，然後根據需要將其指派給使用者和群組。如需詳細資訊，請參閱 [Amazon Redshift 中的 Identity and Access Management](https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-authentication-access-control.html)。
```
create external table spectrum.sales(
salesid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
saledate date,
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'='170000');
```
下列範例會建立使用 JsonSerDe 參考 JSON 格式資料的資料表。
```
create external table spectrum.cloudtrail_json (
event_version int,
event_id bigint,
event_time timestamp,
event_type varchar(10),
awsregion varchar(20),
event_name varchar(max),
event_source varchar(max),
requesttime timestamp,
useragent varchar(max),
recipientaccountid bigint)
row format serde 'org.openx.data.jsonserde.JsonSerDe'
with serdeproperties (
'dots.in.keys' = 'true',
'mapping.requesttime' = 'requesttimestamp'
) location 's3://amzn-s3-demo-bucket/json/cloudtrail';
```
下列 CREATE EXTERNAL TABLE AS 範例會建立未分割的外部資料表。然後它會寫入 SELECT 查詢結果，作為 Apache Parquet 到目標 Amazon S3 位置。
```
CREATE EXTERNAL TABLE spectrum.lineitem
STORED AS parquet
LOCATION 'S3://amzn-s3-demo-bucket/cetas/lineitem/'
AS SELECT * FROM local_lineitem;
```
下列範例會建立已分割的外部資料表，並在 SELECT 查詢中包含分割區欄位。
```
CREATE EXTERNAL TABLE spectrum.partitioned_lineitem
PARTITIONED BY (l_shipdate, l_shipmode)
STORED AS parquet
LOCATION 'S3://amzn-s3-demo-bucket/cetas/partitioned_lineitem/'
AS SELECT l_orderkey, l_shipmode, l_shipdate, l_partkey FROM local_table;
```
如需外部資料目錄中現有資料庫的清單，請查詢 [SVV\_EXTERNAL\_DATABASES](r_SVV_EXTERNAL_DATABASES.md) 系統畫面。
```
select eskind,databasename,esoptions from svv_external_databases order by databasename;
```
```
eskind | databasename | esoptions
-------+--------------+----------------------------------------------------------------------------------
1 | default | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"}
1 | sampledb | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"}
1 | spectrumdb | {"REGION":"us-west-2","IAM_ROLE":"arn:aws:iam::123456789012:role/mySpectrumRole"}
```
若要檢視外部資料表的詳細資訊，請查詢 [SVV\_EXTERNAL\_TABLES](r_SVV_EXTERNAL_TABLES.md) 和 [SVV\_EXTERNAL\_COLUMNS](r_SVV_EXTERNAL_COLUMNS.md) 系統畫面。
以下範例會查詢 SVV\_EXTERNAL\_TABLES 檢視。
```
select schemaname, tablename, location from svv_external_tables;
```
```
schemaname | tablename | location
-----------+----------------------+--------------------------------------------------------
spectrum | sales | s3://redshift-downloads/tickit/spectrum/sales
spectrum | sales_part | s3://redshift-downloads/tickit/spectrum/sales_partition
```
以下範例會查詢 SVV\_EXTERNAL\_COLUMNS 檢視。
```
select * from svv_external_columns where schemaname like 'spectrum%' and tablename ='sales';
```
```
schemaname | tablename | columnname | external_type | columnnum | part_key
-----------+-----------+------------+---------------+-----------+---------
spectrum | sales | salesid | int | 1 | 0
spectrum | sales | listid | int | 2 | 0
spectrum | sales | sellerid | int | 3 | 0
spectrum | sales | buyerid | int | 4 | 0
spectrum | sales | eventid | int | 5 | 0
spectrum | sales | saledate | date | 6 | 0
spectrum | sales | qtysold | smallint | 7 | 0
spectrum | sales | pricepaid | decimal(8,2) | 8 | 0
spectrum | sales | commission | decimal(8,2) | 9 | 0
spectrum | sales | saletime | timestamp | 10 | 0
```
若要檢視資料表分割區，請使用下列查詢。
```
select schemaname, tablename, values, location
from svv_external_partitions
where tablename = 'sales_part';
```
```
schemaname | tablename | values | location
-----------+------------+----------------+-------------------------------------------------------------------------
spectrum | sales_part | ["2008-01-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01
spectrum | sales_part | ["2008-02-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-02
spectrum | sales_part | ["2008-03-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03
spectrum | sales_part | ["2008-04-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-04
spectrum | sales_part | ["2008-05-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-05
spectrum | sales_part | ["2008-06-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-06
spectrum | sales_part | ["2008-07-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-07
spectrum | sales_part | ["2008-08-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-08
spectrum | sales_part | ["2008-09-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-09
spectrum | sales_part | ["2008-10-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-10
spectrum | sales_part | ["2008-11-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-11
spectrum | sales_part | ["2008-12-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-12
```
下列範例會傳回外部資料表相關的資料檔案大小總和。
```
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-02/ | 1444
```
## 資料分割範例
若要建立以日期分割的外部資料表，請執行以下命令。
```
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 date)
row format delimited
fields terminated by '|'
stored as textfile
location 's3://redshift-downloads/tickit/spectrum/sales_partition/'
table properties ('numRows'='170000');
```
請執行下列 ALTER TABLE 命令以新增分割區。
```
alter table spectrum.sales_part
add if not exists partition (saledate='2008-01-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-02-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-02/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-03-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-04-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-04/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-05-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-05/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-06-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-06/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-07-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-07/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-08-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-08/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-09-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-09/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-10-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-10/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-11-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-11/';
alter table spectrum.sales_part
add if not exists partition (saledate='2008-12-01')
location 's3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-12/';
```
若要從分割資料表選取資料，請執行下列查詢。
```
select top 10 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-12-01'
group by spectrum.sales_part.eventid
order by 2 desc;
```
```
eventid | sum
--------+---------
914 | 36173.00
5478 | 27303.00
5061 | 26383.00
4406 | 26252.00
5324 | 24015.00
1829 | 23911.00
3601 | 23616.00
3665 | 23214.00
6069 | 22869.00
5638 | 22551.00
```
若要檢視外部資料表分割區，請查詢 [SVV\_EXTERNAL\_PARTITIONS](r_SVV_EXTERNAL_PARTITIONS.md) 系統畫面。
```
select schemaname, tablename, values, location from svv_external_partitions
where tablename = 'sales_part';
```
```
schemaname | tablename | values | location
-----------+------------+----------------+--------------------------------------------------
spectrum | sales_part | ["2008-01-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-01
spectrum | sales_part | ["2008-02-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-02
spectrum | sales_part | ["2008-03-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-03
spectrum | sales_part | ["2008-04-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-04
spectrum | sales_part | ["2008-05-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-05
spectrum | sales_part | ["2008-06-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-06
spectrum | sales_part | ["2008-07-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-07
spectrum | sales_part | ["2008-08-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-08
spectrum | sales_part | ["2008-09-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-09
spectrum | sales_part | ["2008-10-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-10
spectrum | sales_part | ["2008-11-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-11
spectrum | sales_part | ["2008-12-01"] | s3://redshift-downloads/tickit/spectrum/sales_partition/saledate=2008-12
```
## 資料列格式範例
下面顯示的範例會針對以 AVRO 格式儲存的資料檔案，指定 ROW FORMAT SERDE 參數。
```
create external table spectrum.sales(salesid int, listid int, sellerid int, buyerid int, eventid int, dateid int, qtysold int, pricepaid decimal(8,2), comment VARCHAR(255))
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
WITH SERDEPROPERTIES ('avro.schema.literal'='{\"namespace\": \"dory.sample\",\"name\": \"dory_avro\",\"type\": \"record\", \"fields\": [{\"name\":\"salesid\", \"type\":\"int\"},
{\"name\":\"listid\", \"type\":\"int\"},
{\"name\":\"sellerid\", \"type\":\"int\"},
{\"name\":\"buyerid\", \"type\":\"int\"},
{\"name\":\"eventid\",\"type\":\"int\"},
{\"name\":\"dateid\",\"type\":\"int\"},
{\"name\":\"qtysold\",\"type\":\"int\"},
{\"name\":\"pricepaid\", \"type\": {\"type\": \"bytes\", \"logicalType\": \"decimal\", \"precision\": 8, \"scale\": 2}}, {\"name\":\"comment\",\"type\":\"string\"}]}')
STORED AS AVRO
location 's3://amzn-s3-demo-bucket/avro/sales' ;
```
下面顯示的範例會使用 RegEx 指定 ROW FORMAT SERDE 參數。
```
create external table spectrum.types(
cbigint bigint,
cbigint_null bigint,
cint int,
cint_null int)
row format serde 'org.apache.hadoop.hive.serde2.RegexSerDe'
with serdeproperties ('input.regex'='([^\\x01]+)\\x01([^\\x01]+)\\x01([^\\x01]+)\\x01([^\\x01]+)')
stored as textfile
location 's3://amzn-s3-demo-bucket/regex/types';
```
下面顯示的範例會使用 Grok 指定 ROW FORMAT SERDE 參數。
```
create external table spectrum.grok_log(
timestamp varchar(255),
pid varchar(255),
loglevel varchar(255),
progname varchar(255),
message varchar(255))
row format serde 'com.amazonaws.glue.serde.GrokSerDe'
with serdeproperties ('input.format'='[DFEWI], \\[%{TIMESTAMP_ISO8601:timestamp} #%{POSINT:pid:int}\\] *(?:DEBUG|FATAL|ERROR|WARN|INFO) -- +%{DATA:progname}: %{GREEDYDATA:message}')
stored as textfile
location 's3://DOC-EXAMPLE-BUCKET/grok/logs';
```
下面範例會示範在 S3 儲存貯體中定義 Amazon S3 伺服器存取日誌。您可以使用 Redshift Spectrum 查詢 Amazon S3 存取日誌。
```
CREATE EXTERNAL TABLE spectrum.mybucket_s3_logs(
bucketowner varchar(255),
bucket varchar(255),
requestdatetime varchar(2000),
remoteip varchar(255),
requester varchar(255),
requested varchar(255),
operation varchar(255),
key varchar(255),
requesturi_operation varchar(255),
requesturi_key varchar(255),
requesturi_httpprotoversion varchar(255),
httpstatus varchar(255),
errorcode varchar(255),
bytessent bigint,
objectsize bigint,
totaltime varchar(255),
turnaroundtime varchar(255),
referrer varchar(255),
useragent varchar(255),
versionid varchar(255)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'input.regex' = '([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) \"([^ ]*)\\s*([^ ]*)\\s*([^ ]*)\" (- |[^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\") ([^ ]*).*$')
LOCATION 's3://amzn-s3-demo-bucket/s3logs’;
```
下面顯示的範例會針對 ION 格式資料，指定 ROW FORMAT SERDE 參數。
```
CREATE EXTERNAL TABLE {{tbl_name}} ({{columns}})
ROW FORMAT SERDE 'com.amazon.ionhiveserde.IonHiveSerDe'
STORED AS
INPUTFORMAT 'com.amazon.ionhiveserde.formats.IonInputFormat'
OUTPUTFORMAT 'com.amazon.ionhiveserde.formats.IonOutputFormat'
LOCATION '{{s3://amzn-s3-demo-bucket/prefix}}'
```
## 資料處理範例
下列範例會存取檔案：[spi\_global\_rankings.csv](https://s3.amazonaws.com/redshift-downloads/docs-downloads/spi_global_rankings.csv)。您可以將 `spi_global_rankings.csv` 檔案上傳至 Amazon S3 儲存貯體以嘗試下列範例。
以下範例會建立外部結構描述 `schema_spectrum_uddh` 和資料庫 `spectrum_db_uddh`。針對 `aws-account-id`，輸入 AWS 您的帳戶 ID，並針對 `role-name`輸入您的 Redshift Spectrum 角色名稱。
```
create external schema schema_spectrum_uddh
from data catalog
database 'spectrum_db_uddh'
iam_role 'arn:aws:iam::{{aws-account-id}}:role/{{role-name}}'
create external database if not exists;
```
以下範例會在外部結構描述 `schema_spectrum_uddh` 中建立外部資料表 `soccer_league`。
```
CREATE EXTERNAL TABLE schema_spectrum_uddh.soccer_league
(
league_rank smallint,
prev_rank smallint,
club_name varchar(15),
league_name varchar(20),
league_off decimal(6,2),
league_def decimal(6,2),
league_spi decimal(6,2),
league_nspi integer
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n\l'
stored as textfile
LOCATION 's3://spectrum-uddh/league/'
table properties ('skip.header.line.count'='1');
```
檢查 `soccer_league` 資料表中的列數。
```
select count(*) from schema_spectrum_uddh.soccer_league;
```
列數會隨其顯示。
```
count
645
```
下列查詢會顯示前 10 名的俱樂部。因為俱樂部 `Barcelona` 在字串中有無效的字元，所以會顯示 NULL 做為名稱。
```
select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;
```
```
league_rank club_name league_name league_nspi
1 Manchester City Barclays Premier Lea 34595
2 Bayern Munich German Bundesliga 34151
3 Liverpool Barclays Premier Lea 33223
4 Chelsea Barclays Premier Lea 32808
5 Ajax Dutch Eredivisie 32790
6 Atletico Madrid Spanish Primera Divi 31517
7 Real Madrid Spanish Primera Divi 31469
8 NULL Spanish Primera Divi 31321
9 RB Leipzig German Bundesliga 31014
10 Paris Saint-Ger French Ligue 1 30929
```
下列範例會修改 `soccer_league` 資料表，以指定 `invalid_char_handling`、`replacement_char` 和 `data_cleansing_enabled` 外部資料表屬性來插入問號 (?) 替代非預期字元。
```
alter table schema_spectrum_uddh.soccer_league
set table properties ('invalid_char_handling'='REPLACE','replacement_char'='?','data_cleansing_enabled'='true');
```
下列範例會針對排名 1 到 10 的團隊查詢 `soccer_league` 資料表。
```
select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;
```
由於資料表屬性已修改，因此結果會顯示前 10 名的俱樂部，並在俱樂部 `Barcelona` 的第八列包含問號 (?) 替代字元。
```
league_rank club_name league_name league_nspi
1 Manchester City Barclays Premier Lea 34595
2 Bayern Munich German Bundesliga 34151
3 Liverpool Barclays Premier Lea 33223
4 Chelsea Barclays Premier Lea 32808
5 Ajax Dutch Eredivisie 32790
6 Atletico Madrid Spanish Primera Divi 31517
7 Real Madrid Spanish Primera Divi 31469
8 Barcel?na Spanish Primera Divi 31321
9 RB Leipzig German Bundesliga 31014
10 Paris Saint-Ger French Ligue 1 30929
```
下列範例會修改資料表 `soccer_league` 來指定 `invalid_char_handling` 外部資料表屬性，以捨棄含有非預期字元的資料列。
```
alter table schema_spectrum_uddh.soccer_league
set table properties ('invalid_char_handling'='DROP_ROW','data_cleansing_enabled'='true');
```
下列範例會針對排名 1 到 10 的團隊查詢 `soccer_league` 資料表。
```
select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;
```
結果會顯示熱門俱樂部，不包括俱樂部 `Barcelona` 的第八列。
```
league_rank club_name league_name league_nspi
1 Manchester City Barclays Premier Lea 34595
2 Bayern Munich German Bundesliga 34151
3 Liverpool Barclays Premier Lea 33223
4 Chelsea Barclays Premier Lea 32808
5 Ajax Dutch Eredivisie 32790
6 Atletico Madrid Spanish Primera Divi 31517
7 Real Madrid Spanish Primera Divi 31469
9 RB Leipzig German Bundesliga 31014
10 Paris Saint-Ger French Ligue 1 30929
```