Analyze logs by using the JDBC API

Supported version

Log Service supports only JDBC 5.1.49.

Parameters

• Syntax

  mysql -hhost -user -password -port
  use database;

• Example

  mysql -hmy-project.cn-hangzhou-intranet.log.aliyuncs.com -ubq****mo86kq -p4f****uZP -P10005
  use my-project; 

• Parameters

  Parameter	Description
  host	The endpoint of Log Service. You must add the project name to the endpoint, for example, my-project.cn-hangzhou-intranet.log.aliyuncs.com. You can connect to Log Service only over the classic network or a virtual private cloud (VPC). For more information, see Endpoints for the classic network and VPC.
  port	The port that you can use to connect to 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 name of the Log Service project.
  table	The name of the Log Service Logstore. Note You must specify the Logstore in a query statement.

Search and analysis 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	Example	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 operation	and or not	Examples: a = "x" and b ="y" or a = "x" and not b ="y".
  Full-text search	__line__ ="abc"	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
  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 analytic statement in the preceding query statement with a time condition expression and include the statement and expression in a WHERE clause as a search condition. This clause complies with the SQL-92 syntax, 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 connect to Log Service. Then, you can use the MySQL syntax to query data in Log Service. You can use the JDBC API or MySQLdb API for Python to connect to Log Service. The following script shows how to use the JDBC API to connect to 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://projectname.cn-hangzhou-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 connect to Log Service
  Use a MySQL client to connect to Log Service over the classic network or a VPC.

  • Enter your project name at 1.
  • Enter your Logstore name at 2.