JDBC sink connector
You can download all the Pulsar connectors on download page.
The JDBC sink connectors allow pulling messages from Pulsar topics and persist the messages to ClickHouse, MariaDB, PostgreSQL, and SQLite.
Currently, INSERT, DELETE and UPDATE operations are supported. SQLite, MariaDB and PostgreSQL also support UPSERT operations and idempotent writes.
Configuration
The configuration of all JDBC sink connectors has the following properties.
Property
Name | Type | Required | Default | Description |
---|---|---|---|---|
userName | String | false | " " (empty string) | The username used to connect to the database specified by jdbcUrl .Note: userName is case-sensitive. |
password | String | false | " " (empty string) | The password used to connect to the database specified by jdbcUrl . Note: password is case-sensitive. |
jdbcUrl | String | true | " " (empty string) | The JDBC URL of the database that the connector connects to. |
tableName | String | true | " " (empty string) | The name of the table that the connector writes to. |
nonKey | String | false | " " (empty string) | A comma-separated list containing the fields used in updating events. |
key | String | false | " " (empty string) | A comma-separated list containing the fields used in where condition of updating and deleting events. |
timeoutMs | int | false | 500 | The JDBC operation timeout in milliseconds. |
batchSize | int | false | 200 | The batch size of updates made to the database. |
insertMode | enum( INSERT,UPSERT,UPDATE) | false | INSERT | If it is configured as UPSERT, the sink uses upsert semantics rather than plain INSERT/UPDATE statements. Upsert semantics refer to atomically adding a new row or updating the existing row if there is a primary key constraint violation, which provides idempotence. |
nullValueAction | enum(FAIL, DELETE) | false | FAIL | How to handle records with NULL values. Possible options are DELETE or FAIL . |
useTransactions | boolean | false | true | Enable transactions of the database. |
excludeNonDeclaredFields | boolean | false | false | All the table fields are discovered automatically. excludeNonDeclaredFields indicates if the table fields not explicitly listed in nonKey and key must be included in the query. By default all the table fields are included. To leverage of table fields defaults during insertion, it is suggested to set this value to false . |
useJdbcBatch | boolean | false | false | Use the JDBC batch API. This option is suggested to improve write performance. |
Example of ClickHouse
-
JSON
{
"configs": {
"userName": "clickhouse",
"password": "password",
"jdbcUrl": "jdbc:clickhouse://localhost:8123/pulsar_clickhouse_jdbc_sink",
"tableName": "pulsar_clickhouse_jdbc_sink"
"useTransactions": "false"
}
} -
YAML
tenant: "public"
namespace: "default"
name: "jdbc-clickhouse-sink"
inputs: [ "persistent://public/default/jdbc-clickhouse-topic" ]
sinkType: "jdbc-clickhouse"
configs:
userName: "clickhouse"
password: "password"
jdbcUrl: "jdbc:clickhouse://localhost:8123/pulsar_clickhouse_jdbc_sink"
tableName: "pulsar_clickhouse_jdbc_sink"
useTransactions: "false"
Example of MariaDB
-
JSON
{
"configs": {
"userName": "mariadb",
"password": "password",
"jdbcUrl": "jdbc:mariadb://localhost:3306/pulsar_mariadb_jdbc_sink",
"tableName": "pulsar_mariadb_jdbc_sink"
}
} -
YAML
tenant: "public"
namespace: "default"
name: "jdbc-mariadb-sink"
inputs: [ "persistent://public/default/jdbc-mariadb-topic" ]
sinkType: "jdbc-mariadb"
configs:
userName: "mariadb"
password: "password"
jdbcUrl: "jdbc:mariadb://localhost:3306/pulsar_mariadb_jdbc_sink"
tableName: "pulsar_mariadb_jdbc_sink"
Example of OpenMLDB
OpenMLDB does not support DELETE and UPDATE operations
-
JSON
{
"configs": {
"jdbcUrl": "jdbc:openmldb:///pulsar_openmldb_db?zk=localhost:6181&zkPath=/openmldb",
"tableName": "pulsar_openmldb_jdbc_sink"
}
} -
YAML
tenant: "public"
namespace: "default"
name: "jdbc-openmldb-sink"
inputs: [ "persistent://public/default/jdbc-openmldb-topic" ]
sinkType: "jdbc-openmldb"
configs:
jdbcUrl: "jdbc:openmldb:///pulsar_openmldb_db?zk=localhost:6181&zkPath=/openmldb"
tableName: "pulsar_openmldb_jdbc_sink"
Example of PostgreSQL
Before using the JDBC PostgreSQL sink connector, you need to create a configuration file through one of the following methods.
-
JSON
{
"configs": {
"userName": "postgres",
"password": "password",
"jdbcUrl": "jdbc:postgresql://localhost:5432/pulsar_postgres_jdbc_sink",
"tableName": "pulsar_postgres_jdbc_sink"
}
} -
YAML
tenant: "public"
namespace: "default"
name: "jdbc-postgres-sink"
inputs: [ "persistent://public/default/jdbc-postgres-topic" ]
sinkType: "jdbc-postgres"
configs:
userName: "postgres"
password: "password"
jdbcUrl: "jdbc:postgresql://localhost:5432/pulsar_postgres_jdbc_sink"
tableName: "pulsar_postgres_jdbc_sink"
For more information on how to use this JDBC sink connector, see connect Pulsar to PostgreSQL.
Example of SQLite
-
JSON
{
"configs": {
"jdbcUrl": "jdbc:sqlite:db.sqlite",
"tableName": "pulsar_sqlite_jdbc_sink"
}
} -
YAML
tenant: "public"
namespace: "default"
name: "jdbc-sqlite-sink"
inputs: [ "persistent://public/default/jdbc-sqlite-topic" ]
sinkType: "jdbc-sqlite"
configs:
jdbcUrl: "jdbc:sqlite:db.sqlite"
tableName: "pulsar_sqlite_jdbc_sink"