To create a database connection from scratch, expand Metadata in the Repository tree view, right-click Db Connections and select Create connection from the contextual menu to open the database connection setup wizard.
To centralize database connection parameters you have defined in a Job, click the icon in the Basic settings view of the relevant database component with its Property Type set to Built-in to open the database connection setup wizard.
To modify an existing database connection, right-click the connection item from the Repository tree view, and select Edit connection to open the connection setup wizard.
Then define the general properties and parameters of the connection in the wizard.
In the connection setup wizard, give your connection a name in the Name field. This name will appear as the database connection name under the Metadata node of the Repository tree view.
Fill in the optional Purpose and Description fields as required. The information you fill in the Description field will appear as a tooltip when you move your mouse pointer over the connection.
If needed, set the connection version and status in the Version and Status fields respectively. You can also manage the version and status of a repository item in the [Project Settings] dialog box. For more information, see Version management and Status management respectively.
If needed, click the Select button next to the Path field to select a folder under the Db connections node to hold your newly created database connection. Note that you cannot select a folder if you are editing an existing database connection, but you can drag and drop a connection to a new folder whenever you want.
Click Next when completed. The second step requires you to fill in or edit database connection data.
Select the type of the database to which you want to connect and fill in the connection details. The fields you need to fill vary depending on the database type you select.
When you are creating the database connection of some databases like AS/400, HSQDB, Informix, Microsoft SQL, MySQL, Oracle, SAP HANA, Sybase, or Teradata, you can specify additional connection properties through the Additional parameters field in the Database Settings area.
In Talend Studio 6.0 and onwards, due to limitations of Java 8, ODBC is no longer supported for Access database connections, and the only supported database driver type is JDBC.
Also due to Java 8 limitations, you cannot create Generic ODBC or Microsoft SQL Server (ODBC) connections in Talend Studio 6.0 and onwards unless you import such connections created in an earlier version of Talend Studio - in that case, you can create Generic ODBC and Microsoft SQL Server (ODBC) connections but they work only with Java 7.
For an MS SQL Server (JDBC) connection, when Microsoft is selected from the Db Version list, you need to download the Microsoft JDBC driver for SQL Server on Microsoft Download Center, unpack the downloaded zip file, choose a jar in the unzipped folder based on your JRE version, rename the jar to mssql-jdbc.jar and install it manually. For more information about choosing the jar, see the System Requirements information on Microsoft Download Center.
If you need to connect to Hive, we recommend using one of the Talend solutions with Big Data.
If you are creating an MSSQL connection, in order to be able to retrieve all table schemas in the database, be sure to:
- enter dbo in the Schema field if you are connecting to MSSQL 2000,
- remove dbo from the Schema field if you are connecting to MSSQL 2005/2008.
Click Check to check your connection.
If the connection fails, a message box is displayed to indicate this failure and from that message box. From that message box, click the Details button to read further information.
If a missing library or driver (.jar file) has provoked this failure, you can read that from the Details panel and then install the specified library or driver.
The Studio provides multiple approaches to automate the installation. For further information, see the chapter describing how to install external modules of the Talend Installation Guide.
If you are creating a Teradata connection, select Yes for the Use SQL Mode option at the bottom of the wizard to use the SQL queries to retrieve metadata. The JDBC driver is not recommended with this database because of possible bad performance.
If needed, fill in the database properties information. That is all for the first operation on database connection setup. Click Finish to close the connection setup wizard.
The newly created database connection will be saved under the Db Connections node in the Repository tree view, and several folders for SQL queries and different types of schemas, such as Calculation View schemas (only for SAP HANA), Synonym schemas (for Oracle, IBM DB2 and MSSQL), Table schemas, and View schemas will be created under the database connection node.
Now you can drag and drop the database connection onto the design workspace as a database component to reuse the defined database connection details in your Job.