tpch.sql

-- BASED ON TPCH_1000GB schema in demodb

CREATE OR REPLACE CONNECTION EXASOL_DEMO_DATA
TO '' --Need to fill in where this data resides
USER '' -- hope it's not needed
IDENTIFIED BY '' -- hope it's not needed

CREATE SCHEMA TPCH_1000GB;

CREATE TABLE 
    "CUSTOMER" 
    ( 
        "C_CUSTKEY"    INTEGER NOT NULL, 
        "C_NAME"       VARCHAR(25) ASCII NOT NULL, 
        "C_ADDRESS"    VARCHAR(40) ASCII NOT NULL, 
        "C_NATIONKEY"  INTEGER NOT NULL, 
        "C_PHONE"      CHAR(15) ASCII NOT NULL, 
        "C_ACCTBAL"    DECIMAL(12,2) NOT NULL, 
        "C_MKTSEGMENT" CHAR(10) ASCII NOT NULL, 
        "C_COMMENT"    VARCHAR(117) ASCII NOT NULL, 
        "TEST"         VARCHAR(100), 
        DISTRIBUTE BY "C_CUSTKEY" 
    );
    
CREATE TABLE 
    "LINEITEM" 
    ( 
        "L_ORDERKEY"      INTEGER NOT NULL, 
        "L_PARTKEY"       INTEGER NOT NULL, 
        "L_SUPPKEY"       INTEGER NOT NULL, 
        "L_LINENUMBER"    INTEGER NOT NULL, 
        "L_QUANTITY"      DECIMAL(12,2) NOT NULL, 
        "L_EXTENDEDPRICE" DECIMAL(12,2) NOT NULL, 
        "L_DISCOUNT"      DECIMAL(15,2) NOT NULL, 
        "L_TAX"           DECIMAL(15,2) NOT NULL, 
        "L_RETURNFLAG"    CHAR(1) ASCII NOT NULL, 
        "L_LINESTATUS"    CHAR(1) ASCII NOT NULL, 
        "L_SHIPDATE"      DATE NOT NULL, 
        "L_COMMITDATE"    DATE NOT NULL, 
        "L_RECEIPTDATE"   DATE NOT NULL, 
        "L_SHIPINSTRUCT"  CHAR(25) ASCII NOT NULL, 
        "L_SHIPMODE"      CHAR(10) ASCII NOT NULL, 
        "L_COMMENT"       VARCHAR(44) ASCII NOT NULL, 
        DISTRIBUTE BY "L_ORDERKEY" 
    );
    
CREATE TABLE 
    "NATION" 
    ( 
        "N_NATIONKEY" INTEGER NOT NULL, 
        "N_NAME"      CHAR(25) ASCII NOT NULL, 
        "N_REGIONKEY" INTEGER NOT NULL, 
        "N_COMMENT"   VARCHAR(152) ASCII, 
        "TEST"        VARCHAR(100) 
    );
    
CREATE TABLE 
    "ORDERS" 
    ( 
        "O_ORDERKEY"      INTEGER NOT NULL, 
        "O_CUSTKEY"       INTEGER NOT NULL, 
        "O_ORDERSTATUS"   CHAR(1) ASCII NOT NULL, 
        "O_TOTALPRICE"    DECIMAL(15,2) NOT NULL, 
        "O_ORDERDATE"     DATE NOT NULL, 
        "O_ORDERPRIORITY" CHAR(15) ASCII NOT NULL, 
        "O_CLERK"         CHAR(15) ASCII NOT NULL, 
        "O_SHIPPRIORITY"  INTEGER NOT NULL, 
        "O_COMMENT"       VARCHAR(79) ASCII NOT NULL, 
        "TEST"            VARCHAR(100), 
        DISTRIBUTE BY "O_CUSTKEY" 
    );
    
CREATE TABLE 
    "PART" 
    ( 
        "P_PARTKEY"     INTEGER NOT NULL, 
        "P_NAME"        VARCHAR(55) ASCII NOT NULL, 
        "P_MFGR"        CHAR(25) ASCII NOT NULL, 
        "P_BRAND"       CHAR(10) ASCII NOT NULL, 
        "P_TYPE"        VARCHAR(25) ASCII NOT NULL, 
        "P_SIZE"        DECIMAL(10,0) NOT NULL, 
        "P_CONTAINER"   CHAR(10) ASCII NOT NULL, 
        "P_RETAILPRICE" DECIMAL(12,2) NOT NULL, 
        "P_COMMENT"     VARCHAR(23) ASCII NOT NULL, 
        "TEST"          VARCHAR(100), 
        DISTRIBUTE BY "P_PARTKEY" 
    );
    
CREATE TABLE 
    "PARTSUPP" 
    ( 
        "PS_PARTKEY"    INTEGER NOT NULL, 
        "PS_SUPPKEY"    INTEGER NOT NULL, 
        "PS_AVAILQTY"   INTEGER NOT NULL, 
        "PS_SUPPLYCOST" DECIMAL(12,2) NOT NULL, 
        "PS_COMMENT"    VARCHAR(199) ASCII NOT NULL, 
        "TEST"          VARCHAR(199), 
        DISTRIBUTE BY "PS_PARTKEY" 
    );
    
CREATE TABLE 
    "REGION" 
    ( 
        "R_REGIONKEY" INTEGER NOT NULL, 
        "R_NAME"      CHAR(25) ASCII NOT NULL, 
        "R_COMMENT"   VARCHAR(152) ASCII, 
        "TEST"        VARCHAR(100) 
    );
    
CREATE TABLE 
    "SUPPLIER" 
    ( 
        "S_SUPPKEY"   INTEGER NOT NULL, 
        "S_NAME"      CHAR(25) ASCII NOT NULL, 
        "S_ADDRESS"   VARCHAR(40) NOT NULL, 
        "S_NATIONKEY" INTEGER NOT NULL, 
        "S_PHONE"     CHAR(15) ASCII NOT NULL, 
        "S_ACCTBAL"   DECIMAL(12,2) NOT NULL, 
        "S_COMMENT"   VARCHAR(101) ASCII NOT NULL, 
        "TEST"        VARCHAR(100), 
        DISTRIBUTE BY "S_SUPPKEY" 
    );
    
ALTER TABLE "CUSTOMER" ADD CONSTRAINT "PK_CUSTOMER" PRIMARY KEY ("C_CUSTKEY");
ALTER TABLE "CUSTOMER" ADD CONSTRAINT "FK_NATION" FOREIGN KEY ("C_NATIONKEY") REFERENCES "NATION" ("N_NATIONKEY");
ALTER TABLE "LINEITEM" ADD CONSTRAINT "PK_LINEITEM" PRIMARY KEY ("L_LINENUMBER", "L_ORDERKEY");
ALTER TABLE "LINEITEM" ADD CONSTRAINT "FK_PARTSUPP" FOREIGN KEY ("L_PARTKEY", "L_SUPPKEY") REFERENCES "PARTSUPP" ("PS_PARTKEY", "PS_SUPPKEY");
ALTER TABLE "NATION" ADD CONSTRAINT "PK_NATION" PRIMARY KEY ("N_NATIONKEY");
ALTER TABLE "NATION" ADD CONSTRAINT "FK_REGION" FOREIGN KEY ("N_REGIONKEY") REFERENCES "REGION" ("R_REGIONKEY");
ALTER TABLE "ORDERS" ADD CONSTRAINT "PK_ORDERS" PRIMARY KEY ("O_ORDERKEY");
ALTER TABLE "ORDERS" ADD CONSTRAINT "FK_CUSTOMER" FOREIGN KEY ("O_CUSTKEY") REFERENCES "CUSTOMER" ("C_CUSTKEY");
ALTER TABLE "PART" ADD CONSTRAINT "PK_PART" PRIMARY KEY ("P_PARTKEY");
ALTER TABLE "PARTSUPP" ADD CONSTRAINT "PK_PARTSUPP" PRIMARY KEY ("PS_PARTKEY", "PS_SUPPKEY");
ALTER TABLE "PARTSUPP" ADD CONSTRAINT "FK_PART" FOREIGN KEY ("PS_PARTKEY") REFERENCES "PART" ("P_PARTKEY") ;
ALTER TABLE "PARTSUPP" ADD CONSTRAINT "FK_SUPP" FOREIGN KEY ("PS_SUPPKEY") REFERENCES "SUPPLIER" ("S_SUPPKEY");
ALTER TABLE "REGION" ADD CONSTRAINT "PK_REGION" PRIMARY KEY ("R_REGIONKEY");
ALTER TABLE "SUPPLIER" ADD CONSTRAINT "PK_SUPPLIER" PRIMARY KEY ("S_SUPPKEY");
ALTER TABLE "SUPPLIER" ADD CONSTRAINT "FK_NATION" FOREIGN KEY ("S_NATIONKEY") REFERENCES "NATION" ("N_NATIONKEY");

IMPORT INTO TPCH_1000GB.REGION FROM CSV AT EXASOL_DEMO_DATA FILE 'tpch_1000gb/region.csv';
IMPORT INTO TPCH_1000GB.NATION FROM CSV AT EXASOL_DEMO_DATA FILE 'tpch_1000gb/nation.csv';
IMPORT INTO TPCH_1000GB.SUPPLIER FROM CSV AT EXASOL_DEMO_DATA FILE 'tpch_1000gb/supplier.csv';

-- Use multiple files to improve performance and have all nodes import in parallel
IMPORT INTO TPCH_1000GB.CUSTOMER FROM CSV AT EXASOL_DEMO_DATA FILE 'tpch_1000gb/customer_pt1.csv' FILE 'tpch_1000gb/customer_pt2.csv' FILE 'tpch_1000gb/customer_pt3.csv' FILE 'tpch_1000gb/customer_pt4.csv';
IMPORT INTO TPCH_1000GB.ORDERS FROM CSV AT EXASOL_DEMO_DATA FILE 'tpch_1000gb/orders_pt1.csv' FILE 'tpch_1000gb/orders_pt2.csv' FILE 'tpch_1000gb/orders_pt3.csv' FILE 'tpch_1000gb/orders_pt4.csv';
IMPORT INTO TPCH_1000GB.PART FROM CSV AT EXASOL_DEMO_DATA FILE 'tpch_1000gb/part_pt1.csv' FILE 'tpch_1000gb/part_pt2.csv' FILE 'tpch_1000gb/part_pt3.csv' FILE 'tpch_1000gb/part_pt4.csv';
IMPORT INTO TPCH_1000GB.PARTSUPP FROM CSV AT EXASOL_DEMO_DATA FILE 'tpch_1000gb/partsupp_pt1.csv' FILE 'tpch_1000gb/partsupp_pt2.csv' FILE 'tpch_1000gb/partsupp_pt3.csv' FILE 'tpch_1000gb/partsupp_pt4.csv';
IMPORT INTO TPCH_1000GB.LINEITEM FROM CSV AT EXASOL_DEMO_DATA FILE 'tpch_1000gb/lineitem_pt1.csv' FILE 'tpch_1000gb/lineitem_pt2.csv' FILE 'tpch_1000gb/lineitem_pt3.csv' FILE 'tpch_1000gb/lineitem_pt4.csv';

SELECT 'All data imported! Enjoy exploring the TPC-H dataset!';