【云小课】EI第9课 车海茫茫中寻找你--GaussDB(DWS)海量数据分析

Hi,EI 发表于 2020/08/25 11:56:20 2020/08/25
【摘要】 数据仓库服务GaussDB(DWS)使用OBS作为集群数据与外部数据互相转化的平台,支持用户将数据从集群外导入到集群中,快速将样例数据从OBS导入集群。 本示例将加载8.9亿条交通卡口车辆通行模拟数据到数据仓库单个数据库表中,并进行车辆精确查询和车辆模糊查询,展示GaussDB(DWS)对于历史详单数据的高性能查询能力。

准备工作

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

  • 已下载客户端并连接到集群

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

导入交通卡口样例数据

使用SQL客户端工具连接到集群后,就可以在SQL客户端工具中,执行以下步骤导入交通卡口车辆通行的样例数据并执行查询。

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

    create database traffic encoding 'utf8' template template0;
  2. 执行以下步骤切换为连接新建的数据库。

    • 在Data Studio客户端的“对象浏览器”窗口,右键单击数据库连接名称,在弹出菜单中单击“刷新”,刷新后就可以看到新建的数据库。

    • 右键单击“traffic”数据库名称,在弹出菜单中单击“打开连接”。

    • 右键单击“traffic”数据库名称,在弹出菜单中单击“打开新的终端”,即可打开连接到指定数据库的SQL命令窗口,后面的步骤,请全部在该命令窗口中执行。

  3. 执行以下语句,创建用于存储卡口车辆信息的数据库表。

    create schema traffic_data;
    set current_schema= traffic_data;
    drop table if exists GCJL;
    CREATE TABLE GCJL
    (
                     kkbh   VARCHAR(20),
                     hphm   VARCHAR(20),
                     gcsj   DATE ,
                     cplx   VARCHAR(8),
                     cllx   VARCHAR(8),
                     csys   VARCHAR(8)
    )
    with (orientation = column, COMPRESSION=MIDDLE)
    distribute by hash(hphm);
  4. 创建外表。外表用于识别和关联OBS上的源数据。<Access_Key_Id>和<Secret_Access_Key>替换为实际值,在创建访问密钥(AK和SK)中获取。

    create schema tpchobs;
    set current_schema = 'tpchobs';
    drop FOREIGN table if exists GCJL_OBS;
    CREATE FOREIGN TABLE GCJL_OBS
    (
                like traffic_data.GCJL
    )
    SERVER gsmpp_server
    OPTIONS (
                   encoding 'utf8',
                   location 'obs://dws-demo-cn-north-4/traffic-data/gcxx',
                   format 'text',
                   delimiter ',',
                   access_key '<Access_Key_Id>',
                   secret_access_key '<Secret_Access_Key>',
                   chunksize '64',
                   IGNORE_EXTRA_DATA 'on'
    );
  5. 执行以下语句,将数据从外表导入到数据库表中。

    insert into traffic_data.GCJL select * from tpchobs.GCJL_OBS;

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

车辆分析

  1. 执行Analyze

    用于收集与数据库中普通表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC中。执行计划生成器会使用这些统计数据,以生成最有效的查询执行计划。

    执行以下语句生成表统计信息:

    Analyze;
  2. 查询数据表中的数据量

    执行如下语句,可以查看已加载的数据条数。

    set current_schema= traffic_data;
    Select count(*) from traffic_data.gcjl;
  3. 车辆精确查询

    执行以下语句,指定车牌号码和时间段查询车辆轨迹。GaussDB(DWS) 在应对点查时秒级响应。

    set current_schema= traffic_data;
    select hphm, kkbh, gcsj
    from traffic_data.gcjl
    where hphm = '粤D12345'
    and gcsj between '2016-01-06' and '2016-01-07'
    order by gcsj desc;
  4. 车辆模糊查询

    执行以下语句,指定车牌号码和时间段查询车辆轨迹,GaussDB(DWS) 在应对模糊查询时秒级响应。

    set current_schema= traffic_data;
    select hphm, kkbh, gcsj
    from traffic_data.gcjl
    where hphm like '%A23F%'
    and kkbh in('508', '1125', '2120')
    and gcsj between '2016-01-01' and '2016-01-07'
    order by hphm,gcsj desc;



了解更多数据仓库服务内容,猛戳此处据仓库服务

【版权声明】本文为华为云社区用户原创内容,转载时必须标注文章的来源(华为云社区),文章链接,文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:cloudbbs@huaweicloud.com进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。