snippet.sql

DROP TABLE IF EXISTS NATION;
DROP TABLE IF EXISTS REGION;
DROP TABLE IF EXISTS PART;
DROP TABLE IF EXISTS SUPPLIER;
DROP TABLE IF EXISTS PARTSUPP;
DROP TABLE IF EXISTS CUSTOMER;
DROP TABLE IF EXISTS ORDERS;
DROP TABLE IF EXISTS LINEITEM;
 
CREATE TABLE NATION(
N_NATIONKEY  BIGINT NOT NULL,
N_NAME       CHAR(25) NOT NULL,
N_REGIONKEY  BIGINT NOT NULL,
N_COMMENT    VARCHAR(152))
WITH (appendonly=TRUE, orientation=COLUMN, compresstype=zlib, compresslevel=5, oids=FALSE) DISTRIBUTED BY (N_NATIONKEY);
 
CREATE TABLE REGION(
R_REGIONKEY  BIGINT NOT NULL,
R_NAME       CHAR(25) NOT NULL,
R_COMMENT    VARCHAR(152))
WITH (appendonly=TRUE, orientation=COLUMN, compresstype=zlib, compresslevel=5, oids=FALSE) DISTRIBUTED BY (R_REGIONKEY);
 
CREATE TABLE PART(
P_PARTKEY     BIGINT NOT NULL,
P_NAME        VARCHAR(55) NOT NULL,
P_MFGR        CHAR(25) NOT NULL,
P_BRAND       CHAR(10) NOT NULL,
P_TYPE        VARCHAR(25) NOT NULL,
P_SIZE        BIGINT NOT NULL,
P_CONTAINER   CHAR(10) NOT NULL,
P_RETAILPRICE DECIMAL(15,2) NOT NULL,
P_COMMENT     VARCHAR(23) NOT NULL )
WITH (appendonly=TRUE, orientation=COLUMN, compresstype=zlib, compresslevel=5, oids=FALSE) DISTRIBUTED BY (P_PARTKEY);
 
CREATE TABLE SUPPLIER(
S_SUPPKEY     BIGINT NOT NULL,
S_NAME        CHAR(25) NOT NULL,
S_ADDRESS     VARCHAR(40) NOT NULL,
S_NATIONKEY   BIGINT NOT NULL,
S_PHONE       CHAR(15) NOT NULL,
S_ACCTBAL     DECIMAL(15,2) NOT NULL,
S_COMMENT     VARCHAR(101) NOT NULL)
WITH (appendonly=TRUE, orientation=COLUMN, compresstype=zlib, compresslevel=5, oids=FALSE) DISTRIBUTED BY (S_SUPPKEY);
 
CREATE TABLE PARTSUPP(
PS_PARTKEY     BIGINT NOT NULL,
PS_SUPPKEY     BIGINT NOT NULL,
PS_AVAILQTY    BIGINT NOT NULL,
PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
PS_COMMENT     VARCHAR(199) NOT NULL )
WITH (appendonly=TRUE, orientation=COLUMN, compresstype=zlib, compresslevel=5, oids=FALSE) DISTRIBUTED BY (PS_PARTKEY);
 
CREATE TABLE CUSTOMER ( C_CUSTKEY     BIGINT NOT NULL,
C_NAME        VARCHAR(25) NOT NULL,
C_ADDRESS     VARCHAR(40) NOT NULL,
C_NATIONKEY   BIGINT NOT NULL,
C_PHONE       CHAR(15) NOT NULL,
C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
C_MKTSEGMENT  CHAR(10) NOT NULL,
C_COMMENT     VARCHAR(117) NOT NULL)
WITH (appendonly=TRUE, orientation=COLUMN, compresstype=zlib, compresslevel=5, oids=FALSE) DISTRIBUTED BY (C_CUSTKEY);
 
CREATE TABLE ORDERS(
O_ORDERKEY       BIGINT NOT NULL,
O_CUSTKEY        BIGINT NOT NULL,
O_ORDERSTATUS    CHAR(1) NOT NULL,
O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
O_ORDERDATE      DATE NOT NULL,
O_ORDERPRIORITY  CHAR(15) NOT NULL,  
O_CLERK          CHAR(15) NOT NULL, 
O_SHIPPRIORITY   BIGINT NOT NULL,
O_COMMENT        VARCHAR(79) NOT NULL)
WITH (appendonly=TRUE, orientation=COLUMN) DISTRIBUTED   BY (O_ORDERKEY);
 
CREATE TABLE LINEITEM(
L_ORDERKEY    BIGINT NOT NULL,
L_PARTKEY     BIGINT NOT NULL,
L_SUPPKEY     BIGINT NOT NULL,
L_LINENUMBER  BIGINT NOT NULL,
L_QUANTITY    DECIMAL(15,2) NOT NULL,
L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
L_DISCOUNT    DECIMAL(15,2) NOT NULL,
L_TAX         DECIMAL(15,2) NOT NULL,
L_RETURNFLAG  CHAR(1) NOT NULL,
L_LINESTATUS  CHAR(1) NOT NULL,
L_SHIPDATE    DATE NOT NULL,
L_COMMITDATE  DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE     CHAR(10) NOT NULL,
L_COMMENT      VARCHAR(44) NOT NULL)
WITH (appendonly=TRUE, orientation=COLUMN, compresstype=zlib, compresslevel=5, oids=FALSE) DISTRIBUTED BY (L_ORDERKEY);

snippet.sql

DROP EXTERNAL TABLE IF EXISTS NATION_EXT;
DROP EXTERNAL TABLE IF EXISTS REGION_EXT;
DROP EXTERNAL TABLE IF EXISTS PART_EXT;
DROP EXTERNAL TABLE IF EXISTS SUPPLIER_EXT;
DROP EXTERNAL TABLE IF EXISTS PARTSUPP_EXT;
DROP EXTERNAL TABLE IF EXISTS CUSTOMER_EXT;
DROP EXTERNAL TABLE IF EXISTS ORDERS_EXT;
DROP EXTERNAL TABLE IF EXISTS LINEITEM_EXT;
 
CREATE READABLE EXTERNAL TABLE NATION_EXT (LIKE NATION) LOCATION ('gpfdist://127.0.0.1:8989/1000s/nation.tbl') FORMAT 'TEXT' (DELIMITER '|');
CREATE READABLE EXTERNAL TABLE REGION_EXT (LIKE REGION) LOCATION ('gpfdist://127.0.0.1:8989/1000s/region.tbl') FORMAT 'TEXT' (DELIMITER '|');
CREATE READABLE EXTERNAL TABLE PART_EXT (LIKE PART) LOCATION ('gpfdist://127.0.0.1:8989/1000s/part.tbl') FORMAT 'TEXT' (DELIMITER '|');
CREATE READABLE EXTERNAL TABLE SUPPLIER_EXT (LIKE SUPPLIER) LOCATION ('gpfdist://127.0.0.1:8989/1000s/supplier.tbl') FORMAT 'TEXT' (DELIMITER '|');
CREATE READABLE EXTERNAL TABLE PARTSUPP_EXT (LIKE PARTSUPP) LOCATION ('gpfdist://127.0.0.1:8989/1000s/partsupp.tbl') FORMAT 'TEXT' (DELIMITER '|');
CREATE READABLE EXTERNAL TABLE CUSTOMER_EXT (LIKE CUSTOMER) LOCATION ('gpfdist://127.0.0.1:8989/1000s/customer.tbl') FORMAT 'TEXT' (DELIMITER '|');
CREATE READABLE EXTERNAL TABLE ORDERS_EXT (LIKE ORDERS) LOCATION ('gpfdist://127.0.0.1:8989/1000s/orders.tbl') FORMAT 'TEXT' (DELIMITER '|');
CREATE READABLE EXTERNAL TABLE LINEITEM_EXT (LIKE LINEITEM) LOCATION ('gpfdist://127.0.0.1:8989/1000s/lineitem.tbl') FORMAT 'TEXT' (DELIMITER '|');
 
INSERT INTO NATION SELECT * FROM NATION_EXT;
DROP EXTERNAL TABLE NATION_EXT;
INSERT INTO REGION SELECT * FROM REGION_EXT;
DROP EXTERNAL TABLE REGION_EXT;
INSERT INTO PART SELECT * FROM PART_EXT;
DROP EXTERNAL TABLE PART_EXT;
INSERT INTO SUPPLIER SELECT * FROM SUPPLIER_EXT;
DROP EXTERNAL TABLE SUPPLIER_EXT;
INSERT INTO PARTSUPP SELECT * FROM PARTSUPP_EXT;
DROP EXTERNAL TABLE PARTSUPP_EXT;
INSERT INTO CUSTOMER SELECT * FROM CUSTOMER_EXT;
DROP EXTERNAL TABLE CUSTOMER_EXT;
INSERT INTO ORDERS SELECT * FROM ORDERS_EXT;
DROP EXTERNAL TABLE ORDERS_EXT;
INSERT INTO LINEITEM SELECT * FROM LINEITEM_EXT;
DROP EXTERNAL TABLE LINEITEM_EXT;

snippet.sql

ANALYZE NATION;
ANALYZE REGION;
ANALYZE PART;
ANALYZE SUPPLIER;
ANALYZE PARTSUPP;
ANALYZE CUSTOMER;
ANALYZE ORDERS;
ANALYZE LINEITEM;

使用tpc-h tools生成查询语句。