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