All Products
Search
Document Center

Simple Log Service:Analyze logs by using the JDBC API

Last Updated:Feb 29, 2024

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.

Important

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.

    Important

    A database can be connected to only one project at a time.

    table

    The name of the Simple Log Service Logstore.

    Important

    You must specify a 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. 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.