# 使用DWS分析某公司供应链需求
本实践将演示从OBS加载样例数据集到DWS 集群中并查询数据的流程，从而向您展示DWS 在数据分析场景中的多表分析与主题分析。
![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/note_3.0-zh-cn.png)
DWS 已经预先生成了1GB的TPC-H-1x的标准数据集，已将数据集上传到了OBS桶的tpch文件夹中，并且已赋予所有华为云用户该OBS桶的只读访问权限，用户可以方便地进行导入。
#### 操作流程
本实践预计时长60分钟，基本流程如下：
1. [准备工作]
2. [步骤一：导入公司样例数据]
3. [步骤二：多表分析与主题分析]
 
 #### 支持区域
当前已上传OBS数据的区域如[表1]所示。
 表1区域和OBS桶名 
| 区域       | OBS桶名                   |
|:---|:---|
| 华北-北京一   | dws-demo-cn-north-1     |
| 华北-北京二   | 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-cn-south-4     |
| 中国-香港    | dws-demo-ap-southeast-1 |
| 亚太-新加坡   | dws-demo-ap-southeast-3 |
| 亚太-曼谷    | dws-demo-ap-southeast-2 |
| 拉美-圣地亚哥  | dws-demo-la-south-2     |
| 非洲-约翰内斯堡 | dws-demo-af-south-1     |
| 拉美-墨西哥城一 | dws-demo-na-mexico-1    |
| 拉美-墨西哥城二 | dws-demo-la-north-2     |
| 莫斯科二     | dws-demo-ru-northwest-2 |
| 拉美-圣保罗一  | dws-demo-sa-brazil-1    |
   
#### 场景描述
了解DWS的基本功能和数据导入，对某公司与供应商的订单数据分析，分析维度如下：
1. 分析某地区供应商为公司带来的收入，该统计信息可用于决策在给定区域是否需要建立一个当地分配中心。
2. 分析零件/供货商关系，可以获得能够以指定的贡献条件供应零件的供货商数量，通过该统计信息可用于决策在订单量大，任务紧急时，是否有充足的供货商。
3. 分析小订单收入损失，通过查询得知如果没有小量订单，平均年收入将损失多少。筛选出比平均供货量的20％还低的小批量订单，如果这些订单不再对外供货，由此计算平均一年的损失。
4. 本实践用到的TPC-H样例包含8张数据库表，其关联关系如[图1]所示。
   图1TPC-H数据表   
   ![](https://support.huaweicloud.com/bestpractice-dws/figure/zh-cn_image_0000002628851250.png "点击放大") 
 
 #### 准备工作
- 已注册账号，且在使用DWS 前检查账号状态，账号不能处于欠费或冻结状态。
- 参见[新增访问密钥](https://support.huaweicloud.com/usermanual-ca/ca_01_0003.html#section1)获取此账号的"AK/SK"。
- 参见[创建集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0019.html)完成集群创建。
 
 #### 步骤一：导入公司样例数据
使用SQL客户端工具连接到集群后，就可以在SQL客户端工具中，执行以下步骤导入TPC-H样例数据并执行查询。
1. 如果是业务调测场景，推荐通过**SQL编辑器** 进行连接，参见[使用SQL编辑器连接集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0803.html#section1)完成连接。
   
   也可以通过其他方式连接（例如命令行gsql工具），参见[连接DWS集群](https://support.huaweicloud.com/mgtg-dws/dws_01_0131.html)。
   
   
2. 在编辑窗口执行如下语句，创建表。 
   ```
   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;
   DROP TABLE if exists nation;
   CREATE TABLE nation
   (
           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
   (
           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);
   ```
   
   
3. 创建外表。外表用于识别和关联OBS上的源数据。 
   ![](https://support.huaweicloud.com/bestpractice-dws/public_sys-resources/caution_3.0-zh-cn.png)
   - *\<obs_bucket_name\>* 表示OBS桶名，当前系统已预置了OBS桶和样例数据，用户无需创建，请替换为DWS所在的实际区域对应的桶名，参见[支持区域]，本实践以"华北-北京四"地区为例，请替换为dws-demo-cn-north-4。不支持跨区域访问OBS桶数据，例如集群在"华北-北京四"，不能将\<obs_bucket_name\>替换成其他区域所对应的桶名。
   
   
   
   - \<Access_Key_Id\>和\<Secret_Access_Key\>替换为实际值，在[准备工作]获取。
   
   - 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险，建议在配置文件或者环境变量中密文存放，使用时解密，确保安全。
    
   ```
   DROP FOREIGN table if exists region_ft;
   CREATE FOREIGN TABLE region_ft
   (
           like region
   )                    
   SERVER gsmpp_server 
   OPTIONS (
           encoding 'utf8',
           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_ft;
   CREATE FOREIGN TABLE nation_ft
   (
           like nation
   )
   SERVER gsmpp_server 
   OPTIONS (
            encoding 'utf8',
            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_ft;
   CREATE FOREIGN TABLE supplier_ft
   (
           like 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_ft;
   CREATE FOREIGN TABLE customer_ft
   (
           like 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_ft;
   CREATE FOREIGN TABLE part_ft
   (
           like 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_ft;
   CREATE FOREIGN TABLE partsupp_ft
   (
           like 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_ft;
   CREATE FOREIGN TABLE orders_ft
   (
           like 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_ft;
   CREATE FOREIGN TABLE lineitem_ft
   (
           like 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',
           IGNORE_EXTRA_DATA 'on'
   );
   ```
   
   
4. 复制并执行以下语句，将外表数据导入到对应的数据库表中。 
   将OBS外表的数据通过insert命令导入DWS 的数据库表中，数据库内核对应的操作为OBS数据高速并发导入DWS 。
   ```
   INSERT INTO lineitem SELECT * FROM lineitem_ft;
   INSERT INTO part SELECT * FROM part_ft;
   INSERT INTO partsupp SELECT * FROM partsupp_ft;
   INSERT INTO customer SELECT * FROM customer_ft;
   INSERT INTO supplier SELECT * FROM supplier_ft;
   INSERT INTO nation SELECT * FROM nation_ft;
   INSERT INTO region SELECT * FROM region_ft;
   INSERT INTO orders SELECT * FROM orders_ft;
   ```
   导入数据需要约10分钟，请耐心等待。
   
   
 
 #### 步骤二：多表分析与主题分析
以下以TPC-H标准查询为例，演示在DWS 中进行的基本数据查询。
在进行数据查询之前，请先执行"Analyze"命令生成与数据库表相关的统计信息。统计信息存储在系统表PG_STATISTIC中，执行计划生成器会使用这些统计数据，以生成最有效的查询执行计划。
查询示例如下：
- **某地区供货商为公司带来的收入查询（TPCH-Q5）**
  通过执行TPCH-Q5查询语句，可以查询到通过某个地区零件供货商获得的收入（收入按sum( l_extendedprice \* (1 - l_discount))计算）统计信息。该统计信息可用于决策在给定的区域是否需要建立一个当地分配中心。
  复制并执行以下TPCH-Q5语句进行查询。该语句的特点是：带有分组、排序、聚集操作并存的多表连接查询操作。
  ```
  SELECT
  n_name,
  sum(l_extendedprice * (1 - l_discount)) as revenue
  FROM
  customer,
  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
  FROM
  partsupp,
  part
  where
  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%'
  )
  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
  FROM
  lineitem,
  part
  where
  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
  );
  ```