Scheduled SQL FAQ

Data accuracy in SQL analysis

Inaccurate analysis results can occur if data is ingested with a delay or if an instance has improper scheduling configurations.

Data ingestion delay: For example, if data is written to Simple Log Service with a 5-minute delay and a Scheduled SQL instance runs at 12:03:00 for the SQL time window [12:02:00, 12:03:00), the query will miss the latest data.
Query processing latency: Data can only be queried after it is written to Simple Log Service, which introduces a short delay, typically less than 3 seconds. Even with low latency, there is a risk of missing data. For example, if an instance runs at 12:03:30 for the SQL time window [12:02:30, 12:03:30), logs written at 12:03:29 are not guaranteed to be queryable by 12:03:30.
Index build timing: When logs from different times are written within the same minute, the index for a later log may be committed to disk at an earlier timestamp. For example, consider an SQL time window of [12:02:30, 12:03:30). If logs with timestamps of 12:02:20 and 12:02:50 are both written at 12:02:50, their index might be committed based on the 12:02:20 timestamp. As a result, the query will not find these logs in the [12:02:30, 12:03:30) time range.

When you use Scheduled SQL, balance data timeliness and accuracy based on your business requirements.

To account for potential data ingestion delays, configure the Execution Latency and SQL Time Window based on your data collection latency and the maximum acceptable delay for results to appear. Setting the end time of the window slightly earlier helps ensure that all data within the SQL time window has arrived before the instance executes.
Align the SQL time window to minute or hour boundaries (for example, on the minute or hour) to ensure data accuracy with out-of-order data uploads.

Resolving data latency issues

Solution 1

To prevent data loss in Scheduled SQL calculations due to log time delays, use an ingest processor to process data before it is written to a Logstore, which ensures that the log time is consistent with the write time.

Make sure that you have created a Project named web-project-test and created a Logstore named website_log. The web console features for website_log must be enabled. You must also write logs to website_log by using Logtail or a Simple Log Service SDK.
Create an ingest processor.
1. Log on to the Simple Log Service console. In the Project list, click the web-project-test Project.
2. In the navigation pane on the left, click Resource > Data Processors.
3. On the Data Processors > Ingest Processor tab, click Create.
4. In the Create Processor panel, enter a processor name and the SPL statement, and then click OK.
  
  SPL statement: * | extend __time__=cast("__tag__:__receive_time__" as bigint).
Associate the ingest processor with the Logstore.
1. In the navigation pane on the left, click Log Storage. Hover the mouse pointer over website_log and click Modify.
2. On the website_log attributes page, click Modify in the upper-right corner. In edit mode, for the Ingest Processor setting, select the parse-logs ingest processor you created, and then click Save.

In the Logstore query and analysis editor, run the following SQL statement to verify that the log time and write time are consistent.

* | SELECT 
    cast("__tag__:__receive_time__" as bigint) - cast(__time__ as bigint) as diff
WHERE 
    cast("__tag__:__receive_time__" as bigint) - cast(__time__ as bigint) > 0

Solution 2

Simple Log Service lets you forcibly overwrite the __time__ field with the server time to prevent latency issues. To enable this feature, submit a ticket.

Preventing SQL analysis failures

Ensure that your SQL syntax is valid.
Ensure that you have created the correct index for the fields that you want to analyze. For example, for the query * | select uid, you must enable analytics for the uid field. For more information, see Create an index.
Ensure that the correct permissions are configured, such as the permission to run SQL analysis and read data from the source logstore.
Ensure that sufficient computing resources are available. For more information about resource limits, see Limitations for query and analysis.
Avoid using overly complex SQL statements or setting an excessively large SQL time window. Otherwise, the query may time out. The server-side timeout is 10 minutes.

Index checks for destination logstores

No. When Simple Log Service writes the results of a Scheduled SQL job to a destination logstore, it does not check the index. If an index is not configured for the destination logstore, you cannot perform SQL analysis on the data. However, this does not affect log consumption or queries.

Configure the index for the destination logstore before you create the Scheduled SQL job. If you did not configure an index in advance, you can use the reindexing feature to build indexes for historical data. For more information, see Reindex logs.

Instance timeouts and subsequent executions

No. A single instance timeout does not affect the generation of subsequent execution plans. The scheduled time for a new instance is based on the scheduled time of the previous one. However, the creation and execution times of subsequent instances will be delayed, and the system will need time to catch up to the original schedule, gradually reducing the result data latency.

Generally, when catching up on a fixed amount of data, a larger scheduling interval results in a faster catch-up speed. For example:

To process one day of data with a 1-minute scheduling interval, 1,440 instances are required, with each instance running for 20 seconds.
To process one day of data with a 1-hour scheduling interval, 24 instances are required, with each instance running for 2 minutes.

Simple Log Service provides distributed query and analysis capabilities and automatically allocates more computing resources to handle larger data volumes.

Tracing data in destination logstores

When a Scheduled SQL job writes data to a destination logstore, Simple Log Service adds the following __tag__ fields by default for data traceability:

__tag__:__instance_id__:2b****06a: The instance ID.
__tag__:__job__:from_now: The job name.
__tag__:__project__:ali-sls-etl-staging: The Project to which the job belongs.
__tag__:__schedule_time__:1618474200: The scheduled time of the job. This value is a Unix timestamp. Unit: seconds.
__tag__:__trigger_time__:1618474259: The execution time of the job. This value is a Unix timestamp. Unit: seconds.

Customizing log time in destination logstores

If your business requires you to query and analyze data based on log time and you want to use a custom value instead of the default, you can use the following examples to customize the log time for data written to the destination logstore.

Note

The __time__ field specifies the log time. You can view the log time in the Logstore.
The current_timestamp function returns the current date, time, and time zone.

What is the default log time for data written to a destination logstore by a Scheduled SQL job?

When you create a Scheduled SQL job, if the SQL statement does not include the __time__ field, the system uses the start time of the data time range as the log time for the results written to the destination logstore. Note that a * | select * query implicitly includes the __time__ field.

As shown in the following figure, the system defaults to using the start time of the SQL query range, 2024-09-14 15:31:00, as the log time for the results of this instance.

Example 1: Use source log time

When you create the Scheduled SQL job, include the __time__ field in your SQL statement in addition to the other required fields. The system then uses the __time__ value from the query result as the log time for the data written to the destination logstore.

Example 2: Use the current time

When you create the Scheduled SQL job, include the cast(to_unixtime(current_timestamp) as bigint) AS __time__ expression in your SQL statement. This sets the current time as the log time for the data written to the destination logstore. For more information, see the to_unixtime function and cast function documentation.

Example 3: Use a custom time field

As shown in the following figure, replace start_time in the SQL statement with an actual time field from your source logs. This field must be of a timestamp type. The system will then use the resulting __time__ value as the log time for the data written to the destination logstore.