August 17, 2023

tCreateTable – Docs for ESB 5.x

  2. Talend for ESB Docs
  3. Docs 5.x
  4. tCreateTable – Docs for ESB 5.x

tCreateTable

tCreateTable_icon32_white.png

tCreateTable Properties

Component family

Databases

  

Function

tCreateTable creates, drops and
creates and clears the specified table.

Purpose

This specific component helps create or drop any database
table

Basic settings

Property type

Either Built-in or Repository.

Since version 5.6, both the Built-In mode and the Repository mode are
available in any of the Talend solutions.

 

 

Built-in: No property data stored
centrally.

 

 

Repository: Select the repository
file in which the properties are stored. The fields that follow are
completed automatically using the data retrieved.

 

Database Type

Select the DBMS type from the list. The component properties may
differ slightly according to the database type selected from the
list.

DB Version

Select the database version in use.

 

Table Action

Select the action to be carried out on the database among:

Create table: when you know already
that the table doesn’t exist.

Create table if not exists: when
you don’t know whether the table is already created or not

Drop table if exits and create:
when you want to drop an existing table and create it again.
 

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.

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:

  1. In the parent level, register the database connection to be shared in the
    Basic settings view of the connection
    component which creates that very database connection.

  2. In the child level, use a dedicated connection component to read that
    registered database connection.

For an example about how to share a database connection across Job levels, see
Talend Studio User
Guide.
 

Host

Database server IP address.

Port

Listening port number of the DB server.

Database name

Name of the database.
 

Schema

Name of the schema.

(DB2, Greenplum, Informix, Oracle, PostgresPlus, Postgresql,
Vertica)

 

Username and
Password

DB user authentication data.

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.

 

Table name

Type in between quotes a name for the newly created table.

 

Schema and Edit
Schema

A schema is a row description. It defines the number of fields to be processed and passed on
to the next component. The schema is either Built-In or
stored remotely in the Repository.

Since version 5.6, both the Built-In mode and the Repository mode are
available in any of the Talend solutions.

 

 

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.

When the schema to be reused has default values that are integers or functions, ensure that
these default values are not enclosed within quotation marks. If they are, you must remove
the quotation marks manually.

For more details, see https://help.talend.com/display/KB/Verifying+default+values+in+a+retrieved+schema.
   

Click Edit schema to make changes to the schema. If the
current schema is of the Repository type, three options are
available:

  • View schema: choose this option to view the
    schema only.

  • Change to built-in property: choose this option
    to change the schema to Built-in for local
    changes.

  • Update repository connection: choose this option to change
    the schema stored in the repository and decide whether to propagate the changes to
    all the Jobs upon completion. If you just want to propagate the changes to the
    current Job, you can select No upon completion and
    choose this schema metadata again in the [Repository
    Content]     window.

Advanced settings

tStatCatcher Statistics

Select this check box to gather the job processing metadata at a
Job level as well as at each component level.

Additional JDBC Parameters

Specify additional connection properties for the DB connection you
are creating. This option is not available if you have selected the
Use an existing connection
check box in the Basic
settings.

(AS/400, MSSQL Server)

Create projection

Select this check box to create a projection.

(Vertica)

Global Variables

QUERY: the SQL query statement being processed. This is a
Flow variable and it returns a string.

ERROR_MESSAGE: the error message generated by the
component when an error occurs. This is an After variable and it returns a string. This
variable functions only if the Die on error check box is
cleared, if the component has this check box.

A Flow variable functions during the execution of a component while an After variable
functions after the execution of the component.

To fill up a field or expression with a variable, press Ctrl +
Space to access the variable list and choose the variable to use from it.

For further information about variables, see Talend Studio
User Guide.

Usage

This component offers the flexibility of the database query and
covers all possible SQL queries. More scenarios are available for
specific database Input components.

Log4j

The activity of this component can be logged using the log4j feature. For more information on this feature, see Talend Studio User
Guide.

For more information on the log4j logging levels, see the Apache documentation at http://logging.apache.org/log4j/1.2/apidocs/org/apache/log4j/Level.html.
Limitation

This component requires installation of its related jar files. For more information about the
installation of these missing jar files, see the section describing how to configure the
Studio of the Talend Installation and Upgrade Guide.

Database-specific fields:

Access

Access File

Name and path of the file to be processed.

Firebird

Firebird File

Name and path of the file to be processed.

HSQLDb

Running Mode

Select from the list the Server Mode that corresponds to your
database setup.

Use TLS/SSL Sockets

Select this check box to enable the secured mode, if
required.

DB Alias

Name of the database.

Case Sensitive

Select this check box to make the table/column name case
sensitive.

Interbase

Interbase File

Name and path of the file to be processed.

JavaDb

Framework Type

Select from the list a framework for your database.
Structure type

Select in the list the structure type.

DB Root Path

Browse to your database root.

Mysql

Temporary table

Select this check box if you want to save the created table
temporarily.

ODBC

ODBC Name

Name of the database.

Oracle

Connection Type

Drop-down list of available drivers:

Oracle SID: Select this
connection type to uniquely identify a particular database on a
system.

Oracle Service Name: Select this
connection type to use the TNS alias that you give when you connect
to the remote database.

Oracle OCI: Select this
connection type to use Oracle Call Interface with a set of
C-language software APIs that provide an interface to the Oracle
database.

WALLET: Select this connection
type to store credentials in an Oracle wallet.

SQLite

SQLite File

Name and path of the file to be processed.

Related topic: see Talend Studio User
Guide.

Teradata

Create

Select the table type from the drop-down list. The type may
be:

– SET TABLE: tables which do not
allow to duplicate.

– MULTI SET TABLE: tables
allowing duplicate rows

Scenario: Creating new table in a Mysql Database

The Job described below aims at creating a table in a database, made of a dummy schema taken from a delimited file schema
stored in the Repository. This Job is composed of a single
component.

Use_Case_tCreateTable1.png

  1. Drop a tCreateTable component from the
    Databases family in the Palette to the design workspace.

  2. In the Basic settings view, and from the
    Database Type list, select Mysql for this
    scenario.

    Use_Case_tCreateTable2.png

  3. From the Table Action list, select Create table.

  4. Select the Use Existing Connection check box
    only if you are using a dedicated DB connection component tMysqlConnection. In this example, we won’t use this
    option.

  5. In the Property type field, select Repository so that the connection fields that
    follow are automatically filled in. If you have not defined your DB
    connection metadata in the DB connection
    directory under the Metadata node, fill in
    the details manually as Built-in    .

  6. In the Table Name field, fill in a name for
    the table to be created.

  7. If you want to retrieve the Schema from the
    Metadata (it doesn’t need to be a DB connection Schema metadata), select
    Repository then the relevant entry.

  8. In any case (Built-in or Repository)
    click Edit Schema to check the data type
    mappingClick Edit Schema
    to define the data structure    .

    Use_Case_tCreateTable3.png

  9. Click the Reset DB Types button in case the
    DB type column is empty or shows discrepancies (marked in orange). This allows
    you to map any data type to the relevant DB data type. Then, click OK to validate your changes and close the dialog
    box.

  10. Save your Job and press F6 to execute
    it.

The table is created empty but with all columns defined in the Schema.

Use_Case_tCreateTable4.png
Document get from Talend https://help.talend.com
Thank you for watching.
Docs 5.x
Subscribe
Notify of
guest
0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x