dbquery

Executes SQL queries against an external database server via JDBC. You must configure a connect profile of the Database type in advance.

Command properties

Syntax

Target

PROFILE

Identifier of the connect profile of the Database type to use. You can configure connect profiles in the web console.

SQL

SQL statement to execute. SELECT, SHOW, EXECUTE, and EXEC statements convert the result set to records and output them. Other SQL statements (INSERT, UPDATE, DELETE, etc.) are executed without producing output records.

You can use placeholders starting with a colon (:) in the SQL statement. Placeholders are replaced with query parameter values set by the set command. A placeholder name must be at least two characters long after the colon, must start with an ASCII letter or an underscore (_), and can contain only ASCII letters, digits, and underscores. A parameter that has not been set is replaced with null. Note that a type cast written with two colons, such as PostgreSQL's ::int, is also recognized as a placeholder and replaced. Values are inserted as follows:

• Numeric types: inserted as-is
• Date types: converted to strings in the format 'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd HH:mm:ss.SSS'
• null: inserted as the string null
• Other types: converted to single-quoted strings

Output fields

Error codes

Parse errors

Runtime errors

Description

The dbquery command executes SQL queries against an external database via JDBC and converts each row of the result set to a record for output.

SQL statements starting with SELECT, SHOW, EXECUTE, or EXEC retrieve a result set. All other SQL statements (INSERT, UPDATE, DELETE, DDL, etc.) are executed without producing output records. A query that starts with a WITH clause also follows this rule: it is only executed, produces no output records, and may cause an error depending on the database.

The SQL statement runs inside a transaction. When the statement finishes, the command commits the transaction. If the statement fails, the command rolls back the transaction and stops the query with an error. The error message shows the SQL statement as it was before the placeholders were replaced. For an Apache Hive database whose connection string starts with jdbc:hive2://, however, the command does not change the auto-commit setting and does not commit or roll back.

When you use placeholders starting with a colon (for example :name) in a SQL statement, they are replaced with query parameter values set by the set command. This allows you to construct dynamic SQL queries.

When connecting to MySQL or MariaDB, forward-only cursors and streaming fetch are used to handle large result sets. For all other databases, result sets are fetched 2,000 rows at a time. You can override this value by setting the JVM system property logpresso.jdbc.fetch_size.

The account that runs the query must have permission to use the specified connect profile. Accounts with the cluster administrator or administrator role can use connect profiles regardless of their sharing settings. Other accounts receive usage permission through the Granted Users and Granted Groups settings of the connect profile.

A connect profile whose identifier is exactly sonar is reserved for internal system use, so its usage is blocked. This restriction does not apply when the system executes dbquery internally, such as for a scheduled query. If you need to temporarily allow it for maintenance, incident response, or another operational purpose, set the boot switch logpresso.sonar.allow_system_profile to true (default: not set, blocked).

Examples

1. Querying data from an external database

   dbquery mydb SELECT * FROM employees WHERE department = 'sales'
   

   Retrieves records from the sales department in the employees table in the database connected via the connect profile whose identifier is mydb.

2. Dynamic query using placeholders

   set start_date = string(dateadd(now(), "day", -7), "yyyy-MM-dd")
   | dbquery mydb SELECT * FROM access_logs WHERE log_date >= :start_date
   

   Inserts the start_date parameter set by the set command into the SQL query to retrieve logs from the past 7 days.

3. Sorting processed query results

   dbquery mydb SELECT src_ip, dst_ip, bytes FROM traffic_logs
   | stats sum(bytes) as total_bytes by src_ip
   | sort -total_bytes
   

   Retrieves traffic logs from the external database, calculates total bytes by source IP, and sorts in descending order.

Change history