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.
ImportantThe 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
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>
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
ImportantIf 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(); } } } } }