Process and store data from a Metricstore to another Metricstore

Target

In this example, the source Metricstore log2metric contains multiple metric columns, including http_status, request_count, request_size, response_size, and response_time. A Scheduled SQL job is created to query the response_time metric of the bucket00788 bucket in the source Metricstore log2metric and store the queried data to the destination Metricstore metric2metric.

Raw Log

Source Metricstore

Processed logs

Destination Metricstore

SQL

Query the response_time metric of the bucket00788 bucket.

* | select promql_query_range('response_time{bucket="bucket00788"}') from metrics limit 1000

Calculation configurations

Modify a Scheduled SQL job Click Edit Configurations. In the Modify Scheduled SQL panel, configure the parameters.

Configuration item	Description
Write Mode	If the source is a Logstore, you can select Import Data from Logstore to Logstore or Import Data from Logstore to Metricstore. In this example, the source is a Metricstore and Import Data from Metricstore to Metricstore is selected.
SQL code	Write code to query the response_time metric of the bucket00788 bucket.

* | select promql_query_range('response_time{bucket="bucket00788"}') from metrics limit 1000

Result metric name

If you select Import Data from Metricstore to Metricstore as Write Mode, we recommend that you specify the name of the result metric when only one metric is involved. If multiple metrics are involved, the specified metric names are renamed.

Hash column

You can enable hashing to write data that has the same tag value to the same shard. This enhances the locality and improves the query efficiency.

Scheduling configurations

Configuration item	Description
Scheduling Interval	he interval at which the SQL code is run. In this example, the interval is 5 minute.
Scheduling Time Range	The time window of logs that are analyzed when the SQL code is run. A value of [@m-5m,@m) specifies that only data within 5 minute is analyzed.
SQL Timeout	The timeout period and the maximum number of retries of the SQL code. For example, the timeout period is 600 seconds and the maximum number of retries is 20.

Attention:

1. If data is written to the source Logstore at a latency, we recommend that you set the Delay Task parameter to a greater value to ensure the integrity of the source data.
2. If the maximum number of retries is reached or the timeout period expires, the current instance of the Scheduled SQL job fails and the next instance is scheduled.

For more information about the calculation and scheduling configurations of a Scheduled SQL job, see Process and save data from a Logstore to another Logstore

Use an SDK to create a Scheduled SQL job

For more information about how to use an SDK to create a Scheduled SQL job, see Use Simple Log Service SDK for Java to create a Scheduled SQL job

Job Management

Go to the Job Management page You can view the created Scheduled SQL jobs in the Simple Log Service console. On the details page of a Scheduled SQL job, you can view the instances of the job. The information about each instance includes the SQL query range. If an instance fails due to reasons such as insufficient permissions or invalid SQL syntax, or the number of processed rows in the Processed Data Size column is 0, you can retry the instance. If the number of processed rows is 0, the source data is delayed or no data exists.

Verify effects

Compare the amount of logs

avg(response_time) by (bucket)