Connect to Simple Log Service by using JDBC

Background information

MySQL is a popular relational database service. A large number of software products can obtain MySQL data by using the MySQL protocol and SQL syntax. To connect to Simple Log Service by using JDBC, you must be familiar with the SQL syntax. Simple Log Service allows you to query and analyze logs based on the MySQL protocol. You can use a standard MySQL client to connect to Simple Log Service. Then, you can use standard SQL syntax to query and analyze logs. The following clients support the MySQL protocol: MySQL client, JDBC, and MySQL Connector.

Limits

• Simple Log Service supports only JDBC 5.1.49.

• If you use drivers such as JDBC in Java or MySQL Connector in Python to access Simple Log Service, you must use only an internal endpoint of Simple Log Service. An internal endpoint can be accessed over the classic network or a virtual private cloud (VPC) of Alibaba Cloud. For more information, see Endpoints. 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.

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 ebike logstore of the trip-demo project. The project resides in the Singapore 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 of the project. Sample code:

   <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</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.

   Important

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

   • JDBC connection format: jdbc:mysql://{project}.{endpoint}:10005/{project}. For more information about {project} and {endpoint}, see Parameter description.

   /**
   * Created by xxx on 2017/6/19.
   */
   import com.mysql.jdbc.*;
   import java.sql.*;
   import java.sql.Connection;
   import java.sql.Statement;
   /**
   * Created by xxx on 2017/6/15.
   */
   public class CollectTest {
    public static void main(String args[]){
        // The project name and an internal endpoint of Simple Log Service. Replace the name and endpoint with actual values. 
        final String endpoint = "ap-southeast-1-intranet.log.aliyuncs.com"; 
        // The port that is used for JDBC-based access. The default port is 10005. 
        final String port = "10005"; 
        // The name of the Simple Log Service project. 
        final String project = "trip-demo"; 
        // The name of the Simple Log Service logstore. 
        final String logstore = "ebike";
        // In this example, the AccessKey ID and AccessKey secret are obtained from environment variables. 
        final String accessKeyId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");  
        final String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");  
        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+"?useSSL=false",accessKeyId,accessKey);
            // Step 3: Create a statement. 
            stmt = conn.createStatement();
            // Step 4: Specify 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 and analysis results. 
            while(rs.next()){
                // Retrieve by column name
                System.out.print("pv:");
                // Display pv in the results. 
                System.out.print(rs.getLong("pv"));
                System.out.print(" ; avg_latency:");
                // Display avg_latency in the results. 
                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();
                }
            }
        }
    }
   }