Skip to Content
Performance Test GuideTest UClickhouseSingle replica instance testMultiple Table Test

Multi-table Testing

Create Cloud Data Warehouse UClickhouse

Please refer to Quick Start

Connect to Cloud Data Warehouse UClickhouse

Please refer to User Guide -> Connect Cluster

Execute Database and Table Creation

DDL statements are as follows:

# Create database CREATE DATABASE IF NOT EXISTS ssb_test ; # Create customer CREATE TABLE IF NOT EXISTS ssb_test.customer ( C_CUSTKEY UInt32, C_NAME String, C_ADDRESS String, C_CITY LowCardinality(String), C_NATION LowCardinality(String), C_REGION LowCardinality(String), C_PHONE String, C_MKTSEGMENT LowCardinality(String) ) ENGINE = MergeTree ORDER BY (C_CUSTKEY); # Create lineorder CREATE TABLE IF NOT EXISTS ssb_test.lineorder ( LO_ORDERKEY UInt32, LO_LINENUMBER UInt8, LO_CUSTKEY UInt32, LO_PARTKEY UInt32, LO_SUPPKEY UInt32, LO_ORDERDATE Date, LO_ORDERPRIORITY LowCardinality(String), LO_SHIPPRIORITY UInt8, LO_QUANTITY UInt8, LO_EXTENDEDPRICE UInt32, LO_ORDTOTALPRICE UInt32, LO_DISCOUNT UInt8, LO_REVENUE UInt32, LO_SUPPLYCOST UInt32, LO_TAX UInt8, LO_COMMITDATE Date, LO_SHIPMODE LowCardinality(String) ) ENGINE = MergeTree PARTITION BY toYear(LO_ORDERDATE) ORDER BY (LO_ORDERDATE, LO_ORDERKEY); # Create part CREATE TABLE IF NOT EXISTS ssb_test.part ( P_PARTKEY UInt32, P_NAME String, P_MFGR LowCardinality(String), P_CATEGORY LowCardinality(String), P_BRAND LowCardinality(String), P_COLOR LowCardinality(String), P_TYPE LowCardinality(String), P_SIZE UInt8, P_CONTAINER LowCardinality(String) ) ENGINE = MergeTree ORDER BY P_PARTKEY; # Create supplier CREATE TABLE IF NOT EXISTS ssb_test.supplier ( S_SUPPKEY UInt32, S_NAME String, S_ADDRESS String, S_CITY LowCardinality(String), S_NATION LowCardinality(String), S_REGION LowCardinality(String), S_PHONE String ) ENGINE = MergeTree ORDER BY S_SUPPKEY; # Create dates CREATE TABLE IF NOT EXISTS ssb_test.dates ( D_DATEKEY UInt32, D_DATE String, D_DAYOFWEEK String, D_MONTH String, D_YEAR UInt32, D_YEARMONTHNUM UInt32, D_YEARMONTH String, D_DAYNUMINWEEK UInt32, D_DAYNUMINMONTH UInt32, D_DAYNUMINYEAR UInt32, D_MONTHNUMINYEAR UInt32, D_WEEKNUMINYEAR UInt32, D_SELLINGSEASON String, D_LASTDAYINWEEKFL UInt32, D_LASTDAYINMONTHFL UInt32, D_HOLIDAYFL UInt32, D_WEEKDAYFL UInt32 ) ENGINE = MergeTree ORDER BY D_DATEKEY;

Data Preparation

Please refer to Performance Testing Tools -> Star Schema Benchmark

Data Import

[root@xxxxx ssb-dbgen]# clickhouse-client --host=<Cloud Data Warehouse UClickhouse Node Address> --port=9000 --user=admin --password=<Management Password Set When Creating Cluster> --database=ssb_test --query "INSERT INTO customer FORMAT CSV" < customer.tbl [root@xxxxx ssb-dbgen]# clickhouse-client --host=<Cloud Data Warehouse UClickhouse Node Address> --port=9000 --user=admin --password=<Management Password Set When Creating Cluster> --database=ssb_test --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl [root@xxxxx ssb-dbgen]# clickhouse-client --host=<Cloud Data Warehouse UClickhouse Node Address> --port=9000 --user=admin --password=<Management Password Set When Creating Cluster> --database=ssb_test --query "INSERT INTO part FORMAT CSV" < part.tbl [root@xxxxx ssb-dbgen]# clickhouse-client --host=<Cloud Data Warehouse UClickhouse Node Address> --port=9000 --user=admin --password=<Management Password Set When Creating Cluster> --database=ssb_test --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl [root@xxxxx ssb-dbgen]# clickhouse-client --host=<Cloud Data Warehouse UClickhouse Node Address> --port=9000 --user=admin --password=<Management Password Set When Creating Cluster> --database=ssb_test --query "INSERT INTO dates FORMAT CSV" < date.tbl

Run Multi-table Performance Test Statements

Please refer to Performance Testing Guide -> Multi-table Performance Test Statements