tJDBCOutput properties for Apache Spark Batch

Property type

Either Built-In or Repository.

Built-In: No property data stored centrally.

Repository: Select the repository file where the properties are stored.

Click this icon to open a database connection wizard and store the database connection parameters you set in the component Basic settings view.

For more information about setting up and storing database connection parameters, see Centralizing database metadata.

Use an existing connection

Select this check box and in the Component List drop-down list, select the desired connection component to reuse the connection details you already defined.

JDBC URL

The JDBC URL of the database to be used. For example, the JDBC URL for the Amazon Redshift database is jdbc:redshift://endpoint:port/database.

jdbc:mysql://XX.XX.XX.XX:3306/Talend?user=ychen&password=talend

Driver JAR

Complete this table to load the driver JARs needed. To do this, click the [+] button under the table to add as many rows as needed, each row for a driver JAR, then select the cell and click the [...] button at the right side of the cell to open the Module dialog box from which you can select the driver JAR to be used. For example, the driver jar RedshiftJDBC41-1.1.13.1013.jar for the Redshift database.

For more information, see Importing a database driver.

Class Name

Enter the class name for the specified driver between double quotation marks. For example, for the RedshiftJDBC41-1.1.13.1013.jar driver, the name to be entered is com.amazon.redshift.jdbc41.Driver.

Username and Password

Enter the authentication information to the database you need to connect to.

To enter the password, click the [...] button next to the password field, enter the password in double quotes in the pop-up dialog box, and click OK to save the settings.

Available only for Spark V1.4. and onwards.

Table name

Name of the table to be written. Note that only one table can be written at a time.

Schema and Edit schema

A schema is a row description. It defines the number of fields (columns) to be processed and passed on to the next component. When you create a Spark Job, avoid the reserved word line when naming the fields.

This component offers the advantage of the dynamic schema feature. This allows you to retrieve unknown columns from source files or to copy batches of columns from a source without mapping each column individually. For further information about dynamic schemas, see Dynamic schema.

This dynamic schema feature is designed for the purpose of retrieving unknown columns of a table and is recommended to be used for this purpose only; it is not recommended for the use of creating tables.

Built-In: You create and store the schema locally for this component only.

Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs.

Action on data

Select an action to be performed on data of the table defined.

• Insert: Add new entries to the table.

• Update: Make changes to existing entries.

• Insert or update: Insert a new record. If the record with the given reference already exists, an update would be made.

• Update or insert: Update the record with the given reference. If the record does not exist in the index pool, a new record would be inserted.

• Delete: Remove entries corresponding to the input flow.

The following actions are only available if you use your component with Dataset API:

• Append: Add data to the existing table if data already exists.
• Overwrite: Overwrites the data in the existing table.
• ErrorIfExists: Show an exception if data already exists.
• Ignore: Leave the existing file as is if data already exists.

Die on error

Select the check box to stop the execution of the Job when an error occurs.

Additional JDBC parameters

Specify additional connection properties for the database connection you are creating. The properties are separated by semicolon and each property is a key-value pair, for example, encryption=1;clientname=Talend.

This field is not available if the Use an existing connection check box is selected.

Left protected char and Right protected char

Enter the symbol reserved by the database you are using, the left part in Left protected char and the right part in Right protected char, so that tJDBCOutput can generate SQL expressions with this reserved symbol properly placed.

For example, if you are using Oracle, double quotation marks (") are reserved for object names and so you need to enter the left and the right marks in these fields, respectively. Then at runtime, tJDBCOutput places double quotations marks around object names such as a table name.

Additional Columns

This option allows you to call SQL functions to perform actions on columns, provided that these are not insert, update or delete actions, or actions that require pre-processing. This option is not available if you have just created the database table (even if you delete it beforehand). Click the [+] button under the table to add column(s), and set the following parameters for each column.

Name: Type in the name of the schema column to be altered or inserted.

SQL expression: Type in the SQL statement to be executed in order to alter or insert the data in the corresponding column.

Position: Select Before, Replace or After, depending on the action to be performed on the reference column.

Reference column: Type in a reference column that the current component can use to locate or replace the new column, or the column to be modified.

Use field options

Select the check box for the corresponding column to customize a request, particularly if multiple actions are being carried out on the data.

• Key in update: Select the check box for the corresponding column based on which the data is updated.

• Key in delete: Select the check box for the corresponding column based on which the data is deleted.

• Updatable: Select the check box if the data in the corresponding column can be updated.

• Insertable: Select the check box if the data in the corresponding column can be inserted.

Use Batch

Select this check box to activate the batch mode for data processing.

This check box is available only when the Insert, the Update or the Delete option is selected from the Action on data list in the Basic settings view.

Batch Size

Specify the number of records to be processed in each batch.

This field appears only when the Use batch mode check box is selected.

Connection pool

In this area, you configure, for each Spark executor, the connection pool used to control the number of connections that stay open simultaneously. The default values given to the following connection pool parameters are good enough for most use cases.

• Max total number of connections: enter the maximum number of connections (idle or active) that are allowed to stay open simultaneously.

  The default number is 8. If you enter -1, you allow unlimited number of open connections at the same time.

• Max waiting time (ms): enter the maximum amount of time at the end of which the response to a demand for using a connection should be returned by the connection pool. By default, it is -1, that is to say, infinite.

• Min number of idle connections: enter the minimum number of idle connections (connections not used) maintained in the connection pool.

• Max number of idle connections: enter the maximum number of idle connections (connections not used) maintained in the connection pool.

Evict connections

Select this check box to define criteria to destroy connections in the connection pool. The following fields are displayed once you have selected it.

• Time between two eviction runs: enter the time interval (in milliseconds) at the end of which the component checks the status of the connections and destroys the idle ones.

• Min idle time for a connection to be eligible to eviction: enter the time interval (in milliseconds) at the end of which the idle connections are destroyed.

• Soft min idle time for a connection to be eligible to eviction: this parameter works the same way as Min idle time for a connection to be eligible to eviction but it keeps the minimum number of idle connections, the number you define in the Min number of idle connections field.

Usage rule

This component is used as an end component and requires an input link.

This component should use a tJDBCConfiguration component present in the same Job to connect to a database. You need to drop a tJDBCConfiguration component alongside this component and configure the Basic settings of this component to use tJDBCConfiguration.

This component, along with the Spark Batch component Palette it belongs to, appears only when you are creating a Spark Batch Job.

Note that in this documentation, unless otherwise explicitly stated, a scenario presents only Standard Jobs, that is to say traditional Talend data integration Jobs.

Spark Connection

This connection is effective on a per-Job basis.