Database Sink

Plugin version: 2.11.0

Writes records to a database table. Each record will be written to a row in the table. For example, you periodically build a recommendation model for products on your online store. The model is stored in a FileSet and you want to export the contents of the FileSet to a database table where it can be served to your users.

Configuration

Properties

Macro Enabled?

Version Introduced

Description

Properties

Macro Enabled?

Version Introduced

Description

Use Connection

No

6.7.0/2.9.0

Optional. Whether to use a connection. If a connection is used, you do not need to provide the credentials.

Connection

Yes

6.7.0/2.9.0

Optional. Name of the connection to use. Project and service account information will be provided by the connection. You also can use the macro function ${conn(connection-name)}.

JDBC Driver name

Yes

6.7.0/2.9.0

Required. Select the JDBC driver to use.

Connection String

Yes

 

Required. JDBC connection string including database name. 

Username

Yes

 

Optional. User identity for connecting to the specified database. Required for databases that need authentication. Optional for databases that do not require authentication.

Password

Yes

 

Password to use to connect to the specified database. Required for databases that need authentication. Optional for databases that do not require authentication.

Connection Arguments

Yes

 

Optional. A list of arbitrary string tag/value pairs as connection arguments. These arguments will be passed to the JDBC driver, as connection arguments, for JDBC drivers that may need additional configurations. This is a semicolon-separated list of key-value pairs, where each pair is separated by an equals ‘=’ and specifies the key and value for the argument. For example, key1=value1;key2=value specifies that the connection will be given arguments key1 mapped to value1 and the argument key2 mapped to value2

Reference Name

No

 

Required. Name used to uniquely identify this sink for lineage, annotating metadata, etc. Typically, the name of the Table Name or view name.

Table Name

Yes

 

Required. Name of the table to export to.

Columns

Yes

 

Required. Comma-separated list of columns in the specified table to export to.

Enable Auto-Commit

No

 

Optional. Whether to enable auto-commit for queries run by this sink. In most cases, set to false. If you use a JDBC driver that results in an error when the commit operation is run, set to true.

Default is False.

Column Name Case

Yes

 

Optional. Sets the case of the column names returned by the column check query. Possible options are upper or lower. By default or for any other input, the column names are not modified and the names returned from the database are used as-is. Note that setting this property provides predictability of column name cases across different databases but might result in column name conflicts if multiple column names are the same when the case is ignored.

Default is No change.

Transaction Isolation Level

Yes

 

Optional. The transaction isolation level for queries run by this sink. For more information, see java.sql.Connection#setTransactionIsolation. The Phoenix JDBC driver will throw an exception if the Phoenix database does not have transactions enabled and this setting is set to true. For drivers like that, this should be set to TRANSACTION_NONE.

Default is TRANSACTION_SERIALIZABLE.

Using Third-Party JARs

For information on how to use the JDBC jar to talk to the database sink, see Using Third-Party JARs.

Example

This example connects to a database using the specified Connection String, which means it will connect to the prod database of a PostgreSQL instance running on localhost. Each input record will be written to a row of the users table, with the value for each column taken from the value of the field in the record. For example, the id field in the record will be written to the id column of that row.

Property

Value

Property

Value

Reference Name

databasesink

Plugin Name

postgres

Plugin Type

jdbc

Connection String

jdbc:postgresql://localhost:5432/prod

Table Name

users

Columns

id,name,email,phone

Username

postgres

Password

""



Created in 2020 by Google Inc.