The TPC-DS is a decision support benchmark. It consists of a suite of business oriented ad-hoc queries and concurrent data modifications. The queries and the data populating the database have been chosen to have broad industry-wide relevance.

This article assumes that you have mastered the basic knowledge and operation of TPC-DS. For the knowledge about TPC-DS not mentioned in this article, you can obtain it from its Official Documentation.

This connector can be used to test the capabilities and query syntax of Spark without configuring access to an external data source. When you query a TPC-DS table, the connector generates the data on the fly using a deterministic algorithm.

Goto Try Kyuubi to explore TPC-DS data instantly!

TPC-DS Integration

To enable the integration of kyuubi spark sql engine and TPC-DS through Apache Spark Datasource V2 and Catalog APIs, you need to:

Dependencies

The classpath of kyuubi spark sql engine with TPC-DS supported consists of

  1. kyuubi-spark-sql-engine-1.9.1_2.12.jar, the engine jar deployed with Kyuubi distributions
  2. a copy of spark distribution
  3. kyuubi-spark-connector-tpcds-1.9.1_2.12.jar, which can be found in the Maven Central

In order to make the TPC-DS connector package visible for the runtime classpath of engines, we can use one of these methods:

  1. Put the TPC-DS connector package into $SPARK_HOME/jars directly
  2. Set spark.jars=kyuubi-spark-connector-tpcds-1.9.1_2.12.jar

Configurations

To add TPC-DS tables as a catalog, we can set the following configurations in $SPARK_HOME/conf/spark-defaults.conf:

  1. # (required) Register a catalog named `tpcds` for the spark engine.
  2. spark.sql.catalog.tpcds=org.apache.kyuubi.spark.connector.tpcds.TPCDSCatalog
  4. # (optional) Excluded database list from the catalog, all available databases are:
  5. #            sf0, tiny, sf1, sf10, sf30, sf100, sf300, sf1000, sf3000, sf10000, sf30000, sf100000.
  6. spark.sql.catalog.tpcds.excludeDatabases=sf10000,sf30000
  8. # (optional) When true, use CHAR/VARCHAR, otherwise use STRING. It affects output of the table schema,
  9. #            e.g. `SHOW CREATE TABLE <table>`, `DESC <table>`.
  10. spark.sql.catalog.tpcds.useAnsiStringType=false
  12. # (optional) TPCDS changed table schemas in v2.6.0, turn off this option to use old table schemas.
  13. #            See detail at: https://www.tpc.org/tpc_documents_current_versions/pdf/tpc-ds_v3.2.0.pdf
  14. spark.sql.catalog.tpcds.useTableSchema_2_6=true
  16. # (optional) Maximum bytes per task, consider reducing it if you want higher parallelism.
  17. spark.sql.catalog.tpcds.read.maxPartitionBytes=128m

TPC-DS Operations

Listing databases under tpcds catalog.

  1. SHOW DATABASES IN tpcds;

Listing tables under tpcds.sf1 database.

  1. SHOW TABLES IN tpcds.sf1;

Switch current database to tpcds.sf1 and run a query against it.

  1. USE tpcds.sf1;
  2. SELECT * FROM store;