Query and analyze logs using SQL statements

You can use MySQL syntax to connect to Log Service in any program that supports a MySQL connector, such as JDBC or Python's MySQLdb. The following code provides a JDBC example:

/**
* 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 endpoint, which includes the Project name and the private network endpoint of Log Service. Replace with your actual endpoint.
     final String endpoint = "trip-demo.cn-hangzhou-intranet.log.aliyuncs.com"; 
     // The default port for JDBC access is 10005.
     final String port = "10005"; 
     // The Log Service Project name.
     final String project = "trip-demo"; 
     // The Log Service Logstore name.
     final String logstore = "ebike";
     // This example obtains the AccessKey ID and AccessKey Secret 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.
         conn = DriverManager.getConnection("jdbc:mysql://"+endpoint+":"+port+"/"+project+"?useSSL=false",accessKeyId,accessKey);
         // Step 3: Create a statement.
         stmt = conn.createStatement();
         // Step 4: Define a query statement to query the number of logs that meet the condition op = 'unlock' on October 11, 2017.
         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.
         ResultSet rs = stmt.executeQuery(sql);
         // Step 6: Extract the query result.
         while(rs.next()){
             // Retrieve by column name
             System.out.print("pv:");
             // Get the pv value from the result.
             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 a command-line tool, run the following command as an administrator to install mysql-connector-python.

pip install mysql-connector-python # For Python 2
pip3 install mysql-connector-python # For Python 3

Create a mysqlQuery.py file and specify the following parameters based on your requirements.

import mysql.connector
import os


def main():
    # User name
    user = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_ID', '')
    # Password
    password = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_SECRET', '')
    # endpoint
    host = 'cn-chengdu-intranet.log.aliyuncs.com'
    # Database name (Project name)
    project = 'aliyun-test-project'
    # Port
    port = 10005
    # Table name (LogStore name)
    logstore = 'logstore-test'
    print("collect database start ")
    # Connect to the database
    cnx = mysql.connector.connect(host=host, user=user, password=password, database=project, 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")
    # Retrieve the query result
    for (column1) in cursor:
        print("{}".format(column1))

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


if __name__ == '__main__':
    main()

Run the following command to query data.

python mysqlQuery.py # For Python 2
python3 mysqlQuery.py # For Python 3

Connect to 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

Switch the database context to your 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'

• The Project is specified in both the host parameter (-h) and the use statement.

• The LogStore is specified as the table name in the select statement.