交通卡口通行车辆分析
本实践将演示交通卡口车辆通行分析,将加载8.9亿条交通卡口车辆通行模拟数据到数据仓库单个数据库表中,并进行车辆精确查询和车辆模糊查询,展示GaussDB(DWS) 对于历史详单数据的高性能查询能力。

GaussDB(DWS) 已预先将样例数据上传到OBS桶的“traffic-data”文件夹中,并给所有华为云用户赋予了该OBS桶的只读访问权限。
支持区域
当前已上传OBS数据的区域如表1所示。
区域 |
OBS桶名 |
---|---|
华北-北京一 |
dws-demo-cn-north-1 |
华北-北京二 |
dws-demo-cn-north-2 |
华北-北京四 |
dws-demo-cn-north-4 |
华北-乌兰察布一 |
dws-demo-cn-north-9 |
华东-上海一 |
dws-demo-cn-east-3 |
华东-上海二 |
dws-demo-cn-east-2 |
华南-广州 |
dws-demo-cn-south-1 |
华南-广州友好 |
dws-demo-cn-south-4 |
中国-香港 |
dws-demo-ap-southeast-1 |
亚太-新加坡 |
dws-demo-ap-southeast-3 |
亚太-曼谷 |
dws-demo-ap-southeast-2 |
拉美-圣地亚哥 |
dws-demo-la-south-2 |
非洲-约翰内斯堡 |
dws-demo-af-south-1 |
拉美-墨西哥城一 |
dws-demo-na-mexico-1 |
拉美-墨西哥城二 |
dws-demo-la-north-2 |
莫斯科二 |
dws-demo-ru-northwest-2 |
拉美-圣保罗一 |
dws-demo-sa-brazil-1 |
步骤一:创建集群
- 登录管理控制台。
- 在“服务列表”中,选择“大数据 > 数据仓库服务”。
- 左侧导航栏单击“集群管理”,进入页面后,单击右上角的“创建数据仓库集群”按钮。
- 参见表2进行参数配置。
表2 软件配置 参数名称
配置方式
区域
选择“华北-北京四”。
说明:本指导以“华北-北京四”为例进行介绍,如果您需要选择其他区域进行操作,请确保所有操作均在同一区域进行。
可用分区
可用区2
产品类型
标准数仓
计算类型
弹性云服务器
存储类型
SSD云盘
CPU架构
X86
节点规格
dws2.m6.4xlarge.8(16 vCPU | 128GB | 2000GB SSD)
说明:如规格售罄,可选择其他可用区或规格。
热数据存储
100GB / 节点
节点数量
3
集群名称
dws-demo
管理员用户
dbadmin
管理员密码
-
确认密码
-
数据库端口
8000
虚拟私有云
vpc-default
子网
subnet-default(192.168.0.0/24)
安全组
自动创建安全组
公网访问
现在购买
宽带
1Mbit/s
高级配置
默认配置
- 信息核对无误,单击“立即购买”,单击“提交”。
- 等待约6分钟,待集群创建成功后,单击集群名称前面的
,弹出集群信息,记录下“公网访问地址”,例如dws-demov.dws.huaweicloud.com。
步骤二:使用Data Studio连接集群
- 请确保客户端主机已安装JDK 1.8.0以上版本,并进入“此电脑 > 属性 > 高级系统设置 > 环境变量”设置JAVA_HOME(例如C:\Program Files\Java\jdk1.8.0_191),并在变量path中添加“;%JAVA_HOME%\bin”。
- 在GaussDB(DWS) 控制台的“连接管理”页面,下载Data Studio客户端。
- 解压下载的Data Studio软件包,进入解压目录后,双击Data Studio.exe启动客户端。
- 在Data Studio主菜单中选择“文件 > 新建连接”,并在弹出框中参照表3所示配置。
表3 Data Studio软件配置 参数名称
配置方式
数据库类型
GaussDB(DWS)
名称
dws-demo
主机
dws-demov.dws.huaweicloud.com
与步骤一:创建集群查询到的“公网访问地址”一致。
端口
8000
数据库
gaussdb
用户名
dbadmin
密码
-
启用SSL
不启用
- 单击“确定”。
步骤三:导入交通卡口样例数据
使用SQL客户端工具连接到集群后,就可以在SQL客户端工具中,执行以下步骤导入交通卡口车辆通行的样例数据并执行查询。
- 执行以下语句,创建traffic数据库。
1
create database traffic encoding 'utf8' template template0;
- 执行以下步骤切换为连接新建的数据库。
- 在Data Studio客户端的“对象浏览器”窗口,右键单击数据库连接名称,在弹出菜单中单击“刷新”,刷新后就可以看到新建的数据库。
- 右键单击“traffic”数据库名称,在弹出菜单中单击“打开连接”。
- 右键单击“traffic”数据库名称,在弹出菜单中单击“打开新的终端”,即可打开连接到指定数据库的SQL命令窗口,后面的步骤,请全部在该命令窗口中执行。
- 执行以下语句,创建用于存储卡口车辆信息的数据库表。
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);
- 创建外表。外表用于识别和关联OBS上的源数据。
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' );
- 执行以下语句,将数据从外表导入到数据库表中。
1
insert into traffic_data.GCJL select * from tpchobs.GCJL_OBS;
导入数据需要一些时间,请耐心等待。
步骤四:车辆分析
- 执行Analyze
用于收集与数据库中普通表内容相关的统计信息,统计结果存储在系统表PG_STATISTIC中。执行计划生成器会使用这些统计数据,以生成最有效的查询执行计划。
执行以下语句生成表统计信息:
1
Analyze;
- 查询数据表中的数据量
执行如下语句,可以查看已加载的数据条数。
1 2
set current_schema= traffic_data; Select count(*) from traffic_data.gcjl;
- 车辆精确查询
执行以下语句,指定车牌号码和时间段查询车辆行驶路线。GaussDB(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;
- 车辆模糊查询
执行以下语句,指定车牌号码和时间段查询车辆行驶路线,GaussDB(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;