创建表_数据仓库服务 GaussDB(DWS)_快速入门_数据库使用入门_创建和管理表_华为云

(1)

快速入门

文档版本 10

发布日期 2021-10-29

(2)

非经本公司书面许可，任何单位和个人不得擅自摘抄、复制本文档内容的部分或全部，并不得以任何形式传播。

商标声明

和其他华为商标均为华为技术有限公司的商标。

本文档提及的其他所有商标或注册商标，由各自的所有人拥有。

注意

您购买的产品、服务或特性等应受华为公司商业合同和条款的约束，本文档中描述的全部或部分产品、服务或特性可能不在您的购买或使用范围之内。除非合同另有约定，华为公司对本文档内容不做任何明示或暗示的声明或保证。

由于产品版本升级或其他原因，本文档内容会不定期进行更新。除非另有约定，本文档仅作为使用指导，本文档中的所有陈述、信息和建议不构成任何明示或暗示的担保。

(3)

1 快速创建 GaussDB(DWS)集群并导入和测试 TPC-DS 数据

本入门提供创建GaussDB(DWS)集群并使用gsql工具导入和测试TPC-DS数据集的指导。

说明

当前仅“华北-北京一”区域的GaussDB(DWS)集群支持TPC-DS数据测试，其他区域暂不支持。

本入门的基本内容如下所示：

1. 获取AK和SK

2. 创建GaussDB(DWS)集群

3. 创建弹性云服务器

4. 下载gsql客户端

5. 使用gsql导入并测试TPC-DS数据集

获取 AK 和 SK

参见创建访问密钥(AK和SK)获取华为云帐号的“AK/SK”。

创建 GaussDB(DWS)集群

步骤1 登录华为云控制台。

步骤2 选择“大数据 > 数据仓库服务GaussDB(DWS)”，进入GaussDB(DWS)服务管理控制台。

步骤3 单击“创建数据仓库集群”，参见表1-1填写集群参数。

表1-1 集群参数

参数名取值

区域华北-北京一

(5)

参数名取值

产品类型云数仓

CPU架构 X86

节点规格 dwsx2.2xlarge 存储类型超高I/O 每节点可用存储 100GB

节点数量 3

集群名称 dws-demo 管理员用户 dbadmin 管理员密码 Dwstest@123 确认密码 Dwstest@123 数据库端口 8000

虚拟私有云 vpc-01（此为举例，选择与后续的ECS在同一个虚拟私有云即可，

如果没有，则需跳转到虚拟私有云控制台进行创建）

子网 subnet-demo(192.168.0.0/24)

安全组自动创建安全组

公网访问暂不使用

企业项目 default

高级配置默认配置

步骤4 单击“立即创建”。核对参数无误，单击“提交”。

等待约10分钟，集群创建成功。

步骤5 单击集群名称进入“基本信息”，记录集群的“内网IP”。

----结束

创建弹性云服务器

步骤1 参见快速购买弹性云服务器购买ECS，需填写如表1-2几个参数。

(6)

表1-2 弹性云服务器

参数名取值

区域华北-北京一

镜像 CentOS 7.6 64bit(40GB)

网络 vpc-01（确保与数据仓库集群在同一个虚拟私有云）

弹性公网IP 现在购买

登录凭证密码

步骤2 单击“立即购买”。等待约1分钟，弹性云服务器创建成功。

步骤3 为弹性云服务器设置安全组，允许客户端主机IP访问。

----结束

下载 gsql 客户端

步骤1 进入GaussDB(DWS)服务管理控制台，左侧单击“连接管理”。

步骤2 选择适配ECS镜像的版本（本入门选择“Redhat x86_64”），单击“下载”。

步骤3 使用SFTP工具将下载的客户端上传至弹性云服务器的/opt目录下。

----结束

使用 gsql 导入并测试 TPC-DS 数据集

步骤1 使用SSH工具以root用户登录弹性服务器，密码为创建弹性云服务器时设置的root密码。

步骤2 进入/opt目录，解压下载客户端文件，以dws_client_8.1.x_redhat_x64.zip为例。

cd /opt

unzip dws_client_8.1.x_redhat_x64.zip

步骤3 执行以下命令，切换到/opt/sample目录下，并设置用户导入样例数据的用户密钥和 OBS访问地址。

其中<Access_Key_Id>、<Secret_Access_Key>请替换为获取AK和SK获取的AK/SK值。

cd sample

/bin/bash setup.sh -ak <Access_Key_Id> -sk <Secret_Access_Key> -obs_location obs.cn-north-1.myhuaweicloud.com

系统显示以下信息表示设置成功：

setup successfully!

步骤4 返回上一级目录，执行gsql环境变量。

cd ..

(7)

步骤5 执行以下命令，进入gsql命令目录，将样例数据导入数据仓库。

cd bin

gsql -d gaussdb -h 192.168.1.10 -U dbadmin -p 8000 -f /opt/sample/

tpcds_load_data_from_obs.sql -r

● -d：连接的数据库名称，默认使用系统数据库gaussdb。

● -h：表示GaussDB(DWS)集群连接地址，从步骤5获取。

● -U：连接数据库的用户名，默认使用dbadmin连接。

● -f：表示SQL脚本所在路径，本例为：/opt/sample/

tpcds_load_data_from_obs.sql。

根据界面提示输入管理员密码，成功连接集群数据库后，系统会自动创建样例数据对应的外表用于关联集群外的数据，然后再创建存放样例数据的目标表，最后通过外表将数据导入到目标表中。

由于数据集较大，导入时间取决于当前DWS集群规格，一般为10~20分钟左右，等待系统显示如下执行时间信息表示导入成功，如下时间仅为示例。

Time:1845600.524 ms

步骤6 导入成功后，执行以下命令连接gaussdb数据库，按提示输入数据库管理员dbadmin密码。

gsql -d gaussdb -h 192.168.1.10 -U dbadmin -p 8000 -r

步骤7 切换到tpcds的schema，并查询表数据，确认TPC-DS数据已默认导入到tpcds的 schema下。

set current_schema = tpcds;

\d

步骤8 退出数据库连接，回到linux窗口。

\q

步骤9 执行以下命令，切换到指定查询目录下并查询样例数据。

cd /opt/sample/query_sql/

/bin/bash tpcds100x.sh

步骤10 根据命令提示，输入集群公网访问地址的IP地址、数据库端口、数据库名称、数据库访问用户以及用户密码。

● 数据库名称默认为“gaussdb”。

● 数据库访问用户和密码使用创建集群时配置的管理员用户和密码。

查询完成后，在当前查询目录，如“sample/query_sql/”下面会生成一个存放查询结果的目录，命名如“query_output_20170914_072341”。

----结束

(8)

2 使用 CDM 将 MySQL 数据迁移至 GaussDB(DWS)集群

本入门提供通过云数据迁移服务CDM将MySQL数据批量迁移到GaussDB(DWS)集群的指导。

本入门的基本内容如下所示：

1. 迁移前数据检查

2. 创建GaussDB(DWS)集群

3. 创建CDM集群

4. 创建连接

5. 新建作业和迁移

6. 迁移后数据一致性验证

场景描述

图2-1 迁移场景

主要包括云上和云下的MySQL数据迁移，支持整库迁移或者单表迁移，本文以云下 MySQL的整库迁移为例。

(9)

CDM通过公网IP访问MySQL数据库，CDM与GaussDB(DWS)在同一个VPC下，

CDM分别与MySQL和DWS建立JDBC连接。

● 云上RDS-MySQL数据迁移：

RDS、CDM和GaussDB(DWS)均在同一个VPC下，CDM分别与MySQL和DWS建立 JDBC连接。如果云上RDS与DWS不在一个VPC，则CDM通过弹性公网IP访问 RDS。

迁移前数据检查

步骤1 连接MySQL实例，查看MySQL数据库情况。

mysql -h <host>-P

<port>

-u <userName>-p--ssl-ca=<caDIR>

表2-1 表 2 参数说明

参数说明

<host> MySQL数据库连接地址。

<port> 数据库端口，默认3306。

<userName> MySQL管理员帐号，默认为root。

<caDIR> CA证书路径，该文件需放在执行该命令的路径下。

出现如下提示时，输入数据库帐号对应的密码：

Enter password:

步骤2 分析需要迁移的数据库名及编码、待迁移的表名、表属性。

例如，查询出待迁移的MySQL目标库为test01、test02以及数据库编码。其中test01库里包括 orders、persons、persons_b三张表和一张视图persons_beijing，test02库里包括一张表persons_c。

1. 查询数据库名。

show databases;

2. 查询数据库编码。

use <databasename>;

status;

(10)

3. 查询库表。

use <databasename>;

show full tables;

须知

– 由于GaussDB(DWS)数据库对表名大小写不敏感，如果原MySQL数据库中存在大小写混用的表名或者纯大写的表名，例如Table01、TABLE01，需要先修改表名为纯小写后才支持迁移，否则会导致迁移后，GaussDB(DWS）无法识别该表。

– 建议将MySQL也设置成大小写不敏感模式，修改方法：修改/etc/my.cnf参数 lower_case_table_names=1，并重启MySQL服务。

(11)

4. 查看各个表的属性，以备迁移后对比。

use <databasename>;

desc <table name>;

----结束

创建 GaussDB(DWS)集群

步骤1 参见快速创建GaussDB(DWS)集群并导入和测试TPC-DS数据进行创建，区域可选择

“华北-北京四”。

说明

确保GaussDB(DWS)集群与CDM集群在同一区域，同一个VPC下。

步骤2 参见快速创建GaussDB(DWS)集群并导入和测试TPC-DS数据使用gsql连接DWS集群。

步骤3 创建迁移前数据检查的目标数据库test01和test02，确保与原MySQL的数据库同名，

数据库编码一致。

create database test01 with encoding 'UTF-8' dbcompatibility 'mysql' template template0;

create database test02 with encoding 'UTF-8' dbcompatibility 'mysql' template template0;

----结束

创建 CDM 集群

步骤1 登录华为云控制台。

步骤2 选择“迁移 > 云数据迁移 CDM”进入CDM管理控制台。

步骤3 单击“购买云数据迁移集群”，按以下参数填写。

(12)

表2-2 CDM 集群参数

参数名取值

当前区域华北-北京四（与DWS选择在同一区域）

可用区可用区1（如果资源售罄则选其他可用区）

集群名称 CDM-demo

实例类型 cdm.large（如售罄请选择其他规格）

虚拟私有云 demo-vpc（与DWS选择在同一个VPC）

子网 subnet-f377(10.1.0.0/24)（示例）

安全组 -

企业项目 default

自动关机关闭

定时开机关闭

定时关机关闭

消息通知关闭

步骤4 单击“立即购买”，核对参数无误，单击“提交”。

步骤5 回到CDM管理控制台的“集群管理”页面，等待约5分钟，集群创建成功后，单击集群名称右侧的“绑定弹性IP”。

步骤6 勾选可用的弹性IP，单击“确认”。如果没有弹性IP，需要跳转到弹性IP界面，购买弹性IP。

----结束

创建连接

步骤1 初次创建MySQL连接，需要上传驱动。

1. 访问MySQL驱动，选择“5.1.48”版本下载。

(13)

3. 回到CDM管理控制台的“集群管理”页面，单击集群名称右侧的“作业管理”，

选择“连接管理 > 驱动管理”。

4. 单击“MySQL”右侧的“上传”，选择mysql-connector-java-xxx.jar，单击“上传文件”。

步骤2 创建MySQL连接。

1. 在CDM管理控制台的“集群管理”页面，单击集群名称右侧的“作业管理”，选择“连接管理 > 新建连接”。

2. 连接器类型勾选“MySQL”，单击“下一步”。（如果是云上RDS，则勾选“云数据库 MySQL”。）

3. 按表2-3填写连接信息，填写后单击“测试”，测试成功后，单击“保存”。

说明

如测试不通过，请确认CDM是否以公网IP方式连接MySQL数据库，如果是公网IP方式，请参见步骤5绑定公网IP。

表2-3 MySQL 连接信息

参数项取值

名称 MySQL

(14)

参数项取值

数据库服务器 192.168.1.100（示例，请填写云下MySQL实际的公网IP，

要确保MySQL服务器已放开白名单访问）

端口 3306

数据库名称 test01 用户名 root

密码 root用户密码

使用本地API 否使用Agent 否

(15)

步骤3 创建DWS连接。

1. 在CDM管理控制台的“集群管理”页面，单击集群名称右侧的“作业管理”，选择“连接管理 > 新建连接”。

2. 连接器类型勾选“数据仓库服务(DWS)”，单击“下一步”。

(16)

3. 按表2-4填写连接信息，填写后单击“测试”，测试成功后，单击“保存”。

表2-4 DWS 连接信息

参数项取值

名称 DWS-test01

数据库服务器单击“选择”，从集群列表中选择要连接的DWS集群。

说明系统会自动刷出同一个区域、同一个VPC下的DWS集群，如果没有，则需要手动填写网络已连通的DWS的访问IP。

端口 8000

数据库名称 test01（参见步骤3确保GaussDB(DWS)已手动创建了对应的数据库）

用户名 dbadmin

密码 dbadmin用户密码使用Agent 否

(17)

4. 重复3.1~3.3，创建DWS-test02连接。

----结束

新建作业和迁移

步骤1 在CDM管理控制台的“集群管理”页面，单击集群名称右侧的“作业管理”，选择

“整库迁移 > 新建作业”。

步骤2 填写如下参数后，单击“下一步”。

● 作业名称：MySQL-DWS-test01

● 源端作业配置：

– 源连接名称：MySQL

(18)

– 模式或表空间：test01

● 目的端作业配置：

– 目的连接名称：DWS-test01 – 模式或表空间：public – 自动创表：不存在时创建 – 是否压缩：是

– 存储模式：列模式 – 其他选项：保持默认

步骤3 勾选所有表，单击，单击“下一步”。

(19)

步骤4 参数保持默认即可，单击“保存并运行”。

步骤5 查看作业运行情况，状态为“Succeeded”，表示迁移成功。

步骤6 重复执行步骤1~步骤5，，迁移数据库test02的所有表

须知

在新建作业时，目标源的DWS库，需选择对应到test02。

----结束

(20)

迁移后数据一致性验证

步骤1 参见快速创建GaussDB(DWS)集群并导入和测试TPC-DS数据使用gsql连接DWS的 test02集群。

gsql -d test01 -h 数据库主机IP -p 8000 -U dbadmin -W 数据库用户密码 -r;

步骤2 查询test01库的表。

select * from pg_tables where schemaname= 'public';

步骤3 查询每个表的数据是否齐全，字段是否完整。

select count(*) from table name;

\d+ table name;

步骤4 抽样检查，验证表数据是否正确。

select * from persons where city = ‘Beijing' order by id_p;

步骤5 重复执行步骤2~步骤4查看其它库和表数据是否正确。

(21)

3 ^{创建集群并连接集群}

3.1 第 1 步：入门前准备

本指南是一个入门教程，向您演示如何创建示例GaussDB(DWS) 集群，连接示例 GaussDB(DWS) 集群数据库、导入存储在OBS中的示例数据和分析示例数据的流程。

您可以使用该入门教程评估GaussDB(DWS) 服务。

在开始创建GaussDB(DWS) 集群之前，请确保您已完成如下前提条件：

● 注册并实名认证公有云帐户

● 确定集群端口

注册并实名认证公有云帐户

如果您还没有公有云帐户，则必须先注册一个。如果您已有实名认证的帐户，则可以跳过此步骤，并使用您已有的帐户。

1. 打开公有云服务网址http://www.huaweicloud.com/，单击页面右上方的“注册”，进入注册页面。

2. 按照页面要求填写用户信息完成注册。

3. 单击右上角用户名，进入基本信息页面，单击“实名认证”，进入实名认证页面。

4. 按照页面提示完成实名认证。

说明

开通云服务需要先进行实名认证。

确定集群端口

● 在创建GaussDB(DWS) 集群时需要指定一个端口供SQL客户端或应用程序通过该端口访问集群。

● 如果您的客户端机器位于防火墙之后，则您需要有一个可用的开放端口，这样才能从SQL客户端工具连接到集群并进行查询分析。

● 如果您不了解可用的开放端口，则请联系网络管理员，在您的防火墙中确定一个开放端口。GaussDB(DWS) 支持的端口范围为8000～30000。

(22)

● 在集群创建之后无法更改集群的端口号，请务必确保在集群创建过程中指定的端口为可用的开放端口。

3.2 第 2 步：创建集群

在使用GaussDB(DWS) 执行数据分析任务前，您首先要创建一个集群，一个

GaussDB(DWS) 集群由多个在相同子网中的节点组成，共同提供服务。请参考以下指导创建集群。

创建集群

步骤1 登录GaussDB(DWS) 管理控制台。

步骤2 单击左侧导航栏的“集群管理”。

步骤3 在“集群管理”页面，单击右上角“创建数据仓库集群”。

步骤4 选择待创建的集群所属的区域。

● 区域：选择“华北-北京四”。

● 可用区：默认即可。

步骤5 选择主机规格。

● 产品类型：根据客户需求选择，例如“标准数仓”类型。

● CPU架构：根据客户需求选择，例如“X86”架构。

● 节点规格：默认即可。

● 节点数量：默认即可，至少3个。

图3-1 配置主机规格

步骤6 填写集群配置参数。

● 集群名称：输入“dws-demo”。

● 集群版本：显示为当前集群版本，暂不支持修改。

● 默认数据库：显示为“gaussdb”。暂不支持修改。

● 管理员用户：默认为“dbadmin”，使用默认值即可。集群创建成功后，客户端

(23)

● 管理员密码：输入密码。

● 确认密码：重复输入一次管理员密码。

● 数据库端口：默认即可。客户端或应用程序将通过该端口连接集群中的数据库。

图3-2 集群配置

步骤7 配置网络参数。

● 虚拟私有云：可以在下拉框中选择已有的虚拟私有云，如果未配置过虚拟私有云，可以单击“查看虚拟私有云”进入虚拟私有云管理控制台，新创建一个虚拟私有云例如“vpc-dws”。然后回到GaussDB(DWS) 管理控制台的创建集群页面，单击“虚拟私有云”下拉框旁边的进行刷新，再选择新创建虚拟私有云。

● 子网：创建虚拟私有云时会默认创建一个子网，您可以选择对应的子网名。

● 安全组：选择“自动创建安全组”。

自动创建的安全组，将被命名为“GaussDB(DWS)-<集群名称>-<GaussDB(DWS)

集群的数据库端口>”，出方向允许所有访问，入方向只开放“数据库端口”以允

许来自客户端或应用程序的访问。

如果您选择的是自定义创建的安全组，则需要在该安全组中添加一条入方向的规则，向访问GaussDB(DWS) 的客户端主机开放GaussDB(DWS) 集群的“数据库端口”，如表3-1所示。添加入规则的具体操作请参见《虚拟私有云用户指南》中的添加安全组规则章节。

表3-1 安全组入规则配置样例

参数名样例值

协议/应用 TCP

(24)

参数名样例值

端口 8000

说明输入创建GaussDB(DWS) 集群时设置的“数据库端口”，这个端口是GaussDB(DWS) 用于接收客户端连接的端口，默认为8000。

源地址选择“IP地址”，输入访问GaussDB(DWS) 的客户端主机的IP地址和子网掩码，例如

“192.168.0.10/16”。

● 公网访问：选择“现在购买”为集群购买一个弹性IP作为集群公网IP。并且，在

“带宽”参数中设置弹性IP的带宽。

图3-3 网络参数

步骤8 配置集群所属的“企业项目”。已开通企业项目管理服务的用户才可以配置该参数。

默认值为default。

企业项目是一种云资源管理方式，企业项目管理服务提供统一的云资源按项目管理，

以及项目内的资源管理、成员管理。

您可以选择默认的企业项目“default”或其他已有的企业项目。如果要创建新的企业项目，请登录企业管理控制台进行创建，详细操作请参考《企业管理用户指南》。

步骤9 高级配置，在本示例中，选择“默认配置”即可。

● 默认配置：表示以下几项高级配置使用系统默认的配置。

– 自动快照：默认开启自动生成集群快照的策略。

– CN部署量：CN即协调节点，默认部署2个CN节点。

– 参数模板：默认将系统默认的数据库参数模板与集群相关联。

– 标签：默认未给集群添加标签。

– 加密数据库：默认为关闭，表示不对数据库进行加密。

● 自定义：选择该选项时页面上将显示自动快照、CN部署量、参数模板、标签、加密数据库这几项高级配置，需要用户进行自定义设置。

步骤10 单击“立即创建”，进入“规格详情”页面。

步骤11 单击“提交”。

(25)

提交成功后开始创建。单击“返回集群列表”返回集群管理页面，所创集群的初始状态为“创建中”，集群创建需要时间，请等待一段时间。创建成功后状态更新为“可用”，用户可以开始使用集群。

----结束

3.3 第 3 步：连接集群

操作场景

您在创建好数据仓库集群，开始使用数据库服务前，需要使用数据库客户端连接到 GaussDB(DWS) 集群中的数据库。本示例将使用Data Studio客户端工具通过公网地址连接GaussDB(DWS) 集群中的数据库。您也可以使用其他SQL客户端连接集群，更多连接方式请参见《数据仓库服务管理指南》中的“连接集群”章节。

1. 获取所要连接的数据库名称、用户名和密码。

首次使用客户端连接集群时，您需使用第2步：创建集群时设置的管理员用户和密码连接到默认数据库“gaussdb”。

2. 获取集群公网访问地址：通过集群公网访问地址连接数据库。

3. 使用Data Studio连接到集群数据库：下载配置Data Studio客户端并连接集群数

据库。

获取集群公网访问地址

步骤2 在左侧导航栏中，单击“集群管理”。

步骤3 在集群列表中，选中已创建集群（如dws-demo），单击“集群名称”前面的向下展开按钮，获取并保存公网访问地址。

该公网访问地址将在使用Data Studio连接到集群数据库时使用。

图3-4 集群管理

----结束

(26)

使用 Data Studio 连接到集群数据库

步骤1 GaussDB(DWS) 提供了基于Windows平台的Data Studio图形界面客户端，该工具依赖JDK，请先在客户端主机上安装Java 1.8.0_141或以上版本的JDK。

在Windows操作系统中，您可以访问JDK官网，下载符合操作系统版本的JDK，并根据指导进行安装。

步骤3 单击“连接管理”。

步骤4 在“下载客户端和驱动”页面，下载“Data Studio图形界面客户端”。

● 请根据操作系统类型，选择“Windows x86”或“Windows x64”，再单击“下载”，可以下载与现有集群版本匹配的Data Studio工具。

如果同时拥有不同版本的集群，单击“下载”时会下载与集群最低版本相对应的 Data Studio工具。如果当前没有集群，单击“下载”时将下载到低版本的Data Studio工具。GaussDB(DWS) 集群可向下兼容低版本的Data Studio工具。

● 单击“历史版本”可根据集群版本下载相应版本的Data Studio工具，建议按集群版本下载配套的工具。

图3-5 下载客户端

如果同时拥有不同版本的集群，系统会弹出对话框，提示您选择“集群版本”然后下载与集群版本相对应的客户端。在“集群管理”页面的集群列表中，单击指定集群的名称，再选择“基本信息”页签，可查看集群版本。

步骤5 解压下载的客户端软件包（32位或64位）到需要安装的路径。

步骤6 打开安装目录，双击Data Studio.exe，启动Data Studio客户端，如图3-6所示。

(27)

图3-6 启动客户端

步骤7 在主菜单中选择 “文件>新建连接” ，如图3-7所示。

图3-7 新建连接

步骤8 在弹出的“新建/选择数据库连接”页面中，如下图所示，输入连接参数。

图3-8 配置连接参数

(28)

表3-2 配置连接参数

字段名称说明举例

数据库类型选择“HUAWEI CLOUD DWS ”。 HUAWEI CLOUD DWS

名称连接名称。 dws-demo

主机名所要连接的集群IP地址（IPv4）或域名。 -

端口号数据库端口。 8000

数据库数据库名称。 gaussdb

用户名所要连接数据库的用户名。 -

密码所要连接数据库的登录密码。 -

保存密码在下拉列表中选择：

● “仅当前会话”：仅在当前会话中保存密码。

● “不保存”：不保存密码。

-

启用SSL 启用时，客户端将使用SSL加密连接方式。SSL连接方式安全性高于普通模式，建议开启。 -

当“启用SSL”设置为开启时，请先参见下载SSL证书下载SSL证书，并解压证书文件。然后，在如图3-8所示的窗口中单击“SSL”页签，设置如下参数：

表3-3 配置 SSL 参数

字段名称说明

客户端SSL证

书选择SSL证书解压目录下的“sslcert\client.crt”文件。

客户端SSL密

钥客户端SSL秘钥只支持PK8格式，请选择SSL证书解压目录下的

“sslcert\client.key.pk8”文件。

根证书当“SSL模式”设为“verify-ca”时，必须设置根证书，请选择SSL 证书解压目录下的“sslcert\cacert.pem”文件。

SSL密码客户端pk8格式SSL秘钥密码。

SSL模式 GaussDB(DWS) 支持的SSL模式有：

● require

● verify-ca

GaussDB(DWS) 不支持“verify-full”模式。

(29)

图3-9 配置 SSL 参数

步骤9 单击“确定”建立数据库连接。

如果启用了SSL，在弹出的“连接安全告警”提示对话框中单击“继续”。

登录成功后，将弹出“最近登录活动”提示框，表示Data Studio已经连接到数据库。

用户即可在Data Studio界面的“SQL终端”窗口中执行SQL语句。

图3-10 登录成功

欲详细了解Data Studio其他功能的使用方法，请按“F1”查看Data Studio用户手册。

----结束

3.4 第 4 步：导入样例数据并查询

本文档为您提供了3个相互独立的入门样例，您可以根据需要选择其中一个或几个样例进行体验。

3.4.1 交通卡口通行车辆分析

本实践将演示交通卡口车辆通行分析，将加载8.9亿条交通卡口车辆通行模拟数据到数据仓库单个数据库表中，并进行车辆精确查询和车辆模糊查询，展示GaussDB(DWS) 对于历史详单数据的高性能查询能力。

(30)

说明

● GaussDB(DWS) 已预先将样例数据上传到OBS桶的“traffic-data”文件夹中，并给所有华为云用户赋予了该OBS桶的只读访问权限。

● GaussDB(DWS) 集群暂不支持跨区域访问OBS桶数据，当前已上传OBS桶数据的区域如支持区域所示，非以下区域的集群暂不支持该实验。

本实践预计时长40分钟，基本流程如下：

1. 准备工作

2. 步骤一：创建集群

3. 步骤二：使用Data Studio连接集群

4. 步骤三：导入交通卡口样例数据

5. 步骤四：车辆分析

支持区域

当前已上传OBS数据的区域如表3-4所示。

表3-4 区域和 OBS 桶名

区域 OBS桶名

华北-北京一 dws-demo

华北-北京二 dws-demo-cn-north-2 华北-北京四 dws-demo-cn-north-4 华北-乌兰察布一 dws-demo-cn-north-9 华东-上海一 dws-demo-cn-east-3 华东-上海二 dws-demo-cn-east-2 华南-广州 dws-demo-cn-south-1 中国-香港 dws-demo-ap-southeast-1 亚太-新加坡 dws-demo-ap-southeast-3 亚太-曼谷 dws-demo-ap-southeast-2 拉美-圣地亚哥 dws-demo-la-south-2

准备工作

● 已注册华为云帐号，且在使用GaussDB(DWS) 前检查帐号状态，帐号不能处于欠费或冻结状态。

● 参见创建访问密钥(AK和SK)获取此帐号的“AK/SK”。

(31)

步骤一：创建集群

步骤1 登录华为云管理控制台。

步骤2 在“服务列表”中，选择“大数据 > 数据仓库服务”。

步骤3 左侧导航栏单击“集群管理”，进入页面后，单击右上角的“创建数据仓库集群”按钮。

步骤4 参见表3-5进行参数配置。

表3-5 软件配置

参数名称配置方式

区域选择“华北-北京四”。

说明本指导以“华北-北京四”为例进行介绍，如果您需要选择其他区域进行操作，

请确保所有操作均在同一区域进行。

可用区可用区2 产品类型标准数仓 CPU架构 X86

节点规格 dws2.m6.4xlarge.8（16 vCPU | 128GB | 2000GB SSD）

说明

如规格售罄，可选择其他可用区或规格。

节点数量 3

集群名称 dws-demo 管理员用户 dbadmin 管理员密码 -

确认密码 - 数据库端口 8000 虚拟私有云 vpc-default

子网 subnet-default(192.168.0.0/24) 安全组自动创建安全组

公网访问现在购买宽带 1Mbit/s 高级配置默认配置

步骤5 信息核对无误，单击“立即购买”，单击“提交”。

步骤6 等待约6分钟，待集群创建成功后，单击集群名称前面的，弹出集群信息，记录下

“公网访问地址”，例如dws-demov.dws.huaweicloud.com。

(32)

----结束

步骤二：使用 Data Studio 连接集群

步骤1 请确保客户端主机已安装JDK 1.8.0以上版本，并进入“此电脑 > 属性 > 高级系统设置

> 环境变量”设置JAVA_HOME（例如C:\Program Files\Java\jdk1.8.0_191），并在变量path中添加“;%JAVA_HOME%\bin”。

步骤2 在GaussDB(DWS) 控制台的“连接管理”页面，下载Data Studio客户端。

步骤3 解压下载的Data Studio软件包，进入解压目录后，双击Data Studio.exe启动客户端。

步骤4 在Data Studio主菜单中选择“文件 > 新建连接”，并在弹出框中参照表3-6所示配置。

表3-6 Data Studio 软件配置 参数名称配置方式数据库类型 GaussDB(DWS) 名称 dws-demo

主机 dws-demov.dws.huaweicloud.com

与步骤一：创建集群查询到的“公网访问地址”一致。

端口 8000

数据库 gaussdb 用户名 dbadmin

密码 -

启用SSL 不启用

步骤5 单击“确定”。

(33)

步骤三：导入交通卡口样例数据

使用SQL客户端工具连接到集群后，就可以在SQL客户端工具中，执行以下步骤导入交通卡口车辆通行的样例数据并执行查询。

步骤1 执行以下语句，创建traffic数据库。

create database traffic encoding 'utf8' template template0;

步骤2 执行以下步骤切换为连接新建的数据库。

1. 在Data Studio客户端的“对象浏览器”窗口，右键单击数据库连接名称，在弹出菜单中单击“刷新”，刷新后就可以看到新建的数据库。

2. 右键单击“traffic”数据库名称，在弹出菜单中单击“打开连接”。

3. 右键单击“traffic”数据库名称，在弹出菜单中单击“打开新的终端”，即可打开连接到指定数据库的SQL命令窗口，后面的步骤，请全部在该命令窗口中执行。

步骤3 执行以下语句，创建用于存储卡口车辆信息的数据库表。

create schema traffic_data;

set current_schema= traffic_data;

drop table if exists GCJL;

CREATE TABLE GCJL ( kkbh VARCHAR(20), hphm VARCHAR(20), gcsj DATE ,

cplx VARCHAR(8), cllx VARCHAR(8), csys VARCHAR(8)

)with (orientation = column, COMPRESSION=MIDDLE) distribute by hash(hphm);

步骤4 创建外表。外表用于识别和关联OBS上的源数据。

须知

● 其中，<obs_bucket_name>代表OBS桶名，仅支持部分区域，当前支持的区域和对应的OBS桶名请参见支持区域。GaussDB(DWS) 集群不支持跨区域访问OBS桶数据。

● 本实践以“华北-北京四”地区为例，可填入dws-demo-cn-north-4，

<Access_Key_Id>和<Secret_Access_Key>替换为实际值，在准备工作获取。

create schema tpchobs;

set current_schema = 'tpchobs';

drop FOREIGN table if exists GCJL_OBS;

CREATE FOREIGN TABLE GCJL_OBS ( like traffic_data.GCJL )SERVER gsmpp_server OPTIONS (

encoding 'utf8',

location 'obs://<obs_bucket_name>/traffic-data/gcxx', format 'text',

delimiter ',',

access_key '<Access_Key_Id>',

secret_access_key '<Secret_Access_Key>', chunksize '64',

IGNORE_EXTRA_DATA 'on' );

(34)

步骤5 执行以下语句，将数据从外表导入到数据库表中。

insert into traffic_data.GCJL select * from tpchobs.GCJL_OBS;

导入数据需要一些时间，请耐心等待。

----结束

步骤四：车辆分析

1. 执行Analyze

用于收集与数据库中普通表内容相关的统计信息，统计结果存储在系统表

PG_STATISTIC中。执行计划生成器会使用这些统计数据，以生成最有效的查询执行计划。

执行以下语句生成表统计信息：

Analyze;

2. 查询数据表中的数据量

执行如下语句，可以查看已加载的数据条数。

Select count(*) from traffic_data.gcjl;

3. 车辆精确查询

执行以下语句，指定车牌号码和时间段查询车辆行驶路线。GaussDB(DWS) 在应对点查时秒级响应。

select hphm, kkbh, gcsj from traffic_data.gcjl where hphm = 'YD38641'

and gcsj between '2016-01-06' and '2016-01-07' order by gcsj desc;

4. 车辆模糊查询

执行以下语句，指定车牌号码和时间段查询车辆行驶路线，GaussDB(DWS) 在应对模糊查询时秒级响应。

select hphm, kkbh, gcsj from traffic_data.gcjl where hphm like 'YA23F%' and kkbh in('508', '1125', '2120')

and gcsj between '2016-01-01' and '2016-01-07' order by hphm,gcsj desc;

3.4.2 某公司供应链需求分析

本实践将演示从OBS加载样例数据集到GaussDB(DWS) 集群中并查询数据的流程，从而向您展示GaussDB(DWS) 在数据分析场景中的多表分析与主题分析。

说明

● GaussDB(DWS) 已经预先生成了1GB的TPC-H-1x的标准数据集，已将数据集上传到了OBS 桶的tpch文件夹中，并且已赋予所有华为云用户该OBS桶的只读访问权限，用户可以方便的进行导入。

支持区域

(35)

1. 准备工作

2. 步骤一：导入公司样例数据

3. 步骤二：多表分析与主题分析

场景描述

目的：了解GaussDB(DWS)的基本功能和数据导入，对某公司与供应商的订单数据分析，分析维度如下：

1. 分析某地区供应商为公司带来的收入，通过该统计信息可用于决策在给定的区域是否需要建立一个当地分配中心。

2. 分析零件/供货商关系，可以获得能够以指定的贡献条件供应零件的供货商数量，

通过该统计信息可用于决策在订单量大，任务紧急时，是否有充足的供货商。

3. 分析小订单收入损失，通过查询得知如果没有小量订单，平均年收入将损失多少。筛选出比平均供货量的20％还低的小批量订单，如果这些订单不再对外供货，由此计算平均一年的损失。

准备工作

● 已注册华为云帐号，且在使用GaussDB(DWS) 前检查帐号状态，帐号不能处于欠费或冻结状态。

● 已创建集群，并已使用Data Studio连接集群，参见交通卡口通行车辆分析。

(36)

步骤一：导入公司样例数据

使用SQL客户端工具连接到集群后，就可以在SQL客户端工具中，执行以下步骤导入 TPC-H样例数据并执行查询。

步骤1 创建数据库表。

TPC-H样例包含8张数据库表，其关联关系如图3-11所示。

图3-11 TPC-H 数据表

复制并执行下列表创建语句，在gaussdb数据库中创建对应的数据表。

CREATE schema tpch;

set current_schema = tpch;

drop table if exists region;

CREATE TABLE REGION

( R_REGIONKEY INT NOT NULL , R_NAME CHAR(25) NOT NULL , R_COMMENT VARCHAR(152)

)with (orientation = column, COMPRESSION=MIDDLE) distribute by replication;

(37)

( N_NATIONKEY INT NOT NULL, N_NAME CHAR(25) NOT NULL, N_REGIONKEY INT NOT NULL, N_COMMENT VARCHAR(152)

)with (orientation = column,COMPRESSION=MIDDLE) distribute by replication;

drop table if exists supplier;

CREATE TABLE SUPPLIER

( S_SUPPKEY BIGINT NOT NULL, S_NAME CHAR(25) NOT NULL, S_ADDRESS VARCHAR(40) NOT NULL, S_NATIONKEY INT NOT NULL,

S_PHONE CHAR(15) NOT NULL, S_ACCTBAL DECIMAL(15,2) NOT NULL, S_COMMENT VARCHAR(101) NOT NULL )with (orientation = column,COMPRESSION=MIDDLE) distribute by hash(S_SUPPKEY);

drop table if exists customer;

CREATE TABLE CUSTOMER

( C_CUSTKEY BIGINT NOT NULL, C_NAME VARCHAR(25) NOT NULL, C_ADDRESS VARCHAR(40) NOT NULL, C_NATIONKEY INT 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 )with (orientation = column,COMPRESSION=MIDDLE) distribute by hash(C_CUSTKEY);

drop table if exists part;

CREATE TABLE PART

( P_PARTKEY BIGINT NOT NULL, 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 BIGINT NOT NULL, P_CONTAINER CHAR(10) NOT NULL, P_RETAILPRICE DECIMAL(15,2) NOT NULL, P_COMMENT VARCHAR(23) NOT NULL )with (orientation = column,COMPRESSION=MIDDLE) distribute by hash(P_PARTKEY);

drop table if exists partsupp;

CREATE TABLE PARTSUPP

( PS_PARTKEY BIGINT NOT NULL, PS_SUPPKEY BIGINT NOT NULL, PS_AVAILQTY BIGINT NOT NULL,

PS_SUPPLYCOST DECIMAL(15,2) NOT NULL, PS_COMMENT VARCHAR(199) NOT NULL )with (orientation = column,COMPRESSION=MIDDLE) distribute by hash(PS_PARTKEY);

drop table if exists orders;

CREATE TABLE ORDERS (

(38)

O_ORDERKEY BIGINT NOT NULL, O_CUSTKEY BIGINT 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 BIGINT NOT NULL, O_COMMENT VARCHAR(79) NOT NULL )with (orientation = column,COMPRESSION=MIDDLE) distribute by hash(O_ORDERKEY);

drop table if exists lineitem;

CREATE TABLE LINEITEM

( L_ORDERKEY BIGINT NOT NULL, L_PARTKEY BIGINT NOT NULL, L_SUPPKEY BIGINT NOT NULL, L_LINENUMBER BIGINT 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 )with (orientation = column,COMPRESSION=MIDDLE) distribute by hash(L_ORDERKEY);

须知

CREATE schema tpchobs;

set current_schema='tpchobs';

drop FOREIGN table if exists region;

CREATE FOREIGN TABLE REGION ( like tpch.region

) SERVER gsmpp_server OPTIONS (

encoding 'utf8',

location 'obs://<obs_bucket_name>/tpch/region.tbl', format 'text',

delimiter '|',

IGNORE_EXTRA_DATA 'on'

(39)

drop FOREIGN table if exists nation;

CREATE FOREIGN TABLE NATION ( like tpch.nation

)SERVER gsmpp_server OPTIONS (

encoding 'utf8',

location 'obs://<obs_bucket_name>/tpch/nation.tbl', format 'text',

delimiter '|',

drop FOREIGN table if exists supplier;

CREATE FOREIGN TABLE SUPPLIER ( like tpch.supplier

encoding 'utf8',

location 'obs://<obs_bucket_name>/tpch/supplier.tbl', format 'text',

delimiter '|',

drop FOREIGN table if exists customer;

CREATE FOREIGN TABLE CUSTOMER ( like tpch.customer

encoding 'utf8',

location 'obs://<obs_bucket_name>/tpch/customer.tbl', format 'text',

delimiter '|',

IGNORE_EXTRA_DATA 'on' );drop FOREIGN table if exists part;

CREATE FOREIGN TABLE PART ( like tpch.part

encoding 'utf8',

location 'obs://<obs_bucket_name>/tpch/part.tbl', format 'text',

delimiter '|',

IGNORE_EXTRA_DATA 'on' );drop FOREIGN table if exists partsupp;

CREATE FOREIGN TABLE PARTSUPP

(40)

( like tpch.partsupp )SERVER gsmpp_server OPTIONS (

encoding 'utf8',

location 'obs://<obs_bucket_name>/tpch/partsupp.tbl', format 'text',

delimiter '|',

IGNORE_EXTRA_DATA 'on' );drop FOREIGN table if exists orders;

CREATE FOREIGN TABLE ORDERS ( like tpch.orders

encoding 'utf8',

location 'obs://<obs_bucket_name>/tpch/orders.tbl', format 'text',

delimiter '|',

IGNORE_EXTRA_DATA 'on' );drop FOREIGN table if exists lineitem;

CREATE FOREIGN TABLE LINEITEM ( like tpch.lineitem

encoding 'utf8',

location 'obs://<obs_bucket_name>/tpch/lineitem.tbl', format 'text',

delimiter '|',

步骤3 复制并执行以下语句，将外表数据导入到对应的数据库表中。

将OBS外表的数据通过insert命令导入GaussDB(DWS) 的数据库表中，数据库内核对应的操作为OBS数据高速并发导入GaussDB(DWS) 。

insert into tpch.lineitem select * from tpchobs.lineitem;

insert into tpch.part select * from tpchobs.part;

insert into tpch.partsupp select * from tpchobs.partsupp;

insert into tpch.customer select * from tpchobs.customer;

insert into tpch.supplier select * from tpchobs.supplier;

insert into tpch.nation select * from tpchobs.nation;

insert into tpch.region select * from tpchobs.region;

insert into tpch.orders select * from tpchobs.orders;

导入数据需要约10分钟，请耐心等待。

----结束

步骤二：多表分析与主题分析

(41)

在进行数据查询之前，请先执行“Analyze”命令生成与数据库表相关的统计信息。统计信息存储在系统表PG_STATISTIC中，执行计划生成器会使用这些统计数据，以生成最有效的查询执行计划。

查询示例如下：

● 某地区供货商为公司带来的收入查询（TPCH-Q5）

通过执行TPCH-Q5查询语句，可以查询到通过某个地区零件供货商获得的收入

（收入按sum( l_extendedprice * (1 - l_discount))计算）统计信息。该统计信息可用于决策在给定的区域是否需要建立一个当地分配中心。

复制并执行以下TPCH-Q5语句进行查询。该语句的特点是：带有分组、排序、聚集操作并存的多表连接查询操作。

set current_schema='tpch';

Select n_name,

sum(l_extendedprice * (1 - l_discount)) as revenue fromcustomer,

orders, lineitem, supplier, nation, region where

c_custkey = o_custkey and l_orderkey = o_orderkey and l_suppkey = s_suppkey and c_nationkey = s_nationkey and s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name = 'ASIA'

and o_orderdate >= '1994-01-01'::date

and o_orderdate < '1994-01-01'::date + interval '1 year' group by

n_name order by revenue desc;

● 零件/供货商关系查询（TPCH-Q16）

通过执行TPCH-Q16查询语句，可以获得能够以指定的贡献条件供应零件的供货商数量。该信息可用于决策在订单量大，任务紧急时，是否有充足的供货商。

复制并执行以下TPCH-Q16语句进行查询，该语句的特点是：带有分组、排序、

聚集、去重、NOT IN子查询操作并存的多表连接操作。

select p_brand, p_type, p_size,

count(distinct ps_suppkey) as supplier_cnt frompartsupp,

partwhere

p_partkey = ps_partkey and p_brand <> 'Brand#45'

and p_type not like 'MEDIUM POLISHED%' and p_size in (49, 14, 23, 45, 19, 3, 36, 9) and ps_suppkey not in (

select s_suppkey from supplier where

s_comment like '%Customer%Complaints%'

(42)

)group by p_brand, p_type, p_size order by

supplier_cnt desc, p_brand, p_type, p_size limit 100;

● 小订单收入损失查询（TPCH-Q17）

通过查询得知如果没有小量订单，平均年收入将损失多少。筛选出比平均供货量的20％还低的小批量订单，如果这些订单不再对外供货，由此计算平均一年的损失。

复制并执行以下TPCH-Q17语句进行查询，该语句的特点是：带有聚集、聚集子查询操作并存的两表连接操作。

select

sum(l_extendedprice) / 7.0 as avg_yearly fromlineitem,

partwhere

p_partkey = l_partkey and p_brand = 'Brand#23' and p_container = 'MED BOX' and l_quantity < (

select 0.2 * avg(l_quantity) from lineitem

where l_partkey = p_partkey );

3.4.3 零售业百货公司经营状况分析

零售业百货公司样例简介

本实践将演示以下场景：从OBS加载各个零售商场每日经营的业务数据到数据仓库对应的表中，然后对商铺营业额、客流信息、月度销售排行、月度客流转化率、月度租售比、销售坪效等KPI信息进行汇总和查询。本示例旨在展示在零售业场景中

GaussDB(DWS) 数据仓库的多维度查询分析的能力。

说明

● GaussDB(DWS) 已预先将样例数据上传到OBS桶的“retail-data”文件夹中，并给所有华为云用户赋予了该OBS桶的只读访问权限。

1. 准备工作

2. 步骤一：导入零售业百货公司样例数据

3. 步骤二：经营状况分析

支持区域

(43)

准备工作

● 已注册华为云帐号，帐号不能处于欠费或冻结状态。

● 已创建集群，并已使用Data Studio连接集群，参见步骤一：创建集群和步骤二：

使用Data Studio连接集群。

步骤一：导入零售业百货公司样例数据

使用SQL客户端工具连接到集群后，就可以在SQL客户端工具中，执行以下步骤导入零售业百货公司样例数据并执行查询。

步骤1 执行以下语句，创建retail数据库。

create database retail encoding 'utf8' template template0;

步骤2 执行以下步骤切换为连接新建的数据库。

1. 在Data Studio客户端的“对象浏览器”窗口，右键单击数据库连接名称，在弹出菜单中单击“刷新”，刷新后就可以看到新建的数据库。

2. 右键单击“retail”数据库名称，在弹出菜单中单击“打开连接”。

3. 右键单击“retail”数据库名称，在弹出菜单中单击“打开新的终端”，即可打开连接到指定数据库的SQL命令窗口，后面的步骤，请全部在该命令窗口中执行。

步骤3 创建数据库表。

样例数据包含10张数据库表，其关联关系如图3-12所示。

(44)

图3-12 百货公司样例数据表

复制并执行以下语句，创建零售业百货公司信息数据库表。

create schema retail_data;

set current_schema='retail_data';

DROP TABLE IF EXISTS STORE;

CREATE TABLE STORE ( ID INT,

STORECODE VARCHAR(10), STORENAME VARCHAR(100), FIRMID INT,

FLOOR INT, BRANDID INT,

RENTAMOUNT NUMERIC(18,2), RENTAREA NUMERIC(18,2)

) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;

DROP TABLE IF EXISTS POS;

CREATE TABLE POS(

ID INT,

POSCODE VARCHAR(20), STATUS INT,

MODIFICATIONDATE DATE

)WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;

DROP TABLE IF EXISTS BRAND;

CREATE TABLE BRAND ( ID INT,

BRANDCODE VARCHAR(10), BRANDNAME VARCHAR(100), SECTORID INT

DROP TABLE IF EXISTS SECTOR;

CREATE TABLE SECTOR(

ID INT,

SECTORCODE VARCHAR(10), SECTORNAME VARCHAR(20), CATEGORYID INT

)

(45)

DROP TABLE IF EXISTS CATEGORY;

CREATE TABLE CATEGORY(

ID INT,

CODE VARCHAR(10), NAME VARCHAR(20)

DROP TABLE IF EXISTS FIRM;

CREATE TABLE FIRM(

ID INT,

CODE VARCHAR(4), NAME VARCHAR(40), CITYID INT,

CITYNAME VARCHAR(10), CITYCODE VARCHAR(20)

DROP TABLE IF EXISTS DATE;

CREATE TABLE DATE(

ID INT, DATEKEY DATE, YEAR INT, MONTH INT, DAY INT, WEEK INT, WEEKDAY INT

DROP TABLE IF EXISTS PAYTYPE;

CREATE TABLE PAYTYPE(

ID INT,

CODE VARCHAR(10), TYPE VARCHAR(10), SIGNDATE DATE

DROP TABLE IF EXISTS SALES;

CREATE TABLE SALES(

ID INT, POSID INT, STOREID INT, DATEKEY INT, PAYTYPE INT,

TOTALAMOUNT NUMERIC(18,2), DISCOUNTAMOUNT NUMERIC(18,2), ITEMCOUNT INT,

PAIDAMOUNT NUMERIC(18,2)

) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);

DROP TABLE IF EXISTS FLOW;

CREATE TABLE FLOW ( ID INT,

STOREID INT, DATEKEY INT, INFLOWVALUE INT

) WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY HASH(ID);

(46)

须知

create schema retail_obs_data;

set current_schema='retail_obs_data';

drop FOREIGN table if exists SALES_OBS;

CREATE FOREIGN TABLE SALES_OBS ( like retail_data.SALES

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/sales', format 'csv',

delimiter ',',

IGNORE_EXTRA_DATA 'on', header 'on'

);

drop FOREIGN table if exists FLOW_OBS;

CREATE FOREIGN TABLE FLOW_OBS ( like retail_data.flow

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/flow', format 'csv',

delimiter ',',

);

drop FOREIGN table if exists BRAND_OBS;

CREATE FOREIGN TABLE BRAND_OBS ( like retail_data.brand

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/brand', format 'csv',

delimiter ',',

);

(47)

CREATE FOREIGN TABLE CATEGORY_OBS ( like retail_data.category

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/category', format 'csv',

delimiter ',',

);

drop FOREIGN table if exists DATE_OBS;

CREATE FOREIGN TABLE DATE_OBS ( like retail_data.date

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/date', format 'csv',

delimiter ',',

);

drop FOREIGN table if exists FIRM_OBS;

CREATE FOREIGN TABLE FIRM_OBS ( like retail_data.firm

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/firm', format 'csv',

delimiter ',',

);

drop FOREIGN table if exists PAYTYPE_OBS;

CREATE FOREIGN TABLE PAYTYPE_OBS ( like retail_data.paytype

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/paytype', format 'csv',

delimiter ',',

IGNORE_EXTRA_DATA 'on',

(48)

header 'on' );

drop FOREIGN table if exists POS_OBS;

CREATE FOREIGN TABLE POS_OBS ( like retail_data.pos

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/pos', format 'csv',

delimiter ',',

);

drop FOREIGN table if exists SECTOR_OBS;

CREATE FOREIGN TABLE SECTOR_OBS ( like retail_data.sector

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/sector', format 'csv',

delimiter ',',

);

drop FOREIGN table if exists STORE_OBS;

CREATE FOREIGN TABLE STORE_OBS ( like retail_data.store

encoding 'utf8',

location 'obs://<obs_bucket_name>/retail-data/store', format 'csv',

delimiter ',',

);

步骤5 复制并执行以下语句，导入外表数据到集群。

insert into retail_data.store select * from retail_obs_data.STORE_OBS;

insert into retail_data.sector select * from retail_obs_data.SECTOR_OBS;

insert into retail_data.paytype select * from retail_obs_data.PAYTYPE_OBS;

insert into retail_data.firm select * from retail_obs_data.FIRM_OBS;

insert into retail_data.flow select * from retail_obs_data.FLOW_OBS;

insert into retail_data.category select * from retail_obs_data.CATEGORY_OBS;

insert into retail_data.date select * from retail_obs_data.DATE_OBS;

insert into retail_data.pos select * from retail_obs_data.POS_OBS;

(49)

insert into retail_data.brand select * from retail_obs_data.BRAND_OBS;

insert into retail_data.sales select * from retail_obs_data.SALES_OBS;

导入数据需要一些时间，请耐心等待。

步骤6 复制并执行以下语句，创建视图v_sales_flow_details。

CREATE VIEW v_sales_flow_details AS SELECT

FIRM.ID FIRMID, FIRM.NAME FIRNAME, FIRM. CITYCODE, CATEGORY.ID CATEGORYID, CATEGORY.NAME CATEGORYNAME, SECTOR.ID SECTORID, SECTOR.SECTORNAME,

BRAND.ID BRANDID, BRAND.BRANDNAME,

STORE.ID STOREID, STORE.STORENAME, STORE.RENTAMOUNT, STORE.RENTAREA,

DATE.DATEKEY, SALES.TOTALAMOUNT, DISCOUNTAMOUNT, ITEMCOUNT, PAIDAMOUNT, INFLOWVALUE FROM SALES

INNER JOIN STORE ON SALES.STOREID = STORE.ID INNER JOIN FIRM ON STORE.FIRMID = FIRM.ID INNER JOIN BRAND ON STORE.BRANDID = BRAND.ID INNER JOIN SECTOR ON BRAND.SECTORID = SECTOR.ID

INNER JOIN CATEGORY ON SECTOR.CATEGORYID = CATEGORY.ID INNER JOIN DATE ON SALES.DATEKEY = DATE.ID

INNER JOIN FLOW ON FLOW.DATEKEY = DATE.ID AND FLOW.STOREID = STORE.ID;

----结束

步骤二：经营状况分析

以下以零售百货公司标准查询为例，演示在GaussDB(DWS) 中进行的基本数据查询。

在进行数据查询之前，请先执行“Analyze”命令生成与数据库表相关的统计信息。统计信息存储在系统表PG_STATISTIC中，执行计划生成器会使用这些统计数据，以生成最有效的查询执行计划。

查询示例如下：

● 查询各商铺的总营业额

复制并执行以下语句查询各商铺的总营业额。

SELECT DATE_TRUNC('month',datekey) AT TIME ZONE 'UTC' AS __timestamp, SUM(paidamount)

AS sum__paidamount FROM v_sales_flow_details

GROUP BY DATE_TRUNC('month',datekey) AT TIME ZONE 'UTC' ORDER BY SUM(paidamount) DESC;

● 查询各门店营收及租售比状况

复制并执行以下语句进行营收及租售比状况查询。

SELECT firname AS firname, storename AS storename, SUM(paidamount) AS sum__paidamount,

AVG(RENTAMOUNT)/SUM(PAIDAMOUNT) AS rentamount_sales_rate

FROM v_sales_flow_details GROUP BY firname, storename ORDER BY SUM(paidamount) DESC;

● 各省营业汇总分析

复制并执行以下语句进行汇总分析查询。

SELECT citycode AS citycode,

(50)

SUM(paidamount) AS sum__paidamount FROM v_sales_flow_details GROUP BY citycode

ORDER BY SUM(paidamount) DESC;

● 各门店租售比和客流转化率对比分析

SELECT brandname AS brandname, firname AS firname,

SUM(PAIDAMOUNT)/AVG(RENTAREA) AS sales_rentarea_rate, SUM(ITEMCOUNT)/SUM(INFLOWVALUE) AS poscount_flow_rate, AVG(RENTAMOUNT)/SUM(PAIDAMOUNT) AS rentamount_sales_rate FROM v_sales_flow_details

GROUP BY brandname, firname ORDER BY sales_rentarea_rate DESC;

● 品牌业态分析

SELECT categoryname AS categoryname, brandname AS brandname,

SUM(paidamount) AS sum__paidamount FROM v_sales_flow_details

GROUP BY categoryname, brandname

ORDER BY sum__paidamount DESC;

● 查询各品牌每日营业状况

SELECT brandname AS brandname,

DATE_TRUNC('day', datekey) AT TIME ZONE 'UTC' AS __timestamp, SUM(paidamount) AS sum__paidamount

FROM v_sales_flow_details

WHERE datekey >= '2016-01-01 00:00:00' AND datekey <= '2016-01-30 00:00:00' GROUP BY brandname,

DATE_TRUNC('day', datekey) AT TIME ZONE 'UTC' ORDER BY sum__paidamount ASC

LIMIT 50000;

3.5 第 5 步：查看其它资料并清理资源

查看其它资料

完成如上操作步骤后，我们推荐您可以参考如下资料继续对数据仓库服务进行更详细深入的了解：

● 《数据仓库服务管理指南》：本指南在此入门的基础上，对创建、管理、监控以

及连接集群的概念和相关操作提供全面详细的信息。

● 《数据仓库服务数据库开发指南》：本指南在此入门的基础上，为数据库开发人

员提供全面详细的信息，帮助他们了解如何构建、管理和查询GaussDB(DWS) 数据库，包括SQL语法、用户管理、数据导入导出等指导。

清理资源

当完成快速入门的样例后，如果您不再需要使用本样例创建的样例数据、集群、ECS以及VPC时，您可以删除这些资源，以免资源浪费或占用您的配额。

步骤1 删除GaussDB(DWS) 集群。

在GaussDB(DWS) 管理控制台，单击“集群管理”，在集群列表中集群“dws-

demo”所在行，单击“更多 > 删除”。然后在弹出对话框中勾选“释放与集群绑定的

(51)

如果待删除集群使用了自动创建的安全组，且该自动创建的安全组没有被别的集群使用，删除集群时，该安全组也会一起被自动删除。

步骤2 删除子网。删除前请先确保该子网未被其他资源绑定。

登录虚拟私有云管理控制台，在左侧导航树单击“虚拟私有云”，在虚拟私有云列表中，单击名称“vpc-dws”，然后在子网列表中“subnet-dws”所在行单击“删除”。

步骤3 删除虚拟私有云。删除前请先确保该虚拟私有云未被其他资源绑定。

登录虚拟私有云管理控制台，在虚拟私有云列表中，找到虚拟私有云“vpc-dws) ”，

单击其所在行的“删除”。

具体步骤，请参见《虚拟私有云用户指南》中“虚拟私有云和子网 > 删除虚拟私有云”章节。

----结束

创建表_数据仓库服务 GaussDB(DWS)_快速入门_数据库使用入门_创建和管理表_华为云

快速入门

目 录

1 快速创建 GaussDB(DWS)集群并导入和测试 TPC-DS 数据...1

2 使用 CDM 将 MySQL 数据迁移至 GaussDB(DWS)集群... 5

3 创建集群并连接集群... 18

4 数据库使用入门... 49

5 导入数据入门示例... 96

1 快速创建 GaussDB(DWS)集群并导入和测试 TPC-DS 数据

获取 AK 和 SK

创建 GaussDB(DWS)集群

创建弹性云服务器

下载 gsql 客户端

使用 gsql 导入并测试 TPC-DS 数据集

2 使用 CDM 将 MySQL 数据迁移至 GaussDB(DWS)集群

场景描述

迁移前数据检查

<port>

创建 GaussDB(DWS)集群

创建 CDM 集群

创建连接

新建作业和迁移

迁移后数据一致性验证

3 创建集群并连接集群

3.1 第 1 步：入门前准备

注册并实名认证公有云帐户

确定集群端口

3.2 第 2 步：创建集群

创建集群

3.3 第 3 步：连接集群

操作场景

获取集群公网访问地址

使用 Data Studio 连接到集群数据库

3.4 第 4 步：导入样例数据并查询

3.4.1 交通卡口通行车辆分析

支持区域

准备工作

步骤一：创建集群

步骤二：使用 Data Studio 连接集群

步骤三：导入交通卡口样例数据

步骤四：车辆分析

3.4.2 某公司供应链需求分析

支持区域

场景描述

准备工作

步骤一：导入公司样例数据

步骤二：多表分析与主题分析

3.4.3 零售业百货公司经营状况分析

零售业百货公司样例简介

支持区域

准备工作

步骤一：导入零售业百货公司样例数据

步骤二：经营状况分析

3.5 第 5 步：查看其它资料并清理资源

查看其它资料

清理资源

目录

3 ^{创建集群并连接集群}