TPC-DS benchmark|V4.3.3|OceanBase Database| docs|Distributed Database

TPC-DS benchmark

Last Updated：2024-12-02 03:48:29 Updated

This topic describes how to run the TPC-DS benchmark on OceanBase Database in analytical processing (AP) scenarios.

Note

To enhance user experience and ensure optimal performance, OceanBase Database has been extensively optimized since V4.0.0. The performance testing methods outlined here focus on tuning basic parameters to provide developers with an improved database experience.

What is TPC-DS?

TPC-DS is a decision support benchmark that contains multi-dimensional regular application models for a decision support system, including queries and data maintenance. It is a representative benchmark for assessing the performance of the system under test (SUT) as a decision support system.

This benchmark illustrates decision support systems that:

Test large-scale data.
Answer real-world business questions.
Execute queries with diverse or complex requirements, such as ad-hoc queries, reporting, iterative online analytical processing (OLAP), and data mining.
Have high CPU and I/O load.
Periodically perform synchronization with source online transaction processing (OLTP) databases through database maintenance.
Provide big data solutions, such as relational database management systems (RDBMSs) and Hadoop/Spark-based systems.

The benchmark result measures the query response time in single-user mode, query throughput in multi-user mode, and data maintenance performance in complex multi-user decision support scenarios.

TPC-DS adopts multi-dimensional schemas such as the star or snowflake schema. It contains seven fact tables and 17 dimension tables, with an average of 18 columns per table. Workloads for TPC-DS include 99 SQL queries, covering the core parts of SQL-1999 and SQL-2003 as well as OLAP. TPC-DS contains complex applications such as statistics collection on large data sets, report generation, online queries, and data mining. The test data and values are skewed, which is consistent with real data. TPC-DS is quite close to real scenarios, and it is also difficult to run the test.

Environment preparation

Prepare your testing environment based on the following requirements:

Java Development Kit (JDK): Use V1.8u131 or later.
Make: Run the yum install make command to install make.
GCC: Run the yum install gcc command to install GCC.
MySQL-devel: Run the yum install mysql-devel command to install MySQL-devel.
MySQL Connector/Python: Run the sudo yum install MySQL-python command to install MySQL Connector/Python.
PrettyTable: Run the pip install prettytable command to install PrettyTable.
Java Database Connectivity (JDBC): Use mysql-connector-java-5.1.47.
TPC-DS tools: Click here to download the tools.
OBClient: For more information, see OBClient Documentation.
OceanBase Database: For more information, see Quick start with OceanBase Database Community Edition.
IOPS: We recommend that the disk IOPS be above 10,000.

Tenant specifications:

CREATE RESOURCE UNIT tpcds_unit max_cpu 30, memory_size '180g';
CREATE RESOURCE POOL tpcds_pool unit = 'tpcds_unit', unit_num = 1, zone_list=('zone1','zone2','zone3');
CREATE TENANT tpcds_mysql resource_pool_list=('tpcds_pool'),  zone_list('zone1', 'zone2', 'zone3'), primary_zone=RANDOM, locality='F@zone1,F@zone2,F@zone3' set variables ob_compatibility_mode='mysql', ob_tcp_invited_nodes='%',secure_file_priv = '';

Notice

The tenant specifications mentioned above are based on the hardware configuration in the TPC-DS benchmark report. You need to adjust the specifications based on the hardware configurations of your database.
When deploying a cluster, it is recommended not to use the obd cluster autodeploy command. This command prioritizes stability and does not maximize resource utilization (for example, it does not use all available memory). It is advisable to optimize the configuration file separately to maximize resource utilization.

Cluster deployment

This test requires a total of four servers. Deploy TPC-DS on one server for stress testing, and set up the OceanBase cluster on the remaining three servers in a 1:1:1 configuration.

Note

In the TPC-DS test, the server for deploying TPC-DS requires only four CPU cores and 16 GB of memory.
After a successful deployment, create the tenants and users needed for the TPC-DS test. The sys tenant is a built-in system tenant for cluster management and do not use it to perform the test. Set the tenant's primary_zone to RANDOM, which indicates that the leader of new table partitions is randomly assigned to one of these three servers.

Manual TPC-DS test

Environment tuning

OceanBase Database tuning.

Run the obclient -h$host_ip -P$host_port -uroot@sys -A command under the system tenant and then execute the following statements:

ALTER SYSTEM flush plan cache GLOBAL;
ALTER system SET enable_sql_audit=false;
select sleep(5);
ALTER system SET enable_perf_event=false;
ALTER system SET syslog_level='PERF';
alter system set enable_record_trace_log=false;

Tenant tuning.

Run the obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A command as the test user. Then execute the following statements:

SET GLOBAL ob_sql_work_area_percentage = 80;
SET GLOBAL ob_query_timeout = 36000000000;
SET GLOBAL ob_trx_timeout = 36000000000;
SET GLOBAL max_allowed_packet = 67108864;
# parallel_servers_target = max_cpu * server_num * 8
SET GLOBAL parallel_servers_target = 624;

Set the default table format to `column` for a user tenant

Run the obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A command as the test user. Then execute the following statement:

alter system set default_table_store_format = 'column';

Install the TPC-DS tools

Download TPC-DS tools. For more information, visit the TPC-DS Tools Download page.

Unzip the package and enter the tools subdirectory of the TPC-DS directory.

[wieck@localhost ~] $ unzip 49FA3DBA-FE6C-463C-952B-62B8E9D43372-TPC-DS-Tool.zip
[wieck@localhost ~] $ cd DSGen-software-code-3.2.0rc1/tools

Compile the file.
```
[wieck@localhost tools] $ make
```
After the file is compiled, executable binary files named dsdgen and dsqgen are generated in the tools subdirectory.

Generate data

You can generate 10 GB, 100 GB, or 1 TB of data as needed. The following example shows the command to generate 100 GB of data.

Create a directory to store the data file.
```
[wieck@localhost tools] $ mkdir test-ds
```

Build test data.

[wieck@localhost tools] $ ./dsdgen -sc 100 -dir test-ds -TERMINATE N

Modify the test data.

Modify NULL values.

If you use vertical lines (|) to separate fields, a,NULL,c,d,NULL is exported to a text file in the a||c|d| format. As a result, when you use the LOAD DATA statement to import data, an error will be returned indicating that the import fails. Therefore, the NULL values must be converted.

[wieck@localhost tools] $ vim fix-null.sh
#!/bin/bash
# To replace the NULL value in the first field with `0`, replace `^|` with `0|`.
# To replace the NULL value in the middle field with `0`, replace `||` with `|0|`.
# To replace the NULL value in the last field with `0`, replace `|$` with `|0`.
for s_f in `ls *dat`
do
    echo "$s_f"
    i=1
    while [ `egrep '\|\||^\||\|$' $s_f |wc -l` -gt 0 ]
    do 
        echo $i
        sed 's/^|/0|/g;s/||/|0|/g;s/|$/|0/g' -i $s_f
        ((i++))
    done
done

[wieck@localhost tools] $ sh fix-null.sh

Modify date fields.

[wieck@localhost tools] $ vim fix-date.sh
for s_f in item.dat store.dat web_page.dat web_site.dat call_center.dat
do
# Replace NULL values of the first and second date fields with 0.
sed 's/^\([A-Za-z0-9]*|[A-Za-z0-9]*\)|0|0|\(.*\)/\1|0000-00-00|0000-00-00|\2/' -i $s_f

# Replace the NULL value of the second date field with 0.
sed 's/^\([0-9A-Za-z]*|[A-Za-z0-9]*|[0-9]\{4\}-[0-9]\{2\}-[0-9]\{2\}\)|0|\(.*\)/\1|0000-00-00|\2/' -i $s_f

# Replace the NULL value of the first date field with 0.
sed 's/^\([0-9A-Za-z]*|[A-Za-z0-9]*\)|0|\([0-9]\{4\}-[0-9]\{2\}-[0-9]\{2\}|.*\)/\1|0000-00-00|\2/' -i $s_f

done

[wieck@localhost tools] $ sh fix-date.sh

Generate SQL query statements

Create a file to store test SQL statements.
```
[wieck@localhost tools] $  mkdir sql-ds
```

Use dsqgen to generate test SQL statements. Query template files are stored in the DSGen-software-code-3.2.0rc1/query_templates directory.

Before you use dsqgen to generate test SQL statements, you must add define _END = "" to the target template file in the DSGen-software-code-3.2.0rc1/query_templates directory. Otherwise, an error will be returned. To generate test SQL statements, perform the following steps:
1. Create an .sh file and write code into the file.
```
[wieck@localhost tools] $ vim query.sh
for i in `ls query*tpl`
do 
    echo $i;  
    echo "define _END = \"\";" >> $i
done

[wieck@localhost tools] $ sh query.sh
```

Use dsqgen to generate test SQL statements.

[wieck@localhost tools] $ mkdir sql-ds
[wieck@localhost tools] $ vim sql-ds.sh
#!/bin/sh
for i in `seq 1 99`
do
./dsqgen -directory ../query_templates/ -template "query${i}.tpl" -dialect netezza  -output ./sql-ds/
scp ./sql-ds/query_0.sql ./sql-ds/sql${i}.sql
done

[wieck@localhost tools] $ sh sql-ds.sh

The return result is as follows:

qgen2 Query Generator (Version 3.2.0)
Copyright Transaction Processing Performance Council (TPC) 2001 - 2021
Warning: This scale factor is valid for QUALIFICATION ONLY
......
Copyright Transaction Processing Performance Council (TPC) 2001 - 2021
qgen2 Query Generator (Version 3.2.0)
Copyright Transaction Processing Performance Council (TPC) 2001 - 2021

View the generated test file.

[wieck@localhost tools] $  ls sql-ds/
query_0.sql  sql17.sql  sql25.sql  sql33.sql  sql41.sql  sql5.sql   sql58.sql  sql66.sql  sql74.sql  sql82.sql  sql90.sql  sql99.sql
sql1.sql     sql18.sql  sql26.sql  sql34.sql  sql42.sql  sql50.sql  sql59.sql  sql67.sql  sql75.sql  sql83.sql  sql91.sql
sql10.sql    sql19.sql  sql27.sql  sql35.sql  sql43.sql  sql51.sql  sql6.sql   sql68.sql  sql76.sql  sql84.sql  sql92.sql
sql11.sql    sql2.sql   sql28.sql  sql36.sql  sql44.sql  sql52.sql  sql60.sql  sql69.sql  sql77.sql  sql85.sql  sql93.sql
sql12.sql    sql20.sql  sql29.sql  sql37.sql  sql45.sql  sql53.sql  sql61.sql  sql7.sql   sql78.sql  sql86.sql  sql94.sql
sql13.sql    sql21.sql  sql3.sql   sql38.sql  sql46.sql  sql54.sql  sql62.sql  sql70.sql  sql79.sql  sql87.sql  sql95.sql
sql14.sql    sql22.sql  sql30.sql  sql39.sql  sql47.sql  sql55.sql  sql63.sql  sql71.sql  sql8.sql   sql88.sql  sql96.sql
sql15.sql    sql23.sql  sql31.sql  sql4.sql   sql48.sql  sql56.sql  sql64.sql  sql72.sql  sql80.sql  sql89.sql  sql97.sql
sql16.sql    sql24.sql  sql32.sql  sql40.sql  sql49.sql  sql57.sql  sql65.sql  sql73.sql  sql81.sql  sql9.sql   sql98.sql

Modify the SQL query statements.

Specify the execution concurrency

You can execute the following statement in the sys tenant to view the total number of available CPU cores for a tenant:

obclient> SELECT sum(cpu_capacity_max) FROM oceanbase.__all_virtual_server;

Here is a sample SQL query statement sql1 after modification:

with customer_total_return as
(select /*+    parallel(96) */ sr_customer_sk as ctr_customer_sk --- Specify the execution concurrency.
,sr_store_sk as ctr_store_sk
,sum(SR_FEE) as ctr_total_return
from store_returns
,date_dim
where sr_returned_date_sk = d_date_sk
and d_year =2000
group by sr_customer_sk
,sr_store_sk)
select  c_customer_id
from customer_total_return ctr1
,store
,customer
where ctr1.ctr_total_return > (select avg(ctr_total_return)*1.2
from customer_total_return ctr2
where ctr1.ctr_store_sk = ctr2.ctr_store_sk)
and s_store_sk = ctr1.ctr_store_sk
and s_state = 'TN'
and ctr1.ctr_customer_sk = c_customer_sk
order by c_customer_id
limit 100;

Modify the date functions in the SQL query statements.

Change the (cast('2002-08-04' as date) + 30 days) function to the date_add(cast('2002-08-04' as date),interval 30 day) or (cast('2002-08-04' as date) + INTERVAL 30 day) function. Change the (cast ('2002-05-18' as date) - 30 days) function to the date_sub(cast ('2002-05-18' as date),interval 30 day) or (cast('2002-08-04' as date) - INTERVAL 30 day) function.

Here is a sample SQL query statement with a date function after modification:

select  i_item_id
    ,i_item_desc
    ,i_current_price
from item, inventory, date_dim, catalog_sales
where i_current_price between 22 and 22 + 30
and inv_item_sk = i_item_sk
and d_date_sk=inv_date_sk
and d_date between cast('2001-06-02' as date) and (cast('2001-06-02' as date) + INTERVAL 60 day)  ----- Modify the date function.
and i_manufact_id in (678,964,918,849)
and inv_quantity_on_hand between 100 and 500
and cs_item_sk = i_item_sk
group by i_item_id,i_item_desc,i_current_price
order by i_item_id
limit 100;

Modify the syntax of the rollup function in the SQL query statements.

Change the syntax of the rollup function to group by <col_name> with rollup.

Here is a sample SQL query statement with the rollup function after modification:

select  i_item_id,
        ca_country,
        ca_state,
        ca_county,
        avg( cast(cs_quantity as decimal(12,2))) agg1,
        avg( cast(cs_list_price as decimal(12,2))) agg2,
        avg( cast(cs_coupon_amt as decimal(12,2))) agg3,
        avg( cast(cs_sales_price as decimal(12,2))) agg4,
        avg( cast(cs_net_profit as decimal(12,2))) agg5,
        avg( cast(c_birth_year as decimal(12,2))) agg6,
        avg( cast(cd1.cd_dep_count as decimal(12,2))) agg7
from catalog_sales, customer_demographics cd1,
    customer_demographics cd2, customer, customer_address, date_dim, item
where cs_sold_date_sk = d_date_sk and
    cs_item_sk = i_item_sk and
    cs_bill_cdemo_sk = cd1.cd_demo_sk and
    cs_bill_customer_sk = c_customer_sk and
    cd1.cd_gender = 'M' and
    cd1.cd_education_status = 'College' and
    c_current_cdemo_sk = cd2.cd_demo_sk and
    c_current_addr_sk = ca_address_sk and
    c_birth_month in (9,5,12,4,1,10) and
    d_year = 2001 and
    ca_state in ('ND','WI','AL'
                ,'NC','OK','MS','TN')
group by i_item_id, ca_country, ca_state, ca_county with rollup
order by ca_country,
        ca_state,
        ca_county,
        i_item_id
limit 100;

Create tables

Create a schema file named create_tpcds_mysql_table_part.ddl.

[wieck@localhost tools] $ mkdir load
[wieck@localhost tools] $ cd load
[wieck@localhost load] $ vim create_tpcds_mysql_table_part.ddl
SET global NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SET global NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SS.FF';
SET global NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SS.FF TZR TZD';

set global ob_query_timeout=10800000000;
set global ob_trx_timeout=10000000000;

set global ob_sql_work_area_percentage=80;

set global optimizer_use_sql_plan_baselines = true;
set global optimizer_capture_sql_plan_baselines = true;

alter system set ob_enable_batched_multi_statement='true';

set global parallel_servers_target=10000;

create tablegroup if not exists tpcds_tg_catalog_group_range binding true partition by hash partitions 128;
create tablegroup if not exists tpcds_tg_store_group_range_72_hash binding true 
partition by range columns 1 subpartition BY hash subpartitions 64
(partition part_1 values less than (2450846), 
partition part_2 values less than (2450874), 
partition part_3 values less than (2450905), 
partition part_4 values less than (2450935), 
partition part_5 values less than (2450966), 
partition part_6 values less than (2450996), 
partition part_7 values less than (2451027), 
partition part_8 values less than (2451058), 
partition part_9 values less than (2451088), 
partition part_10 values less than (2451119),
partition part_11 values less than (2451149),
partition part_12 values less than (2451180),
partition part_13 values less than (2451211),
partition part_14 values less than (2451239),
partition part_15 values less than (2451270),
partition part_16 values less than (2451300),
partition part_17 values less than (2451331),
partition part_18 values less than (2451361),
partition part_19 values less than (2451392),
partition part_20 values less than (2451423),
partition part_21 values less than (2451453),
partition part_22 values less than (2451484),
partition part_23 values less than (2451514),
partition part_24 values less than (2451545),
partition part_25 values less than (2451576),
partition part_26 values less than (2451605),
partition part_27 values less than (2451636),
partition part_28 values less than (2451666),
partition part_29 values less than (2451697),
partition part_30 values less than (2451727),
partition part_31 values less than (2451758),
partition part_32 values less than (2451789),
partition part_33 values less than (2451819),
partition part_34 values less than (2451850),
partition part_35 values less than (2451880),
partition part_36 values less than (2451911),
partition part_37 values less than (2451942),
partition part_38 values less than (2451970),
partition part_39 values less than (2452001),
partition part_40 values less than (2452031),
partition part_41 values less than (2452062),
partition part_42 values less than (2452092),
partition part_43 values less than (2452123),
partition part_44 values less than (2452154),
partition part_45 values less than (2452184),
partition part_46 values less than (2452215),   
partition part_47 values less than (2452245),
partition part_48 values less than (2452276),
partition part_49 values less than (2452307),
partition part_50 values less than (2452335),
partition part_51 values less than (2452366),
partition part_52 values less than (2452396),
partition part_53 values less than (2452427),
partition part_54 values less than (2452457),
partition part_55 values less than (2452488),
partition part_56 values less than (2452519),
partition part_57 values less than (2452549),
partition part_58 values less than (2452580),
partition part_59 values less than (2452610),
partition part_60 values less than (2452641),
partition part_61 values less than (2452672),
partition part_62 values less than (2452700),
partition part_63 values less than (2452731),
partition part_64 values less than (2452761),
partition part_65 values less than (2452792),
partition part_66 values less than (2452822),
partition part_67 values less than (2452853),
partition part_68 values less than (2452884),
partition part_69 values less than (2452914),
partition part_70 values less than (2452945),
partition part_71 values less than (2452975),
partition part_72 values less than (2453006),
partition part_73 values less than (maxvalue));

create table dbgen_version
(
    dv_version                varchar(16)                   ,
    dv_create_date            date                          ,
    dv_create_time            varchar(20)                   ,
    dv_cmdline_args           varchar(200)
);

CREATE TABLE customer_address
(
 ca_address_sk      bigint NOT NULL,
 ca_address_id      varchar(16) NOT NULL,
 ca_street_number   varchar(10),
 ca_street_name     varchar(60),
 ca_street_type     varchar(15),
 ca_suite_number    varchar(10),
 ca_city        varchar(60),
 ca_county      varchar(30),
 ca_state       varchar(2),
 ca_zip         varchar(10),
 ca_country         varchar(20),
 ca_gmt_offset      decimal(5,2),
 ca_location_type   varchar(20),
 primary key(ca_address_sk)
) partition by hash (ca_address_sk) partitions 128;

create table customer_demographics
(
 cd_demo_sk                bigint not null,
 cd_gender                 char(1),
 cd_marital_status         char(1),
 cd_education_status       char(20),
 cd_purchase_estimate      int,
 cd_credit_rating          char(10),
 cd_dep_count              int,
 cd_dep_employed_count     int,
 cd_dep_college_count      int,
 primary key(cd_demo_sk)
) partition by hash (cd_demo_sk) partitions 128;

create table date_dim
(
 d_date_sk                 bigint not null,
 d_date_id                 char(16) not null,
 d_date                    date,
 d_month_seq               int,
 d_week_seq                int,
 d_quarter_seq             int,
 d_year                    int,
 d_dow                     int,
 d_moy                     int,
 d_dom                     int,
 d_qoy                     int,
 d_fy_year                 int,
 d_fy_quarter_seq          int,
 d_fy_week_seq             int,
 d_day_name                char(9),
 d_quarter_name            char(6),
 d_holiday                 char(1),
 d_weekend                 char(1),
 d_following_holiday       char(1),
 d_first_dom               int,
 d_last_dom                int,
 d_same_day_ly             int,
 d_same_day_lq             int,
 d_current_day             char(1),
 d_current_week            char(1),
 d_current_month           char(1),
 d_current_quarter         char(1),
 d_current_year            char(1),
 primary key(d_date_sk)
);

create table warehouse
(
 w_warehouse_sk            bigint not null,
 w_warehouse_id            char(16) not null,
 w_warehouse_name          varchar(20),
 w_warehouse_sq_ft         int,
 w_street_number           char(10),
 w_street_name             varchar(60),
 w_street_type             char(15),
 w_suite_number            char(10),
 w_city                    varchar(60),
 w_county                  varchar(30),
 w_state                   char(2),
 w_zip                     char(10),
 w_country                 varchar(20),
 w_gmt_offset              decimal(5,2),
 primary key(w_warehouse_sk)
 );

create table ship_mode
(
    sm_ship_mode_sk           bigint,
    sm_ship_mode_id           char(16) not null,
    sm_type                   char(30),
    sm_code                   char(10),
    sm_carrier                char(20),
    sm_contract               char(20),
    primary key(sm_ship_mode_sk)
) ;

create table time_dim
(
 t_time_sk                 bigint not null,
 t_time_id                 char(16) not null,
 t_time                    int,
 t_hour                    int,
 t_minute                  int,
 t_second                  int,
 t_am_pm                   char(2),
 t_shift                   char(20),
 t_sub_shift               char(20),
 t_meal_time               char(20),
 primary key(t_time_sk)
 );

create table reason
(
    r_reason_sk     bigint not null,
    r_reason_id     char(16) not null,
    r_reason_desc   char(100),
    PRIMARY key(r_reason_sk)
);

create table income_band
(
 ib_income_band_sk         bigint not null,
 ib_lower_bound            int,
 ib_upper_bound            int,
 PRIMARY key(ib_income_band_sk)
);

create table item
(
 i_item_sk                 bigint not null,
 i_item_id                 char(16) not null,
 i_rec_start_date          date,
 i_rec_end_date            date,
 i_item_desc               varchar(200),
 i_current_price           decimal(7,2),
 i_wholesale_cost          decimal(7,2),
 i_brand_id                int,
 i_brand                   char(50),
 i_class_id                int,
 i_class                   char(50),
 i_category_id             int,
 i_category                char(50),
 i_manufact_id             int,
 i_manufact                char(50),
 i_size                    char(20),
 i_formulation             char(20),
 i_color                   char(20),
 i_units                   char(10),
 i_container               char(10),
 i_manager_id              int,
 i_product_name            char(50),
 PRIMARY key(i_item_sk)
) ;

create table store
(
 s_store_sk                bigint not null,
 s_store_id                char(16) not null,
 s_rec_start_date          date,
 s_rec_end_date            date,
 s_closed_date_sk          bigint,
 s_store_name              varchar(50),
 s_number_employees        int,
 s_floor_space             int,
 s_hours                   char(20),
 s_manager                 varchar(40),
 s_market_id               int,
 s_geography_class         varchar(100),
 s_market_desc             varchar(100),
 s_market_manager          varchar(40),
 s_division_id             int,
 s_division_name           varchar(50),
 s_company_id              int,
 s_company_name            varchar(50),
 s_street_number           varchar(10),
 s_street_name             varchar(60),
 s_street_type             char(15),
 s_suite_number            char(10),
 s_city                    varchar(60),
 s_county                  varchar(30),
 s_state                   char(2),
 s_zip                     char(10),
 s_country                 varchar(20),
 s_gmt_offset              decimal(5,2),
 s_tax_percentage          decimal(5,2),
 PRIMARY key(s_store_sk)
);

create table call_center
(
 cc_call_center_sk             bigint not null,
 cc_call_center_id             char(16) not null,
 cc_rec_start_date             date,
 cc_rec_end_date               date,
 cc_closed_date_sk             bigint,
 cc_open_date_sk               bigint,
 cc_name                       varchar(50),
 cc_class                      varchar(50),
 cc_employees                  int,
 cc_sq_ft                      int,
 cc_hours                      char(20),
 cc_manager                    varchar(40),
 cc_mkt_id                     int,
 cc_mkt_class                  char(50),
 cc_mkt_desc                   varchar(100),
 cc_market_manager             varchar(40),
 cc_division                   int,
 cc_division_name              varchar(50),
 cc_company                    int,
 cc_company_name               char(50),
 cc_street_number              char(10),
 cc_street_name                varchar(60),
 cc_street_type                char(15),
 cc_suite_number               char(10),
 cc_city                       varchar(60),
 cc_county                     varchar(30),
 cc_state                      char(2),
 cc_zip                        char(10),
 cc_country                    varchar(20),
 cc_gmt_offset                 decimal(5,2),
 cc_tax_percentage             decimal(5,2),
 PRIMARY key(cc_call_center_sk)
);

CREATE TABLE customer
(
  c_customer_sk         bigint NOT NULL,
  c_customer_id         char(16) NOT NULL,
  c_current_cdemo_sk        bigint,
  c_current_hdemo_sk        bigint,
  c_current_addr_sk         bigint,
  c_first_shipto_date_sk    bigint,
  c_first_sales_date_sk     bigint,
  c_salutation          char(10),
  c_first_name          char(20),
  c_last_name           char(30),
  c_preferred_cust_flag     char(1),
  c_birth_day           int,
  c_birth_month         int,
  c_birth_year          int,
  c_birth_country       varchar(20),
  c_login           char(13),
  c_email_address       char(50),
  c_last_review_date_sk     bigint,
  PRIMARY key(c_customer_sk)
)partition by hash (c_customer_sk) partitions 128;

create table web_site
(
 web_site_sk               bigint not null,
 web_site_id               char(16) not null,
 web_rec_start_date        date,
 web_rec_end_date          date,
 web_name                  varchar(50),
 web_open_date_sk          bigint,
 web_close_date_sk         bigint,
 web_class                 varchar(50),
 web_manager               varchar(40),
 web_mkt_id                int,
 web_mkt_class             varchar(50),
 web_mkt_desc              varchar(100),
 web_market_manager        varchar(40),
 web_company_id            int,
 web_company_name          char(50),
 web_street_number         char(10),
 web_street_name           varchar(60),
 web_street_type           char(15),
 web_suite_number          char(10),
 web_city                  varchar(60),
 web_county                varchar(30),
 web_state                 char(2),
 web_zip                   char(10),
 web_country               varchar(20),
 web_gmt_offset            decimal(5,2),
 web_tax_percentage        decimal(5,2),
 PRIMARY key(web_site_sk)
);

create table store_returns
(
 sr_returned_date_sk       bigint,
 sr_return_time_sk         bigint,
 sr_item_sk                bigint not null,
 sr_customer_sk            bigint,
 sr_cdemo_sk               bigint,
 sr_hdemo_sk               bigint,
 sr_addr_sk                bigint,
 sr_store_sk               bigint,
 sr_reason_sk              bigint,
 sr_ticket_number          bigint not null,
 sr_return_quantity        int,
 sr_return_amt             decimal(7,2),
 sr_return_tax             decimal(7,2),
 sr_return_amt_inc_tax     decimal(7,2),
 sr_fee                    decimal(7,2),
 sr_return_ship_cost       decimal(7,2),
 sr_refunded_cash          decimal(7,2),
 sr_reversed_charge        decimal(7,2),
 sr_store_credit           decimal(7,2),
 sr_net_loss               decimal(7,2)
)
partition by range (sr_returned_date_sk)
subpartition BY hash(sr_item_sk) subpartitions 64
(partition part_1 values less than (2450846), 
partition part_2 values less than (2450874), 
partition part_3 values less than (2450905), 
partition part_4 values less than (2450935), 
partition part_5 values less than (2450966), 
partition part_6 values less than (2450996), 
partition part_7 values less than (2451027), 
partition part_8 values less than (2451058), 
partition part_9 values less than (2451088), 
partition part_10 values less than (2451119),
partition part_11 values less than (2451149),
partition part_12 values less than (2451180),
partition part_13 values less than (2451211),
partition part_14 values less than (2451239),
partition part_15 values less than (2451270),
partition part_16 values less than (2451300),
partition part_17 values less than (2451331),
partition part_18 values less than (2451361),
partition part_19 values less than (2451392),
partition part_20 values less than (2451423),
partition part_21 values less than (2451453),
partition part_22 values less than (2451484),
partition part_23 values less than (2451514),
partition part_24 values less than (2451545),
partition part_25 values less than (2451576),
partition part_26 values less than (2451605),
partition part_27 values less than (2451636),
partition part_28 values less than (2451666),
partition part_29 values less than (2451697),
partition part_30 values less than (2451727),
partition part_31 values less than (2451758),
partition part_32 values less than (2451789),
partition part_33 values less than (2451819),
partition part_34 values less than (2451850),
partition part_35 values less than (2451880),
partition part_36 values less than (2451911),
partition part_37 values less than (2451942),
partition part_38 values less than (2451970),
partition part_39 values less than (2452001),
partition part_40 values less than (2452031),
partition part_41 values less than (2452062),
partition part_42 values less than (2452092),
partition part_43 values less than (2452123),
partition part_44 values less than (2452154),
partition part_45 values less than (2452184),
partition part_46 values less than (2452215),   
partition part_47 values less than (2452245),
partition part_48 values less than (2452276),
partition part_49 values less than (2452307),
partition part_50 values less than (2452335),
partition part_51 values less than (2452366),
partition part_52 values less than (2452396),
partition part_53 values less than (2452427),
partition part_54 values less than (2452457),
partition part_55 values less than (2452488),
partition part_56 values less than (2452519),
partition part_57 values less than (2452549),
partition part_58 values less than (2452580),
partition part_59 values less than (2452610),
partition part_60 values less than (2452641),
partition part_61 values less than (2452672),
partition part_62 values less than (2452700),
partition part_63 values less than (2452731),
partition part_64 values less than (2452761),
partition part_65 values less than (2452792),
partition part_66 values less than (2452822),
partition part_67 values less than (2452853),
partition part_68 values less than (2452884),
partition part_69 values less than (2452914),
partition part_70 values less than (2452945),
partition part_71 values less than (2452975),
partition part_72 values less than (2453006),
partition part_73 values less than (maxvalue));

create table household_demographics
(
 hd_demo_sk                bigint not null,
 hd_income_band_sk         bigint,
 hd_buy_potential          char(15),
 hd_dep_count              int,
 hd_vehicle_count          int,
 PRIMARY key(hd_demo_sk)
);

create table web_page
(
 wp_web_page_sk            bigint not null,
 wp_web_page_id            char(16) not null,
 wp_rec_start_date         date,
 wp_rec_end_date           date,
 wp_creation_date_sk       bigint,
 wp_access_date_sk         bigint,
 wp_autogen_flag           char(1),
 wp_customer_sk            bigint,
 wp_url                    varchar(100),
 wp_type                   char(50),
 wp_char_count             int,
 wp_link_count             int,
 wp_image_count            int,
 wp_max_ad_count           int,
 PRIMARY key(wp_web_page_sk)
);

create table promotion
(
 p_promo_sk                bigint not null,
 p_promo_id                char(16) not null,
 p_start_date_sk           bigint,
 p_end_date_sk             bigint,
 p_item_sk                 bigint,
 p_cost                    decimal(15,2),
 p_response_target         int,
 p_promo_name              char(50),
 p_channel_dmail           char(1),
 p_channel_email           char(1),
 p_channel_catalog         char(1),
 p_channel_tv              char(1),
 p_channel_radio           char(1),
 p_channel_press           char(1),
 p_channel_event           char(1),
 p_channel_demo            char(1),
 p_channel_details         varchar(100),
 p_purpose                 char(15),
 p_discount_active         char(1),
 PRIMARY key(p_promo_sk)
);

create table catalog_page
(
 cp_catalog_page_sk     bigint not null,
 cp_catalog_page_id     varchar(16) not null,
 cp_start_date_sk   bigint,
 cp_end_date_sk     bigint,
 cp_department      varchar(50),
 cp_catalog_number  int,
 cp_catalog_page_number int,
 cp_description     varchar(100),
 cp_type        varchar(100),
 primary key(cp_catalog_page_sk)
 );

create table inventory
(
 inv_date_sk               bigint not null,
 inv_item_sk               bigint not null,
 inv_warehouse_sk          bigint not null,
 inv_quantity_on_hand      int
)
partition by range (inv_date_sk)
subpartition BY hash(inv_item_sk) subpartitions 64
(partition part_1 values less than (2450846), 
partition part_2 values less than (2450874), 
partition part_3 values less than (2450905), 
partition part_4 values less than (2450935), 
partition part_5 values less than (2450966), 
partition part_6 values less than (2450996), 
partition part_7 values less than (2451027), 
partition part_8 values less than (2451058), 
partition part_9 values less than (2451088), 
partition part_10 values less than (2451119),
partition part_11 values less than (2451149),
partition part_12 values less than (2451180),
partition part_13 values less than (2451211),
partition part_14 values less than (2451239),
partition part_15 values less than (2451270),
partition part_16 values less than (2451300),
partition part_17 values less than (2451331),
partition part_18 values less than (2451361),
partition part_19 values less than (2451392),
partition part_20 values less than (2451423),
partition part_21 values less than (2451453),
partition part_22 values less than (2451484),
partition part_23 values less than (2451514),
partition part_24 values less than (2451545),
partition part_25 values less than (2451576),
partition part_26 values less than (2451605),
partition part_27 values less than (2451636),
partition part_28 values less than (2451666),
partition part_29 values less than (2451697),
partition part_30 values less than (2451727),
partition part_31 values less than (2451758),
partition part_32 values less than (2451789),
partition part_33 values less than (2451819),
partition part_34 values less than (2451850),
partition part_35 values less than (2451880),
partition part_36 values less than (2451911),
partition part_37 values less than (2451942),
partition part_38 values less than (2451970),
partition part_39 values less than (2452001),
partition part_40 values less than (2452031),
partition part_41 values less than (2452062),
partition part_42 values less than (2452092),
partition part_43 values less than (2452123),
partition part_44 values less than (2452154),
partition part_45 values less than (2452184),
partition part_46 values less than (2452215),   
partition part_47 values less than (2452245),
partition part_48 values less than (2452276),
partition part_49 values less than (2452307),
partition part_50 values less than (2452335),
partition part_51 values less than (2452366),
partition part_52 values less than (2452396),
partition part_53 values less than (2452427),
partition part_54 values less than (2452457),
partition part_55 values less than (2452488),
partition part_56 values less than (2452519),
partition part_57 values less than (2452549),
partition part_58 values less than (2452580),
partition part_59 values less than (2452610),
partition part_60 values less than (2452641),
partition part_61 values less than (2452672),
partition part_62 values less than (2452700),
partition part_63 values less than (2452731),
partition part_64 values less than (2452761),
partition part_65 values less than (2452792),
partition part_66 values less than (2452822),
partition part_67 values less than (2452853),
partition part_68 values less than (2452884),
partition part_69 values less than (2452914),
partition part_70 values less than (2452945),
partition part_71 values less than (2452975),
partition part_72 values less than (2453006),
partition part_73 values less than (maxvalue));

create table catalog_returns
(
 cr_returned_date_sk        bigint,
 cr_returned_time_sk        bigint,
 cr_item_sk             bigint not null,
 cr_refunded_customer_sk    bigint,
 cr_refunded_cdemo_sk       bigint,
 cr_refunded_hdemo_sk       bigint,
 cr_refunded_addr_sk        bigint,
 cr_returning_customer_sk   bigint,
 cr_returning_cdemo_sk      bigint,
 cr_returning_hdemo_sk      bigint,
 cr_returning_addr_sk       bigint,
 cr_call_center_sk      bigint,
 cr_catalog_page_sk         bigint ,
 cr_ship_mode_sk        bigint ,
 cr_warehouse_sk        bigint ,
 cr_reason_sk           bigint ,
 cr_order_number        bigint not null,
 cr_return_quantity         int,
 cr_return_amount       decimal(7,2),
 cr_return_tax          decimal(7,2),
 cr_return_amt_inc_tax      decimal(7,2),
 cr_fee             decimal(7,2),
 cr_return_ship_cost        decimal(7,2),
 cr_refunded_cash       decimal(7,2),
 cr_reversed_charge         decimal(7,2),
 cr_store_credit        decimal(7,2),
 cr_net_loss            decimal(7,2)
)
tablegroup = tpcds_tg_catalog_group_range
partition by hash(cr_item_sk) partitions 128
;

create table web_returns
(
 wr_returned_date_sk       bigint,
 wr_returned_time_sk       bigint,
 wr_item_sk                bigint not null,
 wr_refunded_customer_sk   bigint,
 wr_refunded_cdemo_sk      bigint,
 wr_refunded_hdemo_sk      bigint,
 wr_refunded_addr_sk       bigint,
 wr_returning_customer_sk  bigint,
 wr_returning_cdemo_sk     bigint,
 wr_returning_hdemo_sk     bigint,
 wr_returning_addr_sk      bigint,
 wr_web_page_sk            bigint,
 wr_reason_sk              bigint,
 wr_order_number           bigint not null,
 wr_return_quantity        int,
 wr_return_amt             decimal(7,2),
 wr_return_tax             decimal(7,2),
 wr_return_amt_inc_tax     decimal(7,2),
 wr_fee                    decimal(7,2),
 wr_return_ship_cost       decimal(7,2),
 wr_refunded_cash          decimal(7,2),
 wr_reversed_charge        decimal(7,2),
 wr_account_credit         decimal(7,2),
 wr_net_loss               decimal(7,2)
)
partition by range(wr_returned_date_sk) 
subpartition BY hash(wr_item_sk) subpartitions 64
(partition part_1 values less than (2450846), 
partition part_2 values less than (2450874), 
partition part_3 values less than (2450905), 
partition part_4 values less than (2450935), 
partition part_5 values less than (2450966), 
partition part_6 values less than (2450996), 
partition part_7 values less than (2451027), 
partition part_8 values less than (2451058), 
partition part_9 values less than (2451088), 
partition part_10 values less than (2451119),
partition part_11 values less than (2451149),
partition part_12 values less than (2451180),
partition part_13 values less than (2451211),
partition part_14 values less than (2451239),
partition part_15 values less than (2451270),
partition part_16 values less than (2451300),
partition part_17 values less than (2451331),
partition part_18 values less than (2451361),
partition part_19 values less than (2451392),
partition part_20 values less than (2451423),
partition part_21 values less than (2451453),
partition part_22 values less than (2451484),
partition part_23 values less than (2451514),
partition part_24 values less than (2451545),
partition part_25 values less than (2451576),
partition part_26 values less than (2451605),
partition part_27 values less than (2451636),
partition part_28 values less than (2451666),
partition part_29 values less than (2451697),
partition part_30 values less than (2451727),
partition part_31 values less than (2451758),
partition part_32 values less than (2451789),
partition part_33 values less than (2451819),
partition part_34 values less than (2451850),
partition part_35 values less than (2451880),
partition part_36 values less than (2451911),
partition part_37 values less than (2451942),
partition part_38 values less than (2451970),
partition part_39 values less than (2452001),
partition part_40 values less than (2452031),
partition part_41 values less than (2452062),
partition part_42 values less than (2452092),
partition part_43 values less than (2452123),
partition part_44 values less than (2452154),
partition part_45 values less than (2452184),
partition part_46 values less than (2452215),   
partition part_47 values less than (2452245),
partition part_48 values less than (2452276),
partition part_49 values less than (2452307),
partition part_50 values less than (2452335),
partition part_51 values less than (2452366),
partition part_52 values less than (2452396),
partition part_53 values less than (2452427),
partition part_54 values less than (2452457),
partition part_55 values less than (2452488),
partition part_56 values less than (2452519),
partition part_57 values less than (2452549),
partition part_58 values less than (2452580),
partition part_59 values less than (2452610),
partition part_60 values less than (2452641),
partition part_61 values less than (2452672),
partition part_62 values less than (2452700),
partition part_63 values less than (2452731),
partition part_64 values less than (2452761),
partition part_65 values less than (2452792),
partition part_66 values less than (2452822),
partition part_67 values less than (2452853),
partition part_68 values less than (2452884),
partition part_69 values less than (2452914),
partition part_70 values less than (2452945),
partition part_71 values less than (2452975),
partition part_72 values less than (2453006),
partition part_73 values less than (maxvalue))
;

create table web_sales
(
 ws_sold_date_sk           bigint,
 ws_sold_time_sk           bigint,
 ws_ship_date_sk           bigint,
 ws_item_sk                bigint not null,
 ws_bill_customer_sk       bigint,
 ws_bill_cdemo_sk          bigint,
 ws_bill_hdemo_sk          bigint,
 ws_bill_addr_sk           bigint,
 ws_ship_customer_sk       bigint,
 ws_ship_cdemo_sk          bigint,
 ws_ship_hdemo_sk          bigint,
 ws_ship_addr_sk           bigint,
 ws_web_page_sk            bigint,
 ws_web_site_sk            bigint,
 ws_ship_mode_sk           bigint,
 ws_warehouse_sk           bigint,
 ws_promo_sk               bigint,
 ws_order_number           bigint not null,
 ws_quantity               int,
 ws_wholesale_cost         decimal(7,2),
 ws_list_price             decimal(7,2),
 ws_sales_price            decimal(7,2),
 ws_ext_discount_amt       decimal(7,2),
 ws_ext_sales_price        decimal(7,2),
 ws_ext_wholesale_cost     decimal(7,2),
 ws_ext_list_price         decimal(7,2),
 ws_ext_tax                decimal(7,2),
 ws_coupon_amt             decimal(7,2),
 ws_ext_ship_cost          decimal(7,2),
 ws_net_paid               decimal(7,2),
 ws_net_paid_inc_tax       decimal(7,2),
 ws_net_paid_inc_ship      decimal(7,2),
 ws_net_paid_inc_ship_tax  decimal(7,2),
 ws_net_profit             decimal(7,2)
) 
partition by range (ws_sold_date_sk) 
subpartition BY hash(ws_item_sk) subpartitions 64
(partition part_1 values less than (2450846), 
partition part_2 values less than (2450874), 
partition part_3 values less than (2450905), 
partition part_4 values less than (2450935), 
partition part_5 values less than (2450966), 
partition part_6 values less than (2450996), 
partition part_7 values less than (2451027), 
partition part_8 values less than (2451058), 
partition part_9 values less than (2451088), 
partition part_10 values less than (2451119),
partition part_11 values less than (2451149),
partition part_12 values less than (2451180),
partition part_13 values less than (2451211),
partition part_14 values less than (2451239),
partition part_15 values less than (2451270),
partition part_16 values less than (2451300),
partition part_17 values less than (2451331),
partition part_18 values less than (2451361),
partition part_19 values less than (2451392),
partition part_20 values less than (2451423),
partition part_21 values less than (2451453),
partition part_22 values less than (2451484),
partition part_23 values less than (2451514),
partition part_24 values less than (2451545),
partition part_25 values less than (2451576),
partition part_26 values less than (2451605),
partition part_27 values less than (2451636),
partition part_28 values less than (2451666),
partition part_29 values less than (2451697),
partition part_30 values less than (2451727),
partition part_31 values less than (2451758),
partition part_32 values less than (2451789),
partition part_33 values less than (2451819),
partition part_34 values less than (2451850),
partition part_35 values less than (2451880),
partition part_36 values less than (2451911),
partition part_37 values less than (2451942),
partition part_38 values less than (2451970),
partition part_39 values less than (2452001),
partition part_40 values less than (2452031),
partition part_41 values less than (2452062),
partition part_42 values less than (2452092),
partition part_43 values less than (2452123),
partition part_44 values less than (2452154),
partition part_45 values less than (2452184),
partition part_46 values less than (2452215),   
partition part_47 values less than (2452245),
partition part_48 values less than (2452276),
partition part_49 values less than (2452307),
partition part_50 values less than (2452335),
partition part_51 values less than (2452366),
partition part_52 values less than (2452396),
partition part_53 values less than (2452427),
partition part_54 values less than (2452457),
partition part_55 values less than (2452488),
partition part_56 values less than (2452519),
partition part_57 values less than (2452549),
partition part_58 values less than (2452580),
partition part_59 values less than (2452610),
partition part_60 values less than (2452641),
partition part_61 values less than (2452672),
partition part_62 values less than (2452700),
partition part_63 values less than (2452731),
partition part_64 values less than (2452761),
partition part_65 values less than (2452792),
partition part_66 values less than (2452822),
partition part_67 values less than (2452853),
partition part_68 values less than (2452884),
partition part_69 values less than (2452914),
partition part_70 values less than (2452945),
partition part_71 values less than (2452975),
partition part_72 values less than (2453006),
partition part_73 values less than (maxvalue))
;

create table catalog_sales
(
 cs_sold_date_sk           bigint,
 cs_sold_time_sk           bigint,
 cs_ship_date_sk           bigint,
 cs_bill_customer_sk       bigint,
 cs_bill_cdemo_sk          bigint,
 cs_bill_hdemo_sk          bigint,
 cs_bill_addr_sk           bigint,
 cs_ship_customer_sk       bigint,
 cs_ship_cdemo_sk          bigint,
 cs_ship_hdemo_sk          bigint,
 cs_ship_addr_sk           bigint,
 cs_call_center_sk         bigint,
 cs_catalog_page_sk        bigint,
 cs_ship_mode_sk           bigint,
 cs_warehouse_sk           bigint,
 cs_item_sk                bigint not null,
 cs_promo_sk               bigint,
 cs_order_number           bigint not null,
 cs_quantity               int,
 cs_wholesale_cost         decimal(7,2),
 cs_list_price             decimal(7,2),
 cs_sales_price            decimal(7,2),
 cs_ext_discount_amt       decimal(7,2),
 cs_ext_sales_price        decimal(7,2),
 cs_ext_wholesale_cost     decimal(7,2),
 cs_ext_list_price         decimal(7,2),
 cs_ext_tax                decimal(7,2),
 cs_coupon_amt             decimal(7,2),
 cs_ext_ship_cost          decimal(7,2),
 cs_net_paid               decimal(7,2),
 cs_net_paid_inc_tax       decimal(7,2),
 cs_net_paid_inc_ship      decimal(7,2),
 cs_net_paid_inc_ship_tax  decimal(7,2),
 cs_net_profit             decimal(7,2)
 )
partition by range (cs_sold_date_sk) 
subpartition BY hash(cs_item_sk) subpartitions 64
(partition part_1 values less than (2450846), 
partition part_2 values less than (2450874), 
partition part_3 values less than (2450905), 
partition part_4 values less than (2450935), 
partition part_5 values less than (2450966), 
partition part_6 values less than (2450996), 
partition part_7 values less than (2451027), 
partition part_8 values less than (2451058), 
partition part_9 values less than (2451088), 
partition part_10 values less than (2451119),
partition part_11 values less than (2451149),
partition part_12 values less than (2451180),
partition part_13 values less than (2451211),
partition part_14 values less than (2451239),
partition part_15 values less than (2451270),
partition part_16 values less than (2451300),
partition part_17 values less than (2451331),
partition part_18 values less than (2451361),
partition part_19 values less than (2451392),
partition part_20 values less than (2451423),
partition part_21 values less than (2451453),
partition part_22 values less than (2451484),
partition part_23 values less than (2451514),
partition part_24 values less than (2451545),
partition part_25 values less than (2451576),
partition part_26 values less than (2451605),
partition part_27 values less than (2451636),
partition part_28 values less than (2451666),
partition part_29 values less than (2451697),
partition part_30 values less than (2451727),
partition part_31 values less than (2451758),
partition part_32 values less than (2451789),
partition part_33 values less than (2451819),
partition part_34 values less than (2451850),
partition part_35 values less than (2451880),
partition part_36 values less than (2451911),
partition part_37 values less than (2451942),
partition part_38 values less than (2451970),
partition part_39 values less than (2452001),
partition part_40 values less than (2452031),
partition part_41 values less than (2452062),
partition part_42 values less than (2452092),
partition part_43 values less than (2452123),
partition part_44 values less than (2452154),
partition part_45 values less than (2452184),
partition part_46 values less than (2452215),   
partition part_47 values less than (2452245),
partition part_48 values less than (2452276),
partition part_49 values less than (2452307),
partition part_50 values less than (2452335),
partition part_51 values less than (2452366),
partition part_52 values less than (2452396),
partition part_53 values less than (2452427),
partition part_54 values less than (2452457),
partition part_55 values less than (2452488),
partition part_56 values less than (2452519),
partition part_57 values less than (2452549),
partition part_58 values less than (2452580),
partition part_59 values less than (2452610),
partition part_60 values less than (2452641),
partition part_61 values less than (2452672),
partition part_62 values less than (2452700),
partition part_63 values less than (2452731),
partition part_64 values less than (2452761),
partition part_65 values less than (2452792),
partition part_66 values less than (2452822),
partition part_67 values less than (2452853),
partition part_68 values less than (2452884),
partition part_69 values less than (2452914),
partition part_70 values less than (2452945),
partition part_71 values less than (2452975),
partition part_72 values less than (2453006),
partition part_73 values less than (maxvalue));

create table store_sales
(
 ss_sold_date_sk           bigint,
 ss_sold_time_sk           bigint,
 ss_item_sk                bigint not null,
 ss_customer_sk            bigint,
 ss_cdemo_sk               bigint,
 ss_hdemo_sk               bigint,
 ss_addr_sk                bigint,
 ss_store_sk               bigint,
 ss_promo_sk               bigint,
 ss_ticket_number          bigint not null,
 ss_quantity               int,
 ss_wholesale_cost         decimal(7,2),
 ss_list_price             decimal(7,2),
 ss_sales_price            decimal(7,2),
 ss_ext_discount_amt       decimal(7,2),
 ss_ext_sales_price        decimal(7,2),
 ss_ext_wholesale_cost     decimal(7,2),
 ss_ext_list_price         decimal(7,2),
 ss_ext_tax                decimal(7,2),
 ss_coupon_amt             decimal(7,2),
 ss_net_paid               decimal(7,2),
 ss_net_paid_inc_tax       decimal(7,2),
 ss_net_profit             decimal(7,2)
)
partition by range(ss_sold_date_sk) 
subpartition BY hash(ss_item_sk) subpartitions 64
(partition part_1 values less than (2450846), 
partition part_2 values less than (2450874), 
partition part_3 values less than (2450905), 
partition part_4 values less than (2450935), 
partition part_5 values less than (2450966), 
partition part_6 values less than (2450996), 
partition part_7 values less than (2451027), 
partition part_8 values less than (2451058), 
partition part_9 values less than (2451088), 
partition part_10 values less than (2451119),
partition part_11 values less than (2451149),
partition part_12 values less than (2451180),
partition part_13 values less than (2451211),
partition part_14 values less than (2451239),
partition part_15 values less than (2451270),
partition part_16 values less than (2451300),
partition part_17 values less than (2451331),
partition part_18 values less than (2451361),
partition part_19 values less than (2451392),
partition part_20 values less than (2451423),
partition part_21 values less than (2451453),
partition part_22 values less than (2451484),
partition part_23 values less than (2451514),
partition part_24 values less than (2451545),
partition part_25 values less than (2451576),
partition part_26 values less than (2451605),
partition part_27 values less than (2451636),
partition part_28 values less than (2451666),
partition part_29 values less than (2451697),
partition part_30 values less than (2451727),
partition part_31 values less than (2451758),
partition part_32 values less than (2451789),
partition part_33 values less than (2451819),
partition part_34 values less than (2451850),
partition part_35 values less than (2451880),
partition part_36 values less than (2451911),
partition part_37 values less than (2451942),
partition part_38 values less than (2451970),
partition part_39 values less than (2452001),
partition part_40 values less than (2452031),
partition part_41 values less than (2452062),
partition part_42 values less than (2452092),
partition part_43 values less than (2452123),
partition part_44 values less than (2452154),
partition part_45 values less than (2452184),
partition part_46 values less than (2452215),   
partition part_47 values less than (2452245),
partition part_48 values less than (2452276),
partition part_49 values less than (2452307),
partition part_50 values less than (2452335),
partition part_51 values less than (2452366),
partition part_52 values less than (2452396),
partition part_53 values less than (2452427),
partition part_54 values less than (2452457),
partition part_55 values less than (2452488),
partition part_56 values less than (2452519),
partition part_57 values less than (2452549),
partition part_58 values less than (2452580),
partition part_59 values less than (2452610),
partition part_60 values less than (2452641),
partition part_61 values less than (2452672),
partition part_62 values less than (2452700),
partition part_63 values less than (2452731),
partition part_64 values less than (2452761),
partition part_65 values less than (2452792),
partition part_66 values less than (2452822),
partition part_67 values less than (2452853),
partition part_68 values less than (2452884),
partition part_69 values less than (2452914),
partition part_70 values less than (2452945),
partition part_71 values less than (2452975),
partition part_72 values less than (2453006),
partition part_73 values less than (maxvalue));

Load data

You can write scripts based on the data and SQL query statements generated in the preceding steps. As an example, you can take the following steps to load data:

Create a script named load.py.

[wieck@localhost load] $ vim load.py

#!/usr/bin/env python
#-*- encoding:utf-8 -*-
import os
import sys
import time
import commands
hostname='$host_ip'  # Notice! ! You need to specify the IP address of an OBServer node, such as OBServer node A.
port='$host_port'               # The port number of OBServer node A.
tenant='$tenant_name'              # The tenant name.
user='$user'               # The username.
password='$password'           # The password.
data_path='$path'         # Notice! ! You need to specify the directory of tbl on OBServer node A.
db_name='$db_name'             # The database name.
# Create a table
cmd_str='obclient -h%s -P%s -u%s@%s -p%s -D%s < create_tpcds_mysql_table_part.ddl'%(hostname,port,user,tenant,password,db_name)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str='obclient -h%s -P%s -u%s@%s -p%s  -D%s -e "show tables;" '%(hostname,port,user,tenant,password,db_name)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/dbgen_version.dat' into table dbgen_version fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/customer_address.dat' into table customer_address fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c -D%s -e "load data /*+ parallel(80) */ infile '%s/customer_demographics.dat' into table customer_demographics fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/date_dim.dat' into table date_dim fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s   -D%s -e "load data /*+ parallel(80) */ infile '%s/warehouse.dat' into table warehouse fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/ship_mode.dat' into table ship_mode fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/time_dim.dat' into table time_dim fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/reason.dat' into table reason fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/income_band.dat' into table income_band fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/item.dat' into table item fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c -D%s -e "load data /*+ parallel(80) */ infile '%s/store.dat' into table store fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/call_center.dat' into table call_center fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s   -D%s -e "load data /*+ parallel(80) */ infile '%s/customer.dat' into table customer fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/web_site.dat' into table web_site fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/store_returns.dat' into table store_returns fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/household_demographics.dat' into table household_demographics fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/web_page.dat' into table web_page fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/promotion.dat' into table promotion fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c -D%s -e "load data /*+ parallel(80) */ infile '%s/catalog_page.dat' into table catalog_page fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/inventory.dat' into table inventory fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s   -D%s -e "load data /*+ parallel(80) */ infile '%s/catalog_returns.dat' into table catalog_returns fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/web_returns.dat' into table web_returns fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/web_sales.dat' into table web_sales fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/catalog_sales.dat' into table catalog_sales fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result
cmd_str=""" obclient -h%s -P%s -u%s@%s -p%s -c  -D%s -e "load data /*+ parallel(80) */ infile '%s/store_sales.dat' into table store_sales fields terminated by '|';" """ %(hostname,port,user,tenant,password,db_name,data_path)
result = commands.getstatusoutput(cmd_str)
print result

Load the data.

Notice

To load data, you need to install the OBClient.
```
[wieck@localhost load] $ python load.py
```

Perform a major compaction.

obclient > ALTER SYSTEM SET undo_retention = 100;
obclient > ALTER SYSTEM MAJOR FREEZE;

Check whether the major compaction is complete.

SELECT * FROM oceanbase.DBA_OB_ZONE_MAJOR_COMPACTION\G

The return result is as follows:

*************************** 1. row ***************************
            ZONE: zone1
  BROADCAST_SCN: 1716172011046213913
        LAST_SCN: 1716172011046213913
LAST_FINISH_TIME: 2024-05-20 10:35:07.829496
      START_TIME: 2024-05-20 10:26:51.579881
          STATUS: IDLE
1 row in set

If the value of STATUS is IDLE and the values of BROADCAST_SCN and LAST_SCN are equal, the major compaction is complete.

Manually collect statistics.

Run the obclient -h$host_ip -P$host_port -u$user@$tenant -p$password -A -D$database command as the test user. Then execute the following statements:

call dbms_stats.gather_table_stats(NULL, 'date_dim', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'warehouse', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'ship_mode', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'time_dim', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'reason', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'income_band', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'item', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'store', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'call_center', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'web_site', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'household_demographics', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'web_page', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'promotion', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'catalog_page', degree=>128, granularity=>'AUTO', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'inventory', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'web_sales', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'catalog_sales', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'store_sales', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'catalog_returns', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'web_returns', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'store_returns', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'customer_address', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'customer_demographics', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');
call dbms_stats.gather_table_stats(NULL, 'customer', degree=>128, granularity=>'GLOBAL', method_opt=>'FOR ALL COLUMNS SIZE 128');

Run the test

You can write scripts based on the data and SQL query statements generated in the preceding steps. As an example, you can take the following steps to perform the test:

Write a script named tpcds.sh in the sql-ds directory.

[wieck@localhost queries] $ vim tpcds.sh

  #!/bin/bash
  TPCDS_TEST="obclient -h $host_ip -P $host_port -utpcds_100g_part@tpcds_mysql  -D tpcds_100g_part  -ptest -c"
  # Run a warmup.
  for i in {1..99}
  do
      sql1="source sql${i}.sql"
      echo $sql1| $TPCDS_TEST >db${i}.log  || ret=1
  done
  # Run the formal test.
  for i in {1..99}
  do
      starttime=`date +%s%N`
      echo `date  '+[%Y-%m-%d %H:%M:%S]'` "BEGIN Q${i}"
      sql1="source sql${i}.sql"
      echo $sql1| $TPCDS_TEST >db${i}.log  || ret=1
      stoptime=`date +%s%N`
      costtime=`echo $stoptime $starttime | awk '{printf "%0.2f\n", ($1 - $2) / 1000000000}'`
      echo `date  '+[%Y-%m-%d %H:%M:%S]'` "END,COST ${costtime}s"
  done

Run the test script.
```
sh tpcds.sh
```

OceanBase

Customer Stories

Documentation

TPC-DS benchmark

Note

What is TPC-DS?

Environment preparation

Notice

Cluster deployment

Note

Manual TPC-DS test

Environment tuning

Set the default table format to column for a user tenant

Install the TPC-DS tools

Generate data

Generate SQL query statements

Create tables

Load data

Notice

Run the test

Set the default table format to `column` for a user tenant