文档首页 > > 快速入门> 导入数据入门示例> 导入TPC-H样例数据并查询

导入TPC-H样例数据并查询

分享
更新时间: 2019/06/10 GMT+08:00

TPC-H样例简介

本示例将演示从OBS加载TPC-H数据集到DWS集群中并查询数据的流程,从而向您展示DWS在数据分析场景中的多表分析与主题分析。DWS已经预先生成了1GB的TPC-H-1x的标准数据集,已将数据集上传到了OBS桶的tpch文件夹中,并且已赋予所有华为云用户该OBS桶的只读访问权限,用户可以方便的进行导入。

导入TPC-H样例数据

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

  1. 创建数据库表。

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

    图1 TPC-H数据表
    复制并执行下列表创建语句,在postgres数据库中创建对应的数据表。
    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上的源数据。

    外表是对远端数据结构的定义,一个外表可以用来像数据库表一样进行查询,但是实际并没有存储数据在DWS中,当外表被调用的时候,DWS会根据外表中远端数据源的定义从外部数据源获取、更新数据。DWS当前支持OBS、HDFS外表数据源的并行高速访问和查询,本示例中通过定义外表导入OBS上的数据到普通的数据表中。

    创建外表语句如下。请参见语句下方的参数说明,将创建外表语句中的参数<obs_bucket_name>、<Access_Key_Id>和<Secret_Access_Key>替换为实际值,然后在客户端工具中执行替换后的语句创建外表。

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

    参数说明:

    • <obs_bucket_name>:表示样例数据存储的OBS桶名,集群所属的区域不同OBS桶名也不同。

      “华北-北京一”区域,OBS桶名为“dws-demo”。

      其他区域的OBS桶名为“dws-demo-<Region>”,其中<Region>表示区域。区域信息请参见地区和终端节点。例如,Region为ap-southeast-1,<obs_bucket_name>即为“dws-demo-ap-southeast-1”。

    • <Access_Key_Id>和<Secret_Access_Key>:分别表示访问密钥ID和私有访问密钥。请参见创建访问密钥(AK和SK)进行获取。然后,将获取到的值替换到创建外表语句中。

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

    将OBS外表的数据通过insert命令导入DWS的数据库表中,数据库内核对应的操作为OBS数据高速并发导入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;

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

查询样例数据

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

在进行数据查询之前,请先执行“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
    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子查询操作并存的多表连接操作。

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

    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
    );
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区