Scenarios
When log data is scattered across multiple topics, you can use SQL to perform cross-topic statistical analysis, for example, in the following scenarios:
In a microservices architecture, logs of each service are stored in different topics. It is necessary to aggregate and query the time consumption of each service according to the request ID.
Logs are divided into error logs and business access logs, stored in different topics. Query the associated access records in the access logs based on information such as user ID and order ID in the error logs.
Prerequisites
Logs are ingested into standard storage. SQL cannot be used for statistical analysis of low-frequency storage.
A maximum of 5 log topics can be used in association analysis simultaneously.
Operation Steps
Cross-topic association analysis is implemented through SQL statements and can be used in log search and analysis, dashboards, scheduled SQL, and alarm policies. Taking log search and analysis as an example, for operation steps, see operation steps for search and analysis syntax and rules. Syntax and Rules
In SQL, each log topic is equivalent to a database table, with the table name being the topic ID. You can use syntax such as JOIN, UNION, INTERSECT, and EXCEPT for association analysis. Table Names
If a topic ID contains the special character -, you need to use double quotes to wrap it in SQL, for example, "0c037c7c-dbf0-4646-bb8b-9e414e5928f7".
When querying data in the current topic, you can use log to represent the current topic, or omit it. For example * | select * from log, or * | select *.
Query Time Range
By default, SQL uses the time range specified for the entire query statement. If the multiple topics involved in an associated query share the same time range, you do not need to specify the query time range additionally in the SQL. For details, see the example. To specify a query time range for a topic individually, you can use the WHERE clause to filter the log time __TIMESTAMP__. Search conditions
In a query statement, the search condition before the pipe symbol applies only to the log topic of the current query. To filter data for other topics, use the WHERE clause in SQL. For example, in the following query, 18cae058-c290-xxx-ade9-97d8eca2c86a is not the current log topic ID. The search condition level:error does not take effect, and count(*) returns the total number of log entries.
level:error | select count(*) from "18cae058-c290-xxx-ade9-97d8eca2c86a"
To count the number of error logs for this topic, use the following statement:
* | select count(*) from "18cae058-c290-xxx-ade9-97d8eca2c86a" where level = 'error'
Example 2
Scenario Description
A business system contains two services. Order management service logs are stored in topic A, and payment service logs are stored in topic B. Query the time consumption of each service based on the request ID.
The ID of log topic A is "0c037c7c-dbf0-4646-bb8b-9e414e5928f7", and the log data is as follows.
{
"RequestId": "4f7g2k9m",
"timeCost": 856,
"timestamp": "2024-01-31T19:44:08+08:00",
},
{
"RequestId": "6m4v1ztf",
"timeCost": 1452,
"timestamp": "2024-01-31T19:45:08+08:00",
}
The ID of log topic B is "8c05119c-c3e8-4e8f-95b7-81eab4702b0a", and the log data is as follows.
{
"reqid": "4f7g2k9m",
"costms": 247,
"timestamp": "2024-01-31T19:44:08+08:00",
},
{
"reqid": "6m4v1ztf",
"costms": 365,
"timestamp": "2024-01-31T19:45:08+08:00",
}
Query Statement
Run the query statement in log topic A of the order management service logs.
* |
SELECT
order_service.RequestId,
order_service.timeCost AS "Order management service time consumption",
pay_service.costms AS "Payment service time consumption"
FROM
log AS order_service
JOIN "8c05119c-c3e8-4e8f-95b7-81eab4702b0a" AS pay_service
ON order_service.RequestId = pay_service.reqid
LIMIT 100
Querying Results
|
4f7g2k9m | 856 | 247 |
6m4v1ztf | 1452 | 365 |