SingleStore DB

Create the Database
  1. Navigate to the SQL Editor in SingleStore DB Studio.

  2. Copy the following queries into the SQL Editor by clicking the copy icon in the code block.

    Warning

    After you pasted the query into SQL Editor, select all queries before clicking Run.

    The following queries will create eight tables associated with business data.

    DROP DATABASE IF EXISTS tpch;
    CREATE DATABASE tpch;
    USE tpch;
    
    CREATE TABLE `customer` (
    `c_custkey` int(11) NOT NULL,
    `c_name` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `c_address` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `c_nationkey` int(11) NOT NULL,
    `c_phone` char(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `c_acctbal` decimal(15,2) NOT NULL,
    `c_mktsegment` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `c_comment` varchar(117) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `l_linestatus` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `l_shipdate` date NOT NULL,
    `l_commitdate` date NOT NULL,
    `l_receiptdate` date NOT NULL,
    `l_shipinstruct` char(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `l_shipmode` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `l_comment` varchar(44) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    SHARD KEY (`l_orderkey`) USING CLUSTERED COLUMNSTORE
    );
    
    CREATE TABLE `nation` (
    `n_nationkey` int(11) NOT NULL,
    `n_name` char(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `n_regionkey` int(11) NOT NULL,
    `n_comment` varchar(152) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `o_totalprice` decimal(15,2) NOT NULL,
    `o_orderdate` date NOT NULL,
    `o_orderpriority` char(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `o_clerk` char(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `o_shippriority` int(11) NOT NULL,
    `o_comment` varchar(79) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    SHARD KEY (`o_orderkey`) USING CLUSTERED COLUMNSTORE
    );
    
    CREATE TABLE `part` (
    `p_partkey` int(11) NOT NULL,
    `p_name` varchar(55) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `p_mfgr` char(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `p_brand` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `p_type` varchar(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `p_size` int(11) NOT NULL,
    `p_container` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `p_retailprice` decimal(15,2) NOT NULL,
    `p_comment` varchar(23) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    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) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `r_comment` varchar(152) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    SHARD KEY (`r_regionkey`) USING CLUSTERED COLUMNSTORE
    );
    
    CREATE TABLE `supplier` (
    `s_suppkey` int(11) NOT NULL,
    `s_name` char(25) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `s_address` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `s_nationkey` int(11) NOT NULL,
    `s_phone` char(15) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    `s_acctbal` decimal(15,2) NOT NULL,
    `s_comment` varchar(101) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
    SHARD KEY (`s_suppkey`) USING CLUSTERED COLUMNSTORE
    );
    
  3. In the SQL Editor, select all of the queries. On Mac OS X, use ⌘ + A. On Windows, Ctrl + A.

  4. Click Run.

  5. Your tables are all setup if you see Success on all queries.