Simple Log Service:Use SQL statements to query and analyze logs

Use a program that supports the MySQL connector to connect to Simple Log Service, such as JDBC and Python MySQLdb. Then, use the MySQL syntax to query data in Simple Log Service. In this example, JDBC is used.

/**
* Created by mayunlei on 2017/6/19.
*/
import com.mysql.jdbc.*;
import java.sql.*;
import java.sql.Connection;
import java.sql.Statement;
/**
* Created by mayunlei on 2017/6/15.
*/
public class CollectTest {
 public static void main(String args[]){
     // The project name and an internal endpoint of Simple Log Service. Replace the name and endpoint with actual values. 
     final String endpoint = "trip-demo.cn-hangzhou-intranet.log.aliyuncs.com"; 
     // The port that is used for JDBC-based access. The default port is 10005. 
     final String port = "10005"; 
     // The name of the Simple Log Service project. 
     final String project = "trip-demo"; 
     // The name of the Simple Log Service Logstore. 
     final String logstore = "ebike";
     // In this example, the AccessKey ID and AccessKey secret are obtained from environment variables. 
     final String accessKeyId = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_ID");  
     final String accessKey = System.getenv("ALIBABA_CLOUD_ACCESS_KEY_SECRET");  
     Connection conn = null;
     Statement stmt = null;
     try {
         // Step 1: Load the JDBC driver. 
         Class.forName("com.mysql.jdbc.Driver");
         // Step 2: Create a connection string. 
         conn = DriverManager.getConnection("jdbc:mysql://"+endpoint+":"+port+"/"+project+"?useSSL=false",accessKeyId,accessKey);
         // Step 3: Create a statement. 
         stmt = conn.createStatement();
         // Step 4: Specify a query statement to query the number of logs that were generated on October 11, 2017 and whose op field is set to unlock. 
         String sql = "select count(1) as pv from "+logstore+"  " +
                 "where     __date__  >=  '2017-10-11 00:00:00'   " +
                 "     and  __date__  <   '2017-10-12 00:00:00'" +
                 " and     op ='unlock'";
         // Step 5: Execute the query statement. 
         ResultSet rs = stmt.executeQuery(sql);
         // Step 6: Extract the query and analysis results. 
         while(rs.next()){
             // Retrieve by column name
             System.out.print("pv:");
             // Display pv in the results. 
             System.out.print(rs.getLong("pv"));
             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();
             }
         }
     }
 }
}

Query data.

In the command-line tool, run one of the following commands as an administrator to install mysql-connector-python:

pip install mysql-connector-python # Command for Python 2.
pip3 install mysql-connector-python # Command for Python 3.

Create the mysqlQuery.py file and specify the parameters in the following code based on your business requirements.

import mysql.connector
import os


def main():
    # The username.
    user = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_ID', '')
    # The password.
    password = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_SECRET', '')
    # The endpoint.
    host = 'cn-chengdu-intranet.log.aliyuncs.com'
    # The database name.
    project = 'aliyun-test-project'
    # The port number.
    port = 10005
    # The Logstore name.
    logstore = 'logstore-test'
    print("collect database start ")
    # Connect to the database.
    cnx = mysql.connector.connect(host=host, user=user, password=password, database=database, port=port)
    print("collect database success ")
    # Create a cursor object.
    cursor = cnx.cursor()

    # Execute a query.
    query = "select count(1) as pv from " + logstore + " where __date__  >=  '2024-05-27 00:00:00' and  __date__  <   '2024-05-31 00:00:00'"
    print("query data success")
    cursor.execute(query)
    print("query data success")
    # Obtain the query result.
    for (column1) in cursor:
        print("{}".format(column1))

    # Close the cursor and the connection.
    cursor.close()
    cnx.close()


if __name__ == '__main__':
    main()

Run one of the following commands to query data:

python mysqlQuery.py # Command for Python 2.
python3 mysqlQuery.py # Command for Python 3.

Run the following command to connect to Simple Log Service:

mysql -h my-project.host -uuser -ppassword -P port

Example:

mysql -h my-project.cn-hangzhou-intranet.log.aliyuncs.com -ubq****mo86kq -p4f****uZP -P 10005

Run the following command to enter the current project:

use my-project;

Query data.

select count(1) as pv from my-logstore where __date__  >=  '2024-05-27 00:00:00' and  __date__  <   '2024-05-31 00:00:00'

• Replace the values marked 1 and 2 with your project name.

• Replace the value marked 3 with your Logstore name.