Important

The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.

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, while SingleStore 9.0 is recommended for production workloads.

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?