Amazon Redshift は、パッチ 198 以降、新しい Python UDF の作成をサポートしなくなります。既存の 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/)を参照してください。
# 例
次の例では、SALES という名前のテーブルを `spectrum` という名前の Amazon Redshift 外部スキーマに作成します。データはタブ区切りのテキストファイルになっています。TABLE PROPERTIES 句は、numRows プロパティを 170,000 行に設定します。
CREATE EXTERNAL TABLE の実行に使用する ID によっては、設定が必要な 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` のクラブの第 8 列に疑問符 (?) 置換文字があります。
```
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` のクラブの第 8 列は含まれません。
```
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
```