JDBC Sink connector

The JDBC Sink connector is a Stateless NiFi dataflow developed by Cloudera that is running in the Kafka Connect framework. Learn about the connector, its properties, and configuration.

The JDBC Sink connector fetches messages from Kafka and loads them into a database table. The topic this connector receives messages from is determined by the value of the topics property in the configuration. The messages the connector receives from Kafka can be in either Avro or JSON format and must contain records that can be inserted into the database table. The schema of the records can be a predefined schema retrieved from Schema Registry (for both Avro and JSON data), it can be embedded in the Avro data, or inferred from the JSON data. The strategy that is used to retrieve the schema is determined by the Schema Access Strategy property.

Properties and configuration

Configuration is passed to the connector in a JSON file during creation. The properties of the connector can be categorized into three groups. These are as follows:

Common connector properties

These are the properties of the Kafka Connect framework that are accepted by all connectors. For a comprehensive list of these properties, see the Apache Kafka documentation.

Stateless NiFi Sink properties

These are the properties that are specific to the Stateless NiFi Sink connector. All Stateless NiFi Sink connectors share and accept these properties. For a comprehensive list of these properties, see the Stateless NiFi Sink properties reference.

Connector/dataflow-specific properties

These properties are unique to this specific connector. Or to be more precise, unique to the dataflow running within the connector. These properties use the following prefix:

parameter.[***CONNECTOR NAME***] Parameters:

For a comprehensive list of these properties, see the JDBC Sink properties reference.

Notes and limitations

Required properties must be assigned a valid value even if they are not used in the particular configuration. If a required property is not used, either leave its default value, or completely remove the property from the configuration JSON.
If a property that has a default value is completely removed from the configuration JSON, the system uses the default value.
Properties not marked as required must be completely removed from the configuration JSON if not set.
Schema Branch and Schema Version can not be specified at the same time.

Configuration example

In this example, the connector fetches messages in JSON format from Kafka, translates it to a SQL INSERT statement in order to load the data into a PostgreSQL database. The schema of the records must match the schema of the database table. The record schema in this example is inferred from the input JSON.

{
 "connector.class": "org.apache.nifi.kafka.connect.StatelessNiFiSinkConnector",
 "meta.smm.predefined.flow.name": "JDBC Sink",
 "meta.smm.predefined.flow.version": "1.0.0",
 "key.converter": "org.apache.kafka.connect.storage.StringConverter",
 "value.converter": "org.apache.kafka.connect.converters.ByteArrayConverter",
 "tasks.max": "1",
 "nexus.url": "https://repository.cloudera.com/artifactory/repo",
 "extensions.directory": "/tmp/nifi-stateless-extensions",
 "working.directory": "/tmp/nifi-stateless-working",
 "failure.ports": "Retry from PutDatabaseRecord,Failure from PutDatabaseRecord",
 "topics": "[***KAFKA TOPIC NAME***]",
 "parameter.JDBC Sink Parameters:Database Connection URL": "[***JDBC URL***]",
 "parameter.JDBC Sink Parameters:Database Driver Location": "[***PATH TO JDBC DRIVER***]",
 "parameter.JDBC Sink Parameters:Database Driver Class Name": "org.postgresql.Driver",
 "parameter.JDBC Sink Parameters:Database Type": "PostgreSQL",
 "parameter.JDBC Sink Parameters:Database User Name": "[***USERNAME***]",
 "parameter.JDBC Sink Parameters:Database User Password": "[***PASSWORD***]",
 "parameter.JDBC Sink Parameters:Database Table Name": "[***TABLE NAME***]",
 "parameter.JDBC Sink Parameters:Database Schema Name": "[***SCHEMA NAME***]",
 "parameter.JDBC Sink Parameters:Kafka Message Data Format": "JSON",
 "parameter.JDBC Sink Parameters:Schema Access Strategy": "Infer Schema",
 "parameter.JDBC Sink Parameters:Quote Table Name": "true",
 "parameter.JDBC Sink Parameters:Quote Column Names": "true"
}

The following list collects the properties from the configuration example that must be customized for this use case.

topics: The name of the Kafka topic the connector fetches messages from.
Database Connection URL: The JDBC URL of the PostgreSQL database. For example, jdbc:postgresql://myhost:5432/mydb.
Database Driver Location: The path to the PostgreSQL JDBC driver JAR file (or the directory containing the JAR).
Database Driver Class Name: The Java class name of the PostgreSQL Driver implementation.
Database Type: The type of the database. Because this is a PostgreSQL example, this property is set to PostgreSQL.
Database User Name: The username used for authenticating to the database.
Database User Password: The name of the database table to load data to.
Kafka Message Data Format: The format of the messages the connector receives from Kafka.
Schema Access Strategy: Specifies the strategy used for determining the schema of the Kafka record. In this example, the property is set to Infer Schema. This means that the schema is determined (inferred) from the JSON data.
Quote Table Name and Quote Column Name: These properties specify whether to place double quotes around the table or column names in the SQL statement used by the connector. Some databases, like Oracle and PostgreSQL are case-sensitive for table and column names. By default, Oracle converts all table column names to uppercase if they are not double-quoted upon creation. Similarly, PostgreSQL converts names to lowercase. If tables or columns are double-quoted upon creation and have camel cased names, set these properties to true so that double quotes are also used around the table or column names in the SQL statement used by the connector. If this is not done, the connector will not find the tables on the database.

Stateless NiFi Sink properties reference

Review the following reference for a comprehensive list of the connector properties that are specific to the Stateless NiFi Sink connector.

In addition to the properties listed here, Stateless NiFi connectors also accept the properties of the Kafka Connect framework. For a comprehensive list of these properties, see the Apache Kafka documentation.

attribute.prefix

Description: The prefix to add to the key of each header that matches the regular expression specified in headers.as.attributes.regex. For example, if the header key is MyHeader, its value is MyValue, headers.as.attributes.regex is set to My.*, and this property is set to kafka, the flowfile that is created for the Kafka message will have an attribute named kafka.MyHeader with a value of MyValue.
Default Value
Accepted Values
Required: false

dataflow.timeout

Description: Specifies the maximum amount of time to wait for the dataflow to complete. If the dataflow does not complete before this timeout, the thread is interrupted and the dataflow is considered as a failure. The session is rolled back and the connector retriggers the flow. Defaults to 60 seconds if not specified.
Default Value: 60 seconds
Accepted Values
Required: false

extensions.directory

Description: Specifies the directory that stores downloaded extensions. Extensions are the NAR (NiFi Archive) files containing the processors and controller services a flow might use. Since Stateless NiFi is only the NiFi engine, it does not contain any of the processors and controller services you might use in your flow. When deploying the connector with the custom flow, the system needs to download the specific extensions that your flow uses from Nexus (unless they are already present in this directory). These extensions are stored in this directory. Because the default directory might not be writable, and to aid in upgrade scenarios, Cloudera recommends that you always specify an extensions directory.
Default Value: /tmp/nifi-stateless-extensions
Accepted Values
Required: true

failure.ports

Description: A comma separated list of output ports that are considered as failure conditions. If any flowfile is routed to an output port specified in this property, the dataflow is considered a failure and the session is rolled back. After a set amount of time, the dataflow reattempts to process the Kafka record. Any data transferred to an output port that is not in the list of failure ports is discarded.
Because of how Stateless NiFi Sink connectors behave, even if a single flowfile ends up in an output port that is marked as failure, the entire sessions is rolled back with all messages in the batch. Furthermore, if a flowfile ends up in a failure port in each subsequent iteration, the result is an endless loop. With some sink connectors (for example. MQTT Sink) this is the desired behavior. For more information regarding this behavior, see Dataflow execution and scheduling.
Default Value
Accepted Values
Required: false

flow.snapshot

Description: Specifies the dataflow to run. When using Streams Messaging Manager to deploy a connector, the value you set in this property must be a JSON object. URLs, file paths, or escaped JSON strings are not supported when using Streams Messaging Manager. Alternatively, if using the Kafka Connect REST API to deploy a connector, this can be a file containing the dataflow, a URL that points to a dataflow, or a string containing the entire dataflow as an escaped JSON. Cloudera however, does not recommend using the Kafka Connect REST API to interact with this connector or Kafka Connect.
Default Value
Accepted Values
Required: true

headers.as.attributes.regex

Description: A Java regular expression that is evaluated against all Kafka record headers. Headers are added to the flowfile as an attribute if the header key matches the regular expression. The header key is used as the attribute name. The header value is used as the attribute value. Additionally, the name of the attribute can also contain an optional prefix which is defined by the attribute.prefix property.
Default Value
Accepted Values
Required: false

input.port

Description: The name of the input port in the NiFi dataflow that Kafka records are sent to. If the dataflow contains exactly one input port, this property is optional and can be omitted. However, if the dataflow contains multiple input ports, this property must be specified.
Default Value
Accepted Values
Required: false

krb5.file

Description: Specifies the krb5.conf file to use if the dataflow interacts with any services that are secured using Kerberos. Defaults to /etc/krb5.conf if not specified.
Default Value: /etc/krb5.conf
Accepted Values
Required: false

name

Description: The name of the connector. On the Streams Messaging Manager UI, the connector names are specified using the Enter Name field. The name that you enter in the Enter Name field is automatically set as the value of the name property when the connector is deployed. Because of this, the name property is omitted from the configuration template provided in Streams Messaging Manager. If you manually add the name property to the configuration in Streams Messaging Manager, ensure that the value you set matches the connector name specified in the Enter Name field. Otherwise, the connector fails to deploy.
Default Value
Accepted Values
Required: true

nexus.url

Description: Specifies the Base URL of the Nexus instance to source extensions from. If configuring a Nexus instance that has multiple repositories, include the name of the repository in the URL. For example, https://nexus-private.myorganization.org/nexus/repository/my-repository/. If the property is not specified, the necessary extensions (the ones used by the flow) must be provided in the extensions directory before deploying the connector.
Default Value
Accepted Values
Required: true

parameter.`[FLOW PARAMETER NAME]`

Description: Specifies a parameter to use in the dataflow. For example, assume that you have the following entry in your connector configuration "parameter.Directory": "/mydir". In a case like this, any parameter context in the dataflow that has a parameter named Directory gets the specified value (/mydir). If the dataflow has child process groups, and those child process groups have their own parameter contexts, the value is used for all parameter contexts that contain a parameter named Directory. Parameters can also be applied to specific parameter contexts only. This can be done by prefixing the parameter name (Directory) with the name of the parameter context followed by a colon. For example, parameter.My Context:Directory only applies the specified value for the Directory parameter in the Parameter Context named My Context.
Default Value
Accepted Values
Required: false

working.directory

Description: Specifies a directory on the Connect server that NiFi should use for unpacking extensions that it needs to perform the dataflow. The contents of extensions.directory are unpacked here. Defaults to /tmp/nifi-stateless-working if not specified.
Default Value: /tmp/nifi-stateless-working
Accepted Values
Required: false

JDBC Sink properties reference

Review the following reference for a comprehensive list of the connector properties that are specific to the JDBC Sink connector.

The properties listed in this reference must be added to the connector configuration with the following prefix:

parameter.[***CONNECTOR NAME***] Parameters:

In addition to the properties listed here, this connector also accepts certain properties of the Kafka Connect framework as well as the properties of the NiFi Stateless Sink connector. When creating a new connector using the Streams Messaging Manager UI, all valid properties are presented in the default configuration template. You can view the configuration template to get a full list of valid properties. In addition, for more information regarding the accepted properties not listed here, you can review the Apache Kafka documentation and the Stateless NiFi Sink properties reference.

Database Connection URL

Description: The database specific connection URL used for connecting to the database. For example, jdbc:postgresql://localhost:5432/postgres.
Default Value
Accepted Values
Required: true

Database Driver Class Name

Description: The database driver class name. For example, org.postgresql.Driver.
Default Value
Accepted Values
Required: true

Database Driver Location

Description: A comma-separated list of files or folders containing the JDBC client libraries.
Default Value
Accepted Values
Required: true

Database Schema Name

Description: The name of the database schema containing the table to load data.
Default Value
Accepted Values
Required: false

Database Table Name

Description: The name of the database table to load data.
Default Value
Accepted Values
Required: true

Database Type

Description: The database type used for generating database specific SQL queries.
Default Value: Generic
Accepted Values: Generic, Oracle, Oracle 12+, MS SQL 2008, MS SQL 2012+, MySQL, PostgreSQL
Required: true

Database User Name

Description: The database user name. If username/password authentication is not required by the database server, this property must be completely removed from the configuration JSON.
Default Value
Accepted Values
Required: false

Database User Password

Description: The database user password. If username/password authentication is not required by the database server, this property must be completely removed from the configuration JSON.
Default Value
Accepted Values
Required: false

Date Format

Description: Specifies the format to use when reading date fields from JSON.
Default Value: yyyy-MM-dd
Accepted Values
Required: true

Kafka Message Data Format

Description: Specifies the format of the messages the connector receives from Kafka.
Default Value: Avro
Accepted Values: Avro, JSON
Required: true

Kerberos Keytab

Description: The fully-qualified filename of the kerberos keytab associated with the principal for accessing Schema Registry.
Default Value: The location of the default keytab which is empty and can only be used for unsecure connections.
Accepted Values
Required: true

Kerberos Principal

Description: The Kerberos principal used for authenticating to Schema Registry.
Default Value: default
Accepted Values
Required: true

Quote Column Names

Description: Specifies whether to use quotes around the column names in the generated SQL statement.
Default Value: false
Accepted Values: true, false
Required: true

Quote Table Name

Description: Specifies whether to use quotes around the table name in the generated SQL statement.
Default Value: false
Accepted Values: true, false
Required: true

Schema Access Strategy

Description

Specifies the strategy used for determining the schema of the Kafka record. The value you set here depends on the data format set in

Kafka Message Data
              Format.

If set to Schema Registry, the schema is read from Schema Registry. This setting can be used with both Avro and JSON formats.
If set to Infer Schema, the schema is inferred based on the input file. This setting can only be used if Kafka Message Data Format is JSON.
If set to Embedded Schema, the schema embedded in the input is used. This setting can only be used if Kafka Message Data Format is Avro.
If set to HWX Content-Encoded Schema Reference, the schema is read from Schema Registry. This setting can only be used if Kafka Message Data Format is Avro. In this case the Avro messages are expected to have a reference to the schema in Schema Registry encoded within the message content.

Default Value

Schema Registry

Accepted Values

Schema Registry, Infer Schema, Embedded Schema, HWX Content-Encoded Schema Reference

Required

true

Schema Branch

Description: The name of the branch to use when looking up the schema in Schema Registry. Schema Branch and Schema Version cannot be specified at the same time. If one is specified, the other needs to be removed from the configuration. If Schema Registry is not used, this property must be completely removed from the configuration.
Default Value
Accepted Values
Required: false

Schema Name

Description: The schema name to look up in Schema Registry. If the Schema Access Strategy property is set to Schema Registry, this property must contain a valid schema name. If Schema Registry is not used, this property must be completely removed from the configuration JSON.
Default Value
Accepted Values
Required: false

Schema Registry URL

Description: The URL of the Schema Registry server. If Schema Registry is not used, this property must be completely removed from the configuration JSON.
Default Value: http://localhost:7788/api/v1
Accepted Values
Required: true

Schema Version

Description: The version of the schema to look up in Schema Registry. If Schema Registry is used and a schema version is not specified, the latest version of the schema is retrieved. Schema Branch and Schema Version cannot be specified at the same time. If one is specified, the other needs to be removed from the configuration. If Schema Registry is not used, this property must be completely removed from the configuration.
Default Value
Accepted Values
Required: false

Statement Type

Description: Specifies the type of SQL statement that is generated to put data into the database.
Default Value: INSERT
Accepted Values: INSERT, INSERT_IGNORE, UPDATE, UPSERT
Required: true

Time Format

Description: Specifies the format to use when reading Time fields from JSON.
Default Value: HH:mm:ss
Accepted Values
Required: true

Timestamp Format

Description: Specifies the format to use when reading Timestamp fields from JSON.
Default Value: yyyy-MM-dd HH:mm:ss.SSS
Accepted Values
Required: true

Truststore Filename

Description: The fully-qualified filename of a truststore. This truststore is used to establish a secure connection with Schema Registry using HTTPS.
Default Value: The location of the default truststore which is empty and can only be used for unsecure connections.
Accepted Values
Required: true

Truststore Password

Description: The password used to access the contents of the truststore configured in the Truststore Filename property
Default Value: password
Accepted Values
Required: true

Truststore Type

Description: The type of the truststore configured in the Truststore Filename property.
Default Value: JKS
Accepted Values: BCFKS, PKCS12, JKS
Required: true