Updated at: 2022-12-09 03:49:50
In log data processing, to query the relational database remotely and add the relevant field parameters in the query result to the associated log, you can use the Add DB Data function.
The Add DB Data function can realize the association and integration of log data and database business data, to build a more comprehensive data base and enable the association query and analysis in complex scenarios. It supports common relational databases: MySQL, Oracle, SQLServer, DB2 and others. The specific steps are as follows:
1. In the New rule configuration, after the Original Log is parsed and verified, click Add DB Data to configure the remote query and associated log parameters, as follows: 
Field Name Description
Remote Query Query Plan The Cron expression
Lines/Query It must be larger than the number of records in the SQL Statement query result, which is recommended to be larger than the actual number of records in the database. Otherwise, it will throw an exception message into the log aggregation result.
Query Name The custom remote query name
Note: The name must not be repeated with that of other parameters.
DB Connection Select the created database and perform connection verification
SQL Statement The custom SQL statement for remote query to access data sets from a remote database
Note: SQL query statement cannot contain double quotes, and only single quotes are allowed!
Local Cache Name The custom name of local cache table, which is used to store the data set accessed from the remote database
Note: The name must not be repeated with that of other parameters.
Cache Sheet Column Define the name, type and length of columns in the local cache table
Note：
1. The type sequence must match the sequence of the data types in the SQL statement query result: 
• Remote Query: Select id, name, age, sex, country from Persons
• Local Query: Select sex from a (cached table name): Then the table column definition should define id, name, age, and sex, the corresponding types will be int, varchar, int, varchar, and country may not be defined, since it is not used in Local Query.
2. The length must be filled in. If you fill in 0, the system will be abnormal.
Index Column The custom table column name
Associated Log SQL The custom SQL statement for querying cached data in the local cache table and adding the data to the current log data
Note: To add a custom variable in the SQL statement, you need to add a colon in front of the custom variable.
Example, chara is a custom variable, and the corresponding SQL Statement:
select country from a where name = :chara
Custom Variable Associate a custom variable in SQL with a parsed field in the log. That is, replace custom variable in SQL statement with log field value. It supports splicing multiple log fields.
Note: The splice character in the middle of the splice field can be changed according to the actual situation. If there is no splice character, you do not need to fill in.
Target Field The custom name of the target field. The target field is the new field added to the log data. The value of the field is the database data value queried by the SQL statement in the local cache table.
Note: The name must not be repeated with that of other parameters.
2. Click Verify. After the verification of the parameters is qualified, click OK to save the parsing rule.