Simple Log Service:Use Simple Log Service SDK for Python to use the Dedicated SQL feature

Sample code to use the Dedicated SQL feature

Operation description

• execute_logstore_sql operation

  You can call the execute_logstore_sql operation to use the Dedicated SQL feature. Create a request 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. This value is specified when you create the Simple Log Service client, so you do not need to configure it again.
  logstore_name	String	Yes	N/A	The name of the logstore. This value is specified when you create the Simple Log Service client, so you do not need to configure it again.
  from	Long	Yes	int(time.time() - 60)	The beginning of the time range to query. The value is a UNIX timestamp representing the number of seconds that have elapsed since the epoch time January 1, 1970, 00:00:00 UTC.
  to	Long	Yes	int(time.time())	The end of the time range to query. The value is a UNIX timestamp representing the number of seconds that have elapsed since the epoch time January 1, 1970, 00:00:00 UTC.
  query	String	Yes	"* | select count(1) as cnt"	The query statement. Format: Search statement|Analytic statement. For more information, see Syntax. By default, Simple Log Service returns 100 rows of data. You can use a LIMIT clause to specify the number of data rows to return. For more information, see LIMIT clause.
  powerSql	Boolean	No	True	Specifies whether to use the Dedicated SQL feature. For more information, see High-performance fully accurate query and analysis (Dedicated SQL). True: The Dedicated SQL feature is used. False (default): The Standard SQL feature is used.

• execute_project_sql operation

  You can call the execute_project_sql operation to use the Dedicated SQL feature. Create a request 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. This value is specified when you create the Simple Log Service client, so you do not need to configure it again.
  query	String	Yes	"select count(1) as cnt from %s where __time__ > %s" % (logstore_name, int(time.time() - 60))	The SQL statement. You must specify the search condition in the WHERE clause of the SQL statement. By default, Simple Log Service returns 100 rows of data. You can use a LIMIT clause to specify the number of data rows to return. For more information, see LIMIT clause.
  powerSql	Boolean	No	True	Specifies whether to use the Dedicated SQL feature. For more information, see High-performance fully accurate query and analysis (Dedicated SQL). True: The Dedicated SQL feature is used. False (default): The Standard SQL feature is used.

Sample code

The following sample code shows how to use the Dedicated SQL feature. For more information, see aliyun-log-python-sdk.

# encoding: utf-8
from __future__ import print_function

import time
import os
from aliyun.log import *


def main():
    # Specify a Simple Log Service endpoint. 
    endpoint = 'ap-southeast-1.log.aliyuncs.com'
    # Configure environment variables. In this example, the AccessKey ID and AccessKey secret are obtained from environment variables. 
    access_key_id = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_ID', '')
    access_key = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_SECRET', '')
    # The name of the project. 
    project_name = 'aliyun-test-project'
    # The name of the logstore. 
    logstore_name = 'aliyun-test-logstore'

    # Create a Simple 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)
    # Display the statistics about the analysis results. 
    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 consumed to execute the SQL statement. 
    print("elapsed_mills: %s" % res.get_elapsed_mills())
    # Display 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 Dedicated SQL feature based on the CPU time. For more information, see the topics that are related to 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)
    # Display the statistics about the analysis results. 
    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 consumed to execute the SQL statement. 
    print("elapsed_mills: %s" % res.get_elapsed_mills())
    # Display 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 Dedicated SQL feature based on the CPU time. For more information, see the topics that are related to 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()

Sample code to create, modify, and query an SQL instance

After the Dedicated SQL feature is enabled, you can create an SQL instance to specify the number of CUs. You can adjust the number of CUs to control costs. For more information, see High-performance fully accurate query and analysis (Dedicated SQL).

The following sample code shows how to create, modify, and query an SQL instance. For more information, see aliyun-log-python-sdk.

# encoding: utf-8
from __future__ import print_function

import time
import os
from aliyun.log import *

def main():
    # The Simple Log Service endpoint. For more information, see Endpoints. In this example, the Simple Log Service endpoint for the China (Hangzhou) region is used. Replace the parameter value with the actual endpoint. 
    endpoint = 'ap-southeast-1.log.aliyuncs.com'
    # Configure environment variables. In this example, the AccessKey ID and AccessKey secret are obtained from environment variables. 
    access_key_id = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_ID', '')
    access_key = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_SECRET', '')
    # The name of the project. 
    project_name = 'aliyun-test-project'
    # The name of the logstore. 
    logstore_name = 'aliyun-test-logstore'

    # Create a Simple 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. Valid values: 0 to 1000. 
    print("===sample_create_sql_instance===")
    res = client.create_sql_instance(project_name, 500)
    res.log_print()

    # Modify the configurations of the SQL instance. You can change the number of CUs for the SQL instance. Valid values: 0 to 1000. 
    print("===sample_update_sql_instance===")
    res = client.update_sql_instance(project_name, 800)
    res.log_print()

    # Query 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()