Analyze logs by using JDBC

Prerequisites

Connection parameters

mysql -hcn-shanghai-intranet.log.aliyuncs.com -ubq2sjzesjmo86kq -p4fdO1fTDDuZP -P10005
use sample-project; // Use a project.

Syntax

• Filter syntax
  The following table describes the filter syntax in a WHERE clause.

  Semantics	Example	Description
  String search	key = "value"	Queries data after word-breaking.
  String fuzzy search	key = "valu*"	Queries data in fuzzy match mode after word segmentation.
  Value comparison	num_field > 1	Supported comparison operators include >, >=, =, <, and <=.
  Logical operations	and or not	Example: a = "x" and b ="y" or 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 the syntax description in Real-time analysis.

• SQL-92 syntax

  SQL-92 syntax is a combination of filter syntax and computation syntax.

  The following query is used as an example:

  status>200 |select avg(latency),max(latency) ,count(1) as c GROUP BY  method  ORDER BY c DESC  LIMIT 20

  According to the standard SQL-92 syntax, the filter and the time condition in the query can be combined into a new query condition as follows:

  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 JDBC

• Calls by using applications

  You can use the MySQL syntax to connect to Log Service in any application that supports MySQL Connector. For example, you can use JDBC or Python MySQLdb.

  Example:

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

• Calls by using tools

  In the classic network or VPC, you can use the MySQL client to connect to Log Service.