Help Center> Data Lake Insight> Best Practices> Data Analysis> Analyzing Driving Behavior Data
Updated on 2023-07-31 GMT+08:00

Analyzing Driving Behavior Data

Application Scenarios

Cloud computing and big data provide companies with data analysis and mining capabilities required in the Internet of Vehicle (IoV) field, helping companies or department of motor vehicles manage and analyze vehicle and driving behavior data quickly and scientifically.

Solution Architecture

DLI can query the records of vehicle driving features based on the detail records and freight order data regularly reported by the freight forwarder.

Data Types describes the data types used by DLI to record the data.

Figure 1 Solution Overview

Process

To use DLI to analyze driving behavior data, perform the following steps:

Step 1: Uploading Data. Upload the data to OBS.

Step 2: Analyzing Data. Use DLI to query the data.

Example Code

Download the data package for sample data and detailed SQL statements.

Solution Advantages

  • Free of data migration: DLI can interconnect with multiple data sources. You only need to create SQL tables and map data sources.
  • Easy to use: You can use standard SQL statements to compile metric analysis logic without paying attention to the complex distributed computing platform.
  • Pay-per-use: Log analysis is scheduled periodically based on time-critical requirements. There is a long idle period between every two scheduling operations. DLI uses the pay-per-use billing mode, which effectively reduces your costs.

Resource Planning and Costs

Table 1 Resource planning and costs

Resource

Description

Cost

OBS

You need to create an OBS bucket and upload data to OBS for data analysis using DLI.

You will be charged for using the following OBS resources:

  • Storage Fee for storing static website files in OBS.
  • Request Fee for accessing static website files stored in OBS.
  • Traffic Fee for using a custom domain name to access OBS over the public network.

The actual fee depends on the size of the stored file, the number of user access requests, and the traffic volume. Estimate the fee based on your service requirements.

DLI

Before creating a SQL job, you need to purchase a queue. When using queue resources, you are billed based on the CUH of the queue.

For example, if you purchase a pay-per-use queue, you will be billed based on the number of CUHs used by the queue.

Usage is billed by the hour. For example, 58 minutes of usage will be rounded to the hour. CUH pay-per-use billing = Unit price x Number of CUs x Number of hours.

Data Types

  • Detail records

    Detail records include the regularly reported location records and data of alarms triggered by abnormal driving behavior.

    Table 2 Detail records

    Field

    Data Type

    Description

    driverID

    string

    Driver ID

    carNumber

    string

    License plate number

    latitude

    double

    Latitude

    longitude

    double

    Longitude

    speed

    int

    Speed

    direction

    int

    Direction

    siteName

    string

    Site name

    time

    timestamp

    Report time of the records

    isRapidlySpeedup

    int

    Whether the vehicle rapidly speeds up. 1 indicates that the vehicle suddenly speeds up, and 0 indicates that the vehicle does not.

    isRapidlySlowdown

    int

    Whether the vehicle suddenly slows down.

    isNeutralSlide

    int

    Whether the vehicle is coasting.

    isNeutralSlideFinished

    int

    Whether vehicle coasting has stopped.

    neutralSlideTime

    bigint

    Time length of vehicle coasting.

    isOverspeed

    int

    Whether the vehicle is speeding.

    isOverspeedFinished

    int

    Whether the vehicle stops speeding.

    overspeedTime

    bigint

    Duration of the vehicle speeding

    isFatigueDriving

    int

    Whether fatigue driving occurs.

    isHthrottleStop

    int

    Whether the driver revs the engine in neutral.

    isOilLeak

    int

    Abnormal oil consumption

  • Order data

    Order data refers to the records of freight orders.

    Table 3 Order data

    Field

    Data Type

    Description

    orderNumber

    string

    Order ID

    driverID

    string

    Driver ID

    carNumber

    string

    License plate number

    customerID

    string

    Customer ID

    sourceCity

    string

    Departure

    targetCity

    string

    Destination

    expectArriveTime

    timestamp

    Expected delivery time

    time

    timestamp

    Time when a record is generated.

    action

    string

    Event type, including creating an order, dispatching goods, delivering packages, and signing orders.

Step 1: Uploading Data

Upload the data to OBS for data analysis using DLI.
  1. Download OBS Browser+. For details about the download address, see Object Storage Service Tool Guide.
  2. Install OBS Browser+. For details about the installation procedure, see Object Storage Service Tool Guide.
  3. Log in to OBS Browser+. OBS Browser+ supports two login modes: AK login (using access keys) or authorization code login. For details about the login procedure, see Object Storage Service Tool Guide.
  4. Upload data using the OBS browser+.

    Start the OBS Browser+, click Create Bucket on the homepage. Select a region and enter a bucket name (for example, DLI-demo). After the bucket is created, return to the bucket list and click DLI-demo. OBS Browser+ supports upload by dragging. You can drag one or more files or folders from a local path to the object list of a bucket or a parallel file system on OBS Browser+. You can even drag a file or folder directly to a specified folder on OBS Browser+.

    Obtain the test data by downloading the Best_Practice_01.zip file and decompressing it. Perform the following operations:

    • Detail records: Upload the detail-records folder in the Data directory to the root directory of the OBS bucket.
    • Order data: Upload the order-records folder in the Data directory to the root directory of the OBS bucket.

Step 2: Analyzing Data

Use DLI to query the data for analysis.

  1. Creating a Database and a Table
    1. On the homepage of the management console, choose Service List > Analytics > Data Lake Insight.
    2. On the DLI console, click SQL Editor.
    3. In the left pane of the SQL Editor, select the Databases tab and click to create the demo database.
      Figure 2 Creating a database

      Database Name cannot be set to default because default is the built-in database.

    4. Choose the demo database, and enter the following SQL statement in the editing box:
      create table detail_records(
        driverID String,
        carNumber String, 
        latitude double,
        longitude double,
        speed int,
        direction int,
        siteName String,
        time timestamp,
        isRapidlySpeedup int,
        isRapidlySlowdown int,
        isNeutralSlide int,
        isNeutralSlideFinished int,
        neutralSlideTime long,
        isOverspeed int,
        isOverspeedFinished int,
        overspeedTime long,
        isFatigueDriving int,
        isHthrottleStop int,
        isOilLeak int) USING CSV OPTIONS (PATH 'obs://dli-demo/detail-records/');

      Replace the file path in the preceding statement with the actual OBS path where the detail records are stored.

    5. Click Execute to create the detail_records table. See Figure 3.
      Figure 3 Creating the detail_records table
    6. Run the following SQL statements to create the event_records table in the demo database. The operation is similar to 1.d and 1.e.
      create table event_records(
        driverID String,
        carNumber String, 
        latitude double,
        longitude double,
        speed int,
        direction int,
        siteName String,
        time timestamp,
        isRapidlySpeedup int,
        isRapidlySlowdown int,
        isNeutralSlide int,
        isNeutralSlideFinished int,
        neutralSlideTime long,
        isOverspeed int,
        isOverspeedFinished int,
        overspeedTime long,
        isFatigueDriving int,
        isHthrottleStop int,
        isOilLeak int) 
    7. Run the following SQL statements to extract the alarm and event data from the detail records and insert it into the event_records table.
      insert into table event_records
      (select *
      from detail_records
      where isRapidlySpeedup > 0
      OR isRapidlySlowdown > 0
      OR isNeutralSlide > 0
      OR isNeutralSlideFinished > 0
      OR isOverspeed > 0
      OR isOverspeedFinished > 0
      OR isFatigueDriving > 0
      OR isHthrottleStop > 0
      OR isOilLeak > 0)
    8. Use another method to create the order_records table.
      On the left of the SQL job editor, click the Databases tab and click the demo database. Click the plus icon (+) on the right of Table to create a table, and set Data Location to DLI. Set the column types according to Order data.
      Figure 4 Creating the order_records table
    9. Import the OBS data to the order_records table. Choose Data Management > Databases and Tables. Click the demo database to go to the table management page. In the Operation column of the order_records table, choose More > Import. Set File Format to CSV, the data storage path to obs://DLI-demo/order-records/, and retain default values for the rest parameters. Click OK.

      The default timestamp format is yyyy-MM-dd HH:mm:ss. To use other formats, select Advanced Settings and enter the desired timestamp format (not modified in this example).

      Figure 5 Importing table data
  2. Querying Data
    1. Run the following SQL statements to query the alarm events of all drivers in a certain time period.

      You can save the frequently-used query statements as a template by clicking More > Save as Template in the upper right corner of the editing window. The template is available for future use or can be modified in the SQL editor again.

      Choose Job Templates > SQL Templates and click the Custom Templates tab. In the Operation column of the target template, click Execute to switch to the SQL editor. You can modify it as needed.

      select
        driverID,
        carNumber,
        sum(isRapidlySpeedup) as rapidlySpeedupTimes,
        sum(isRapidlySlowdown) as rapidlySlowdownTimes,
        sum(isNeutralSlide) as neutralSlideTimes,
        sum(neutralSlideTime) as neutralSlideTimeTotal,
        sum(isOverspeed) as overspeedTimes,
        sum(overspeedTime) as overspeedTimeTotal,
        sum(isFatigueDriving) as fatigueDrivingTimes,
        sum(isHthrottleStop) as hthrottleStopTimes,
        sum(isOilLeak) as oilLeakTimes
      from
        event_records
      where
        time >= "2017-01-01 00:00:00"
        and time <= "2017-02-01 00:00:00"
      group by
        driverID,
        carNumber
      order by
        rapidlySpeedupTimes desc,
        rapidlySlowdownTimes desc,
        neutralSlideTimes desc,
        neutralSlideTimeTotal desc,
        overspeedTimes desc,
        overspeedTimeTotal desc,
        fatigueDrivingTimes desc,
        hthrottleStopTimes desc,
        oilLeakTimes desc

      In the query result, click to view graphical results.

      • Set Graph Type to the bar chart.
      • Set X-AXIS to driverID.
      • Set Y-AXIS to rapidlySpeedupTimes.
      • Set Results to 10.

      The command output is as follows:

      Figure 6 Rapid acceleration
    2. Run the following SQL statement to query the detailed record of a driver in a certain time period.
      select
        *
      from
        event_records
      where
        driverID = "panxian1000005"
        and time >= "2017-01-01 00:00:00"
        and time <= "2017-02-01 00:00:00"

      In the query result, click to view graphical results.

      • Set Graph Type to the bar chart.
      • Set X-AXIS to driverID.
      • Set Y-AXIS to speed.
      • Set Results to 10.

      The command output is as follows:

      Figure 7 Speeding record
    3. Run the following SQL statement to query the order information.
      select
        *
      from
        order_records
      where
        orderNumber = "2017013013584419488"
      order by
        time desc
      Figure 8 Order information
    4. Run the following SQL statement to query a vehicle's driving feature according to the driver ID and time of departure.
      select
        driverID,
        carNumber,
        latitude,
        longitude,
        siteName,
        time
      from
        detail_records
      where
        driverID = "panxian1000005"
        and time > "2017-01-30 16:00:00"
        and siteName IS NOT NULL
      order by
        time desc

      In the query result, click to view graphical results.

      • Set Graph Type to the bar chart.
      • Set X-AXIS to time.
      • Set Y-AXIS to latitude.
      • Set Results to 10.

      The command output is as follows:

      Figure 9 Driving information