更新时间:2024-03-06 GMT+08:00

某公司供应链需求分析

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

GaussDB(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

场景描述

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

  1. 分析某地区供应商为公司带来的收入,通过该统计信息可用于决策在给定的区域是否需要建立一个当地分配中心。
  2. 分析零件/供货商关系,可以获得能够以指定的贡献条件供应零件的供货商数量,通过该统计信息可用于决策在订单量大,任务紧急时,是否有充足的供货商。
  3. 分析小订单收入损失,通过查询得知如果没有小量订单,平均年收入将损失多少。筛选出比平均供货量的20%还低的小批量订单,如果这些订单不再对外供货,由此计算平均一年的损失。

准备工作

  • 已注册账号,且在使用GaussDB(DWS) 前检查账号状态,账号不能处于欠费或冻结状态。
  • 获取此账号的“AK/SK”。
  • 已创建集群,并已使用Data Studio连接集群,参见交通卡口通行车辆分析

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

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

  1. 创建数据库表。

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

    图1 TPC-H数据表
    复制并执行下列表创建语句,在gaussdb数据库中创建对应的数据表。
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10
     11
     12
     13
     14
     15
     16
     17
     18
     19
     20
     21
     22
     23
     24
     25
     26
     27
     28
     29
     30
     31
     32
     33
     34
     35
     36
     37
     38
     39
     40
     41
     42
     43
     44
     45
     46
     47
     48
     49
     50
     51
     52
     53
     54
     55
     56
     57
     58
     59
     60
     61
     62
     63
     64
     65
     66
     67
     68
     69
     70
     71
     72
     73
     74
     75
     76
     77
     78
     79
     80
     81
     82
     83
     84
     85
     86
     87
     88
     89
     90
     91
     92
     93
     94
     95
     96
     97
     98
     99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    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;
    
    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);
    

  2. 创建外表。外表用于识别和关联OBS上的源数据。

    • 其中,<obs_bucket_name>代表OBS桶名,仅支持部分区域,当前支持的区域和对应的OBS桶名请参见支持区域。GaussDB(DWS) 集群不支持跨区域访问OBS桶数据。
    • 本实践以““中国-香港”地区为例,可填入dws-demo-ap-southeast-1,<Access_Key_Id>和<Secret_Access_Key>替换为实际值,在准备工作获取。
    • 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险,建议在配置文件或者环境变量中密文存放,使用时解密,确保安全。
    • 创建外表如果提示“ERROR: schema "xxx" does not exist Position”,则说明schema不存在,请先参照上一步创建schema。
      1
      2
      3
      4
      5
      6
      7
      8
      9
     10
     11
     12
     13
     14
     15
     16
     17
     18
     19
     20
     21
     22
     23
     24
     25
     26
     27
     28
     29
     30
     31
     32
     33
     34
     35
     36
     37
     38
     39
     40
     41
     42
     43
     44
     45
     46
     47
     48
     49
     50
     51
     52
     53
     54
     55
     56
     57
     58
     59
     60
     61
     62
     63
     64
     65
     66
     67
     68
     69
     70
     71
     72
     73
     74
     75
     76
     77
     78
     79
     80
     81
     82
     83
     84
     85
     86
     87
     88
     89
     90
     91
     92
     93
     94
     95
     96
     97
     98
     99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    117
    118
    119
    120
    121
    122
    123
    124
    125
    126
    127
    128
    129
    130
    131
    132
    133
    134
    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 '|',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on'
    );
     
    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 '|',
             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',
            IGNORE_EXTRA_DATA 'on'
    );
    

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

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

    1
    2
    3
    4
    5
    6
    7
    8
    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分钟,请耐心等待。

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

以下以TPC-H标准查询为例,演示在GaussDB(DWS) 中进行的基本数据查询。

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

查询示例如下:

  • 某地区供货商为公司带来的收入查询(TPCH-Q5)

    通过执行TPCH-Q5查询语句,可以查询到通过某个地区零件供货商获得的收入(收入按sum( l_extendedprice * (1 - l_discount))计算)统计信息。该统计信息可用于决策在给定的区域是否需要建立一个当地分配中心。

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

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    SET current_schema='tpch';
    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子查询操作并存的多表连接操作。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    SET current_schema='tpch';
    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语句进行查询,该语句的特点是:带有聚集、聚集子查询操作并存的两表连接操作。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    SET current_schema='tpch';
    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
    );