Data May Fail to Be Imported When HammerDB Is Used to Test Greenplum

Symptom

Data may fail to be imported for some server models when HammerDB is used to test Greenplum. You can import data using TBL files.

Key Process and Cause Analysis

A possible cause is that the default HammerDB configuration does not match the system environment. You can import data using TBL files.

Conclusion and Solution

1. Generate TBL files.

   dbset db pg
   dbset bm tpc-h
   dgset directory /data/tbl
   dgset scale_fact 1
   dgset vu 1
   datagenrun

   Command	Description
   dgset directory /data/tbl	Specifies the path for storing the TBL files. The path must exist and have available space.
   dgset scale_fact 1	Specifies the data volume.
   dgset vu 1	Specifies the number of concurrent tasks, which is related to the number of generated TBL files.
   datagenrun	Creates TBL files.

   

   

2. Check the generated TBL files.

   ll ./data/tbl/

   

3. Copy the generated TBL files to the database server. 192.168.67.106 is the IP address of the target database server. You can replace it with the actual IP address.

   1	scp -r /data/tbl/ 192.168.67.106:/data/

   

4. Create a database on the database server and create tables based on the test model. You can view the OLAP test script in src/postgresql/pgolap.tcl in the HammerDB installation path.

   Create tables (using row-store by default).

   CREATE TABLE ORDERS (O_ORDERDATE TIMESTAMP, O_ORDERKEY NUMERIC NOT NULL, O_CUSTKEY NUMERIC NOT NULL, O_ORDERPRIORITY CHAR(15), O_SHIPPRIORITY NUMERIC, O_CLERK CHAR(15), O_ORDERSTATUS CHAR(1), O_TOTALPRICE NUMERIC, O_COMMENT VARCHAR(79))  DISTRIBUTED BY (O_ORDERKEY);
   CREATE TABLE PARTSUPP (PS_PARTKEY NUMERIC NOT NULL, PS_SUPPKEY NUMERIC NOT NULL, PS_SUPPLYCOST NUMERIC NOT NULL, PS_AVAILQTY NUMERIC, PS_COMMENT VARCHAR(199))  DISTRIBUTED BY (PS_PARTKEY,PS_SUPPKEY);
   CREATE TABLE CUSTOMER(C_CUSTKEY NUMERIC NOT NULL, C_MKTSEGMENT CHAR(10), C_NATIONKEY NUMERIC, C_NAME VARCHAR(25), C_ADDRESS VARCHAR(40), C_PHONE CHAR(15), C_ACCTBAL NUMERIC, C_COMMENT VARCHAR(118))  DISTRIBUTED BY (C_CUSTKEY);
   CREATE TABLE PART(P_PARTKEY NUMERIC NOT NULL, P_TYPE VARCHAR(25), P_SIZE NUMERIC, P_BRAND CHAR(10), P_NAME VARCHAR(55), P_CONTAINER CHAR(10), P_MFGR CHAR(25), P_RETAILPRICE NUMERIC, P_COMMENT VARCHAR(23))  DISTRIBUTED BY (P_PARTKEY);
   CREATE TABLE SUPPLIER(S_SUPPKEY NUMERIC NOT NULL, S_NATIONKEY NUMERIC, S_COMMENT VARCHAR(102), S_NAME CHAR(25), S_ADDRESS VARCHAR(40), S_PHONE CHAR(15), S_ACCTBAL NUMERIC)   DISTRIBUTED BY (S_SUPPKEY);
   CREATE TABLE NATION(N_NATIONKEY NUMERIC NOT NULL, N_NAME CHAR(25), N_REGIONKEY NUMERIC, N_COMMENT VARCHAR(152))  DISTRIBUTED BY (N_NATIONKEY);
   CREATE TABLE REGION(R_REGIONKEY NUMERIC, R_NAME CHAR(25), R_COMMENT VARCHAR(152))  DISTRIBUTED BY (R_REGIONKEY);
   CREATE TABLE LINEITEM(L_SHIPDATE TIMESTAMP, L_ORDERKEY NUMERIC NOT NULL, L_DISCOUNT NUMERIC NOT NULL, L_EXTENDEDPRICE NUMERIC NOT NULL, L_SUPPKEY NUMERIC NOT NULL, L_QUANTITY NUMERIC NOT NULL, L_RETURNFLAG CHAR(1), L_PARTKEY NUMERIC NOT NULL, L_LINESTATUS CHAR(1), L_TAX NUMERIC NOT NULL, L_COMMITDATE TIMESTAMP, L_RECEIPTDATE TIMESTAMP, L_SHIPMODE CHAR(10), L_LINENUMBER NUMERIC NOT NULL, L_SHIPINSTRUCT CHAR(25), L_COMMENT VARCHAR(44))  DISTRIBUTED BY (L_LINENUMBER, L_ORDERKEY);

   

5. Import the TBL files as the corresponding tables to the database.

   1	copy ${table_name} from '/data/tbl/${table_name}.tbl' WITH DELIMITER AS '|';

   ${table_name} indicates the table name. There are eight tables in total.

   

6. Create indexes.

   CREATE INDEX REGION_PK ON REGION (R_REGIONKEY);
   CREATE INDEX NATION_PK ON NATION (N_NATIONKEY);
   CREATE INDEX SUPPLIER_PK ON SUPPLIER (S_SUPPKEY);
   CREATE INDEX PARTSUPP_PK ON PARTSUPP (PS_PARTKEY,PS_SUPPKEY);
   CREATE INDEX PART_PK ON PART (P_PARTKEY);
   CREATE INDEX ORDERS_PK ON ORDERS (O_ORDERKEY,o_orderdate);
   CREATE INDEX LINEITEM_PK ON LINEITEM (L_LINENUMBER, L_ORDERKEY,l_shipdate);
   CREATE INDEX CUSTOMER_PK ON CUSTOMER (C_CUSTKEY);