Using DWS to Query Vehicle Routes at Traffic Checkpoints in Seconds

Video Tutorial

General Procedure

This practice takes about 40 minutes. The basic process is as follows:

1. Making Preparations
2. Step 1: Creating a Cluster
3. Step 2: Importing Sample Data
4. Step 3: Performing Vehicle Analysis

Supported Regions

Table 1 describes the regions where OBS data has been uploaded.

Making Preparations

• You have registered a DWS account and checked the account status before using DWS. The account cannot be in arrears or frozen.
• Obtain the AK/SK of the account by referring to Creating an Access Key.

Step 1: Creating a Cluster

Create a DWS cluster by referring to Creating a Cluster. Select CN-Hong Kong for Region. Select Coupled storage and compute for Version. This practice is for service commissioning. You can choose not to configure an EIP or load balancer.

CN-Hong Kong is used as an example. You can select another region as required. Ensure that all operations are performed in the same region.

Step 2: Importing Sample Data

After connecting to the cluster using the SQL client tool, perform the following operations on the SQL client tool to import the sample data from traffic checkpoints and perform data queries.

1. In service commissioning, you are advised to use the SQL Editor to connect to the cluster. For details, see Using the SQL Editor to Connect to a Cluster.

   You can also use other methods (such as the command-line tool gsql) to connect to the cluster. For details, see Connecting to a DWS Cluster.

2. In the SQL window of the SQL editor, run the following statement to create the traffic database:

   1	CREATE DATABASE traffic encoding 'utf8' template template0;

3. On the SQL editor page, switch to the new traffic database in the upper part.

   

4. In the new database, run the following statement to create a table:

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

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

5. Create a foreign table, which is used to identify and associate the source data on OBS.
   

   • <obs_bucket_name> indicates the OBS bucket name corresponding to the region where DWS is located. For details about supported regions, see Supported Regions. The OBS bucket and sample data have been preset in the system, and you do not need to create them. In this manual, the CN-Hong Kong region is used and set <obs_bucket_name> to dws-demo-ap-southeast-1. OBS bucket data cannot be accessed across regions. For example, if the cluster is located in CN-Hong Kong, you cannot set <obs_bucket_name> to the bucket name of another region.

   • Replace <Access_Key_Id> and <Secret_Access_Key> with the actual values obtained in Making Preparations.
   • Hard-coded or plaintext AK/SK is risky. For security, encrypt your AK/SK and store them in the configuration file or environment variables.

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

   DROP FOREIGN table if exists GCJL_OBS; CREATE FOREIGN TABLE GCJL_OBS ( like 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' );

6. Import data from a foreign table to a database table.

   1	INSERT INTO GCJL SELECT * FROM GCJL_OBS;

   It takes some time to import data.

Step 3: Performing Vehicle Analysis

1. Execute ANALYZE.

   This statement collects statistics related to ordinary tables in databases. The statistics are saved to the system catalog PG_STATISTIC. When you run the planner, the statistics help you develop an efficient query execution plan.

   Execute the following statement to generate the table statistics:

   1

   ANALYZE;

2. Querying the data volume of the data table

   Execute the following statement to query the number of loaded data records:

   1	SELECT count(*) FROM gcjl;

3. Accurate vehicle query

   Run the following statements to query the driving route of a vehicle by the license plate number and time segment. DWS responds to the request in seconds.

   1 2 3 4 5

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

4. Fuzzy vehicle query

   Run the following statements to query the driving route of a vehicle by the license plate number and time segment. DWS responds to the request in seconds.

   1 2 3 4 5 6

   SELECT hphm, kkbh, gcsj FROM 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;