Sql type
KUMA supports multiple types of databases.
When creating a connector, you must specify general connector settings and specific database connection settings.
On the Basic settings tab, you must specify the following values for the connector:
- Name (required)—unique name of the resource. Must contain 1 to 128 Unicode characters.
- Type (required)—connector type, sql.
- Tenant (required)—name of the tenant that owns the resource.
- Default query (required)—SQL query that is executed when connecting to the database.
- Reconnect to the database every time a query is sent — the check box is cleared by default.
- Poll interval, sec —interval for executing SQL queries. This value is specified in seconds. The default value is 10 seconds.
- Description—resource description: up to 4,000 Unicode characters.
To connect to the database, you need to define the values of the following settings on the Basic settings tab:
- URL (required)—secret that stores a list of URLs for connecting to the database.
If necessary, you can edit or create a secret.
When creating connections, strings containing account credentials with special characters may be incorrectly processed. If an error occurs when creating a connection but you are sure that the settings are correct, enter the special characters in percent encoding.
- Identity column (required)—name of the column that contains the ID for each row of the table.
- Identity seed (required)—identity column value that will be used to determine the specific line to start reading data from the SQL table.
- Query—field for an additional SQL query. The query indicated in this field is performed instead of the default query.
- Poll interval, sec —interval for executing SQL queries. The interval defined in this field replaces the default interval for the connector.
This value is specified in seconds. The default value is 10 seconds.
button.
button.On the Advanced settings tab, you need to specify the following settings for the connector:
- Character encoding—the specific encoding of the characters. The default value is
UTF-8.KUMA converts SQL responses to UTF-8 encoding. You can configure the SQL server to send responses in UTF-8 encoding or change the encoding of incoming messages on the KUMA side.
- Debug—a drop-down list where you can specify whether resource logging should be enabled. By default it is Disabled.
Within a single connector, you can create a connection for multiple supported databases.
Supported SQL types and their specific usage features
The UNION operator is not supported by the SQL Connector resources.
The following SQL types are supported:
- MSSQL
Example URLs:
sqlserver://{user}:{password}@{server:port}/{instance_name}?database={database}– (recommended option)sqlserver://{user}:{password}@{server}?database={database}
The characters
@p1are used as a placeholder in the SQL query.If you need to connect using domain account credentials, specify the account name in
<domain>%5C<user>format. For example:sqlserver://domain%5Cuser:password@ksc.example.com:1433/SQLEXPRESS?database=KAV. - MySQL
Example URL:
mysql://{user}:{password}@tcp({server}:{port})/{database}The characters
?are used as placeholders in the SQL query. - PostgreSQL
Example URL:
postgres://{user}:{password}@{server}/{database}?sslmode=disableThe characters
$1are used as a placeholder in the SQL query. - CockroachDB
Example URL:
postgres://{user}:{password}@{server}:{port}/{database}?sslmode=disableThe characters
$1are used as a placeholder in the SQL query. - SQLite3
Example URL:
sqlite3://file:{file_path}A question mark (
?) is used as a placeholder in the SQL query.When querying SQLite3, if the initial value of the ID is in datetime format, you must add a date conversion with the sqlite datetime function to the SQL query. For example: select * from connections where datetime(login_time) > datetime(?, 'utc') order by login_time. In this example,
connectionsis the SQLite table, and the value of the variable?is taken from the Identity seed field, and it must be specified in the {date}T{time}Z format (for example, 2021-01-01T00:10:00Z). - Oracle DB
In version 2.1.3 or later, KUMA uses a new driver for connecting to oracle. When upgrading, KUMA renames the connection secret to 'oracle-deprecated' and the connector continues to work. If no events are received after starting the collector with the 'oracle-deprecated' driver type, create a new secret with the 'oracle' driver and use it for connecting.
We recommend using the new driver.
Example URL of a secret with the new 'oracle' driver:
oracle://{user}:{password}@{server}:{port}/{service_name}
oracle://{user}:{password}@{server}:{port}/?SID={SID_VALUE}
Example URL of a secret with the legacy 'oracle-deprecated' driver:
oracle-deprecated://{user}/{password}@{server}:{port}/{service_name}
The
:valSQL variable is used as a placeholder in.When accessing Oracle DB, if the initial ID value is used in the datetime format, you must consider the type of the field in the database itself and, if necessary, add conversions of the time string in the query to ensure correct operation of the sql connector. For example, if the Connections table in the database has a login_time field, the following conversions are possible:
- If the login_time field has the TIMESTAMP type, then depending on the database settings, the login_time field may contain a value in the YYYY-MM-DD HH24:MI:SS format (for example, 2021-01-01 00:00:00). Then, in the Identity seed field, specify 2021-01-01T00:00:00Z, and perform the conversion in the query using the to_timestamp function. For example:
select * from connections where login_time > to_timestamp(:val, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') - If the login_time field has the TIMESTAMP type, then depending on the database settings, the login_time field may contain a value in the YYYY-MM-DD"T"HH24:MI:SSTZH:TZM format (for example, 2021-01-01T00:00:00+03:00). Then, in the Identity seed field, specify 2021-01-01T00:00:00+03:00, and perform the conversion in the query using the to_timestamp_tz function. For example:
select * from connections_tz where login_time > to_timestamp_tz(:val, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')For more details about the to_timestamp and to_timestamp_tz functions, refer to the official Oracle documentation.
To interact with Oracle DB, you must install the libaio1 Astra Linux package.
- If the login_time field has the TIMESTAMP type, then depending on the database settings, the login_time field may contain a value in the YYYY-MM-DD HH24:MI:SS format (for example, 2021-01-01 00:00:00). Then, in the Identity seed field, specify 2021-01-01T00:00:00Z, and perform the conversion in the query using the to_timestamp function. For example:
- Firebird® SQL
Example URL:
firebirdsql://{user}:{password}@{server}:{port}/{database}A question mark (
?) is used as a placeholder in the SQL query.If a problem occurs when connecting Firebird on Windows, use the full path to the database file. For example:
firebirdsql://{user}:{password}@{server}:{port}/C:\Users\user\firebird\db.FDB
A sequential request for database information is supported in SQL queries. For example, if you type select * from <name of data table> where id > <placeholder> in the Query field, the Identity seed field value will be used as the placeholder value the first time you query the table. In addition, the service that utilizes the SQL connector saves the ID of the last read entry, and the ID of this entry will be used as the placeholder value in the next query to the database.