tJDBCOutput properties for Apache Spark Streaming

JDBC

author
Talend Documentation Team
EnrichVersion
6.5
EnrichProdName
Talend Data Management Platform
Talend Real-Time Big Data Platform
Talend Big Data
Talend MDM Platform
Talend Big Data Platform
Talend Open Studio for ESB
Talend ESB
Talend Data Fabric
Talend Data Integration
Talend Open Studio for MDM
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Data Services Platform
task
Data Governance > Third-party systems > Database components > JDBC components
Design and Development > Third-party systems > Database components > JDBC components
Data Quality and Preparation > Third-party systems > Database components > JDBC components
EnrichPlatform
Talend Studio

These properties are used to configure tJDBCOutput running in the Spark Streaming Job framework.

The Spark Streaming tJDBCOutput component belongs to the Databases family.

This component can be used to write data to a RDS MariaDB, a RDS PostgreSQL or a RDS SQLServer database.

The component in this framework is available only if you have subscribed to Talend Real-Time Big Data Platform or Talend Data Fabric.

Basic settings

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 Talend Studio User Guide.

Use an existing connection

Select this check box and in the Component List click the relevant connection component to reuse the connection details you already defined.

JDBC URL

Specify 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.

If you are using Spark V1.3, this URL should contain the authentication information, such as:
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 Select Module wizard from which you can select the driver JAR of your interest. For example, the driver jar RedshiftJDBC41-1.1.13.1013.jar for the Redshift database.

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, and then in the pop-up dialog box enter the password between double quotes and click OK to save the settings.

Available only for Spark V1.4. and onwards.

Table

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 Repository. When you create a Spark Job, avoid the reserved word line when naming the fields.

 

Built-In: You create and store the schema locally for this component only. Related topic: see Talend Studio User Guide.

 

Repository: You have already created the schema and stored it in the Repository. You can reuse it in various projects and Job designs. Related topic: see Talend Studio User Guide.

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.

Die on error

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

Advanced settings

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 corrsponding 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

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 Streaming component Palette it belongs to, appears only when you are creating a Spark Streaming 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

You need to use the Spark Configuration tab in the Run view to define the connection to a given Spark cluster for the whole Job. In addition, since the Job expects its dependent jar files for execution, you must specify the directory in the file system to which these jar files are transferred so that Spark can access these files:
  • Yarn mode: when using Google Dataproc, specify a bucket in the Google Storage staging bucket field in the Spark configuration tab; when using other distributions, use a tHDFSConfiguration component to specify the directory.

  • Standalone mode: you need to choose the configuration component depending on the file system you are using, such as tHDFSConfiguration or tS3Configuration.

This connection is effective on a per-Job basis.