July 30, 2023

tRedshiftInput – Docs for ESB 7.x

tRedshiftInput

Reads data from a database and extracts fields based on a query so that you may
apply changes to the extracted data.

tRedshiftInput executes a DB query
with a strictly defined order which must correspond to the schema
definition. Then it passes on the field list to the next component
through a Main row link.

Depending on the Talend
product you are using, this component can be used in one, some or all of the following
Job frameworks:

tRedshiftInput Standard properties

These properties are used to configure tRedshiftInput running in the Standard Job framework.

The Standard
tRedshiftInput component belongs to the Cloud and the Databases families.

The component in this framework is available in all Talend
products
.

Note: This component is a specific version of a dynamic database
connector. The properties related to database settings vary depending on your database
type selection. For more information about dynamic database connectors, see Dynamic database components.

Basic settings

Database

Select a type of database from the list and click
Apply.

Property type

Either Built-in or Repository
.

 

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.

tRedshiftInput_1.png

Click this icon to open a database connection wizard and store the
database connection parameters you set in the component Basic settings view.

For more information about setting up and storing database connection
parameters, see
Talend Studio User
Guide
.

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

Hostname or IP address of the database server.

Port

Listening port number of the database server.

Database

Name of the database.

Schema

Exact name of the schema.

Username and Password

Database 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.

Additional JDBC Parameters

Specify additional JDBC properties for the connection you are creating. The
properties are separated by ampersand & and each property is a key-value pair. For
example, ssl=true &
sslfactory=com.amazon.redshift.ssl.NonValidatingFactory
, which means the
connection will be created using SSL.

Schema and Edit
Schema

A schema is a row description. It defines the number of fields
(columns) to be processed and passed on to the next component. When you create a Spark
Job, avoid the reserved word line when naming the
fields.

This
component offers the advantage of the dynamic schema feature. This allows you to
retrieve unknown columns from source files or to copy batches of columns from a source
without mapping each column individually. For further information about dynamic schemas,
see
Talend Studio

User Guide.

This
dynamic schema feature is designed for the purpose of retrieving unknown columns of a
table and is recommended to be used for this purpose only; it is not recommended for the
use of creating tables.

 

Built-In: You create and store the schema locally for this component
only.

 

Repository: You have already created the schema and stored it in the
Repository. You can reuse it in various projects and Job designs.

 

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.

Table Name

Name of the table from which the data will be read.

Query Type and Query

Enter your database query paying particularly attention to sequence
the fields properly in order to match the schema definition.

If using the dynamic schema feature, the SELECT
query must include the *
wildcard, to retrieve all of the columns from the table
selected.

Guess Query

Click the Guess Query button to
generate the query which corresponds to your table schema in the Query
field.

Guess schema

Click the Guess schema button to
retrieve the table schema.

Advanced settings

Log file

Specify the path to the log file to be generated during the
data retrieval process.

Note that if the logging information needs to be saved and
this component reuses the connection created by a tRedshiftConnection component, you must specify the property loglevel=1/2/3 in the Additional JDBC Parameters
field of the tRedshiftConnection component.

There is no limitation on the size of the log file. If the
specificed log file already exists, it will be overwritten.

Logging level

Select a logging level from the drop-down list to specify
which kind of events will be logged in the log file.

Use cursor

Select this check box to help to decide the row set to work with at a
time and thus optimize performance.

Trim all the String/Char columns

Select this check box to remove leading and trailing whitespace from
all the String/Char columns.

Trim column

Remove leading and trailing whitespace from defined columns.

JDBC url
Select a way to access to an Amazon Redshift database from the
JDBC url drop-down list.

  • Standard: Use the
    standard way to access the Redshift database.
  • SSO: Use the IAM
    Single Sign-ON (SSO) authentication way to access the Redshift Database. Before selecting
    this option, ensure that the IAM role added to your Redshift cluster has appropriate
    access rights and permissions to this cluster. You can ask the administrator of your AWS
    services for more details.

    This option is available only when Use an existing connection check box is not selected from
    the Basic settings.

tStatCatcher Statistics

Select this check box to collect log data at the component
level.

Global Variables

Global Variables

NB_LINE: the number of rows processed. This is an After
variable and it returns an integer.

QUERY: the 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

Usage rule

This component covers all possible SQL queries for Amazon Redshift
databases.

Dynamic settings

Click the [+] button to add a row in the table
and fill the Code field with a context
variable to choose your database connection dynamically from multiple
connections planned in your Job. This feature is useful when you need to
access database tables having the same data structure but in different
databases, especially when you are working in an environment where you
cannot change your Job settings, for example, when your Job has to be
deployed and executed independent of Talend Studio.

The Dynamic settings table is
available only when the Use an existing
connection
check box is selected in the Basic settings view. Once a dynamic parameter is
defined, the Component List box in the
Basic settings view becomes unusable.

For examples on using dynamic parameters, see Reading data from databases through context-based dynamic connections and Reading data from different MySQL databases using dynamically loaded connection parameters. For more information on Dynamic
settings
and context variables, see Talend Studio
User Guide.

Limitation

Due to license incompatibility, one or more JARs required to use
this component are not provided. You can install the missing JARs for this particular
component by clicking the Install button
on the Component tab view. You can also
find out and add all missing JARs easily on the Modules tab in the
Integration
perspective of your studio. You can find more details about how to install external modules in
Talend Help Center (https://help.talend.com)
.

Handling data with Redshift

This scenario describes a Job that writes the personal information into Redshift, then
retrieves the information in Redshift and displays it on the console.

tRedshiftInput_2.png

The scenario requires the following six components:

  • tRedshiftConnection: opens a connection to
    Redshift.

  • tFixedFlowInput: defines the personal
    information data structure, and sends the data to the next component.

  • tRedshiftOutput: writes the data it receives
    from the preceding component into Redshift.

  • tRedshiftInput: reads the data from
    Redshift.

  • tLogRow: displays the data it receives from
    the preceding component on the console.

  • tRedshiftClose: closes the connection to
    Redshift.

Dropping and linking the components

  1. Drop the six components listed previously from the Palette onto the design workspace.
  2. Connect tFixedFlowInput to tRedshiftOutput using a Row > Main
    connection.
  3. Connect tRedshiftInput to tLogRow also using a Row > Main
    connection.
  4. Connect tRedshiftConnection to tFixedFlowInput using a Trigger > OnSubjobOk
    connection.
  5. Connect tFixedFlowInput to tRedshiftInput and tRedshiftInput to tRedshiftClose also using a Trigger > OnSubjobOk
    connection.

Configuring the components

Opening a connection to Redshift

  1. Double-click tRedshiftConnection to open
    its Basic settings view.

    tRedshiftInput_3.png

  2. Select Built-In from the Property Type drop-down list.

    In the Host, Port, Database, Schema, Username, and Password fields,
    enter the information required for the connection to Redshift.
  3. In Advanced settings view, select
    Auto Commit check box to commit any
    changes to Redshift upon each transaction.

Defining the input data

  1. Double-click tFixedFlowInput to open its
    Basic settings view.

    tRedshiftInput_4.png

  2. Click the […] button next to Edit schema to open the schema editor.

    tRedshiftInput_5.png

  3. In the schema editor, click the [+]
    button to add three columns: id of the
    interger type, name of the string type,
    and age of the integer type.
  4. Click OK to validate the changes and
    accept the propagation prompted by the pop-up [Propagate] dialog box.
  5. In the Mode area, select Use Inline Content (delimited file) and enter the
    following personal information in the Content field.

Writing the data into Redshift

  1. Double-click tRedshiftOutput to open its
    Basic settings view.

    tRedshiftInput_6.png

  2. Select the Use an existing connection
    check box, and then select the connection you have already configured for
    tRedshiftConnection from the Component List drop-down list.
  3. In the Table field, enter or browse to
    the table into which you want to write the data, redshiftexample in this scenario.
  4. Select Drop table if exists and create
    from the Action on table drop-down list,
    and select Insert from the Action on data drop-down list.
  5. Click Sync columns to retrieve the schema
    from the preceding component tFixedFlowInput.

Retrieving the data from Redshift

  1. Double-click tRedshiftInput to open its
    Basic settings view.

    tRedshiftInput_7.png

  2. Select the Use an existing connection
    check box, and then select the connection you have already configured for
    tRedshiftConnection from the Component List drop-down list.
  3. Click the […] button next to Edit schema to open the schema editor.
  4. In the schema editor, click the [+]
    button to add three columns: id of the
    interger type, name of the string type,
    and age of the integer type. The data
    structure is same as the structure you have defined for tFixedFlowInput.
  5. Click OK to validate the changes and
    accept the propagation prompted by the pop-up [Propagate] dialog box.
  6. In the Table Name field, enter or browse
    to the table into which you write the data, redshiftexample in this scenario.
  7. Click the Guess Query button to generate
    the query. The Query field will be filled
    with the automatically generated query.

Displaying the defined information

  1. Double-click tLogRow to open its
    Basic settings view.

    tRedshiftInput_8.png

  2. In the Mode area, select Table (print values in cells of a table) for a
    better view of the results.

Closing the connection to Redshift

  1. Double-click tRedshiftClose to open its
    Basic settings view.

    tRedshiftInput_9.png

  2. From Component List, select the
    connection you have already configured for tRedshiftConnection.

Saving and executing the Job

  1. Press Ctrl+S to save the Job.
  2. Press F6 to execute the Job.

    tRedshiftInput_10.png

    The personal information is written to the specified target Redshift
    database, and then the data is retrieved from the database and displayed on
    the console.

tRedshiftInput properties for Apache Spark Batch

These properties are used to configure tRedshiftInput running in the Spark Batch Job framework.

The Spark Batch
tRedshiftInput component belongs to the Databases family.

The component in this framework is available in all subscription-based Talend products with Big Data
and Talend Data Fabric.

Basic settings

Property type

Either Built-In or Repository.

Built-In: No property data stored centrally.

Repository: Select the repository file where the
properties are stored.

tRedshiftInput_1.png

Click this icon to open a database connection wizard and store the
database connection parameters you set in the component Basic
settings
view.

For more information about setting up and storing database
connection parameters, see Talend Studio User Guide.

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.

Host

Enter the endpoint of the database you need to connect to in
Redshift.

Port

Enter the port number of the database you need to connect to in
Redshift.

The related information can be found in the Cluster Database
Properties area in the Web console of your Redshift.

For further information, see Managing clusters console.

Username and Password

Enter the authentication information to the Redshift database you
need to connect to.

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.

Database

Enter the name of the database you need to connect to in
Redshift.

The related information can be found in the Cluster Database
Properties area in the Web console of your Redshift.

For further information, see Managing clusters console.

Schema

Enter the name of the database schema to be used in Redshift. The
default schema is called PUBLIC.

A schema in terms of Redshift is similar to a operating system
directory. For further information about a Redshift schema, see Schemas.

Additional JDBC Parameters

Specify additional JDBC properties for the connection you are creating. The
properties are separated by ampersand & and each property is a key-value pair. For
example, ssl=true &
sslfactory=com.amazon.redshift.ssl.NonValidatingFactory
, which means the
connection will be created using SSL.

S3 configuration

Select the tS3Configuration component from which you want Spark to use the
configuration details to connect to S3.

You need drop the tS3Configuration component to be used alongside
tRedshiftConfiguration in
the same Job so that this tS3Configuration is displayed on the S3 configuration list.

S3 temp path

Enter the location in S3 in which the data to be
transferred from or to Redshift is temporarily stored.

This path is independent of the temporary path you need
to set in the Basic settings
tab of tS3Configuration.

Schema and Edit
Schema

A schema is a row description. It defines the number of fields
(columns) to be processed and passed on to the next component. When you create a Spark
Job, avoid the reserved word line when naming the
fields.

 

Built-In: You create and store the schema locally for this component
only.

 

Repository: You have already created the schema and stored it in the
Repository. You can reuse it in various projects and Job designs.

 

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.

Table name

Enter the name of the table from which the data will be read.

Read from

Select the type of the source of the data to be read.

  • Table: tRedshiftInput reads the data of the table you
    specify in the Table name
    field.

  • Query: tRedshiftInput reads the result of the query you
    write in the Query
    field.

Query Type and Query

Specify the database query statement paying particularly attention to the
properly sequence of the fields which must correspond to the schema definition.

Guess Query

Click the Guess Query button to
generate the query which corresponds to your table schema in the Query
field.

Advanced settings

Trim all the String/Char columns

Select this check box to remove leading whitespace and trailing
whitespace from all String/Char columns.

Trim column

This table is filled automatically with the schema being used. Select the check
box(es) corresponding to the column(s) to be trimmed.

Usage

Usage rule

This component is used as a start component and requires an output
link..

This component should use a tRedshiftConfiguration component present in the same Job to
connect to Redshift. You need to drop a tRedshiftConfiguration component alongside this component and configure the
Basic settings of this component to use
tRedshiftConfiguration.

This component, along with the Spark Batch component Palette it belongs to,
appears only when you are creating a Spark Batch Job.

Note that in this documentation, unless otherwise explicitly stated, a
scenario presents only Standard Jobs, that is to
say traditional
Talend
data integration Jobs.

Spark Connection

In the Spark
Configuration
tab in the Run
view, 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:

  • Yarn mode (Yarn client or Yarn cluster):

    • When using Google Dataproc, specify a bucket in the
      Google Storage staging bucket
      field in the Spark configuration
      tab.

    • When using HDInsight, specify the blob to be used for Job
      deployment in the Windows Azure Storage
      configuration
      area in the Spark
      configuration
      tab.

    • When using Altus, specify the S3 bucket or the Azure
      Data Lake Storage for Job deployment in the Spark
      configuration
      tab.
    • When using Qubole, add a
      tS3Configuration to your Job to write
      your actual business data in the S3 system with Qubole. Without
      tS3Configuration, this business data is
      written in the Qubole HDFS system and destroyed once you shut
      down your cluster.
    • When using on-premise
      distributions, use the configuration component corresponding
      to the file system your cluster is using. Typically, this
      system is HDFS and so use tHDFSConfiguration.

  • Standalone mode: use the
    configuration component corresponding to the file system your cluster is
    using, such as tHDFSConfiguration or
    tS3Configuration.

    If you are using Databricks without any configuration component present
    in your Job, your business data is written directly in DBFS (Databricks
    Filesystem).

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.


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