# Single Table Testing

## Create a New Cloud Data Warehouse UClickhouse

Please refer to [Quick Start](/docs/uclickhouse/gettingstart)

## Connect to Cloud Data Warehouse UClickhouse

Please refer to **Operation Guide -> [Connect Cluster](/uclickhouse/operation_guide/connect_cluster)**

## Execute Database and Table Creation

**The DDL statements are as follows:**

```sql
# 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](/docs/uclickhouse/uclickhouse_test/tool/ssb)**

## Data Import

```shell
[root@xxxxx ssb-dbgen]# clickhouse-client --host=<UClickhouse node address> --port=9000 --user=admin            --password=<Cluster setup admin password> --database=ssb_test --query "INSERT INTO customer FORMAT CSV" < customer.tbl
[root@xxxxx ssb-dbgen]# clickhouse-client --host=<UClickhouse node address> --port=9000 --user=admin            --password=<Cluster setup admin password> --database=ssb_test --query "INSERT INTO supplier FORMAT CSV" < supplier.tbl
[root@xxxxx ssb-dbgen]# clickhouse-client --host=<UClickhouse node address> --port=9000 --user=admin            --password=<Cluster setup admin password> --database=ssb_test --query "INSERT INTO part FORMAT CSV" < part.tbl
[root@xxxxx ssb-dbgen]# clickhouse-client --host=<UClickhouse node address> --port=9000 --user=admin            --password=<Cluster setup admin password> --database=ssb_test --query "INSERT INTO lineorder FORMAT CSV" < lineorder.tbl
[root@xxxxx ssb-dbgen]# clickhouse-client --host=<UClickhouse node address> --port=9000 --user=admin            --password=<Cluster setup admin password> --database=ssb_test --query "INSERT INTO dates FORMAT CSV" < date.tbl
```

## Create Single Table

**Execute the following statements:**

```sql
CREATE TABLE IF NOT EXISTS ssb_test.lineorder_flat  
    ENGINE = MergeTree
    PARTITION BY toYear(LO_ORDERDATE)
    ORDER BY (LO_ORDERDATE, LO_ORDERKEY) AS
SELECT
    l.LO_ORDERKEY AS LO_ORDERKEY,
    l.LO_LINENUMBER AS LO_LINENUMBER,
    l.LO_CUSTKEY AS LO_CUSTKEY,
    l.LO_PARTKEY AS LO_PARTKEY,
    l.LO_SUPPKEY AS LO_SUPPKEY,
    l.LO_ORDERDATE AS LO_ORDERDATE,
    l.LO_ORDERPRIORITY AS LO_ORDERPRIORITY,
    l.LO_SHIPPRIORITY AS LO_SHIPPRIORITY,
    l.LO_QUANTITY AS LO_QUANTITY,
    l.LO_EXTENDEDPRICE AS LO_EXTENDEDPRICE,
    l.LO_ORDTOTALPRICE AS LO_ORDTOTALPRICE,
    l.LO_DISCOUNT AS LO_DISCOUNT,
    l.LO_REVENUE AS LO_REVENUE,
    l.LO_SUPPLYCOST AS LO_SUPPLYCOST,
    l.LO_TAX AS LO_TAX,
    l.LO_COMMITDATE AS LO_COMMITDATE,
    l.LO_SHIPMODE AS LO_SHIPMODE,
    c.C_NAME AS C_NAME,
    c.C_ADDRESS AS C_ADDRESS,
    c.C_CITY AS C_CITY,
    c.C_NATION AS C_NATION,
    c.C_REGION AS C_REGION,
    c.C_PHONE AS C_PHONE,
    c.C_MKTSEGMENT AS C_MKTSEGMENT,
    s.S_NAME AS S_NAME,
    s.S_ADDRESS AS S_ADDRESS,
    s.S_CITY AS S_CITY,
    s.S_NATION AS S_NATION,
    s.S_REGION AS S_REGION,
    s.S_PHONE AS S_PHONE,
    p.P_NAME AS P_NAME,
    p.P_MFGR AS P_MFGR,
    p.P_CATEGORY AS P_CATEGORY,
    p.P_BRAND AS P_BRAND,
    p.P_COLOR AS P_COLOR,
    p.P_TYPE AS P_TYPE,
    p.P_SIZE AS P_SIZE,
    p.P_CONTAINER AS P_CONTAINER
FROM ssb_test.lineorder AS l
         INNER JOIN ssb_test.customer AS c ON c.C_CUSTKEY = l.LO_CUSTKEY
         INNER JOIN ssb_test.supplier AS s ON s.S_SUPPKEY = l.LO_SUPPKEY
         INNER JOIN ssb_test.part AS p ON p.P_PARTKEY = l.LO_PARTKEY;
```

## Run Single Table Performance Testing Statements

Please refer to **Performance Testing Guide -> [Single Table Performance Testing Statements](/docs/uclickhouse/uclickhouse_test/single_query)**