文档首页 > > 快速入门> 导入数据入门示例> 导入零售业百货公司样例数据并查询

导入零售业百货公司样例数据并查询

分享
更新时间: 2019/06/10 19:42

零售业百货公司样例简介

DWS支持用户将数据从集群外导入到集群中。用户可以参考以下指导,快速将样例数据从OBS导入集群。

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

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

导入零售业百货公司样例数据

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

  1. 执行以下语句,创建retail数据库。

    create database retail encoding 'utf8' template template0;

  2. 执行以下步骤切换为连接新建的数据库。
  3. 创建数据库表(也称为数据库普通表)。

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

    图1 百货公司样例数据表
    复制并执行以下语句,创建零售业百货公司信息数据库表。
    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
    )
    WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
    
    DROP TABLE IF EXISTS SECTOR;
    CREATE TABLE SECTOR(
            ID INT, 
            SECTORCODE VARCHAR(10), 
            SECTORNAME VARCHAR(20), 
            CATEGORYID INT
    )
    WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
    
    DROP TABLE IF EXISTS CATEGORY;
    CREATE TABLE CATEGORY(
            ID INT, 
            CODE VARCHAR(10), 
            NAME VARCHAR(20)
    )
    WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
    
    DROP TABLE IF EXISTS FIRM;
    CREATE TABLE FIRM(
            ID INT, 
            CODE VARCHAR(4), 
            NAME VARCHAR(40), 
            CITYID INT, 
            CITYNAME VARCHAR(10),
            CITYCODE VARCHAR(20)
    )
    WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
    
    DROP TABLE IF EXISTS DATE;
    CREATE TABLE DATE(
            ID INT, 
            DATEKEY DATE, 
            YEAR INT, 
            MONTH INT, 
            DAY INT, 
            WEEK INT, 
            WEEKDAY INT
    )
    WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
    
    DROP TABLE IF EXISTS PAYTYPE;
    CREATE TABLE PAYTYPE(
            ID INT, 
            CODE VARCHAR(10), 
            TYPE VARCHAR(10), 
            SIGNDATE DATE
    )
    WITH (ORIENTATION = COLUMN, COMPRESSION=MIDDLE) DISTRIBUTE BY REPLICATION;
    
    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);

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

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

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

    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
    )
    SERVER gsmpp_server 
    OPTIONS (
            encoding 'utf8',
            location 'obs://<obs_bucket_name>/retail-data/sales',
            format 'csv',
            delimiter ',',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on',
            header 'on'
    );
    
    drop FOREIGN table if exists FLOW_OBS;
    CREATE FOREIGN TABLE FLOW_OBS
    (
            like retail_data.flow
    )
    SERVER gsmpp_server 
    OPTIONS (
            encoding 'utf8',
            location 'obs://<obs_bucket_name>/retail-data/flow',
            format 'csv',
            delimiter ',',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on',
            header 'on'
    );
    
    drop FOREIGN table if exists BRAND_OBS;
    CREATE FOREIGN TABLE BRAND_OBS
    (
            like retail_data.brand
    )
    SERVER gsmpp_server 
    OPTIONS (
            encoding 'utf8',
            location 'obs://<obs_bucket_name>/retail-data/brand',
            format 'csv',
            delimiter ',',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on',
            header 'on'
    );
    
    
    drop FOREIGN table if exists CATEGORY_OBS;
    CREATE FOREIGN TABLE CATEGORY_OBS
    (
           like retail_data.category
    )
    SERVER gsmpp_server 
    OPTIONS (
           encoding 'utf8',
           location 'obs://<obs_bucket_name>/retail-data/category',
           format 'csv',
           delimiter ',',
           access_key '<Access_Key_Id>',
           secret_access_key '<Secret_Access_Key>',
           chunksize '64',
           IGNORE_EXTRA_DATA 'on',
           header 'on'
    );
    
    drop FOREIGN table if exists DATE_OBS;
    CREATE FOREIGN TABLE DATE_OBS
    (
            like retail_data.date
    )
    SERVER gsmpp_server 
    OPTIONS (
            encoding 'utf8',
            location 'obs://<obs_bucket_name>/retail-data/date',
            format 'csv',
            delimiter ',',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on',
            header 'on'
    );
    
    drop FOREIGN table if exists FIRM_OBS;
    CREATE FOREIGN TABLE FIRM_OBS
    (
            like retail_data.firm
    )
    SERVER gsmpp_server 
    OPTIONS (
            encoding 'utf8',
            location 'obs://<obs_bucket_name>/retail-data/firm',
            format 'csv',
            delimiter ',',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on',
            header 'on'
    );
    
    
    drop FOREIGN table if exists PAYTYPE_OBS;
    CREATE FOREIGN TABLE PAYTYPE_OBS
    (
            like retail_data.paytype
    )
    SERVER gsmpp_server 
    OPTIONS (
            encoding 'utf8',
            location 'obs://<obs_bucket_name>/retail-data/paytype',
            format 'csv',
            delimiter ',',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on',
            header 'on'
    );
    
    
    drop FOREIGN table if exists POS_OBS;
    CREATE FOREIGN TABLE POS_OBS
    (
            like retail_data.pos
    )
    SERVER gsmpp_server 
    OPTIONS (
            encoding 'utf8',
            location 'obs://<obs_bucket_name>/retail-data/pos',
            format 'csv',
            delimiter ',',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on',
            header 'on'
    );
    
    drop FOREIGN table if exists SECTOR_OBS;
    CREATE FOREIGN TABLE SECTOR_OBS
    (
            like retail_data.sector
    )
    SERVER gsmpp_server 
    OPTIONS (
            encoding 'utf8',
            location 'obs://<obs_bucket_name>/retail-data/sector',
            format 'csv',
            delimiter ',',
            access_key '<Access_Key_Id>',
            secret_access_key '<Secret_Access_Key>',
            chunksize '64',
            IGNORE_EXTRA_DATA 'on',
            header 'on'
    );
    
    
    drop FOREIGN table if exists STORE_OBS;
    CREATE FOREIGN TABLE STORE_OBS
    (
             like retail_data.store
    )
    SERVER gsmpp_server 
    OPTIONS (
             encoding 'utf8',
             location 'obs://<obs_bucket_name>/retail-data/store',
             format 'csv',
             delimiter ',',
             access_key '<Access_Key_Id>',
             secret_access_key '<Secret_Access_Key>',
             chunksize '64',
             IGNORE_EXTRA_DATA 'on',
             header '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)进行获取。然后,将获取到的值替换到创建外表语句中。

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

    set current_schema='retail_data';
    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;

查询样例数据

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

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

查询示例如下:

  • 查询各商铺的总营业

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

    set current_schema='retail_data';
    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;
  • 查询各门店营收及租售比状况

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

    set current_schema='retail_data';
    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;
  • 各省营业汇总分析

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

    set current_schema='retail_data';
    SELECT citycode AS citycode,
    SUM(paidamount)
    AS sum__paidamount
    FROM v_sales_flow_details
    GROUP BY citycode
    ORDER BY SUM(paidamount) DESC;
  • 各门店租售比和客流转化率对比分析
    set current_schema='retail_data';
    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;
  • 品牌业态分析
    set current_schema='retail_data';
    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;
  • 查询各品牌每日营业状况
    set current_schema='retail_data';
    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;
分享:

    相关文档

    相关产品

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

提交成功!

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

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

*必选

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

字符长度不能超过200

提交反馈 取消

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

跳转到云社区