Important

Self-managed SingleStore will soon transition from version 9.1 RC to version 10. This new semantic versioning scheme will provide SingleStore with finer control over engine and feature releases that were not possible with the current versioning scheme.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10 prior to its general availability. Ahead of this transition, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 10.

Create Database and Tables

Connect to your SingleStore deployment and run the following SQL commands to create a database named tpch and eight associated tables.

Note

The SQL Editor only runs the queries that you select, so ensure you have them all selected before selecting Run.

SQL

DROP DATABASE IF EXISTS tpch;
CREATE DATABASE tpch;
USE tpch;

CREATE TABLE `customer` (
  `c_custkey` int(11) NOT NULL,
  `c_name` varchar(25) COLLATE utf8mb4_bin NOT NULL,
  `c_address` varchar(40) COLLATE utf8mb4_bin NOT NULL,
  `c_nationkey` int(11) NOT NULL,
  `c_phone` char(15) COLLATE utf8mb4_bin NOT NULL,
  `c_acctbal` decimal(15,2) NOT NULL,
  `c_mktsegment` char(10) COLLATE utf8mb4_bin NOT NULL,
  `c_comment` varchar(117) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY pk (`c_custkey`) UNENFORCED RELY,
  SHARD KEY (`c_custkey`) USING CLUSTERED COLUMNSTORE
);

CREATE TABLE `lineitem` (
  `l_orderkey` bigint(11) NOT NULL,
  `l_partkey` int(11) NOT NULL,
  `l_suppkey` int(11) NOT NULL,
  `l_linenumber` int(11) NOT NULL,
  `l_quantity` decimal(15,2) NOT NULL,
  `l_extendedprice` decimal(15,2) NOT NULL,
  `l_discount` decimal(15,2) NOT NULL,
  `l_tax` decimal(15,2) NOT NULL,
  `l_returnflag` char(1) COLLATE utf8mb4_bin NOT NULL,
  `l_linestatus` char(1) COLLATE utf8mb4_bin NOT NULL,
  `l_shipdate` date NOT NULL,
  `l_commitdate` date NOT NULL,
  `l_receiptdate` date NOT NULL,
  `l_shipinstruct` char(25) COLLATE utf8mb4_bin NOT NULL,
  `l_shipmode` char(10) COLLATE utf8mb4_bin NOT NULL,
  `l_comment` varchar(44) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY pk (`l_orderkey`, `l_linenumber`) UNENFORCED RELY,
  SHARD KEY (`l_orderkey`) USING CLUSTERED COLUMNSTORE
);

CREATE TABLE `nation` (
  `n_nationkey` int(11) NOT NULL,
  `n_name` char(25) COLLATE utf8mb4_bin NOT NULL,
  `n_regionkey` int(11) NOT NULL,
  `n_comment` varchar(152) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY pk (`n_nationkey`) UNENFORCED RELY,
  SHARD KEY (`n_nationkey`) USING CLUSTERED COLUMNSTORE
);

CREATE TABLE `orders` (
  `o_orderkey` bigint(11) NOT NULL,
  `o_custkey` int(11) NOT NULL,
  `o_orderstatus` char(1) COLLATE utf8mb4_bin NOT NULL,
  `o_totalprice` decimal(15,2) NOT NULL,
  `o_orderdate` date NOT NULL,
  `o_orderpriority` char(15) COLLATE utf8mb4_bin NOT NULL,
  `o_clerk` char(15) COLLATE utf8mb4_bin NOT NULL,
  `o_shippriority` int(11) NOT NULL,
  `o_comment` varchar(79) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY pk (`o_orderkey`) UNENFORCED RELY,
  SHARD KEY (`o_orderkey`) USING CLUSTERED COLUMNSTORE
);

CREATE TABLE `part` (
  `p_partkey` int(11) NOT NULL,
  `p_name` varchar(55) COLLATE utf8mb4_bin NOT NULL,
  `p_mfgr` char(25) COLLATE utf8mb4_bin NOT NULL,
  `p_brand` char(10) COLLATE utf8mb4_bin NOT NULL,
  `p_type` varchar(25) COLLATE utf8mb4_bin NOT NULL,
  `p_size` int(11) NOT NULL,
  `p_container` char(10) COLLATE utf8mb4_bin NOT NULL,
  `p_retailprice` decimal(15,2) NOT NULL,
  `p_comment` varchar(23) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY pk (`p_partkey`) UNENFORCED RELY,
  SHARD KEY (`p_partkey`) USING CLUSTERED COLUMNSTORE
);

CREATE TABLE `partsupp` (
  `ps_partkey` int(11) NOT NULL,
  `ps_suppkey` int(11) NOT NULL,
  `ps_availqty` int(11) NOT NULL,
  `ps_supplycost` decimal(15,2) NOT NULL,
  `ps_comment` varchar(199) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY pk (`ps_partkey`,`ps_suppkey`) UNENFORCED RELY,
  SHARD KEY(`ps_partkey`),
  KEY (`ps_partkey`,`ps_suppkey`) USING CLUSTERED COLUMNSTORE
);

CREATE TABLE `region` (
  `r_regionkey` int(11) NOT NULL,
  `r_name` char(25) COLLATE utf8mb4_bin NOT NULL,
  `r_comment` varchar(152) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY pk (`r_regionkey`) UNENFORCED RELY,
  SHARD KEY (`r_regionkey`) USING CLUSTERED COLUMNSTORE
);

CREATE TABLE `supplier` (
  `s_suppkey` int(11) NOT NULL,
  `s_name` char(25) COLLATE utf8mb4_bin NOT NULL,
  `s_address` varchar(40) COLLATE utf8mb4_bin NOT NULL,
  `s_nationkey` int(11) NOT NULL,
  `s_phone` char(15) COLLATE utf8mb4_bin NOT NULL,
  `s_acctbal` decimal(15,2) NOT NULL,
  `s_comment` varchar(101) COLLATE utf8mb4_bin NOT NULL,
  UNIQUE KEY pk (`s_suppkey`) UNENFORCED RELY,
  SHARD KEY (`s_suppkey`) USING CLUSTERED COLUMNSTORE
);

Create Database and Tables

Was this article helpful?

Was this article helpful?