Create a scheduled SQL job

Updated at:

Call CreateScheduledSQL to create a scheduled SQL job using the SLS SDK for Python.

Note

The Simple Log Service SDK no longer updates APIs related to scheduled SQL. Use the Alibaba Cloud SDK to manage scheduled SQL:

On the CreateScheduledSQL API debugging page in the Alibaba Cloud OpenAPI Explorer, click the SDK Example tab on the right panel. Select your target language (Java, TypeScript, Go, PHP, Python, .NET, C++, etc.) to view the corresponding SDK sample code. You can click Run Example for online debugging or Download Full Project.

Prerequisites

Parameters

def create_scheduled_sql(self, project_name, scheduled_sql):

Request parameters

Parameter

Type

Required

Description

Example

project_name

String

Yes

The name of the Project.

ali-test-project

scheduled_sql

object

Yes

The configuration of the scheduled SQL job.

-

scheduled_sql parameters

Parameter

Type

Required

Description

Example

name

String

Yes

The scheduled SQL job name. Requirements:

  • Unique within a Project.

  • Contains only lowercase letters, digits, hyphens (-), and underscores (_).

  • Starts and ends with a lowercase letter or digit.

  • 4 to 63 characters.

test-001

displayName

String

Yes

The display name of the job.

test-001

description

String

No

The description of the job.

Create a scheduled SQL job.

schedule

object

Yes

The scheduling configuration of the job.

-

configuration

object

Yes

The configuration of the scheduled SQL job.

-

schedule parameters

Parameter

Type

Required

Description

Example

type

String

Yes

The scheduling type. Valid values:

  • Cron: The job runs based on the cron expression specified in the cronExpression parameter.

  • Hourly: The job runs hourly at the start of the hour.

  • Daily: The job runs daily at the hour specified in the hour parameter.

  • Weekly: The job runs weekly on the day and at the hour specified in the dayOfWeek and hour parameters.

  • FixedRate: The job runs at a fixed interval specified in the interval parameter.

Cron

cronExpression

String

No

The cron expression.

0/5 * * * *

runImmediately

bool

No

Whether to run the job immediately after creation.

False

timeZone

String

No

The time zone for the cron expression. Defaults to UTC+8.

+0800

delay

int

No

The delay before the job runs, in seconds. Maximum: 86,400.

4

interval

String

No

The execution interval when type is FixedRate. Maximum: 30 days. Supported units: d (day), h (hour), m (minute), s (second). Example: 3h. Unit combinations such as 3h5m are not supported.

1m

configuration parameters

Parameter

Type

Required

Description

Example

script

String

Yes

The query and analysis statement for the scheduled SQL job.

* | select *

sqlType

String

Yes

The type of the SQL statement.

searchQuery

destEndpoint

String

Yes

The destination endpoint.

cn-hangzhou-intranet.log.aliyuncs.com

destProject

String

Yes

The destination Project.

project-demo

sourceLogstore

String

Yes

The source Logstore.

source-logstore-demo

destLogstore

String

Yes

The destination Logstore.

Warning

Do not set the destination to the same store as the source. This can cause a write loop that incurs additional storage and traffic costs. You are responsible for the resulting resource consumption and costs.

dest-logstore-demo

roleArn

String

Yes

The RAM role ARN to assume for running the SQL query.

acs:ram::123456789:role/aliyunlogetlrole

destRoleArn

String

Yes

The RAM role ARN to assume for writing data to the destination.

acs:ram::123456789:role/aliyunlogetlrole

fromTimeExpr

String

Yes

The start of the SQL time window.

@m-1m

toTimeExpr

String

Yes

The end of the SQL time window.

@m

maxRunTimeInSeconds

int

Yes

The SQL query timeout, in seconds. Valid values: 60 to 1,800.

600

resourcePool

String

Yes

The resource pool type.

enhanced

maxRetries

int

Yes

The maximum number of retries after an SQL query times out. Valid values: 1 to 100.

20

fromTime

int

Yes

The start time. Create a scheduled SQL job to import data from a Logstore to a Metricstore.

1712592000

toTime

int

Yes

The end time. Create a scheduled SQL job to import data from a Logstore to a Metricstore.

0

dataFormat

String

Yes

The data transformation format. Valid values: log2log, log2metric, and metric2metric.

log2log

parameters

object

Yes

The SQL configuration. Create a scheduled SQL job to import data from a Logstore to a Metricstore.

{
 addLabels: "{}",
 hashLabels: "[]",
 labelKeys: "[\"your label1\",\"your label2\"]",
 metricKeys: "[\"your Indicator1\",\"your Indicator2\"]",
 metricName: "",
 timeKey: ""
}

Response parameters

Response parameters are defined in the Create a scheduled SQL job API.

Example

import os
import time
from aliyun.log import LogClient
from aliyun.log.scheduled_sql import *

# The AccessKey ID of your Alibaba Cloud account. We recommend that you set the AccessKey ID as an environment variable.
accessKeyId = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_ID', '')
# The AccessKey Secret of your Alibaba Cloud account. We recommend that you set the AccessKey Secret as an environment variable.
accessKeySecret = os.environ.get('ALIBABA_CLOUD_ACCESS_KEY_SECRET', '')

# The endpoint of the region where the source Project is located.
endpoint = "cn-hangzhou.log.aliyuncs.com"
# The RAM role ARN.
roleArn = "acs:ram::141******5616316:role/aliyunserviceroleforslsaudit"
# The name of the source Project.
project = "demo-test-project"
# The name of the source Logstore.
source_logstore = "test-logstore"
# The name of the source Metricstore.
source_metricstore = ""

# The endpoint of the region where the destination Project is located.
dest_endpoint = "cn-hangzhou.log.aliyuncs.com"
# The RAM role ARN for the destination.
dest_role_arn = "acs:ram::141******5616316:role/aliyunserviceroleforslsaudit"
# The name of the destination Project.
dest_project = "demo-test-project"
# The name of the destination Logstore.
dest_logstore = "test-logstore2"
# The name of the destination Metricstore.
dest_metricstore = ""

# The start time of the scheduled SQL job.
from_time = int(time.time()) - 360

# The job name.
job_name = "test-001"
# The display name.
display_name = "test-001"
# The description.
description = "Create a scheduled SQL job"

# The SQL statement.
script = "* | select *"
# The PromQL statement.
promql = "* | select promql_query_range('key{}') from metrics limit 1000"

# The instance ID of the scheduled SQL job.
instance_id = ""
# The delay for the scheduled SQL job, in seconds.
delay_seconds = 0

# The data transformation format. Valid values: "log2log", "log2metric", and "metric2metric".
data_format = "log2log"

# The scheduling type. Valid values: "FixedRate", "Daily", "Weekly", "Hourly", and "Cron".
schedule_type = "FixedRate"

client = LogClient(endpoint, accessKeyId, accessKeySecret)


def generate_schedule_sql():
    schedule_rule = generate_schedule_rule()
    config = generate_config()

    schedule_sql = ScheduledSQL()
    schedule_sql.setName(job_name)
    schedule_sql.setDisplayName(display_name)
    schedule_sql.setDescription(description)
    schedule_sql.setSchedule(schedule_rule)
    schedule_sql.setConfiguration(config)
    return schedule_sql


def generate_schedule_rule():
    schedule_rule = JobSchedule()
    schedule_rule.setType(schedule_type)
    schedule_rule.setDelay(delay_seconds)
    schedule_rule.setTimeZone('+0800')
    if schedule_type == "FixedRate":
        # The interval. The value must be a string in the "Xs", "Xm", "Xh", or "Xd" format,
        # where X is an integer that specifies the number of seconds, minutes, hours, or days.
        schedule_rule.setInterval('60s')
    elif schedule_type == "Cron":
        # A valid cron expression.
        schedule_rule.setCronExpression("*/2 * * * *")
    elif schedule_type == "Daily":
        # The hour of the day. Valid values: 0 to 23.
        schedule_rule.setHour(1)
    elif schedule_type == "Weekly":
        # The day of the week. Valid values: 1 to 7.
        schedule_rule.setDayOfWeek(1)
        # The hour of the day. Valid values: 0 to 23.
        schedule_rule.setHour(2)
    elif schedule_type == "Hourly":
        pass

    return schedule_rule


def generate_config():
    query = script
    if data_format == 'log2log':
        source = source_logstore
        dest = dest_logstore
    elif data_format == 'log2metric':
        source = source_logstore
        dest = dest_metricstore
    elif data_format == 'metric2metric':
        source = source_metricstore
        dest = dest_metricstore
        query = promql

    parameters = generate_log2log_params()
    config = ScheduledSQLConfiguration()
    config.setScript(query)
    config.setSqlType('searchQuery')
    config.setDestEndpoint(dest_endpoint)
    config.setDestProject(dest_project)
    config.setSourceLogstore(source)
    config.setDestLogstore(dest)
    config.setRoleArn(roleArn)
    config.setDestRoleArn(dest_role_arn)
    config.setFromTimeExpr("@m - 5m")
    config.setToTimeExpr("@m")
    config.setMaxRunTimeInSeconds(1800)
    config.setResourcePool("enhanced")
    config.setMaxRetries(10)
    config.setFromTime(from_time)
    config.setToTime(0)
    config.setDataFormat(data_format)
    config.setParameters(parameters)
    return config


def generate_log2log_params():
    parameters = ScheduledSQLBaseParameters()
    return parameters


if __name__ == '__main__':
    schedule_sql = generate_schedule_sql()
    res = client.create_scheduled_sql(dest_project, schedule_sql)
    res.log_print()

Sample response

CreateScheduleSqlResponse:
headers: {'Server': 'AliyunSLS', 'Content-Length': '0', 'Connection': 'keep-alive', 'Access-Control-Allow-Origin': '*', 'Date': 'Fri, 01 Nov 2024 07:04:39 GMT', 'x-log-time': '1730444679', 'x-log-requestid': '67247D87DB04A454D92A6F6B'}

Process finished with exit code 0

Related documents