Retrieving table schemas - 6.2

Warning

If you are working on an SVN or Git managed project while the Manual lock option is selected in Talend Administration Center, be sure to lock manually your connection in the
Repository tree view before retrieving or updating
table schemas for it. Otherwise the connection is read-only and the Finish button of the wizard is not operable.

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:

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

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.

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

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.

Click OK to close the dialog box.

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:

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

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

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:

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.

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.

Dynamic: a data type that can be set for a single column at the end of a
schema to allow processing fields as VARCHAR(100) columns named either as
'Column<X>' or, if the input includes a header, from the column names
appearing in the header. For more information, see Dynamic schema.

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.