某公司供应链需求分析

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

说明

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

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

支持区域

表3-7 区域和 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

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

1. 准备工作

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

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

场景描述

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

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

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

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

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

准备工作

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

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

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

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

使用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;

( N_NATIONKEY INT NOT NULL,

O_ORDERKEY BIGINT NOT NULL,

drop FOREIGN table if exists region;

CREATE FOREIGN TABLE REGION

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

delimiter '|',

access_key '<Access_Key_Id>',

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

IGNORE_EXTRA_DATA 'on'

drop FOREIGN table if exists nation;

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

delimiter '|',

access_key '<Access_Key_Id>',

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

IGNORE_EXTRA_DATA 'on' );

drop FOREIGN table if exists supplier;

CREATE FOREIGN TABLE SUPPLIER ( like tpch.supplier

)SERVER gsmpp_server OPTIONS (

encoding 'utf8',

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

delimiter '|',

access_key '<Access_Key_Id>',

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

IGNORE_EXTRA_DATA 'on' );

drop FOREIGN table if exists customer;

CREATE FOREIGN TABLE CUSTOMER ( like tpch.customer

)SERVER gsmpp_server OPTIONS (

encoding 'utf8',

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

delimiter '|',

access_key '<Access_Key_Id>',

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

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

CREATE FOREIGN TABLE PART ( like tpch.part

)SERVER gsmpp_server OPTIONS (

encoding 'utf8',

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

delimiter '|',

access_key '<Access_Key_Id>',

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

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

CREATE FOREIGN TABLE PARTSUPP

( like tpch.partsupp )SERVER gsmpp_server OPTIONS (

encoding 'utf8',

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

delimiter '|',

access_key '<Access_Key_Id>',

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

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

CREATE FOREIGN TABLE ORDERS ( like tpch.orders

)SERVER gsmpp_server OPTIONS (

encoding 'utf8',

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

delimiter '|',

access_key '<Access_Key_Id>',

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

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

CREATE FOREIGN TABLE LINEITEM ( like tpch.lineitem

)SERVER gsmpp_server OPTIONS (

encoding 'utf8',

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

delimiter '|',

access_key '<Access_Key_Id>',

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

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分钟，请耐心等待。

----结束

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

在进行数据查询之前，请先执行“Analyze”命令生成与数据库表相关的统计信息。统

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

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

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

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

)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语句进行查询，该语句的特点是：带有聚集、聚集子 查询操作并存的两表连接操作。

set current_schema='tpch';

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