text_search Functions

Definition

This type of function allows you to quickly search for logs by keyword and obtain complete table structure data.

Syntax: text_search(index, query)

1. Index name: The index parameter must be log.
2. Query statement: The query parameter must comply with the LTS log search syntax. If the statement does not comply with the syntax, the query will fail.

Returned result: The table structure data corresponding to the specified log stream is returned, including all columns and filtered rows.

Example 1: Log Search by Keyword

Query logs that contain keyword error:

SELECT *  FROM table(text_search(index =>'log', query => 'error')) LIMIT 100

This is equivalent to:

error | SELECT * FROM log LIMIT 100

• The value of the index parameter is log, indicating that the current log stream is queried.
• The value of the query parameter is error, indicating that all the logs that contain error are filtered.
• All fields (such as timestamp, level, message, and host) of the table are returned.
• LIMIT 100 limits the number of results returned.

Query and analysis result

timestamp          | level  | message                        | host
-------------------|--------|--------------------------------|------
2024-01-01 10:00:00| ERROR  | Connection timeout error       | host1
2024-01-01 11:30:00| ERROR  | Database connection failed     | host2

Example 2: Log Search Based on Join Conditions

Filter the logs of the ERROR level from all logs, find out the trace IDs of these logs, and then filter the logs that contain these trace IDs from all logs. Only trace_id and message are displayed. A maximum of 1,000 records can be returned.

SELECT trace_id, message from log where trace_id in 
(SELECT distinct trace_id
FROM table(text_search(index =>'log', query =>  'level:ERROR')))
limit 1000

• The value of the query parameter is level:ERROR.
• The content following in indicates the join operation. It is equivalent to SELECT t.trace_id,t.message FROM log t INNER JOIN (SELECT DISTINCT trace_id FROM table(text_search(index =>'log', query => 'level:ERROR'))) err ON t.trace_id=err.trace_id LIMIT 1000.

Query and analysis result

trace_id  | message                        
----------|--------------------------------
xxxx_xxxx1|   access memory
xxxx_xxxx1| Memory allocation failed       

Example 3: Counting the number of log events by keyword syntax

Count the number of log events by keyword syntax.

* | WITH method_log AS ( select count(*) as pv from table(text_search(index =>'log', query => 'method : GET'))) , 
content_log AS ( select count(*) as pv2 from table(text_search(index =>'log', query => 'content'))) ,
status_log AS ( select count(*) as pv3 from table(text_search(index =>'log', query => 'status : 200'))) 
SELECT pv,pv2,pv3 from method_log LEFT JOIN content_log ON 1 = 1 LEFT JOIN status_log ON 1 = 1

• The value of the index parameter can only be log, indicating that the current log stream is queried. The function of cross-stream joint query by log stream name or ID is to be released soon.
• status : 200 following the query parameter belongs to the keyword query syntax.

Query and analysis result

pv           pv2          pv3
3235207      2185395      14334124

Performance Optimization Suggestions

1. Limit the number of results: Use the LIMIT clause to limit the returned results.

   SELECT * FROM Table(text_search(index =>'log', query =>  'level:ERROR')) LIMIT 1000;

2. Select exact fields: Select only required fields to reduce data transmission.

   SELECT message FROM Table(text_search(index =>'log', query =>  'ERROR'));

3. Optimize the query condition: Use the Lucene syntax for pre-filtering to reduce the backend processing pressure.

   -- Perform field-based search instead of full-text search.
   text_search(index =>'log', query =>  'level:ERROR')  -- Fast
   text_search(index =>'log', query =>  'ERROR')        -- Slow (full-text scanning)