You can use the JDBC API to connect a database such as a MySQL database to Log Service and use the ANSI SQL-92 syntax to query logs.
Prerequisites
- An AccessKey pair is created for an Alibaba Cloud account or a RAM user.
If you use an AccessKey pair of a RAM user, the RAM user must belong to the Alibaba Cloud account to which the project you want to connect belongs. In addition, the RAM user must be granted read permissions on the project.
- A project and a Logstore are created. For more information, see Step 1: Create a project and a Logstore.
Parameters
This section describes the parameters that are used to connect a MySQL database to Log Service.
mysql -hcn-shanghai-intranet.log.aliyuncs.com -ubq2sjzesjmo86kq -p4fdO1fTDDuZP -P10005
use sample-project;
The following table describes the parameters in the command.
Parameter | Description |
---|---|
host | The endpoint of Log Service. For more information, see Endpoints. You can access Log Service over the classic network or a virtual private cloud (VPC). An example of a VPC-type endpoint is cn-hangzhou-intranet.log.aliyuncs.com. |
port | The port used to access Log Service. Default value: 10005. |
user | The AccessKey ID of your Alibaba Cloud account. |
password | The AccessKey secret of your Alibaba Cloud account. |
database | The Log Service project. |
table | The Logstore. |
Query syntax
- Filtering syntax
Note 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
The following table describes the filtering syntax of a WHERE clause.Semantics Examples Description String search key = "value" Queries data after word-delimiting. String fuzzy search - key has 'valu*'
- key like 'value_%'
Queries data in fuzzy match mode after word-delimiting. Value comparison num_field > 1 The comparison operators include greater than (>), greater than or equal to (>=), equal to (=), less than (<), and less than or equal to (<=). Logical operations and or not Examples: a = "x" and b ="y" and a = "x" and not b ="y". Full-text search __line__ ="abc" Requires the special key (__line__). - Computation syntax
For more information about supported computation operators, see Real-time analysis.
- SQL-92 syntax
The SQL-92 syntax includes the filtering syntax and calculation syntax. Example:
status>200 |select avg(latency),max(latency) ,count(1) as c GROUP BY method ORDER BY c DESC LIMIT 20
You can combine the filtering condition expression in the preceding statement with a time condition expression and include the expressions in a WHERE clause. This clause follows the SQL-92 syntax standard, as shown in the following statement: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
Access Log Service by using the JDBC API
- Use an application to query data in Log Service
You can use an application that supports the MySQL connector to access Log Service and use the MySQL syntax to query data in Log Service. You can use the JDBC API or MySQLdb API to access Log Service. The following script shows how to use the JDBC API to access 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 2: Register JDBC driver Class.forName("com.mysql.jdbc.Driver"); //STEP 3: Open a connection System.out.println("Connecting to a selected database..."); conn = DriverManager.getConnection("jdbc:mysql://cn-shanghai-intranet.log.aliyuncs.com:10005/sample-project","accessid","accesskey"); System.out.println("Connected database successfully...") ; //STEP 4: 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 5: 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 access Log Service
Use a MySQL client to access Log Service over the classic network or a VPC.
- Enter your project name at 1.
- Enter your Logstore name at 2.