This topic describes how to use Log Service SDK for Python to use the Dedicated SQL feature.

Prerequisites

Log Service SDK for Python V2.1.2 or later is installed. For more information, see Install Log Service SDK for Python.

Background information

Log Service provides the Dedicated SQL feature to enhance SQL analysis capabilities. You can use this feature to process hundreds of billions of rows of data. For more information, see Enable Dedicated SQL.
Log Service provides the execute_logstore_sql and execute_project_sql operations. You can call these operations to use the Dedicated SQL feature in an efficient manner.
  • execute_logstore_sql: uses the Dedicated SQL feature in a specified Logstore. This API operation supports the SQL-92 syntax. A query statement is in the Search statement|Analytic statement format and the analytic statement follows the standard SQL-92 syntax.
  • execute_project_sql: uses the Dedicated SQL feature in a specified project. This API operation supports the SQL-92 syntax. You must specify a filter condition and time range in the WHERE clause of an SQL statement.
Note If you want to filter data before you analyze the data, we recommend that you call the execute_logstore_sql operation and specify a query statement that is in the Search statement|Analytic statement format. This improves analysis efficiency.

Sample code that is used to use the Dedicated SQL feature

The following sample code shows how to use the Dedicated SQL feature. For more information, see Alibaba Cloud Log Service SDK for Python.

# encoding: utf-8
from __future__ import print_function

import time

from aliyun.log import *


def main():
    # The Log Service endpoint. For more information, see Endpoints. In this example, the Log Service endpoint for the China (Hangzhou) region is used. Replace the parameter value with the actual endpoint. 
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    # The AccessKey pair that is used to access Log Service. For more information, see AccessKey pair. An Alibaba Cloud account has permissions to call all API operations. If you use the AccessKey pair of your Alibaba Cloud account, your resources are exposed to high security risks. We recommend that you log on as a RAM user that has permissions to call API operations or perform routine O&M tasks. 
    access_key_id = 'your_access_id'
    access_key = 'your_access_key'
    # The name of the project. 
    project_name = 'aliyun-test-project'
    # The name of the Logstore. 
    logstore_name = 'aliyun-test-logstore'

    # Create a Log Service client. 
    client = LogClient(endpoint, access_key_id, access_key)

    # Execute an SQL statement in the specified Logstore. 
    print("===sample_execute_logstore_sql===")
    res = client.execute_logstore_sql(project_name, logstore_name,
                                      int(time.time() - 60),int(time.time()),
                                      "* | select count(1) as cnt",true)
    # Print the statistics of the analysis result. 
    res.log_print()
    # The number of rows of log data that is processed. 
    print("processed_rows: %s" % res.get_processed_rows())
    # The time that is required to execute the SQL statement. 
    print("elapsed_mills: %s" % res.get_elapsed_mills())
    # Indicates whether an SQL statement is used. 
    print("has_sql: %s" % res.get_has_sql())
    # The WHERE clause that precedes the vertical bar (|). 
    print("where_query: %s" % res.get_where_query())
    # The SELECT statement that follows the vertical bar (|). The SELECT statement contains an aggregate function. 
    print("agg_query: %s" % res.get_agg_query())
    # The CPU time that is consumed to execute the SQL statement after the Dedicated SQL feature is enabled. Unit: seconds. You are charged for the CPU time that is consumed when you use the Dedicated SQL feature to perform query and analysis operations. For more information, see Billable items. 
    print("cpu_sec: %s" % res.get_cpu_sec())
    # The number of CPU cores that are used to execute the SQL statement after the Dedicated SQL feature is enabled. 
    print("cpu_cores: %s" % res.get_cpu_cores())


    # Execute an SQL statement in the specified project. 
    print("===sample_execute_project_sql===")
    res = client.execute_project_sql(project_name,"select count(1) as cnt from %s where __time__ > %s"
                                     % (logstore_name, int(time.time() - 60)),true)
    # Print the statistics of the analysis result. 
    res.log_print()
    # The number of rows of log data that is processed. 
    print("processed_rows: %s" % res.get_processed_rows())
    # The time that is required to execute the SQL statement. 
    print("elapsed_mills: %s" % res.get_elapsed_mills())
    # Indicates whether an SQL statement is used. 
    print("has_sql: %s" % res.get_has_sql())
    # The WHERE clause that precedes the vertical bar (|). 
    print("where_query: %s" % res.get_where_query())
    # The SELECT statement that follows the vertical bar (|). The SELECT statement contains an aggregate function. 
    print("agg_query: %s" % res.get_agg_query())
    # The CPU time that is consumed to execute the SQL statement after the Dedicated SQL feature is enabled. Unit: seconds. You are charged for the CPU time that is consumed when you use the Dedicated SQL feature to perform query and analysis operations. For more information, see Billable items. 
    print("cpu_sec: %s" % res.get_cpu_sec())
    # The number of CPU cores that are used to execute the SQL statement after the Dedicated SQL feature is enabled. 
    print("cpu_cores: %s" % res.get_cpu_cores())


if __name__ == '__main__':
    main()
  • execute_logstore_sql operation
    You can call the execute_logstore_sql operation to use the Dedicated SQL feature. Requests must be in the res=client.execute_logstore_sql (project, logstoreName, from, to, query, powerSql) format. The following table describes the request parameters.
    Parameter Type Required Example Description
    project_name String Yes N/A The name of the project.

    When you create a Log Service client, you must specify a value for the project_name parameter. Therefore, you do not need to set the parameter again.

    logstore_name String Yes N/A The name of the Logstore.

    When you create a Log Service client, you must specify a value for the logstore_name parameter. Therefore, you do not need to set the parameter again.

    from Long Yes int(time.time() - 60) The start time of the time range that is specified in the request. The start time is a timestamp that follows the UNIX time format. It is the number of seconds that have elapsed since 00:00:00 UTC, Thursday, January 1, 1970.
    to Long Yes int(time.time()) The end time of the time range that is specified in the request. The end time is a timestamp that follows the UNIX time format. It is the number of seconds that have elapsed since 00:00:00 UTC, Thursday, January 1, 1970.
    query String Yes "* | select count(1) as cnt" The query statement. Format: Search statement|Analytic statement. For more information, see Syntax.

    By default, Log Service returns 100 rows of data. You can use a LIMIT clause to specify the number of rows of data to return. For more information, see LIMIT clause.

    powerSql Boolean No true Specifies whether to use the Dedicated SQL feature. For more information, see Enable Dedicated SQL.
    • true: The Dedicated SQL feature is used.
    • false: The Standard SQL feature is used. This is the default value.
  • execute_project_sql operation

    You can call the execute_project_sql operation to use the Dedicated SQL feature. Requests must be in the res=client.execute_project_sql (project, query, powerSql) format. The following table describes the request parameters.

    Parameter Type Required Example Description
    project_name String Yes aliyun-test-project The name of the project.

    When you create a Log Service client, you must specify a value for the project_name parameter. Therefore, you do not need to set the parameter again.

    query String Yes "select count(1) as cnt from %s where __time__ > %s" % (logstore_name, int(time.time() - 60)) An SQL statement in which the search condition must be specified in the WHERE clause.

    By default, Log Service returns 100 rows of data. You can use a LIMIT clause to specify the number of rows of data to return. For more information, see LIMIT clause.

    powerSql Boolean No true Specifies whether to use the Dedicated SQL feature. For more information, see Enable Dedicated SQL.
    • true: The Dedicated SQL feature is used.
    • false: The Standard SQL feature is used. This is the default value.

Sample code that is used to create and update an SQL instance and obtain the configurations of the SQL instance

After the Dedicated SQL feature is enabled, you can create an SQL instance to specify the number of CUs. You can modify the number of CUs to control the cost of the Dedicated SQL feature. The following sample code shows how to create and update an SQL instance and obtain the configurations of the SQL instance. For more information, see Alibaba Cloud Log Service SDK for Python.

# encoding: utf-8
from __future__ import print_function

import time

from aliyun.log import *

def main():
    # The Log Service endpoint. For more information, see Endpoints. In this example, the Log Service endpoint for the China (Hangzhou) region is used. Replace the parameter value with the actual endpoint. 
    endpoint = 'cn-hangzhou.log.aliyuncs.com'
    # The AccessKey pair that is used to access Log Service. For more information, see AccessKey pair. An Alibaba Cloud account has permissions to call all API operations. If you use the AccessKey pair of your Alibaba Cloud account, your resources are exposed to high security risks. We recommend that you log on as a RAM user that has permissions to call API operations or perform routine O&M tasks. 
    access_key_id = 'your_access_id'
    access_key = 'your_access_key'
    # The name of the project. 
    project_name = 'aliyun-test-project'
    # The name of the Logstore. 
    logstore_name = 'aliyun-test-logstore'

    # Create a Log Service client. 
    client = LogClient(endpoint, access_key_id, access_key)

    # Create an SQL instance. You can specify the number of CUs for the SQL instance. The number of CUs can be from 0 to 1000. 
    print("===sample_create_sql_instance===")
    res = client.create_sql_instance(project_name, 500)
    res.log_print()

    # Update the SQL instance. You can specify the number of CUs for the SQL instance. The number of CUs can be from 0 to 1000. 
    print("===sample_update_sql_instance===")
    res = client.update_sql_instance(project_name, 800)
    res.log_print()

    # Obtain the configurations of the SQL instance. 
    print("===sample_list_sql_instance===")
    res = client.list_sql_instance(project_name)
    res.log_print()


if __name__ == '__main__':
    main()