使用DWS秒级查询交通卡口通行车辆行驶路线

视频介绍

操作流程

本实践预计时长40分钟，基本流程如下：

1. 准备工作
2. 步骤一：创建集群
3. 步骤二：导入交通卡口样例数据
4. 步骤三：车辆分析

支持区域

当前已上传OBS数据的区域如表1所示。

准备工作

• 已注册账号，且在使用DWS 前检查账号状态，账号不能处于欠费或冻结状态。
• 获取此账号的“AK/SK”。

步骤一：创建集群

1. 登录DWS控制台。
2. 在“服务列表”中，选择“大数据 > 数据仓库服务 DWS”。
3. 左侧导航栏单击“专属集群 > 集群列表”，进入页面后，单击右上角的“创建数据仓库集群”按钮。
4. 参见表2进行基础配置。

   表2 基础配置

   参数名称	配置方式
   区域	选择“中国-香港”。 说明： 本指导以“中国-香港”为例进行介绍，如果您需要选择其他区域进行操作，请确保所有操作均在同一区域进行。
   可用分区	单AZ-可用区2
   版本选择	存算一体
   存储类型	SSD云盘
   CPU架构	X86
   节点规格	dws2.m6.4xlarge.8（16 vCPU | 128GB | 2000GB SSD） 说明： 如规格售罄，可选择其他可用区或规格。
   热数据存储	100GB / 节点
   节点数量	3

5. 信息核对无误，单击“下一步：网络配置”，参见表3进行网络配置。

   表3 网络配置

   参数名称	配置方式
   虚拟私有云	vpc-default
   子网	subnet-default(192.168.0.0/24)
   安全组	自动创建安全组
   公网访问	现在购买
   宽带	1Mbit/s
   弹性负载均衡	暂不使用

6. 信息核对无误，单击“下一步：高级配置”，参见表4进行高级配置。

   表4 高级配置

   参数名称	配置方式
   集群名称	dws-demo
   集群版本	使用推荐版本
   管理员用户	dbadmin
   管理员密码	-
   确认密码	-
   数据库端口	8000
   企业项目	default
   高级配置	默认配置

7. 单击“下一步：确认配置”，确认无误后，单击“立即购买”。
8. 等待约6分钟，待集群创建成功后，单击集群名称前面的，弹出集群信息，记录下“公网访问地址”。
   图1 集群信息
   

步骤二：导入交通卡口样例数据

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

1. 创建traffic数据库。

   1	CREATE DATABASE traffic encoding 'utf8' template template0;

2. 切换到新的数据库traffic，创建用于存储卡口车辆信息的数据库表。

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

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

   • <obs_bucket_name>表示OBS桶名，当前系统已预置了OBS桶和样例数据，用户无需创建，请替换为DWS所在的实际区域对应的桶名，参见支持区域，本实践以“中国-香港”地区为例，请替换为dws-demo-ap-southeast-1。不支持跨区域访问OBS桶数据，例如集群在“中国-香港”，不能将<obs_bucket_name>替换成其他区域所对应的桶名。

   • <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

   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://<obs_bucket_name>/traffic-data/gcxx', format 'text', delimiter ',', access_key '<Access_Key_Id>', secret_access_key '<Secret_Access_Key>', chunksize '64', IGNORE_EXTRA_DATA 'on' );

4. 将数据从外表导入到数据库表中。

   1	INSERT INTO traffic_data.GCJL SELECT * FROM tpchobs.GCJL_OBS;

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

步骤三：车辆分析

1. 执行ANALYZE。

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

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

   1

   ANALYZE;

2. 查询数据表中的数据量。

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

   1 2	SET current_schema= traffic_data; SELECT count(*) FROM traffic_data.gcjl;

3. 车辆精确查询。

   执行以下语句，指定车牌号码和时间段查询车辆行驶路线。DWS在应对点查时秒级响应。

   1 2 3 4 5 6

   SET current_schema= traffic_data; SELECT hphm, kkbh, gcsj FROM traffic_data.gcjl where hphm = 'YD38641' and gcsj between '2016-01-06' and '2016-01-07' order by gcsj desc;

4. 车辆模糊查询。

   执行以下语句，指定车牌号码和时间段查询车辆行驶路线，DWS 在应对模糊查询时秒级响应。

   1 2 3 4 5 6 7

   SET current_schema= traffic_data; SELECT hphm, kkbh, gcsj FROM traffic_data.gcjl where hphm like 'YA23F%' and kkbh in('508', '1125', '2120') and gcsj between '2016-01-01' and '2016-01-07' order by hphm,gcsj desc;