Esempi

Nell'esempio seguente viene creata una tabella denominata SALES nello schema esterno Amazon Redshift denominato spectrum. I dati sono in file di testo delimitati da tabulazioni. La clausola TABLE PROPERTIES imposta la proprietà numRows su 170.000 righe.

A seconda dell'identità utilizzata per eseguire CREATE EXTERNAL TABLE, è possibile che sia necessario configurare delle autorizzazioni IAM. Come best practice, consigliamo di collegare le policy di autorizzazioni a un ruolo IAM, che quindi viene assegnato a utenti e gruppi secondo le necessità. Per ulteriori informazioni, consulta Identity and access management in Amazon Redshift.

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');

L'esempio seguente crea una tabella che utilizza JsonSerDe per fare riferimento ai dati in formato 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://DOC-EXAMPLE-BUCKET/json/cloudtrail';

Nell'esempio CREATE EXTERNAL TABLE AS seguente viene creata una tabella esterna non partizionata. Quindi il risultato della query SELECT viene scritto come Apache Parquet nella posizione Amazon S3 di destinazione.

CREATE EXTERNAL TABLE spectrum.lineitem
STORED AS parquet
LOCATION 'S3://DOC-EXAMPLE-BUCKET/cetas/lineitem/'
AS SELECT * FROM local_lineitem;

Nell'esempio seguente viene creata una tabella esterna partizionata e vengono incluse le colonne di partizione nella query SELECT.

CREATE EXTERNAL TABLE spectrum.partitioned_lineitem
PARTITIONED BY (l_shipdate, l_shipmode)
STORED AS parquet
LOCATION 'S3://DOC-EXAMPLE-BUCKET/cetas/partitioned_lineitem/'
AS SELECT l_orderkey, l_shipmode, l_shipdate, l_partkey FROM local_table;

Per un elenco di database esistenti nel catalogo dati esterno, eseguire una query sulla vista di sistema SVV_EXTERNAL_DATABASES.

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"}

Per visualizzare i dettagli delle tabelle esterne, eseguire una query sulle viste di sistema SVV_EXTERNAL_TABLES e SVV_EXTERNAL_COLUMNS.

L'esempio seguente esegue una query sulla vista 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

L'esempio seguente esegue una query sulla vista 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

Per visualizzare le partizioni della tabella, usa la seguente query.

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

L'esempio seguente restituisce la dimensione totale dei file di dati correlati per una tabella esterna.

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

Esempi di partizionamento

Per creare una tabella esterna partizionata per data, esegui il comando seguente.

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');

Per aggiungere le partizioni, esegui il comando 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/';

Per selezionare i dati dalla tabella partizionata, esegui la seguente query.

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

Per visualizzare le partizioni delle tabelle, eseguire una query sulla vista di sistema 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-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

Esempi di formati di riga

Di seguito viene mostrato un esempio di specifica dei parametri ROW FORMAT SERDE per i file di dati memorizzati nel formato AVRO.

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://DOC-EXAMPLE-BUCKET/avro/sales' ;

Di seguito viene illustrato un esempio di specificazione dei parametri ROW FORMAT SERDE utilizzando. RegEx

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://DOC-EXAMPLE-BUCKET/regex/types';

Di seguito viene mostrato un esempio di specifica dei parametri ROW FORMAT SERDE usando Grok.

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}\\] *(?<loglevel>:DEBUG|FATAL|ERROR|WARN|INFO) -- +%{DATA:progname}: %{GREEDYDATA:message}')
stored as textfile
location 's3://DOC-EXAMPLE-BUCKET/grok/logs';

Il seguente esempio mostra come definire un log di accesso al server Amazon S3 in un bucket S3. È possibile utilizzare Redshift Spectrum per eseguire query sui log degli accessi di 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://DOC-EXAMPLE-BUCKET/s3logs’;

Di seguito viene mostrato un esempio di specifica dei parametri ROW FORMAT SERDE per i file di dati memorizzati nel formato ION.

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://DOC-EXAMPLE-BUCKET/prefix'

Esempi di gestione dei dati

Gli esempi seguenti accedono al file: spi_global_rankings.csv. È possibile caricare il file spi_global_rankings.csv in un bucket Amazon S3 per provare questi esempi.

Nell'esempio seguente viene creato lo schema esterno schema_spectrum_uddh e il database spectrum_db_uddh. Peraws-account-id, inserisci l'ID AWS del tuo account e role-name inserisci il nome del tuo ruolo 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;

Nell'esempio seguente viene creata la tabella esterna denominata soccer_league nello schema esterno schema_spectrum_uddh.

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');

Controllare Il numero di righe nella tabella soccer_league.

select count(*) from schema_spectrum_uddh.soccer_league;

Viene visualizzato il numero di righe.

count
645

La seguente query visualizza i primi 10 club. Poiché il club Barcelona include un carattere non valido nella stringa, per il nome viene visualizzato un valore 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

Nell'esempio seguente viene modificata la tabella soccer_league per specificare le proprietà invalid_char_handling, replacement_char e data_cleansing_enabled della tabella esterna per inserire un punto interrogativo (?) come sostituto di caratteri imprevisti.

alter  table schema_spectrum_uddh.soccer_league
set table properties ('invalid_char_handling'='REPLACE','replacement_char'='?','data_cleansing_enabled'='true');

Nell'esempio seguente viene sottoposta a query la tabella soccer_league per team con un grado da 1 a 10.

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

Poiché le proprietà della tabella sono state modificate, i risultati riportano i primi 10 club, con il carattere di sostituzione punto interrogativo (?) nell'ottava riga per il club 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

L'esempio seguente modifica la tabella soccer_league per specificare le proprietà invalid_char_handling della tabella esterna per eliminare le righe con caratteri imprevisti.

alter table schema_spectrum_uddh.soccer_league
set table properties ('invalid_char_handling'='DROP_ROW','data_cleansing_enabled'='true');

Nell'esempio seguente viene sottoposta a query la tabella soccer_league per team con un grado da 1 a 10.

select league_rank,club_name,league_name,league_nspi
from schema_spectrum_uddh.soccer_league
where league_rank between 1 and 10;

I risultati riportano i club principali senza includere l'ottava riga per il 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