You can use the Java Database Connectivity (JDBC) API to connect a database such as a MySQL database to Simple Log Service. Then, you can use the SQL-92 syntax to query and analyze logs.
Prerequisites
An AccessKey pair is created for an Alibaba Cloud account or a RAM user. For more information, see AccessKey pair.
If you want to use the AccessKey pair of a RAM user, make sure that the RAM user belongs to the Alibaba Cloud account of your project and the RAM user is granted the read permissions on the project. For more information, see Configure the permission assistant feature.
A Logstore is created. For more information, see Create a Logstore.
Supported version
Simple Log Service supports only JDBC 5.1.49.
Parameters
This section describes the parameters that you must configure if you want to connect a MySQL database to Simple Log Service.
If you query data in a MySQL database that is connected to Simple Log Service by using the JDBC API, the query result cannot be paginated.
Syntax
mysql -h host -u user -p password -P port use database;
Example
mysql -h my-project.cn-hangzhou-intranet.log.aliyuncs.com -u bq****mo86kq -p 4f****uZP -P 10005 use my-project;
Parameter description
Parameter
Description
host
The Simple Log Service endpoint. You must include a project name in the endpoint. You must enter an endpoint in the
Project name.Internal endpoint
format. Example: my-project.cn-hangzhou-intranet.log.aliyuncs.com.Only an internal endpoint that is accessible over the classic network or a virtual private cloud (VPC) is supported. For more information, see Internal Simple Log Service endpoints.
port
The port that you can use to connect to Simple Log Service. Default value: 10005.
user
The AccessKey ID of your Alibaba Cloud account. We recommend that you use the AccessKey pair of a RAM user.
password
The AccessKey secret of your Alibaba Cloud account. We recommend that you use the AccessKey pair of a RAM user.
database
The name of the Simple Log Service project.
ImportantA database can be connected to only one project at a time.
table
The name of the Simple Log Service Logstore.
ImportantYou must specify a Logstore in a query statement.
Search and analysis syntax
Filtering syntax
NoteYou 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
The following table describes the filtering syntax of a WHERE clause.
Semantics
Example
Description
String search
key = "value"
Queries data after tokenization.
Fuzzy string search
key has 'valu*'
key like 'value_%'
Queries data in fuzzy match mode after tokenization.
Numeric value comparison
num_field > 1
Queries data by using comparison operators. The following comparison operators are supported: greater-than (>), greater-than-or-equal-to (>=), less-than (<), less-than-or-equal-to (<=), and equal-to (=).
Logical operation
and or not
Queries data based on logical operations. Examples: a = "x" and b ="y" and a = "x" and not b ="y".
Full-text search
__line__ ="abc"
Queries data based on full-text search. If you perform a full-text search, you must use the __line__ key.
Calculation syntax
Calculation operators are supported. For more information, see Log analysis overview.
SQL-92 syntax
In the following example, the query statement uses the SQL-92 syntax, which includes filtering and calculation syntax.
status>200 |select avg(latency),max(latency) ,count(1) as c GROUP BY method ORDER BY c DESC LIMIT 20
In the following example, the SQL statement uses the standard SQL-92 syntax, which includes the analytic statement in the preceding query statement and a time condition.
select avg(latency),max(latency) ,count(1) as c from sample-logstore where status>200 and __time__>=1500975424 and __time__ < 1501035044 GROUP BY method ORDER BY c DESC LIMIT 20
Connect to Simple Log Service by using the JDBC API
Use an application to query data in Simple Log Service
You can use an application that supports the MySQL connector to connect to Simple Log Service. Then, you can use the MySQL syntax to query data in Simple Log Service. You can use the JDBC API or MySQLdb API for Python to connect to Simple Log Service. The following script shows how to use the JDBC API to connect to Simple Log Service:
import com.mysql.jdbc.*; import java.sql.*; import java.sql.Connection; import java.sql.ResultSetMetaData; import java.sql.Statement; public class testjdbc { public static void main(String args[]){ Connection conn = null; Statement stmt = null; try { //STEP 1: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); //STEP 2: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection("jdbc:mysql://projectname.cn-hangzhou-intranet.log.aliyuncs.com:10005/sample-project","accessid","accesskey"); System.out.println("Connected database successfully..."); //STEP 3: Execute a query System.out.println("Creating statement..."); stmt = conn.createStatement(); String sql = "SELECT method,min(latency,10) as c,max(latency,10) from sample-logstore where __time__>=1500975424 and __time__ < 1501035044 and latency > 0 and latency < 6142629 and not (method='Postlogstorelogs' or method='GetLogtailConfig') group by method " ; String sql_example2 = "select count(1) ,max(latency),avg(latency), histogram(method),histogram(source),histogram(status),histogram(clientip),histogram(__source__) from test10 where __date__ >'2017-07-20 00:00:00' and __date__ <'2017-08-02 00:00:00' and __line__='abc#def' and latency < 100000 and (method = 'getlogstorelogS' or method='Get**' and method <> 'GetCursorOrData' )"; String sql_example3 = "select count(1) from sample-logstore where __date__ > '2017-08-07 00:00:00' and __date__ < '2017-08-08 00:00:00' limit 100"; ResultSet rs = stmt.executeQuery(sql); //STEP 4: Extract data from result set while(rs.next()){ //Retrieve by column name ResultSetMetaData data = rs.getMetaData(); System.out.println(data.getColumnCount()); for(int i = 0;i < data.getColumnCount();++i) { String name = data.getColumnName(i+1); System.out.print(name+":"); System.out.print(rs.getObject(name)); } 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(); } } } } }
Use a tool to connect to Simple Log Service
You can use a MySQL client to connect to Simple Log Service over the classic network or a VPC.
Replace the values marked 1 and 2 with your project name.
Replace the value marked 3 with your Logstore name.