【云小课】EI第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;

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