Retrieving table schemas

Talend Open Studio for ESB User Guide

EnrichVersion
6.4
EnrichProdName
Talend Open Studio for ESB
task
Design andĀ Development
EnrichPlatform
Talend Studio

To retrieve table schemas from the database connection you have just set up, right-click the connection item from the Repository tree view, and select Retrieve schema from the contextual menu.

Note

An error message will appear if there are no tables to retrieve from the selected database or if you do not have the correct rights to access this database.

A new wizard opens up where you can filter and show different objects (tables, views and synonyms) in your database connection, select tables of interest, and define table schemas.

Note

For the time being, synonyms option works for Oracle, IBM DB2 and MSSQL only.

Filtering database objects

In the Select Filter Conditions area, you can filter the database objects using either of the two options: Set the Name Filter or Use the Sql Filter to filter tables based on objects names or using SQL queries respectively.

To filter database tables based on their names, do the following:

  1. In the Select Filter Conditions area, select the Use the Name Filter option.

  2. In the Select Types area, select the check box(es) of the database object(s) you want to filter or display.

    Note

    Available options can vary according to the selected database.

  3. In the Set the Name Filter area, click Edit... to open the [Edit Filter Name] dialog box.

  4. Enter the filter you want to use in the dialog box. For example, if you want to recuperate the database objects which names start with "A", enter the filter "A%", or if you want to recuperate all database objects which names end with "type", enter "%type" as your filter.

  5. Click OK to close the dialog box.

  6. Click Next to open a new view on the wizard that lists the filtered database objects.

To filter database objects using an SQL query, do the following:

  1. In the Select Filter Conditions area, select the Use Sql Filter option.

  2. In the Set the Sql Filter field, enter the SQL query you want to use to filter database objects.

  3. Click Next to open a new view that lists the filtered database objects.

Selecting tables and defining table schemas

Once you have the filtered list of the database objects (tables, views and synonyms), do the following to load the schemas of the desired objects onto your Repository:

  1. Select one or more database objects on the list and click Next to open a new view on the wizard where you can see the schemas of the selected object.

    Note

    If no schema is visible on the list, click the Check connection button below the list to verify the database connection status.

  2. Modify the schemas if needed.

    Make sure the data type in the Type column is correctly defined.

    For more information regarding Java data types, including date pattern, see Java API Specification.

    Below are the commonly used Talend data types:

    • Object: a generic Talend data type that allows processing data without regard to its content, for example, a data file not otherwise supported can be processed with a tFileInputRaw component by specifying that it has a data type of Object.

    • List: a space-separated list of primitive type elements in an XML Schema definition, defined using the xsd:list element.

    • Document: a data type that allows processing an entire XML document without regarding to its content.

    Warning

    If your source database table contains any default value that is a function or an expression rather than a string, be sure to remove the single quotation marks, if any, enclosing the default value in the end schema to avoid unexpected results when creating database tables using this schema.

    By default, the schema displayed on the Schema panel is based on the first table selected in the list of schemas loaded (left panel). You can change the name of the schema and according to your needs. You can also customize the schema structure in the schema panel.

    The tool bar allows you to add, remove or move columns in your schema. In addition, you can load an XML schema from a file or export the current schema as XML.

    To retrieve a schema based on one of the loaded table schemas, select the DB table schema name in the drop-down list and click Retrieve schema. Note that the retrieved schema then overwrites any current schema and does not retain any custom edits.

    When done, click Finish to complete the database schema creation. All the retrieved schemas are displayed in the Table schemas sub-folder under the relevant database connection node.

    Now you can drag and drop any table schema of the database connection from the Repository tree view onto the design workspace as a new database component or onto an existing component to reuse the metadata. For more information, see How to use centralized metadata in a Job and How to set a repository schema.