Generating TPC-DS database for sql server

Question

How do I populate the Transaction Processing Performance Council's TPC-DS database for SQL Server? I have downloaded the TPC-DS tool but there are few tutorials about how to use it.

Answer 1

I've just succeeded in generating these queries. There are some tips may not the best but useful.

1. cp ${...}/query_templates/* ${...}/tools/
2. add define _END = ""; to each query.tpl
3. ${...}/tools/dsqgen -INPUT templates.lst -OUTPUT_DIR /home/query99/

Answer 2

Let's describe the base steps:

1. Before go to the next steps double-check that the required TPC-DS Kit has not been already prepared for your DB

2. Download TPC-DS Tools

3. Build Tools as described in 'v2.11.0rc2\tools\How_To_Guide-DS-V2.0.0.docx' (I used VS2015)

4. Create DB

Take the DB schema described in tpcds.sql and tpcds_ri.sql (they located in 'v2.11.0rc2\tools\'-folder), suit it to your DB if required.

5. Generate data that be stored to database

# Windows
dsdgen.exe /scale 1 /dir .\tmp /suffix _001.dat

# Linux
dsdgen -scale 1 -dir /tmp -suffix _001.dat

6. Upload data to DB

# example for ClickHouse

database_name=tpcds
ch_password=12345

for file_fullpath in /tmp/tpc-ds/*.dat; do
  filename=$(echo ${file_fullpath##*/})
  tablename=$(echo ${filename%_*})
  echo " - $(date +"%T"): start processing $file_fullpath (table: $tablename)"

  query="INSERT INTO $database_name.$tablename FORMAT CSV"
  cat $file_fullpath | clickhouse-client --format_csv_delimiter="|" --query="$query" --password $ch_password
done

7. Generate queries

# Windows
set tmpl_lst_path="..\query_templates\templates.lst"
set tmpl_dir="..\query_templates"
set dialect_path="..\..\clickhouse-dialect"
set result_dir="..\queries"
set tmpl_name="query1.tpl"

dsqgen /input %tmpl_lst_path% /directory %tmpl_dir% /dialect %dialect_path% /output_dir %result_dir% /scale 1 /verbose y /template %tmpl_name%

# Linux
# see for example https://github.com/pingcap/tidb-bench/blob/master/tpcds/genquery.sh

To fix the error 'Substitution .. is used before being initialized' follow this fix.

Answer 3

In case you are using windows, you gotta have visual studio 2005 or later. Unzip dsgen in the folder tools there is dsgen2.sln file, open it using visual studio and build the project, will generate tables for you, I've tried that and I loaded tables manually into sql server