AnalyticDB:TPC-H testing

Enable the Native and XUANWU_V2 compute engines (optional)

Skip this step if you only want to test the native and XUANWU compute engines. To test the Native and XUANWU_V2 compute engines, run the following commands before creating the test tables:

SET ADB_CONFIG native_engine_opt_enabled = true;
SET ADB_CONFIG rc_ddl_engine_rewrite_xuanwuv2 = true;

Create the test database and tables

Create a test database. If one already exists, skip this step.

CREATE DATABASE IF NOT EXISTS tpch;

Create the following eight tables and one view in the tpch database.

NATION table

CREATE TABLE nation (
  n_nationkey INT NOT NULL COMMENT '',
  n_name VARCHAR NOT NULL COMMENT '',
  n_regionkey INT NOT NULL COMMENT '',
  n_comment VARCHAR COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (n_nationkey)
) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

REGION table

CREATE TABLE region (
  r_regionkey INT NOT NULL COMMENT '',
  r_name VARCHAR NOT NULL COMMENT '',
  r_comment VARCHAR COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (r_regionkey)
) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

PART table

CREATE TABLE part (
  p_partkey INT NOT NULL COMMENT '',
  p_name VARCHAR NOT NULL COMMENT '',
  p_mfgr VARCHAR NOT NULL COMMENT '',
  p_brand VARCHAR(10) NOT NULL COMMENT '',
  p_type VARCHAR NOT NULL COMMENT '',
  p_size INT NOT NULL COMMENT '',
  p_container VARCHAR(10) NOT NULL COMMENT '',
  p_retailprice DOUBLE NOT NULL COMMENT '',
  p_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (p_partkey)
) DISTRIBUTED BY HASH (p_partkey) INDEX_ALL = 'Y' compression='lz4hc';

SUPPLIER table

CREATE TABLE supplier (
  s_suppkey INT NOT NULL COMMENT '',
  s_name VARCHAR NOT NULL COMMENT '',
  s_address VARCHAR NOT NULL COMMENT '',
  s_nationkey INT NOT NULL COMMENT '',
  s_phone VARCHAR(15) NOT NULL COMMENT '',
  s_acctbal DOUBLE NOT NULL COMMENT '',
  s_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (s_suppkey)
) DISTRIBUTED BY HASH (s_suppkey) INDEX_ALL = 'Y' compression='lz4hc';

PARTSUPP table

CREATE TABLE partsupp (
  ps_partkey INT NOT NULL COMMENT '',
  ps_suppkey INT NOT NULL COMMENT '',
  ps_availqty INT NOT NULL COMMENT '',
  ps_supplycost DOUBLE NOT NULL COMMENT '',
  ps_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (ps_partkey, ps_suppkey)
) DISTRIBUTED BY HASH (ps_partkey) INDEX_ALL = 'Y' compression='lz4hc';

CUSTOMER table

CREATE TABLE customer (
  c_custkey INT NOT NULL COMMENT '',
  c_name VARCHAR NOT NULL COMMENT '',
  c_address VARCHAR NOT NULL COMMENT '',
  c_nationkey INT NOT NULL COMMENT '',
  c_phone VARCHAR(15) NOT NULL COMMENT '',
  c_acctbal DOUBLE NOT NULL COMMENT '',
  c_mktsegment VARCHAR(10) NOT NULL COMMENT '',
  c_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  PRIMARY KEY (c_custkey)
) DISTRIBUTED BY HASH (c_custkey) INDEX_ALL = 'Y' compression='lz4hc';

ORDERS table

CREATE TABLE orders (
  o_orderkey BIGINT NOT NULL COMMENT '',
  o_custkey INT NOT NULL COMMENT '',
  o_orderstatus VARCHAR(1) NOT NULL COMMENT '',
  o_totalprice DOUBLE NOT NULL COMMENT '',
  o_orderdate DATE NOT NULL COMMENT '',
  o_orderpriority VARCHAR(15) NOT NULL COMMENT '',
  o_clerk VARCHAR(15) NOT NULL COMMENT '',
  o_shippriority INT NOT NULL COMMENT '',
  o_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  KEY idx_o_custkey (o_custkey),
  KEY idx_o_orderdate (o_orderdate),
  KEY idx_o_orderkey (o_orderkey),
  KEY idx_o_orderstatus (o_orderstatus)
) DISTRIBUTED BY HASH (o_orderkey) PARTITION BY VALUE(date_format(o_orderdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';

LINEITEM table

CREATE TABLE lineitem (
  l_orderkey BIGINT NOT NULL COMMENT '',
  l_partkey INT NOT NULL COMMENT '',
  l_suppkey INT NOT NULL COMMENT '',
  l_linenumber INT NOT NULL COMMENT '',
  l_quantity DOUBLE NOT NULL COMMENT '',
  l_extendedprice DOUBLE NOT NULL COMMENT '',
  l_discount DOUBLE NOT NULL COMMENT '',
  l_tax DOUBLE NOT NULL COMMENT '',
  l_returnflag VARCHAR(1) NOT NULL COMMENT '',
  l_linestatus VARCHAR(1) NOT NULL COMMENT '',
  l_shipdate DATE NOT NULL COMMENT '',
  l_commitdate DATE NOT NULL COMMENT '',
  l_receiptdate DATE NOT NULL COMMENT '',
  l_shipinstruct VARCHAR(25) NOT NULL COMMENT '',
  l_shipmode VARCHAR(10) NOT NULL COMMENT '',
  l_comment VARCHAR NOT NULL COMMENT '',
  dummy VARCHAR,
  KEY idx_l_orderkey (l_orderkey),
  KEY idx_l_partkey (l_partkey),
  KEY idx_l_receiptdate (l_receiptdate),
  KEY idx_l_returnflag (l_returnflag),
  KEY idx_l_shipdate (l_shipdate),
  KEY idx_l_shipinstruct (l_shipinstruct),
  KEY idx_l_shipmode (l_shipmode),
  KEY idx_l_suppkey (l_suppkey),
  clustered key clustered_index(`l_orderkey` ASC)
) DISTRIBUTED BY HASH  (l_orderkey) PARTITION BY VALUE(date_format(l_shipdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL='N'COMPRESSION='lz4hc';

REVENUE0 view

CREATE VIEW `revenue0` AS
SELECT
  `l_suppkey` supplier_no,
  `sum`(
    (
      `l_extendedprice` * (1 - `l_discount`)
    )
  ) total_revenue
FROM
  lineitem
WHERE
  (
    (`l_shipdate` >= DATE '1996-01-01')
    AND (
      `l_shipdate` < (
        DATE '1996-01-01' + INTERVAL '3' MONTH
      )
    )
  )
GROUP BY
  `l_suppkey`;

Import from OSS external tables (recommended)

AnalyticDB for MySQL provides a pre-built 1 TB TPC-H dataset on OSS. The dataset path varies by region — use the path that matches your cluster's region.

Step 1: Create an external database.

CREATE EXTERNAL DATABASE IF NOT EXISTS external_tpch;

Step 2: Create eight external tables.

Replace the OSS path in the LOCATION parameter with the path for your cluster's region.

CREATE EXTERNAl TABLE external_tpch.nation (
	n_nationkey int NOT NULL COMMENT '',
	n_name varchar NOT NULL COMMENT '',
	n_regionkey int NOT NULL COMMENT '',
	n_comment varchar COMMENT '',
	`dummy` varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/nation/';

CREATE EXTERNAl TABLE external_tpch.customer (
	c_custkey int NOT NULL COMMENT '',
	c_name varchar NOT NULL COMMENT '',
	c_address varchar NOT NULL COMMENT '',
	c_nationkey int NOT NULL COMMENT '',
	c_phone varchar(15) NOT NULL COMMENT '',
	c_acctbal double NOT NULL COMMENT '',
	c_mktsegment varchar(10) NOT NULL COMMENT '',
	c_comment varchar NOT NULL COMMENT '',
	`dummy` varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/customer/';

CREATE EXTERNAl TABLE external_tpch.lineitem (
	l_orderkey bigint NOT NULL COMMENT '',
	l_partkey int NOT NULL COMMENT '',
	l_suppkey int NOT NULL COMMENT '',
	l_linenumber int NOT NULL COMMENT '',
	l_quantity double NOT NULL COMMENT '',
	l_extendedprice double NOT NULL COMMENT '',
	l_discount double NOT NULL COMMENT '',
	l_tax double NOT NULL COMMENT '',
	l_returnflag varchar(1) NOT NULL COMMENT '',
	l_linestatus varchar(1) NOT NULL COMMENT '',
	l_shipdate date NOT NULL COMMENT '',
	l_commitdate date NOT NULL COMMENT '',
	l_receiptdate date NOT NULL COMMENT '',
	l_shipinstruct varchar(25) NOT NULL COMMENT '',
	l_shipmode varchar(10) NOT NULL COMMENT '',
	l_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/lineitem/';

CREATE EXTERNAl TABLE external_tpch.orders (
	o_orderkey bigint NOT NULL COMMENT '',
	o_custkey int NOT NULL COMMENT '',
	o_orderstatus varchar(1) NOT NULL COMMENT '',
	o_totalprice double NOT NULL COMMENT '',
	o_orderdate date NOT NULL COMMENT '',
	o_orderpriority varchar(15) NOT NULL COMMENT '',
	o_clerk varchar(15) NOT NULL COMMENT '',
	o_shippriority int NOT NULL COMMENT '',
	o_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/orders/';

CREATE EXTERNAl TABLE external_tpch.part (
	p_partkey int NOT NULL COMMENT '',
	p_name varchar NOT NULL COMMENT '',
	p_mfgr varchar NOT NULL COMMENT '',
	p_brand varchar(10) NOT NULL COMMENT '',
	p_type varchar NOT NULL COMMENT '',
	p_size int NOT NULL COMMENT '',
	p_container varchar(10) NOT NULL COMMENT '',
	p_retailprice double NOT NULL COMMENT '',
	p_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/part/';

CREATE EXTERNAl TABLE external_tpch.partsupp (
	ps_partkey int NOT NULL COMMENT '',
	ps_suppkey int NOT NULL COMMENT '',
	ps_availqty int NOT NULL COMMENT '',
	ps_supplycost double NOT NULL COMMENT '',
	ps_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/partsupp/';

CREATE EXTERNAl TABLE external_tpch.region (
	r_regionkey int NOT NULL COMMENT '',
	r_name varchar NOT NULL COMMENT '',
	r_comment varchar COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/region/';

CREATE EXTERNAl TABLE external_tpch.supplier (
	s_suppkey int NOT NULL COMMENT '',
	s_name varchar NOT NULL COMMENT '',
	s_address varchar NOT NULL COMMENT '',
	s_nationkey int NOT NULL COMMENT '',
	s_phone varchar(15) NOT NULL COMMENT '',
	s_acctbal double NOT NULL COMMENT '',
	s_comment varchar NOT NULL COMMENT '',
	dummy varchar
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE
LOCATION 'oss://dataset-cn-beijing-external/TPC-H/1TB/supplier/';

Step 3: Load data from the external tables into the internal tables.

INSERT OVERWRITE INTO customer
SELECT * FROM external_tpch.customer;

INSERT OVERWRITE INTO lineitem
SELECT * FROM external_tpch.lineitem;

INSERT INTO nation
SELECT * FROM external_tpch.nation;

INSERT OVERWRITE INTO orders
SELECT * FROM external_tpch.orders;

INSERT OVERWRITE INTO part
SELECT * FROM external_tpch.part;

INSERT OVERWRITE INTO partsupp
SELECT * FROM external_tpch.partsupp;

INSERT INTO region
SELECT * FROM external_tpch.region;

INSERT OVERWRITE INTO supplier
SELECT * FROM external_tpch.supplier;

Step 4: Collect statistics.

ANALYZE TABLE customer UPDATE HISTOGRAM;
ANALYZE TABLE lineitem UPDATE HISTOGRAM;
ANALYZE TABLE nation UPDATE HISTOGRAM;
ANALYZE TABLE orders UPDATE HISTOGRAM;
ANALYZE TABLE part UPDATE HISTOGRAM;
ANALYZE TABLE partsupp UPDATE HISTOGRAM;
ANALYZE TABLE region UPDATE HISTOGRAM;
ANALYZE TABLE supplier UPDATE HISTOGRAM;

The query optimizer uses histogram statistics to generate high-quality execution plans. Collecting statistics after data import is essential for optimal query performance. For details, see Statistics.

Import data using LOAD DATA

Use this method if the OSS external table path is not available for your region, or if you want to test with a custom scale factor.

Step 1: Generate test data using DBGEN.

Download the TPC-H data generation tool DBGEN from the TPC website, compile it, and generate the data files. For usage details, see tpch-dbgen.

./dbgen -s $scale -C $chunks -S $i -f

Each command generates one chunk. To generate the dataset in multiple parts, run the command multiple times with different -S values. For more information about how to use dbgen, see tpch-dbgen.

Step 2: Load the data files into AnalyticDB for MySQL.

This example uses Windows line endings (\r\n). Adjust LINES TERMINATED BY for your operating system.

LOAD DATA LOCAL INFILE 'customer.tbl' INTO TABLE CUSTOMER
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'orders.tbl'   INTO TABLE ORDERS
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'lineitem.tbl' INTO TABLE LINEITEM
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'nation.tbl'   INTO TABLE NATION
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'partsupp.tbl' INTO TABLE PARTSUPP
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'part.tbl'     INTO TABLE PART
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'region.tbl'   INTO TABLE REGION
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE 'supplier.tbl' INTO TABLE SUPPLIER
FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n';

Step 3: Collect statistics.

ANALYZE TABLE customer UPDATE HISTOGRAM;
ANALYZE TABLE lineitem UPDATE HISTOGRAM;
ANALYZE TABLE nation UPDATE HISTOGRAM;
ANALYZE TABLE orders UPDATE HISTOGRAM;
ANALYZE TABLE part UPDATE HISTOGRAM;
ANALYZE TABLE partsupp UPDATE HISTOGRAM;
ANALYZE TABLE region UPDATE HISTOGRAM;
ANALYZE TABLE supplier UPDATE HISTOGRAM;

The query optimizer uses histogram statistics to generate high-quality execution plans. Collecting statistics after data import is essential for optimal query performance. For details, see Statistics.