spark-tpc-ds-performance-test - GitLite

KO | EN

gitlite — search

#ai #typescript #llm #golang #deep-learning #linux #docker #macos #java #windows #cli #vue #security #ios

spark-tpc-ds-performance-test

(★ 186)

Use the TPC-DS benchmark to test Spark SQL performance

File Explorer

Download Latest Version (.zip)

- qual.pl
- runqueries.sh
- tpcdsenv.sh
- tpcdsspark.sh
- .gitkeep
- .gitkeep
- .gitkeep
- run-tpcds-on-spark.ipynb
- - create_database.sql
  - create_tables.sql
  - row_counts.sql
  - rowcounts.expected
- - log4j.properties
- - query01.sql
  - query02.sql
  - query03.sql
  - query04.sql
  - query05.sql
  - query06.sql
  - query07.sql
  - query08.sql
  - query09.sql
  - query10.sql
  - query11.sql
  - query12.sql
  - query13.sql
  - query14.sql
  - query15.sql
  - query16.sql
  - query17.sql
  - query18.sql
  - query19.sql
  - query20.sql
  - query21.sql
  - query22.sql
  - query23.sql
  - query24.sql
  - query25.sql
  - query26.sql
  - query27.sql
  - query28.sql
  - query29.sql
  - query30.sql
  - query31.sql
  - query32.sql
  - query33.sql
  - query34.sql
  - query35.sql
  - query36.sql
  - query37.sql
  - query38.sql
  - query39.sql
  - query40.sql
  - query41.sql
  - query42.sql
  - query43.sql
  - query44.sql
  - query45.sql
  - query46.sql
  - query47.sql
  - query48.sql
  - query49.sql
  - query50.sql
  - query51.sql
  - query52.sql
  - query53.sql
  - query54.sql
  - query55.sql
  - query56.sql
  - query57.sql
  - query58.sql
  - query59.sql
  - query60.sql
  - query61.sql
  - query62.sql
  - query63.sql
  - query64.sql
  - query65.sql
  - query66.sql
  - query67.sql
  - query68.sql
  - query69.sql
  - query70.sql
  - query71.sql
  - query72.sql
  - query73.sql
  - query74.sql
  - query75.sql
  - query76.sql
  - query77.sql
  - query78.sql
  - query79.sql
  - query80.sql
  - query81.sql
  - query82.sql
  - query83.sql
  - query84.sql
  - query85.sql
  - query86.sql
  - query87.sql
  - query88.sql
  - query89.sql
  - query90.sql
  - query91.sql
  - query92.sql
  - query93.sql
  - query94.sql
  - query95.sql
  - query96.sql
  - query97.sql
  - query98.sql
  - query99.sql
- .gitkeep
.gitignore
ACKNOWLEDGEMENTS.md
CONTRIBUTING.md
LICENSE
MAINTAINERS.md
README.md

// repository documentation

Was this content helpful?

★ 0 (0 ratings)

# Explore Spark SQL and its performance using TPC-DS workload

> Data Science Experience is now Watson Studio. Although some images in this code pattern may show the service as Data Science Experience, the steps and processes will still work.

[Apache Spark](https://spark.apache.org) is a popular distributed data processing engine that is built around speed, ease of use and sophisticated analytics, with APIs in Java, Scala, Python, R, and SQL. Like other data processing engines, Spark has a unified optimization engine that computes the optimal way to execute a workload with the main purpose of reducing the disk IO and CPU usage.

We can evaluate and measure the performance of Spark SQL using the TPC-DS benchmark. [TPC-DS](http://www.tpc.org/tpcds) is a widely used industry standard decision support benchmark that is used to evaluate performance of data processing engines. Given that TPC-DS exercises some key data warehouse features, running TPC-DS successfully reflects the readiness of Spark in terms of addressing the need of a data warehouse application. Apache Spark v2.0 supports all the ninety-nine decision support queries that is part of this TPC-DS benchmark.

This Code Pattern is aimed at helping Spark developers quickly setup and run the TPC-DS benchmark in their own development setup.

When the reader has completed this Code Pattern, they will understand the following:

* How to setup the TPC-DS toolkit 
* How to generate TPC-DS datasets at different scale factor 
* How to create Spark database artifacts 
* How to run TPC-DS benchmark queries on Spark in local mode and see the results 
* Things to consider when increasing the data scale and run against a spark cluster

![Architecture diagram](https://raw.githubusercontent.com/IBM/spark-tpc-ds-performance-test/master/doc/source/images/architecture.png)

## Flow
* Commandline
  1. Create the spark tables with pre-generated dataset.
  2. Run the entire query set or a subset of queries and monitor the results.
* Notebook
  1. Create the spark tables with pre-generated dataset.
  2. Run the entire query set or individual query.
  3. View the query results or performance summary.
  4. View the performance graph.

## Included components

* [Apache Spark](https://spark.apache.org/): An open-source, fast and general-purpose cluster computing system
* [Jupyter Notebook](https://jupyter.org/): An open-source web application that allows you to create and share documents that contain live code, equations, visualizations and explanatory text.

## Featured technologies

* [Data Science](https://medium.com/ibm-data-science-experience/): Systems and scientific methods to analyze
structured and unstructured data in order to extract knowledge and insights.
* [Artificial Intelligence](https://medium.com/ibm-data-science-experience): Artificial intelligence can be
applied to disparate solution spaces to deliver disruptive technologies.
* [Python](https://www.python.org/): Python is a programming language that lets you work more quickly
and integrate your systems more effectively.

# Steps

There are two modes of exercising this Code Pattern:
* Run locally using a simple interactive command line shell script.
* [Run using a Jupyter notebook in Watson Studio](#run-using-a-jupyter-notebook-in-watson-studio).

## Run locally
1. [Clone the repository](#1-clone-the-repository)
2. [Setup development tools (Optional)](#2-setup-development-tools-optional)
3. [Install Spark](#3-install-spark)
4. [Run the script](#4-run-the-script)

### 1. Clone the repository

Clone the `spark-tpc-ds-performance-test` repo locally. In a terminal, run:

``` 
$ git clone https://github.com/IBM/spark-tpc-ds-performance-test 
```

### 2. Setup development tools (Optional)

Due to licensing restrictions, the TPCDS toolkit is not included as part of the code pattern. Instead, a pre-generated data set with 1GB scale factor is
included in this pattern. If you want to work with a data set with larger scale factor or explore learning the full life sycle of setting up TPCDS, you can
 download the tool kit from [TPC-DS](http://www.tpc.org/tpcds) and compile in your development environment.

Make sure the required development tools are installed in your platform. This Code Pattern is supported on Mac and Linux platforms only. Depending on your platform, run the following command to install the necessary development tools:

* **Ubuntu:**  
``` $ sudo apt-get install gcc make flex bison byacc git ```
* **CentOS/RHEL:**  
``` $ sudo yum install gcc make flex bison byacc git ```
* **MacOS:**  
``` $ xcode-select --install ```

To compile the toolkit you need to the following :

```
unzip <downloaded-tpc-ds-zipfile>
cd <tpc-ds-toolkit-version>/tools
make clean
make OS=<platform>
```

### 3. Install Spark

To successfully run the TPC-DS tests, Spark must be installed and pre-configured to work with an [Apache Hive](https://hive.apache.org/) metastore.

Perform 1 or more of the following options to ensure that Spark is installed and configured correctly. Once completed, modify ```bin/tpcdsenv.sh``` to set `SPARK_HOME` pointing to your Spark installation directory.

**Option 1** - If you already have Spark installed, complete the following steps to ensure your Spark version is properly configured:

```
$ cd $SPARK_HOME
$ bin/spark-shell

// Enter the following command at the scala prompt
  scala> spark.conf
  scale> spark.conf.get("spark.sql.catalogImplementation")
  res5: String = hive
  scala> <ctrl-c>
```
*Note:* You must exit out of the spark-shell process or you will encounters errors when performing the TPC-DS tests.

If the prompt returns `String = hive`, then your installation is properly configured.

**Option 2** - If you don't have an installed Spark version, or your current installation is not properly configured, we suggest trying to pull down version 2.2.0 from the Spark [downloads page](https://spark.apache.org/downloads.html). This version should be configured to work with Apache Hive, but please run the test in the previous option to make sure.

**Option 3** - The last option available is it to download and build it yourself. The first step is to clone the Spark repo:
```
$ git clone https://github.com/apache/spark.git
```
Then build it using these [instructions](https://spark.apache.org/docs/latest/building-spark.html). Please make sure to build Spark with Hive support by following the `Building With Hive and JDBC Support` section.
 
### 4. Run the script

*Note:* Verify that the `bin/tpcdsenv.sh` script has `SPARK_HOME` setup correctly.

Now that we have Spark setup and the TPC-DS scripts downloaded, we are ready to setup and start running the TPC-DS queries using the `bin/tpcdsspark.sh` utility script. This driver script will allow you to compile the TPC-DS toolkit to produce the data and the queries, and then run them to collect results.

Perform the following steps to complete the execution of the script:

```
 $ cd spark-tpc-ds-performance-test
 $ bin/tpcdsspark.sh

==============================================
TPC-DS On Spark Menu
----------------------------------------------
SETUP
 (1) Create spark tables
RUN
 (2) Run a subset of TPC-DS queries
 (3) Run All (99) TPC-DS Queries
CLEANUP
 (4) Cleanup
 (Q) Quit
----------------------------------------------
Please enter your choice followed by [ENTER]: 
```

#### Setup Option: "(1) - Create Spark Tables"

This option creates the tables in the database name specified by `TPCDS_DBNAME` defined in `bin/tpcdsenv.sh`. The default name is `TPCDS` but can be changed if needed. The created tables are based on the pre-generated data.

The SQL statements to create the tables can be found in `src/ddl/individual`, and are created in parquet format for efficient processing.

> Due to licensing restrictions, the TPCDS toolkit is not included as part of the code pattern. Instead, a pre-generated data set with 1GB scale factor is
  included in this pattern. If you want to work with a data set with larger scale factor or explore learning the full life sycle of setting up TPCDS, you can
  download the tool kit from [TPC-DS](http://www.tpc.org/tpcds) and compile in your development environment. Here are the instructions that describes how
  to compile the tool kit and generate data.

1. Compile the toolkit

```
   unzip <downloaded-tpc-ds-zipfile>
   cd <tpc-ds-toolkit-version>/tools
   make clean
   make OS=<platform>
   # (platform can be 'macos' or 'linux').
   ```

2. Generate the data.

```
   cd <tpc-ds-toolkit-version>/src/toolkit/tools
   ./dsdgen -dir <data_gen_dir> -scale <scale_factor>  -verbose y -terminate n 
   # data_gen_dir => The output directory where data will be generated at.
   # scale_factor => The scale factor of data.

3. Generate the queries.

The `dsqgen` utility in the tpcds toolkit may be used to generate the queries. Appropiate options should be passed to this utility. A typical example of its usage is:

```
   cd <tpc-ds-toolkit-version>/tools
   ./dsqgen -VERBOSE Y -DIALECT <dialectname> -DIRECTORY <query-template-dir> -SCALE <scale-factor> -OUTPUT_DIR <output-dir>
   ```

Below is example output for when this option is chosen.

```
==============================================
TPC-DS On Spark Menu
----------------------------------------------
SETUP
 (1) Create spark tables
RUN
 (2) Run a subset of TPC-DS queries
 (3) Run All (99) TPC-DS Queries
CLEANUP
 (4) Cleanup
 (Q) Quit
----------------------------------------------
Please enter your choice followed by [ENTER]: 1
----------------------------------------------

INFO: Creating tables. Will take a few minutes ...
INFO: Progress : [########################################] 100%
INFO: Spark tables created successfully..
Press any key to continue
```
 
#### Run Option: "(2) - Run a subset of TPC-DS queries"

A comma separated list of queries can be specified in this option. The result of each query in the supplied list is generated in `TPCDS_WORK_DIR`, with a default directory location of `work`. The format of the result file is `query<number>.res`.

A summary file named `run_summary.txt` is also generated. It contains information about query number, execution time and number of rows returned.

*Note:*  The query number is a two digit number, so for query 1 the results will be in `query01.res`.

*Note:*  If you are debugging and running queries using this option, make sure to save `run_summary.txt` after each of your runs.

```
==============================================
TPC-DS On Spark Menu
----------------------------------------------
SETUP
 (1) Create spark tables
RUN
 (2) Run a subset of TPC-DS queries
 (3) Run All (99) TPC-DS Queries
CLEANUP
 (4) Cleanup toolkit
 (Q) Quit
----------------------------------------------
Please enter your choice followed by [ENTER]: 2
----------------------------------------------

Enter a comma separated list of queries to run (ex: 1, 2), followed by [ENTER]:
1,2
INFO: Checking pre-reqs for running TPC-DS queries. May take a few seconds..
INFO: Checking pre-reqs for running TPC-DS queries is successful.
INFO: Running TPCDS queries. Will take a few minutes depending upon the number of queries specified.. 
INFO: Progress : [########################################] 100%
INFO: TPCDS queries ran successfully. Below are the result details
INFO: Individual result files: spark-tpc-ds-performance-test/work/query<number>.res
INFO: Summary file: spark-tpc-ds-performance-test/work/run_summary.txt
Press any key to continue
```

#### Run Option: "(3) - Run all (99) TPC-DS queries"

The only difference between this and option `(5)` is that all 99 TPC-DS queries are run instead of a subset.

*Note:* If you are running this on your laptop, it can take a few hours to run all  99 TPC-DS queries.

```
==============================================
TPC-DS On Spark Menu
----------------------------------------------
SETUP
 (1) Create spark tables
RUN
 (2) Run a subset of TPC-DS queries
 (3) Run All (99) TPC-DS Queries
CLEANUP
 (4) Cleanup toolkit
 (Q) Quit
----------------------------------------------
Please enter your choice followed by [ENTER]: 3
----------------------------------------------
INFO: Checking pre-reqs for running TPC-DS queries. May take a few seconds..
INFO: Checking pre-reqs for running TPC-DS queries is successful.
INFO: Running TPCDS queries. Will take a few minutes depending upon the number of queries specified.. 
INFO: Progress : [########################################] 100%
INFO: TPCDS queries ran successfully. Below are the result details
INFO: Individual result files: spark-tpc-ds-performance-test/work/query<number>.res
INFO: Summary file: spark-tpc-ds-performance-test/work/run_summary.txt
Press any key to continue
```

#### Cleanup option: "(4) - Cleanup"

This will clean up all of the files generated during option steps 1, 2, and 3. If you use this option, make sure to run the setup steps (1) before running queries using option 2 and 3.

#### Cleanup option: "(Q) - Quit"

This will exit the script.

## Run using a Jupyter notebook in Watson Studio

1. [Sign up for Watson Studio](#1-sign-up-for-watson-studio)
2. [Create the notebook](#2-create-the-notebook)
3. [Run the notebook](#3-run-the-notebook)
4. [Save and Share](#4-save-and-share)

### 1. Sign up for Watson Studio

Sign up for IBM's [Watson Studio](https://dataplatform.cloud.ibm.com/). By creating a project in Watson Studio a free tier ``Object Storage`` service will be created in your IBM Cloud account.

> Note: When creating your Object Storage service, select the ``Free`` storage type in order to avoid having to pay an upgrade fee.

Take note of your service names as you will need to select them in the following steps.

### 2. Create the notebook

* In [Watson Studio](https://dataplatform.cloud.ibm.com/), click on `Create notebook` to create a notebook.
* Create a project if necessary, provisioning an object storage service if required.
* In the `Assets` tab, select the `Create notebook` option.
* Select the `From URL` tab.
* Enter a name for the notebook.
* Optionally, enter a description for the notebook.
* Enter this Notebook URL: https://github.com/IBM/spark-tpc-ds-performance-test/blob/master/notebooks/run-tpcds-on-spark.ipynb
* Select the free Anaconda runtime.
* Click the `Create` button.

![](https://raw.githubusercontent.com/IBM/spark-tpc-ds-performance-test/master/doc/source/images/create-notebook.png)

### 3. Run the notebook

When a notebook is executed, what is actually happening is that each code cell in
the notebook is executed, in order, from top to bottom.

Each code cell is selectable and is preceded by a tag in the left margin. The tag
format is `In [x]:`. Depending on the state of the notebook, the `x` can be:

* A blank, this indicates that the cell has never been executed.
* A number, this number represents the relative order this code step was executed.
* A `*`, this indicates that the cell is currently executing.

There are several ways to execute the code cells in your notebook:

* One cell at a time.
  * Select the cell, and then press the `Play` button in the toolbar.
* Batch mode, in sequential order.
  * From the `Cell` menu bar, there are several options available. For example, you
    can `Run All` cells in your notebook, or you can `Run All Below`, that will
    start executing from the first cell under the currently selected cell, and then
    continue executing all cells that follow.
* At a scheduled time.
  * Press the `Schedule` button located in the top right section of your notebook
    panel. Here you can schedule your notebook to be executed once at some future
    time, or repeatedly at your specified interval.

### 4. Save and Share

#### How to save your work:

Under the `File` menu, there are several ways to save your notebook:

* `Save` will simply save the current state of your notebook, without any version
  information.
* `Save Version` will save your current state of your notebook with a version tag
  that contains a date and time stamp. Up to 10 versions of your notebook can be
  saved, each one retrievable by selecting the `Revert To Version` menu item.

#### How to share your work:

You can share your notebook by selecting the “Share” button located in the top
right section of your notebook panel. The end result of this action will be a URL
link that will display a “read-only” version of your notebook. You have several
options to specify exactly what you want shared from your notebook:

* `Only text and output`: will remove all code cells from the notebook view.
* `All content excluding sensitive code cells`:  will remove any code cells
  that contain a *sensitive* tag. For example, `# @hidden_cell` is used to protect
  your dashDB credentials from being shared.
* `All content, including code`: displays the notebook as is.
* A variety of `download as` options are also available in the menu.

## Considerations while increasing the scale factor.
This Code Pattern walks us through the steps that need to be performed to run the TPC-DS 
benchmark with the qualification scale factor(1GB). Since this is a performance benchmark, typically 
we need to run the benchmark with varying scale factors to gauge the throughput of the underlying data
processing engine. In the section below, we will briefly touch on things to be considered while increasing
the data and running the workload against a production cluster.

* Generation of the data in larger scale factor:
  In order to increase the scale, please follow the section titled "Scaling and Database Population" in
  the [benchmark spec](http://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v2.2.0.pdf).
* Movement of data to the distributed file system:
  After generating the data, we need to copy or move them to the underlying distributed file system (typically hdfs)
  that your spark cluster is configured to work with.
* Creation of spark tables:
  Modify the create table ddl script to change the path to the location of the data after the above copy step.
  Additionally we may consider to partition the fact tables for better performance.
* We need to tune several spark configs to get optimal performance. Some of them are discussed in the following
  links.
     * [Troubleshooting and Tuning spark for heavy work loads](https://developer.ibm.com/hadoop/2016/07/18/troubleshooting-and-tuning-spark-for-heavy-workloads) 
     * [Scaling up to 100TB](https://developer.ibm.com/hadoop/2017/02/07/experiences-comparing-big-sql-and-spark-sql-at-100tb)

# Learn more

* **Data Analytics Code Patterns**: Enjoyed this Code Pattern? Check out our other [Data Analytics Code Patterns](https://developer.ibm.com/technologies/data-science/)
* **AI and Data Code Pattern Playlist**: Bookmark our [playlist](https://www.youtube.com/playlist?list=PLzUbsvIyrNfknNewObx5N7uGZ5FKH0Fde) with all of our Code Pattern videos
* **Watson Studio**: Master the art of data science with IBM's [Watson Studio](https://dataplatform.cloud.ibm.com/)
* **Spark on IBM Cloud**: Need a Spark cluster? Create up to 30 Spark executors on IBM Cloud with our [Spark service](https://cloud.ibm.com/catalog/services/apache-spark)

# License
This code pattern is licensed under the Apache Software License, Version 2.  Separate third party code objects invoked within this code pattern are licensed by their respective providers pursuant to their own separate licenses. Contributions are subject to the [Developer Certificate of Origin, Version 1.1 (DCO)](https://developercertificate.org/) and the [Apache Software License, Version 2](http://www.apache.org/licenses/LICENSE-2.0.txt).

[Apache Software License (ASL) FAQ](http://www.apache.org/foundation/license-faq.html#WhatDoesItMEAN)