查询编辑器在 Amazon Redshift 集群上的应用

awsuser

/* 创建架构 */CREATE SCHEMA myinternalschema

/* 创建表 */CREATE TABLE myinternalschema.event(

	eventid integer not null distkey,

	venueid smallint not null,

	catid smallint not null,

	dateid smallint not null sortkey,

	eventname varchar(200),

	starttime timestamp);

 s3://aws-redshift-spectrum-sample-data-us-east-1/spectrum/event/allevents_pipe.txt。

/* 加载数据 */

COPY myinternalschema.event FROM 's3://aws-redshift-spectrum-sample-data-us-east-1/spectrum/event/allevents_pipe.txt'

iam_role ‘REPLACE THIS PLACEHOLDER WITH THE IAM ROLE ARN'

delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-east-1';

/* 在 myinternalschema 中查看相同数据集的一个片段 */ SELECT * FROM myinternalschema.eventLIMIT 10;

/* 目前正在进行查询 */ SELECT

userid

 , query

 , pid

 , starttime

 , left(text, 50) as textFROM pg_catalog.stv_inflight

/* 您的 Redshift 集群上的可用磁盘空间 */SELECT SUM(used)::float / SUM(capacity) as pct_fullFROM pg_catalog.stv_partitions

 /* 查找各个表大小 */SELECT t.name, COUNT(tbl) / 1000.0 as gbFROM (SELECT DISTINCT id, name FROM stv_tbl_perm) tJOIN stv_blocklist ON tbl=t.idGROUP BY t.name ORDER BY gb DESC

\/* 查找已锁定且具有相关会话过程 ID 的所有事务 */ select 

  table_id,

  last_update,

  last_commit,

  lock_owner_pid,

  lock_status FROM pg_catalog.stv_locks ORDER BY last_update asc

/* 从最近的 Vacuum 中删除或恢复的行 */* 从 svv_vacuum_summary 中选择

其中 table_name = 'events'

/* 列出连接以及远程主机信息 */ SELECT DISTINCT

 starttime,

 process,

 user_name,

 '169.254.21.1' remotehost,

 remoteportFROM stv_sessionsLEFT JOIN stl_connection_log ON pid = process

  AND starttime > recordtime - interval '1 second'ORDER BY starttime DESC

/* 创建外部 (Amazon S3) 架构 */CREATE EXTERNAL SCHEMA myexternalschemafrom data catalogdatabase 'myexternaldatabase'

region 'us-east-1'

iam_role 'REPLACE THIS PLACEHOLDER WITH THE IAM ROLE ARN' create external database if not exists;

/* 创建外部表 */CREATE EXTERNAL TABLE myexternalschema.sales(

salesid integer,

listid integer,

sellerid integer,

buyerid integer,

eventid integer,

dateid smallint,

qtysold smallint, pricepaid decimal(8,1), commission decimal(8,1), saletime timestamp)row format delimitedfields terminated by '\t'

stored as textfile

位置 's3://aws-redshift-spectrum-sample-data-us-east-1/spectrum/sales/' 

表属性 ('numRows'='171000');

s3://aws-redshift-spectrum-sample-data-us-east-1/spectrum/sales/

/* 将样本数据集中的表加入 Spectrum 表中 *//* 加入外部 (Amazon S3) 和内部 (Amazon Redshift) 表 */SELECT

    myexternalschema.sales.eventid,

    sum(myexternalschema.sales.pricepaid)   from

    myexternalschema.sales,

    myinternalschema.event  where

    myexternalschema.sales.eventid = myinternalschema.event.eventid       

    and myexternalschema.sales.pricepaid > 50  group by

    myexternalschema.sales.eventid  order by

    1 desc;

从 's3://awssampledbuswest2/tickit/allusers_pipe.txt' 中复制用户 
凭证 'aws_iam_role=REPLACE THIS PLACEHOLDER WITH THE IAM ROLE ARN' delimiter '|' region 'us-west-2';

从 's3://awssampledbuswest2/tickit/venue_pipe.txt' 中复制会场 
凭证 'aws_iam_role=REPLACE THIS PLACEHOLDER WITH THE IAM ROLE ARN' delimiter '|' region 'us-west-2';

从 's3://awssampledbuswest2/tickit/category_pipe.txt' 中复制类别 
凭证 'aws_iam_role=REPLACE THIS PLACEHOLDER WITH THE IAM ROLE ARN' delimiter '|' region 'us-west-2';

从 's3://awssampledbuswest2/tickit/date2008_pipe.txt' 中复制日期 
凭证 'aws_iam_role= REPLACE THIS PLACEHOLDER WITH THE IAM ROLE ARN' delimiter '|' region 'us-west-2';

从 's3://awssampledbuswest2/tickit/listings_pipe.txt' 中复制列表 
凭证 'aws_iam_role= REPLACE THIS PLACEHOLDER WITH THE IAM ROLE ARN' delimiter '|' region 'us-west-2';

从 's3://awssampledbuswest2/tickit/sales_tab.txt' 中复制销售
凭证 'aws_iam_role= REPLACE THIS PLACEHOLDER WITH THE IAM ROLE ARN'delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS' region 'us-west-2';