6.1 tpch 分析快速入门_MySQL HeatWave 用户指南

MySQL HeatWave 用户指南 / 第 6 章 HeatWave 快速入门 / 6.1 tpch 分析快速入门

6.1 tpch 分析快速入门

tpch Analytics Quickstart 将引导您使用 MySQL Shell 并行表导入实用程序将数据导入数据库系统，手动将数据加载到 HeatWave 中，并运行查询。

描述了以下主题：

先决条件

一个可操作的 MySQL 数据库系统。
- 对于 OCI 上的 MySQL 数据库服务，请参阅 MySQL 数据库服务指南中的创建数据库系统。
- 对于 AWS 上的 MySQL HeatWave，请参阅 MySQL HeatWave on AWS 服务指南中的创建数据库系统。
- 对于 Oracle Database Service for Azure (ODSA) 中的 HeatWave，请参阅配置 MySQL HeatWave。
一个可操作的 HeatWave 集群。
- 对于 OCI 上的 MySQL 数据库服务，请参阅 MySQL 数据库服务指南中的添加 HeatWave 集群。
- 对于 AWS 上的 MySQL HeatWave，请参阅 MySQL HeatWave on AWS 服务指南中的创建 HeatWave 集群。
- 对于 Oracle Database Service for Azure (ODSA) 中的 HeatWave，请参阅供应 HeatWave 节点。
MySQL Shell 8.0.22 或更高版本。
- 对于 OCI 上的 MySQL 数据库服务，请参阅使用 SSH 和 MySQL Shell 连接到 MySQL 数据库系统。
- 对于 AWS 上的 MySQL HeatWave，请参阅 MySQL HeatWave on AWS 服务指南中的连接 MySQL Shell。

生成 tpch 样本数据

本快速入门中的示例使用示例数据库，该数据库是从TPC Benchmark™ H (TPC-H)规范tpch 派生的临时决策支持数据库。有关架构的概述，请参阅规范文档的逻辑数据库设计部分。 tpch

以下说明描述了如何使用该实用程序生成tpch示例数据。dbgen这些说明假设您使用的是安装了库的 Linuxgcc系统 make。

要生成tpch示例数据：

从TPC Download Current 下载 TPC-H 工具 zip 文件。
将 zip 文件解压缩到系统上的某个位置。

切换到dbgen目录并复制 makefile 模板。

$> cd 2.18.0/dbgen
$> cp makefile.suite makefile

在 makefile 中配置以下设置：

CC = gcc
DATABASE= ORACLE
MACHINE = LINUX
WORKLOAD = TPCH

运行make来构建 dbgen实用程序：
```
$> make
```
发出以下dbgen命令为 tpch数据库生成一组 1GB 的数据文件：
```
$> ./dbgen -s 1
```
该操作可能需要几分钟时间。完成后，以下数据文件出现在工作目录中，tpch数据库中的每个表一个：
```
$> ls -1 *.tbl
customer.tbl
lineitem.tbl
nation.tbl
orders.tbl
partsupp.tbl
part.tbl
region.tbl
supplier.tbl
```

创建示例数据库并导入数据

本主题介绍如何在MySQL数据库系统上创建tpch样例数据库并导入样例数据。示例数据必须在 MySQL 数据库系统上可用，然后才能加载到 HeatWave 集群中。

示例数据库创建和导入操作是使用 MySQL Shell 执行的。MySQL Shell Parallel Table Import Utility 为大型数据文件提供快速数据导入。该实用程序分析输入数据文件，将其分成块，然后使用并行连接将块上传到目标 MySQL 数据库系统。LOAD DATA 该实用程序能够以比使用语句的标准单线程上传快许多倍的速度完成大型数据导入。有关其他信息，请参阅并行表导入实用程序。

在 MySQL 数据库系统上创建 tpch 示例数据库并导入数据：

启动 MySQL Shell 并连接到 MySQL 数据库系统的端点：
```
$> mysqlsh --mysql Username@DBSystem_IP_Address_or_Host_Name
```
该--mysql选项打开一个 ClassicSession，在使用 MySQL Shell Parallel Table Import Utility 时需要它。

MySQL Shell 默认以 JavaScript 执行模式打开。
```
MySQL>JS>
```
将 MySQL Shell 执行模式从 JavaScript 更改为 SQL：
```
MySQL>JS> \sql
```

创建tpch示例数据库和表：

CREATE DATABASE tpch character set utf8mb4;
USE tpch;

CREATE TABLE nation  ( N_NATIONKEY INTEGER primary key,
    N_NAME       CHAR(25) NOT NULL,
    N_REGIONKEY  INTEGER NOT NULL,
    N_COMMENT    VARCHAR(152));
							
CREATE TABLE region  ( R_REGIONKEY INTEGER primary key,
    R_NAME       CHAR(25) NOT NULL,
    R_COMMENT    VARCHAR(152));
							
CREATE TABLE part  ( P_PARTKEY INTEGER primary key,
    P_NAME        VARCHAR(55) NOT NULL,
    P_MFGR        CHAR(25) NOT NULL,
    P_BRAND       CHAR(10) NOT NULL,
    P_TYPE        VARCHAR(25) NOT NULL,
    P_SIZE        INTEGER NOT NULL,
    P_CONTAINER   CHAR(10) NOT NULL,
    P_RETAILPRICE DECIMAL(15,2) NOT NULL,
    P_COMMENT     VARCHAR(23) NOT NULL );
						  
CREATE TABLE supplier  ( S_SUPPKEY INTEGER primary key,
    S_NAME        CHAR(25) NOT NULL,
    S_ADDRESS     VARCHAR(40) NOT NULL,
    S_NATIONKEY   INTEGER NOT NULL,
    S_PHONE       CHAR(15) NOT NULL,
    S_ACCTBAL     DECIMAL(15,2) NOT NULL,
    S_COMMENT     VARCHAR(101) NOT NULL);
							 
CREATE TABLE partsupp  ( PS_PARTKEY INTEGER NOT NULL,
    PS_SUPPKEY     INTEGER NOT NULL,
    PS_AVAILQTY    INTEGER NOT NULL,
    PS_SUPPLYCOST  DECIMAL(15,2)  NOT NULL,
    PS_COMMENT     VARCHAR(199) NOT NULL, primary key (ps_partkey, ps_suppkey) );
						
CREATE TABLE customer  ( C_CUSTKEY INTEGER primary key,
    C_NAME        VARCHAR(25) NOT NULL,
    C_ADDRESS     VARCHAR(40) NOT NULL,
    C_NATIONKEY   INTEGER NOT NULL,
    C_PHONE       CHAR(15) NOT NULL,
    C_ACCTBAL     DECIMAL(15,2)   NOT NULL,
    C_MKTSEGMENT  CHAR(10) NOT NULL,
    C_COMMENT     VARCHAR(117) NOT NULL);
							 
CREATE TABLE orders  ( O_ORDERKEY INTEGER primary key,
    O_CUSTKEY        INTEGER NOT NULL,
    O_ORDERSTATUS    CHAR(1) NOT NULL,
    O_TOTALPRICE     DECIMAL(15,2) NOT NULL,
    O_ORDERDATE      DATE NOT NULL,
    O_ORDERPRIORITY  CHAR(15) NOT NULL,
    O_CLERK          CHAR(15) NOT NULL,
    O_SHIPPRIORITY   INTEGER NOT NULL,
    O_COMMENT        VARCHAR(79) NOT NULL);
						   
CREATE TABLE lineitem ( L_ORDERKEY INTEGER NOT NULL,
    L_PARTKEY     INTEGER NOT NULL,
    L_SUPPKEY     INTEGER NOT NULL,
    L_LINENUMBER  INTEGER 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) NOT NULL,
    L_LINESTATUS  CHAR(1) NOT NULL,
    L_SHIPDATE    DATE NOT NULL,
    L_COMMITDATE  DATE NOT NULL,
    L_RECEIPTDATE DATE NOT NULL,
    L_SHIPINSTRUCT CHAR(25) NOT NULL,
    L_SHIPMODE     CHAR(10) NOT NULL,
    L_COMMENT      VARCHAR(44) NOT NULL,
    primary key(L_ORDERKEY,L_LINENUMBER));

验证tpch模式和表是否已创建：

MySQL>SQL> SHOW TABLES;
+----------------+
| Tables_in_tpch |
+----------------+
| customer       |
| lineitem       |
| nation         |
| orders         |
| part           |
| partsupp       |
| region         |
| supplier       |
+----------------+

改回 JavaScript 执行模式以使用并行表导入实用程序：
```
MySQL>SQL> \js
```

执行以下操作将数据导入到tpchMySQL DB System上的数据库中。

笔记

有关 util.importTable()以下命令中使用的选项的信息，请参阅并行表导入实用程序。使用该 threads选项指定的并行线程数取决于形状的 CPU 内核数。假定样本数据字段以管道“ |”字符结尾。

MySQL>JS> util.importTable("nation.tbl",{table: "nation", fieldsTerminatedBy:"|", 
bytesPerChunk:"100M", threads:16})

MySQL>JS> util.importTable("region.tbl",{table: "region", fieldsTerminatedBy:"|", 
bytesPerChunk:"100M", threads:16})

MySQL>JS> util.importTable("part.tbl",{table: "part", fieldsTerminatedBy:"|", 
bytesPerChunk:"100M", threads:16})

MySQL>JS> util.importTable("supplier.tbl",{table: "supplier", fieldsTerminatedBy:"|" , 
bytesPerChunk:"100M", threads:16})

MySQL>JS> util.importTable("partsupp.tbl",{table: "partsupp", fieldsTerminatedBy:"|", 
bytesPerChunk:"100M", threads:16})

MySQL>JS> util.importTable("customer.tbl",{table: "customer", fieldsTerminatedBy:"|", 
bytesPerChunk:"100M", threads:16})

MySQL>JS> util.importTable("orders.tbl",{table: "orders", fieldsTerminatedBy:"|", 
bytesPerChunk:"100M", threads:16})

MySQL>JS> util.importTable("lineitem.tbl",{table: "lineitem", fieldsTerminatedBy:"|", 
bytesPerChunk:"100M", threads:16})

将 tpch 数据加载到 HeatWave 中

将tpch示例数据加载到 HeatWave 集群中：

笔记

如果您在 AWS 上使用 MySQL HeatWave，您还可以从 MySQL HeatWave 控制台加载数据。请参阅 MySQL HeatWave on AWS 服务指南中的管理 HeatWave 数据。

启动 MySQL Shell 并连接到 MySQL 数据库系统的端点：
```
$> mysqlsh Username@DBSystem_IP_Address_or_Host_Name
```
MySQL Shell 默认以 JavaScript 执行模式打开。
```
MySQL>JS>
```
将 MySQL Shell 执行模式更改为 SQL：
```
MySQL>JS> \sql
```

执行以下操作准备 tpch示例数据库表并将它们加载到 HeatWave Cluster 中。执行的操作包括定义字符串列编码、定义辅助引擎和执行 SECONDARY_LOAD操作。

USE tpch;

ALTER TABLE nation modify `N_NAME` CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE nation modify `N_COMMENT` VARCHAR(152) COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE nation SECONDARY_ENGINE=RAPID;
ALTER TABLE nation SECONDARY_LOAD;

ALTER TABLE region modify `R_NAME` CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE region modify `R_COMMENT` VARCHAR(152) COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE region SECONDARY_ENGINE=RAPID;
ALTER TABLE region SECONDARY_LOAD;

ALTER TABLE part modify `P_MFGR` CHAR(25) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE part modify `P_BRAND` CHAR(10) NOT NULL COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE part modify `P_CONTAINER` CHAR(10) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE part modify `P_COMMENT` VARCHAR(23) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE part SECONDARY_ENGINE=RAPID;
ALTER TABLE part SECONDARY_LOAD;

ALTER TABLE supplier modify `S_NAME` CHAR(25) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE supplier modify `S_ADDRESS` VARCHAR(40) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE supplier modify `S_PHONE` CHAR(15) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';

ALTER TABLE supplier SECONDARY_ENGINE=RAPID;
ALTER TABLE supplier SECONDARY_LOAD;
ALTER TABLE partsupp modify `PS_COMMENT` VARCHAR(199) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE partsupp SECONDARY_ENGINE=RAPID;
ALTER TABLE partsupp SECONDARY_LOAD;

ALTER TABLE customer modify `C_NAME` VARCHAR(25) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE customer modify `C_ADDRESS` VARCHAR(40) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE customer modify `C_MKTSEGMENT` CHAR(10) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE customer modify `C_COMMENT` VARCHAR(117) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';

ALTER TABLE customer SECONDARY_ENGINE=RAPID;
ALTER TABLE customer SECONDARY_LOAD;

ALTER TABLE orders modify `O_ORDERSTATUS` CHAR(1) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE orders modify `O_ORDERPRIORITY` CHAR(15) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE orders modify `O_CLERK` CHAR(15) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE orders SECONDARY_ENGINE=RAPID;
ALTER TABLE orders SECONDARY_LOAD;

ALTER TABLE lineitem modify `L_RETURNFLAG` CHAR(1) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE lineitem modify `L_LINESTATUS` CHAR(1) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE lineitem modify `L_SHIPINSTRUCT` CHAR(25) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE lineitem modify `L_SHIPMODE` CHAR(10) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE lineitem modify `L_COMMENT` VARCHAR(44) NOT NULL 
COMMENT 'RAPID_COLUMN=ENCODING=SORTED';
ALTER TABLE lineitem SECONDARY_ENGINE=RAPID;
ALTER TABLE lineitem SECONDARY_LOAD;

通过查询HeatWave Performance Schema 表中的数据，验证tpch示例数据库表是否已加载到 HeatWave 集群中。LOAD_STATUS加载的表具有 AVAIL_RPDGSTABSTATE加载状态。

MySQL>SQL> USE performance_schema;
MySQL>SQL> SELECT NAME, LOAD_STATUS FROM rpd_tables,rpd_table_id
           WHERE rpd_tables.ID = rpd_table_id.ID;
+------------------------------+---------------------+
| NAME                         | LOAD_STATUS         |
+------------------------------+---------------------+
| tpch.supplier                | AVAIL_RPDGSTABSTATE |
| tpch.partsupp                | AVAIL_RPDGSTABSTATE |
| tpch.orders                  | AVAIL_RPDGSTABSTATE |
| tpch.lineitem                | AVAIL_RPDGSTABSTATE |
| tpch.customer                | AVAIL_RPDGSTABSTATE |
| tpch.nation                  | AVAIL_RPDGSTABSTATE |
| tpch.region                  | AVAIL_RPDGSTABSTATE |
| tpch.part                    | AVAIL_RPDGSTABSTATE |
+------------------------------+---------------------+

运行 tpch 查询

本主题介绍如何查询tpchHeatWave 集群中的数据。将表加载到 HeatWave 集群后，符合条件的查询将自动卸载到 HeatWave 集群以加速处理。运行查询：

笔记

如果您在 AWS 上使用 MySQL HeatWave，您还可以从 MySQL HeatWave 控制台中的查询编辑器运行查询。请参阅 MySQL HeatWave on AWS 服务指南中的运行查询。

启动 MySQL Shell 并连接到 MySQL 数据库系统的端点：
```
$> mysqlsh Username@DBSystem_IP_Address_or_Host_Name
```
MySQL Shell 默认以 JavaScript 执行模式打开。
```
MySQL>JS>
```
将 MySQL Shell 执行模式更改为 SQL：
```
MySQL>JS> \sql
```

更改tpch数据库：

MySQL>SQL> USE tpch;
Default schema set to `tpch`.Fetching table and column names from `tpch` for 
auto-completion... Press ^C to stop.

在运行查询之前，使用它EXPLAIN来验证查询是否可以卸载到 HeatWave 集群。例如：

MySQL>SQL> EXPLAIN SELECT SUM(l_extendedprice * l_discount) AS revenue 
           FROM lineitem WHERE l_shipdate >= date '1994-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: lineitem
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 56834662
     filtered: 33.33
        Extra: Using where; Using secondary engine RAPID

如果可以卸载查询，则输出报告Extra中的列。 EXPLAIN"Using secondary engine RAPID"

验证查询可以卸载后，运行查询并记下执行时间。

MySQL>SQL> SELECT SUM(l_extendedprice * l_discount) AS revenue 
           FROM lineitem WHERE l_shipdate >= date '1994-01-01';
+------------------+
| revenue          |
+------------------+
| 82752894454.9036 |
+------------------+
1 row in set (0.04 sec)

要将 HeatWave 执行时间与 MySQL 数据库系统执行时间进行比较，请禁用该 use_secondary_engine 变量以查看在 MySQL 数据库系统上运行相同查询需要多长时间。例如：
```
MySQL>SQL> SET SESSION use_secondary_engine=OFF;
MySQL>SQL> SELECT SUM(l_extendedprice * l_discount) AS revenue FROM lineitem
           WHERE l_shipdate >= date '1994-01-01';
+------------------+
| revenue          |
+------------------+
| 82752894454.9036 |
+------------------+
1 row in set (24.20 sec)
```
对于tpch您可以运行的其他示例数据库查询，请参阅其他 tpch 查询。有关运行查询的更多信息，请参阅第 2.3 节，“运行查询”。

额外的 tpch 查询

本主题提供了额外的 tpch 查询，您可以运行这些查询来测试 HeatWave 集群。

TPCH-Q1：定价汇总报告查询

如 TPC Benchmark™ H (TPC-H) 规范中所述：“定价汇总报告查询为指定日期发货的所有订单项提供汇总定价报告。该日期在包含的最长发货日期的 60 - 120 天内在数据库中。查询列出扩展价格、折扣扩展价格、折扣扩展价格加税、平均数量、平均扩展价格和平均折扣的总计。这些聚合按 RETURNFLAG和分组，并按和 LINESTATUS的升序排列。一个计数包括每组中的行项目数。” RETURNFLAGLINESTATUS
```
SELECT
    l_returnflag,
    l_linestatus,
    SUM(l_quantity) AS sum_qty,
    SUM(l_extendedprice) AS sum_base_price,
    SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,
    SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,
    AVG(l_quantity) AS avg_qty,
    AVG(l_extendedprice) AS avg_price,
    AVG(l_discount) AS avg_disc,
    COUNT(*) AS count_order
FROM
    lineitem
WHERE
    l_shipdate <= DATE '1998-12-01' - INTERVAL '90' DAY
GROUP BY l_returnflag , l_linestatus
ORDER BY l_returnflag , l_linestatus;
```

TPCH-Q3：发货优先级查询

如 TPC Benchmark™ H (TPC-H) 规范所述：“运输优先级查询检索运输优先级和潜在收入，定义为l_extendedprice * (1-l_discount)截至给定日期。订单按收入递减顺序列出。如果存在超过 10 个未发货的订单，则只列出收入最大的 10 个订单。"

SELECT
    l_orderkey,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue,
    o_orderdate,
    o_shippriority
FROM
    customer,
    orders,
    lineitem
WHERE
    c_mktsegment = 'BUILDING'
        AND c_custkey = o_custkey
        AND l_orderkey = o_orderkey
        AND o_orderdate < DATE '1995-03-15'
        AND l_shipdate > DATE '1995-03-15'
GROUP BY l_orderkey , o_orderdate , o_shippriority
ORDER BY revenue DESC , o_orderdate
LIMIT 10;

TPCH-Q9：产品类型利润衡量查询

如 TPC Benchmark™ H (TPC-H) 规范所述：“产品类型利润度量查询为每个国家/地区和每年查找在其名称中包含指定子字符串且在该年订购的所有零件的利润由该国家/地区的供应商填写。利润定义为[(l_extendedprice*(1-l_discount)) - (ps_supplycost * l_quantity)]指定行中描述零件的所有行项目的总和。查询按字母升序列出国家，对于每个国家，年份和利润按降序排列年（最近的第一个）。”

SELECT
    nation, o_year, SUM(amount) AS sum_profit
FROM
    (SELECT
        n_name AS nation,
            YEAR(o_ORDERdate) AS o_year,
            l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
    FROM
        part
    STRAIGHT_JOIN partsupp
    STRAIGHT_JOIN lineitem
    STRAIGHT_JOIN supplier
    STRAIGHT_JOIN orders
    STRAIGHT_JOIN nation
    WHERE
        s_suppkey = l_suppkey
            AND ps_suppkey = l_suppkey
            AND ps_partkey = l_partkey
            AND p_partkey = l_partkey
            AND o_ORDERkey = l_ORDERkey
            AND s_nationkey = n_nationkey
            AND p_name LIKE '%green%') AS profit
GROUP BY nation , o_year
ORDER BY nation , o_year DESC;

卸载 tpch 表

tpch可以使用以下语句从 HeatWave 卸载表：

笔记

如果您在 AWS 上使用 MySQL HeatWave，您还可以从 MySQL HeatWave 控制台卸载数据。请参阅 MySQL HeatWave on AWS 服务指南中的管理 HeatWave 数据。

USE tpch;

ALTER TABLE customer SECONDARY_UNLOAD;
ALTER TABLE lineitem SECONDARY_UNLOAD;
ALTER TABLE nation SECONDARY_UNLOAD;
ALTER TABLE orders SECONDARY_UNLOAD;
ALTER TABLE part SECONDARY_UNLOAD;
ALTER TABLE partsupp SECONDARY_UNLOAD;
ALTER TABLE region SECONDARY_UNLOAD;
ALTER TABLE supplier SECONDARY_UNLOAD;