构建测试表 - 云原生数据仓库AnalyticDB

您需要在云原生数据仓库 AnalyticDB MySQL 版中创建八张数据表和一个视图以进行性能测试。

开启Native计算引擎和XUANWU_V2引擎（可选）

重要

Native计算引擎目前在邀测中。仅3.2.2.1及以上版本集群支持执行SET命令开启Native计算引擎和XUANWU_V2引擎。3.2.2.1以下内核版本，请提交工单联系技术支持升级内核版本，再直接执行SET命令开启Native计算引擎和XUANWU_V2引擎。

升级内核版本时会重启集群，集群将发生连接闪断，请在业务低峰期执行该操作，并确保应用程序具备重连机制。

如果您只需测试AnalyticDB for MySQL原生计算引擎和XUANWU引擎的性能，可跳过该步骤，直接创建测试表。如果您想测试Native计算引擎和XUANWU_V2引擎的性能，请在构建测试表之前打开Native计算引擎和和XUANWU_V2引擎，方法如下：

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

创建测试表

NATION表

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表

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表

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表

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表

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表

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表

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表

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视图

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