All Products
Search
Document Center

AnalyticDB for MySQL:Create a test table

Last Updated:Aug 04, 2023

This topic describes the scenario information for the performance test of AnalyticDB for MySQL.

The following eight tables and one view are created for the performance test of AnalyticDB for MySQL:

  • A nation table

    CREATE TABLE nation (
      n_nationkey int NOT NULL COMMENT '',
      n_name varchar NOT NULL COMMENT '',
      n_regionkey int NOT NULL COMMENT '',
      n_comment varchar COMMENT '',
      PRIMARY KEY (n_nationkey)
    ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';
  • A region table

    CREATE TABLE region (
      r_regionkey int NOT NULL COMMENT '',
      r_name varchar NOT NULL COMMENT '',
      r_comment varchar COMMENT '',
      PRIMARY KEY (r_regionkey)
    ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';
  • A part table

    CREATE TABLE part (
      p_partkey int NOT NULL COMMENT '',
      p_name varchar NOT NULL COMMENT '',
      p_mfgr varchar NOT NULL COMMENT '',
      p_brand varchar(10) NOT NULL COMMENT '',
      p_type varchar NOT NULL COMMENT '',
      p_size int NOT NULL COMMENT '',
      p_container varchar(10) NOT NULL COMMENT '',
      p_retailprice decimal(15, 2) NOT NULL COMMENT '',
      p_comment varchar NOT NULL COMMENT '',
      PRIMARY KEY (p_partkey)
    ) DISTRIBUTED BY HASH (p_partkey) INDEX_ALL = 'Y' compression='lz4hc';
  • A supplier table

    CREATE TABLE supplier (
      s_suppkey int NOT NULL COMMENT '',
      s_name varchar NOT NULL COMMENT '',
      s_address varchar NOT NULL COMMENT '',
      s_nationkey int NOT NULL COMMENT '',
      s_phone varchar(15) NOT NULL COMMENT '',
      s_acctbal decimal(15, 2) NOT NULL COMMENT '',
      s_comment varchar NOT NULL COMMENT '',
      PRIMARY KEY (s_suppkey)
    ) DISTRIBUTED BY HASH (s_suppkey) INDEX_ALL = 'Y' compression='lz4hc';
  • A partsupp table

    CREATE TABLE partsupp (
      ps_partkey int NOT NULL COMMENT '',
      ps_suppkey int NOT NULL COMMENT '',
      ps_availqty int NOT NULL COMMENT '',
      ps_supplycost decimal(15, 2) NOT NULL COMMENT '',
      ps_comment varchar NOT NULL COMMENT '',
      PRIMARY KEY (ps_partkey, ps_suppkey)
    ) DISTRIBUTED BY HASH (ps_partkey) INDEX_ALL = 'Y' compression='lz4hc';
  • A customer table

    CREATE TABLE customer (
      c_custkey int NOT NULL COMMENT '',
      c_name varchar NOT NULL COMMENT '',
      c_address varchar NOT NULL COMMENT '',
      c_nationkey int NOT NULL COMMENT '',
      c_phone varchar(15) NOT NULL COMMENT '',
      c_acctbal decimal(15, 2) NOT NULL COMMENT '',
      c_mktsegment varchar(10) NOT NULL COMMENT '',
      c_comment varchar NOT NULL COMMENT '',
      PRIMARY KEY (c_custkey)
    ) DISTRIBUTED BY HASH (c_custkey) INDEX_ALL = 'Y' compression='lz4hc';
  • An orders table

    CREATE TABLE orders (
      o_orderkey bigint NOT NULL COMMENT '',
      o_custkey int NOT NULL COMMENT '',
      o_orderstatus varchar(1) NOT NULL COMMENT '',
      o_totalprice decimal(15, 2) NOT NULL COMMENT '',
      o_orderdate date NOT NULL COMMENT '',
      o_orderpriority varchar(15) NOT NULL COMMENT '',
      o_clerk varchar(15) NOT NULL COMMENT '',
      o_shippriority int NOT NULL COMMENT '',
      o_comment varchar NOT NULL COMMENT '',
      KEY idx_o_custkey (o_custkey),
      KEY idx_o_orderdate (o_orderdate),
      KEY idx_o_orderkey (o_orderkey),
      KEY idx_o_orderstatus (o_orderstatus)
    ) DISTRIBUTED BY HASH (o_orderkey) partition by value(date_format(o_orderdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';
  • A lineitem table

    CREATE TABLE lineitem (
      l_orderkey bigint NOT NULL COMMENT '',
      l_partkey int NOT NULL COMMENT '',
      l_suppkey int NOT NULL COMMENT '',
      l_linenumber int NOT NULL COMMENT '',
      l_quantity decimal(15, 2) NOT NULL COMMENT '',
      l_extendedprice decimal(15, 2) NOT NULL COMMENT '',
      l_discount decimal(15, 2) NOT NULL COMMENT '',
      l_tax decimal(15, 2) NOT NULL COMMENT '',
      l_returnflag varchar(1) NOT NULL COMMENT '',
      l_linestatus varchar(1) NOT NULL COMMENT '',
      l_shipdate date NOT NULL COMMENT '',
      l_commitdate date NOT NULL COMMENT '',
      l_receiptdate date NOT NULL COMMENT '',
      l_shipinstruct varchar(25) NOT NULL COMMENT '',
      l_shipmode varchar(10) NOT NULL COMMENT '',
      l_comment varchar NOT NULL COMMENT '',
      KEY idx_l_orderkey (l_orderkey),
      KEY idx_l_partkey (l_partkey),
      KEY idx_l_receiptdate (l_receiptdate),
      KEY idx_l_returnflag (l_returnflag),
      KEY idx_l_shipdate (l_shipdate),
      KEY idx_l_shipinstruct (l_shipinstruct),
      KEY idx_l_shipmode (l_shipmode),
      KEY idx_l_suppkey (l_suppkey)
    ) DISTRIBUTED BY HASH (l_orderkey) partition by value(date_format(l_shipdate, '%Y%m')) PARTITION NUM 1000 INDEX_ALL = 'N' compression='lz4hc';
  • A revenue0 view

    CREATE VIEW `revenue0` AS
    SELECT
      `l_suppkey` supplier_no,
      `sum`(
        (
          `l_extendedprice` * (1 - `l_discount`)
        )
      ) total_revenue
    FROM
      lineitem
    WHERE
      (
        (`l_shipdate` >= DATE '1996-01-01')
        AND (
          `l_shipdate` < (
            DATE '1996-01-01' + INTERVAL '3' MONTH
          )
        )
      )
    GROUP BY
      `l_suppkey`;