AnalyticDB:Create test tables

(Optional) Enable the Native compute engine and the XUANWU_V2 engine

If you want to test the performance of the native compute engine and the XUANWU engine of AnalyticDB for MySQL, skip this step and directly create test tables. For more information, see the "Create test tables" section of this topic. If you want to test the performance of the Native compute engine and the XUANWU_V2 engine of AnalyticDB for MySQL, execute the following statements to enable the Native compute engine and the XUANWU_V2 engine before you create test tables:

SET ADB_CONFIG native_engine_opt_enabled = true;
SET ADB_CONFIG rc_ddl_engine_rewrite_xuanwuv2 = true;

Create test tables

• 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 '',
    dummy VARCHAR,
    PRIMARY KEY (n_nationkey)
  ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

• region table

  CREATE TABLE region (
    r_regionkey INT NOT NULL COMMENT '',
    r_name VARCHAR NOT NULL COMMENT '',
    r_comment VARCHAR COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (r_regionkey)
  ) DISTRIBUTED BY BROADCAST INDEX_ALL = 'Y' compression='lz4hc';

• 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 DOUBLE NOT NULL COMMENT '',
    p_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (p_partkey)
  ) DISTRIBUTED BY HASH (p_partkey) INDEX_ALL = 'Y' compression='lz4hc';

• 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 DOUBLE NOT NULL COMMENT '',
    s_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (s_suppkey)
  ) DISTRIBUTED BY HASH (s_suppkey) INDEX_ALL = 'Y' compression='lz4hc';

• 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 DOUBLE NOT NULL COMMENT '',
    ps_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (ps_partkey, ps_suppkey)
  ) DISTRIBUTED BY HASH (ps_partkey) INDEX_ALL = 'Y' compression='lz4hc';

• 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 DOUBLE NOT NULL COMMENT '',
    c_mktsegment VARCHAR(10) NOT NULL COMMENT '',
    c_comment VARCHAR NOT NULL COMMENT '',
    dummy VARCHAR,
    PRIMARY KEY (c_custkey)
  ) DISTRIBUTED BY HASH (c_custkey) INDEX_ALL = 'Y' compression='lz4hc';

• 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 DOUBLE 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 '',
    dummy VARCHAR,
    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';

• 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 DOUBLE NOT NULL COMMENT '',
    l_extendedprice DOUBLE NOT NULL COMMENT '',
    l_discount DOUBLE NOT NULL COMMENT '',
    l_tax DOUBLE 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 '',
    dummy VARCHAR,
    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';

• 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`;