Amazon Redshift Spectrum 十二大最佳实践（一）

create external schema s3_external_schema from data catalog database 'spectrumdb' iam_role 'arn:aws:iam::<AWS_ACCOUNT_ID>:role/aod-redshift-role'create external database if not exists;

CREATE  external table s3_external_schema.LINEITEM_PART_PARQ (  L_ORDERKEY BIGINT, L_PARTKEY BIGINT, L_SUPPKEY BIGINT, L_LINENUMBER INT, L_QUANTITY DECIMAL(12,2), L_EXTENDEDPRICE DECIMAL(12,2), L_DISCOUNT DECIMAL(12,2), L_TAX DECIMAL(12,2), L_RETURNFLAG VARCHAR(128), L_LINESTATUS VARCHAR(128), L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT VARCHAR(128), L_SHIPMODE VARCHAR(128), L_COMMENT VARCHAR(128))partitioned by (L_SHIPDATE DATE)stored as PARQUETlocation 's3://<your-bucket>/<xyz>/lineitem_partition/';
CREATE  external table s3_external_schema.LINEITEM_CSV (  L_ORDERKEY BIGINT, L_PARTKEY INT, L_SUPPKEY INT, L_LINENUMBER INT, L_QUANTITY DECIMAL(12,2), L_EXTENDEDPRICE DECIMAL(12,2), L_DISCOUNT DECIMAL(12,2), L_TAX DECIMAL(12,2), L_RETURNFLAG VARCHAR(128), L_LINESTATUS VARCHAR(128), L_SHIPDATE DATE , L_COMMITDATE DATE, L_RECEIPTDATE DATE, L_SHIPINSTRUCT VARCHAR(128), L_SHIPMODE VARCHAR(128), L_COMMENT VARCHAR(128))row format delimitedfields terminated by '|'stored as textfilelocation 's3://<your-bucket>/<xyz>/lineitem_csv/';

SELECT  l_returnflag,        l_linestatus,        sum(l_quantity) as sum_qty,        sum(l_extendedprice) as sum_base_price,        sum(l_extendedprice*(1-l_discount)) as sum_disc_price,        sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,        avg(l_quantity) as avg_qty,        avg(l_extendedprice) as avg_priceFROM s3_external_schema.LINEITEM_PART_PARQWHERE l_shipdate BETWEEN '1998-12-01' AND '1998-12-31'GROUP BY l_returnflag, l_linestatusORDER BY l_returnflag, l_linestatus;

SELECT   l_orderkey,         Sum(l_extendedprice * (1 - l_discount)) AS revenue,         o_orderdate,         o_shippriority FROM     customer, orders, s3_external_schema.lineitem_part_parq WHERE    c_mktsegment = 'BUILDING'          AND      c_custkey = o_custkey          AND      l_orderkey = o_orderkey          AND      o_orderdate < date '1995-03-15'          AND      l_shipdate >  date '1995-03-15' GROUP BY l_orderkey, o_orderdate, o_shippriority ORDER BY revenue DESC, o_orderdate LIMIT 20;