Docs

Torch Log Service

Associate external data source for log query

MySQL join query and analysis

After creating a MySQL data source association in TLS, you can run join query and analysis on log topic plus the MySQL database. You can also insert the query and analysis results into the MySQL database. This topic describes the steps and examples for join query and analysis.

Prerequisites

The MySQL data source association has been created, and the data source connection status is successful. For details on how to create a data source association and check the connection status, see Associate to MySQL data source.

Notice

Category	Description
MySQL table size	It is recommended that the amount of data in MySQL tables used for join queries be less than 20 MiB. Large datasets and complex SQL queries from TLS may impact database stability.
Query and analysis statement	When using join queries, specify the SQL analysis statements in the format query statement \| analysis statement. The query statement and query time range do not apply to external data resources. You must specify the data source name in query and analysis statements; do not use MySQL table names directly. The backend service will automatically replace the data source name with the corresponding MySQL database and table names during the query. In query and analysis statements, the log topic name must appear before the JOIN keyword, and the data source name should be placed after the JOIN keyword.

Steps

Select the region where log service is located from the top navigation bar.

In the left navigation bar, select Common functions > Query and analysis.

On the page Query and analysis and in the list of left pane, select the specified project and topic.

On the query and analysis page for the specified log topic, set the time range.

By default, the time range is 5 minutes. You can also customize the time range for query.

Enter the query and analysis condition to execute a joint query analysis.

When performing joint query analysis, use the JOIN clause to connect log topics with the designated external data resource. For supported JOIN syntax, see JOIN clause.

Notice

Pay attention to the relevant limits on MySQL table size and SQL syntax when performing query and analysis. For more details, see Notice.

For example, after creating an external MySQL data resource named tls_join_meta_store, use JOIN syntax for joint analysis between log topics and the MySQL database. During joint analysis, associate the userid field of the current log topic with the uid field in the specified data source to analyze activity levels across different age groups. Example of join query and analysis is as follows.

* |
select
count(*) as pv
from myproject.mytopic l join tls_join_meta_store u on l.userid = u.uid
group by u.age
order by pv desc

Enter the query and analysis statement and save the query results to the MySQL database.

Use the INSERT statement to save the query results from the previous step into an existing table in the database. The sample SQL statement is as follows. After successful execution, you can view the saved query and analysis results in the MySQL database.

* | insert into tls_report_external_store select __time__- __time__ % 300 as min, count(1) as pv group by min

Last updated: 2025.12.31 17:23:43