All Products
Search
Document Center

Simple Log Service:Connect to Simple Log Service by using JDBC

Last Updated:Oct 26, 2023

This topic describes how to connect to Simple Log Service by using Java Database Connectivity (JDBC). This topic also describes how to query and analyze logs by using the MySQL protocol and SQL syntax.

Prerequisites

  • A Resource Access Management (RAM) user is created, and the required permissions are granted to the RAM user. For more information, see Create a RAM user and grant permissions to the RAM user.

  • The ALIBABA_CLOUD_ACCESS_KEY_ID and ALIBABA_CLOUD_ACCESS_KEY_SECRET environment variables are configured. For more information, see Configure environment variables.

    Important
    • The AccessKey pair of an Alibaba Cloud account has permissions on all API operations. We recommend that you use the AccessKey pair of a RAM user to call API operations or perform routine O&M.

    • We recommend that you do not save the AccessKey ID or AccessKey secret in your project code. Otherwise, the AccessKey pair may be leaked, and the security of all resources within your account may be compromised.

  • Field indexes are configured for the required fields and Enable Analytics is turned on for the fields. For more information, see Create indexes.

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 Python MySQLdb.

Use scenarios of JDBC:

  • Use visualization tools such as DataV, Tableau, or Grafana to connect to Simple Log Service in compliance with the MySQL protocol. For more information, see Analyze logs by using the JDBC API.

  • Use libraries such as JDBC in Java or MySQLdb in Python to access Simple Log Service and process query results from an application.

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.

Sample log:

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

    Important

    If you use libraries such as JDBC in Java or MySQLdb in Python to access Simple Log Service from an application, you must use an internal endpoint of Simple 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[]){
         // The project name and an internal endpoint of Simple Log Service. You must replace the name and endpoint with actual values. 
         final String endpoint = "trip-demo.cn-hangzhou-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";
         // Configure environment variables. 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,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();
                 }
             }
         }
     }
    }