Connect to Log Service by using JDBC

Sample log

Each log of a bike-sharing service contains information about a user. The information includes the age, gender, battery usage, bike ID, operation latency, latitude, lock type, longitude, operation type, operation result, and unlocking type. The logs are stored in the Logstore:ebike Logstore of the project:trip_demo project. The project resides in the China (Hangzhou) region.

Time :10-12 14:26:44
__source__: 192.168.0.0
__topic__: v1 
age: 55 
battery: 118497.673842 
bikeid: 36 
gender: male 
latency: 17 
latitude: 30.2931185245 
lock_type: smart_lock 
longitude: 120.052840484 
op: unlock 
op_result: ok 
open_lock: bluetooth 
userid: 292

JDBC-based query and analysis

1. Create a Maven project and add the following JDBC dependency to the pom.xml file:

   <dependency>
    <groupId>MySQL</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.6</version>
   </dependency>

2. Create a Java program to query logs by using JDBC. The following sample code shows how to create a Java program to query logs by using JDBC.
   You must include the __date__ or __time__ field in a WHERE clause to limit the time range of a query. The data type of the __date__ field is timestamp, and the data type of the __time__ field is bigint. Examples:

   • __date__ > '2017-08-07 00:00:00' and __date__ < '2017-08-08 00:00:00'
   • __time__ > 1502691923 and __time__ < 1502692923

   Notice If you use libraries such as JDBC in Java or MySQLdb in Python to access Log Service from an application, you must use an internal endpoint of Log Service. The internal endpoint is accessible over the classic network or a virtual private cloud (VPC). If you use a different endpoint, the following error occurs: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure,Caused by: java.net.ConnectException: Connection timed out: connect.

   /**
   * Created by mayunlei on 2017/6/19.
   */
   import com.mysql.jdbc.*;
   import java.sql.*;
   import java.sql.Connection;
   import java.sql.Statement;
   /**
   * Created by mayunlei on 2017/6/15.
   */
   public class jdbc {
    public static void main(String args[]){
        final String endpoint = "trip-demo.cn-hangzhou-intranet.log.aliyuncs.com"; // The project name and an internal endpoint of Log Service. You must replace the name and endpoint with actual values. For more information, see Endpoints.     
        final String port = "10005"; // The port that is used for JDBC-based access. The default port is 10005. 
        final String project = "trip-demo"; // The name of the project. 
        final String logstore = "ebike"; // The name of the Logstore. 
        final String accessKeyId = "";  // The AccessKey ID of your Alibaba Cloud account. For more information, see AccessKey pair. 
        final String accessKey = "";  // The AccessKey secret of your Alibaba Cloud account. 
        Connection conn = null;
        Statement stmt = null;
        try {
            // Step 1: Load the JDBC driver. 
            Class.forName("com.mysql.jdbc.Driver");
            // Step 2: Create a connection string. 
            conn = DriverManager.getConnection("jdbc:mysql://"+endpoint+":"+port+"/"+project,accessKeyId,accessKey);
            // Step 3: Create a statement. 
            stmt = conn.createStatement();
            // Step 4: Define a query statement to query the number of logs that were generated on October 11, 2017 and whose op field is set to unlock. 
            String sql = "select count(1) as pv,avg(latency) as avg_latency from "+logstore+"  " +
                    "where     __date__  >=  '2017-10-11 00:00:00'   " +
                    "     and  __date__  <   '2017-10-12 00:00:00'" +
                    " and     op ='unlock'";
            // Step 5: Execute the query statement. 
            ResultSet rs = stmt.executeQuery(sql);
            // Step 6: Extract the query result. 
            while(rs.next()){
                //Retrieve by column name
                System.out.print("pv:");
                // Display pv in the result. 
                System.out.print(rs.getLong("pv"));
                System.out.print(" ; avg_latency:");
                // Display avg_latency in the result. 
                System.out.println(rs.getDouble("avg_latency"));
                System.out.println();
            }
            rs.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
   }