openGauss数据库tpch测试

 

CREATE TABLE PART (
    P_PARTKEY        SERIAL,
    P_NAME            VARCHAR(55),
    P_MFGR            CHAR(25),
    P_BRAND            CHAR(10),
    P_TYPE            VARCHAR(25),
    P_SIZE            INTEGER,
    P_CONTAINER        CHAR(10),
    P_RETAILPRICE            DECIMAL,
    P_COMMENT        VARCHAR(23)
);

CREATE TABLE SUPPLIER (
    S_SUPPKEY        SERIAL,
    S_NAME            CHAR(25),
    S_ADDRESS        VARCHAR(40),
    S_NATIONKEY        INTEGER NOT NULL,
    S_PHONE            CHAR(15),
    S_ACCTBAL        DECIMAL,
    S_COMMENT        VARCHAR(101)
);

CREATE TABLE PARTSUPP (
    PS_PARTKEY        INTEGER NOT NULL,
    PS_SUPPKEY        INTEGER NOT NULL,
    PS_AVAILQTY        INTEGER,
    PS_SUPPLYCOST            DECIMAL,
    PS_COMMENT        VARCHAR(199)
);

CREATE TABLE CUSTOMER (
    C_CUSTKEY        SERIAL,
    C_NAME            VARCHAR(25),
    C_ADDRESS        VARCHAR(40),
    C_NATIONKEY        INTEGER NOT NULL, -- references N_NATIONKEY
    C_PHONE            CHAR(15),
    C_ACCTBAL        DECIMAL,
    C_MKTSEGMENT            CHAR(10),
    C_COMMENT        VARCHAR(117)
);

CREATE TABLE ORDERS (
    O_ORDERKEY        SERIAL,
    O_CUSTKEY        INTEGER NOT NULL,
    O_ORDERSTATUS            CHAR(1),
    O_TOTALPRICE            DECIMAL,
    O_ORDERDATE        DATE,
    O_ORDERPRIORITY            CHAR(15),
    O_CLERK            CHAR(15),
    O_SHIPPRIORITY            INTEGER,
    O_COMMENT        VARCHAR(79)
);

CREATE TABLE LINEITEM (
    L_ORDERKEY        INTEGER NOT NULL, -- references O_ORDERKEY
    L_PARTKEY        INTEGER NOT NULL, -- references P_PARTKEY (compound fk to PARTSUPP)
    L_SUPPKEY        INTEGER NOT NULL, -- references S_SUPPKEY (compound fk to PARTSUPP)
    L_LINENUMBER            INTEGER,
    L_QUANTITY        DECIMAL,
    L_EXTENDEDPRICE            DECIMAL,
    L_DISCOUNT        DECIMAL,
    L_TAX            DECIMAL,
    L_RETURNFLAG            CHAR(1),
    L_LINESTATUS            CHAR(1),
    L_SHIPDATE        DATE,
    L_COMMITDATE            DATE,
    L_RECEIPTDATE            DATE,
    L_SHIPINSTRUCT            CHAR(25),
    L_SHIPMODE        CHAR(10),
    L_COMMENT        VARCHAR(44)
);

CREATE TABLE NATION (
    N_NATIONKEY        SERIAL,
    N_NAME            CHAR(25),
    N_REGIONKEY        INTEGER NOT NULL,  -- references R_REGIONKEY
    N_COMMENT        VARCHAR(152)
);

CREATE TABLE REGION (
    R_REGIONKEY    SERIAL,
    R_NAME        CHAR(25),
    R_COMMENT    VARCHAR(152)
);

 

 

COPY region FROM '/data/tpch_data/region.tbl' WITH (FORMAT csv, DELIMITER '|');
COPY nation FROM '/data/tpch_data/nation.tbl' WITH (FORMAT csv, DELIMITER '|');
COPY supplier FROM '/data/tpch_data/supplier.tbl' WITH (FORMAT csv, DELIMITER '|');
COPY customer FROM '/data/tpch_data/customer.tbl' WITH (FORMAT csv, DELIMITER '|');
COPY partsupp FROM '/data/tpch_data/partsupp.tbl' WITH (FORMAT csv, DELIMITER '|');
COPY part FROM '/data/tpch_data/part.tbl' WITH (FORMAT csv, DELIMITER '|');
COPY orders FROM '/data/tpch_data/orders.tbl' WITH (FORMAT csv, DELIMITER '|');
COPY lineitem FROM '/data/tpch_data/lineitem.tbl' WITH (FORMAT csv, DELIMITER '|');