Sql type
KUMA supports multiple types of databases.
The program supports the following types of SQL databases:
- SQLite.
- MSSQL.
- MySQL.
- PostgreSQL.
- Cockroach.
- Oracle.
- Firebird.
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.
- Click the
button.The secret window is displayed.
- Define the values for the following settings:
- Name—the name of the added secret.
- Type—urls.
This value is set by default and cannot be changed.
- URL—URL of the database.
You must keep in mind that each type of database uses its own URL format for connections.
Available URL formats are as follows:
- For SQLite:
sqlite3://file:<file_path>
A question mark (?) is used as a placeholder.
- For MSSQL:
sqlserver://<user>:<password>@<server:port>/<instance_name>?database=<database>
(recommended)sqlserver://<user>:<password>@<server>?database=<database>&encrypt=disable
The characters @p1 are used as a placeholder.
- For MySQL:
mysql://<user>:<password>@tcp(<server>:<port>)/<database>
The characters %s are used as a placeholder.
- For PostgreSQL:
postgres://<user>:<password>@<server>/<database>?sslmode=disable
The characters $1 are used as a placeholder.
- For Cockroach:
postgres://<user>:<password>@<server>:<port>/<database>?sslmode=disable
The characters $1 are used as a placeholder.
- For Firebird:
firebirdsql://<user>:<password>@<server>:<port>/<database>
A question mark (?) is used as a placeholder.
- Description—any additional information.
- If necessary, click Add and specify an additional URL.
In this case, if one URL is not available, the program connects to the next URL specified in the list of addresses.
- Click the Save button.
- Click the
button.The secret window is displayed.
- Specify the values for the settings that you want to change.
You can change the following values:
- Name—the name of the added secret.
- URL—URL of the database.
You must keep in mind that each type of database uses its own URL format for connections.
Available URL formats are as follows:
- For SQLite:
sqlite3://file:<file_path>
A question mark (?) is used as a placeholder.
- For MSSQL:
sqlserver://<user>:<password>@<server:port>/<instance_name>?database=<database>
(recommended)sqlserver://<user>:<password>@<server>?database=<database>&encrypt=disable
The characters @p1 are used as a placeholder.
- For MySQL:
mysql://<user>:<password>@tcp(<server>:<port>)/<database>
The characters ?
are used as placeholders.
- For PostgreSQL:
postgres://<user>:<password>@<server>/<database>?sslmode=disable
The characters $1 are used as a placeholder.
- For Cockroach:
postgres://<user>:<password>@<server>:<port>/<database>?sslmode=disable
The characters $1 are used as a placeholder.
- For Firebird:
firebirdsql://<user>:<password>@<server>:<port>/<database>
A question mark (?) is used as a placeholder.
- Description—any additional information.
- If necessary, click Add and specify an additional URL.
In this case, if one URL is not available, the program connects to the next URL specified in the list of addresses.
- Click the Save button.
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.
Codes of special characters
!
|
#
|
$
|
%
|
&
|
'
|
(
|
)
|
*
|
+
|
%21
|
%23
|
%24
|
%25
|
%26
|
%27
|
%28
|
%29
|
%2A
|
%2B
|
,
|
/
|
:
|
;
|
=
|
?
|
@
|
[
|
]
|
\
|
%2C
|
%2F
|
%3A
|
%3B
|
%3D
|
%3F
|
%40
|
%5B
|
%5D
|
%5C
|
The following special characters are not supported in passwords used to access SQL databases: space, [, ], :, /, #, %, \.
- 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.
On the Advanced settings tab, you need to specify the following settings for the connector:
Within a single connector, you can create a connection for multiple supported databases.
To create a connection for multiple SQL databases:
- Click the Add connection button.
- Specify the URL, Identity column, Identity seed, Query, and Poll interval, sec values.
- Repeat steps 1–2 for each required connection.
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 @p1
are 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=disable
The characters $1
are used as a placeholder in the SQL query.
- CockroachDB
Example URL: postgres://{user}:{password}@{server}:{port}/{database}?sslmode=disable
The characters $1
are 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, connections
is 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 :val
SQL 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.
- 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.
Examples of SQL requests
SQLite, Firebird—select * from table_name where id > ?
MSSQL—select * from table_name where id > @p1
MySQL—select * from table_name where id > ?
PostgreSQL, Cockroach—select * from table_name where id > $1
Oracle—select * from table_name where id > :val
Page top