tJDBCInput
Reads any database using a JDBC API connection and extracts fields based on a
query.
tJDBCInput executes a database query
with a strictly defined order which must correspond to the schema definition. Then it
passes on the field list to the next component via a Main row link.
Depending on the Talend solution you
are using, this component can be used in one, some or all of the following Job
frameworks:
-
Standard: see tJDBCInput Standard properties.
The component in this framework is generally available.
-
MapReduce: see tJDBCInput MapReduce properties.
The component in this framework is available only if you have subscribed to one
of the
Talend
solutions with Big Data. -
Spark Batch: see tJDBCInput properties for Apache Spark Batch.
This component also allows you to connect and read data from a RDS MariaDB, a
RDS PostgreSQL or a RDS SQLServer database.The component in this framework is available only if you have subscribed to one
of the
Talend
solutions with Big Data.
tJDBCInput Standard properties
These properties are used to configure tJDBCInput running in the Standard Job framework.
The Standard
tJDBCInput component belongs to the Databases family.
The component in this framework is generally available.
Basic settings
Property type |
Either Built-In or Repository. |
|
Built-In: No property data stored centrally. |
|
Repository: Select the repository file where the |
Use an existing connection |
Select this check box and in the Component Note:
When a Job contains the parent Job and the child Job, if you need to share an
existing connection between the two levels, for example, to share the connection created by the parent Job with the child Job, you have to:
For an example about how to share a database connection across Job levels, see |
|
Click this icon to open a database connection wizard and store the database connection For more information about setting up and storing database connection parameters, see |
JDBC URL |
Specify the JDBC URL of the database to be used. For example, the |
Driver JAR |
Complete this table to load the driver JARs needed. To do this, click the |
Class Name |
Enter the class name for the specified driver between double quotation marks. |
Username and Password |
Enter the authentication information to the database you need to connect To enter the password, click the […] button next to the |
Schema and Edit |
A schema is a row description. It defines the number of fields (columns) to This component offers the This dynamic schema |
|
Built-In: You create and store the |
|
Repository: You have already created |
Click Edit schema to make changes to the schema.
|
|
Table Name |
Type in the name of the table from which you need to read data. |
Query type and Query |
Specify the database query statement paying particularly attention to the If using the dynamic schema feature, the SELECT query must |
Specify a data source alias |
Select this check box and specify the alias of a data source created on the If you use the component’s own DB configuration, your data source connection will be This check box is not available when the Use an existing |
Advanced settings
Use cursor |
Select this check box to specify the number of rows you want to |
Trim all the String/Char columns |
Select this check box to remove leading whitespace and trailing |
Trim column |
This table is filled automatically with the schema being used. Select the check |
Enable Mapping File for Dynamic |
Select this check box to use the specified metadata mapping file when For more information about metadata mapping files, see the section on |
Mapping File |
Specify the metadata mapping file to use by selecting a type of This list field appears only when the Enable |
tStatCatcher Statistics |
Select this check box to collect log data at the component |
Global Variables
Global Variables |
NB_LINE: the number of rows processed. This is an After
QUERY: the query statement being processed. This is a Flow
ERROR_MESSAGE: the error message generated by the A Flow variable functions during the execution of a component while an After variable To fill up a field or expression with a variable, press Ctrl + For further information about variables, see |
Usage
Usage rule |
This component covers all possible SQL queries for any database using |
Dynamic settings |
Click the [+] button to add a The Dynamic settings table is For examples on using dynamic parameters, see Scenario: Reading data from databases through context-based dynamic connections and Scenario: Reading data from different MySQL databases using dynamically loaded connection parameters. For more information on Dynamic |
Related scenarios
For related topics, see:
Related topic in tContextLoad: see Scenario: Reading data from different MySQL databases using dynamically loaded connection parameters.
tJDBCInput MapReduce properties
These properties are used to configure tJDBCInput running in the MapReduce Job framework.
The MapReduce
tJDBCInput component belongs to the MapReduce family.
The component in this framework is available only if you have subscribed to one
of the
Talend
solutions with Big Data.
Basic settings
Property type |
Either Built-In or Repository. |
|
Built-In: No property data stored centrally. |
|
Repository: Select the repository file where the |
|
Click this icon to open a database connection wizard and store the database connection For more information about setting up and storing database connection parameters, see |
JDBC URL |
Specify the JDBC URL of the database to be used. For example, the |
Driver JAR |
Complete this table to load the driver JARs needed. To do this, click the |
Class Name |
Enter the class name for the specified driver between double quotation marks. |
Username and |
Specify the JDBC URL of the database to be used. For example, the To enter the password, click the […] button next to the |
Schema and Edit |
A schema is a row description. It defines the number of fields (columns) to |
|
Built-In: You create and store the |
|
Repository: You have already created |
Click Edit schema to make changes to the schema.
|
|
Table Name |
Type in the name of the table from which you need to read data. |
Die on error |
Select the check box to stop the execution of the Job when an error Clear the check box to skip any rows on error and complete the process for |
Query type and |
Specify the database query statement paying particularly attention to the If using the dynamic schema feature, the SELECT query must |
Usage
Usage rule |
In a For further information about a Note that in this documentation, unless otherwise |
Hadoop Connection |
You need to use the Hadoop Configuration tab in the This connection is effective on a per-Job basis. |
Limitation |
We recommend using the following databases with the Map/Reduce It may work with other databases as well, but these may not |
Related scenarios
No scenario is available for the Map/Reduce version of this component yet.
tJDBCInput properties for Apache Spark Batch
These properties are used to configure tJDBCInput running in the Spark Batch Job framework.
The Spark Batch
tJDBCInput component belongs to the Databases family.
This component also allows you to connect and read data from a RDS MariaDB, a
RDS PostgreSQL or a RDS SQLServer database.
The component in this framework is available only if you have subscribed to one
of the
Talend
solutions with Big Data.
Basic settings
Property type |
Either Built-In or Repository. |
||
|
Built-In: No property data stored centrally. |
||
|
Repository: Select the repository file where the |
||
Use an existing connection |
Select this check box and in the Component |
||
JDBC URL |
Specify the JDBC URL of the database to be used. For example, the If you are using Spark V1.3, this URL should contain the authentication
information, such as:
|
||
Driver JAR |
Complete this table to load the driver JARs needed. To do this, click the |
||
Class Name |
Enter the class name for the specified driver between double quotation marks. |
||
Username and Password |
Enter the authentication information to the database you need to connect To enter the password, click the […] button next to the Available only for Spark V1.4. and onwards. |
||
Schema and Edit |
A schema is a row description. It defines the number of fields (columns) to |
||
|
Built-In: You create and store the |
||
|
Repository: You have already created |
||
Click Edit schema to make changes to the schema.
|
|||
Table Name |
Type in the name of the table from which you need to read data. |
||
Query type and Query |
Specify the database query statement paying particularly attention to the If you are using Spark V2.0 onwards, the Spark SQL does not recognize the For example, if you need to perform a query in a table system.mytable, in which the system prefix indicates the |
||
Guess Query |
Click the Guess Query button to |
||
Guess schema |
Click the Guess schema button to |
Advanced settings
Additional JDBC parameters |
Specify additional connection properties for the database connection you are This field is not available if the Use an existing |
Spark SQL JDBC parameters |
Add the JDBC properties supported by Spark SQL to this table. For a list of This component automatically set the url, dbtable and driver properties by using the configuration |
Use cursor |
Select this check box to specify the number of rows you want to |
Trim all the String/Char columns |
Select this check box to remove leading whitespace and trailing |
Trim column |
This table is filled automatically with the schema being used. Select the check |
Enable partitioning |
Select this check box to read data in partitions. Define, within double quotation marks, the following parameters to configure
the partitioning:
The average size of the partitions is the result of the difference between the For example, to partition 1000 rows into 4 partitions, if you enter 0 for the |
Usage
Usage rule |
This component is used as a start component and requires an output link.. This component should use a tJDBCConfiguration component present in the same Job to connect to a This component, along with the Spark Batch component Palette it belongs to, appears only Note that in this documentation, unless otherwise |
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:
This connection is effective on a per-Job basis. |
Related scenarios
For a scenario about how to use the same type of component in a Spark Batch Job, see Writing and reading data from MongoDB using a Spark Batch Job.