db
Description
Executes a query to a database.
Syntax
db connection=<field> query=[<string> | <field>] [<timeout>]
Required Arguments
| Parameter | Syntax | Description |
|---|---|---|
connection | connection=<field> | Connection name. |
query | query=[<string> | <field>] | SQL query. |
If a data retrieval query is executed, the current data is replaced with new data. If a data processing query is executed, the resulting data contains the current data.
A database query can include the {{_earliest}} or {{_latest}} placeholders, which will be replaced with the earliest or latest values from the source command.
Optional Arguments
| Parameter | Syntax | Default | Description |
|---|---|---|---|
timeout | <int> | Taken from db_query or cluster setting "sme.sa.timeout.db". | Query execution timeout in milliseconds. Converted to seconds with upward rounding during execution. |
The timeout will only take effect if both the DBMS and the JDBC driver support setQueryTimeout.
Examples
Example 1
Retrieving data from a database defined in the configuration with the name mysql:
db connection=mysql query="SELECT * FROM user_info.user;"
Example 2
Modifying data in a database defined in the configuration with the name mysql. As a result, the data from the source command execution will remain:
db connection=mysql query="INSERT INTO user_info.user (name, email) VALUES ('Ivan', 'ivan@mail.ru');"
Example 3
Executing a command with a saved query. The query saved under the name mysql_query will be executed:
db connection=mysql query=mysql_query
Example 4
Example of a database query with timestamps. The SQL query substitutes the timestamp {{_earliest}} obtained from the search time parameters. This ensures alignment of the database selection time range with the main query's time range.
db connection=mysql query="SELECT * FROM user_info.user WHERE date_field >= {{_earliest}};"