You can use the Java Database Connectivity (JDBC) API to connect a database such as a MySQL database to Log Service. Then, you can use the SQL-92 syntax to query and analyze log data.

Prerequisites

  • An AccessKey pair is created for an Alibaba Cloud account or a RAM user. For more information, see AccessKey pair.

    If you use the AccessKey pair of a RAM user, the RAM user must belong to the Alibaba Cloud account to which the project that you want to connect belongs. The RAM user must be granted the read permissions on the project.

  • A Logstore is created. For more information, see Create a Logstore.

Supported version

Log Service supports only JDBC 5.1.49.

Parameters

This section describes the parameters that you must set if you want to connect a MySQL database to Log Service.
Note If you connect a MySQL database to Log Service by using the JDBC API and then query data, the query result cannot be paginated.
  • 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. Example
    • Enter your project name at 1.
    • Enter your Logstore name at 2.