August 15, 2023

tSnowflakeInput – Docs for ESB 6.x

  2. Talend for ESB Docs
  3. Docs 6.x
  4. tSnowflakeInput – Docs for ESB 6.x

tSnowflakeInput

Reads data from a Snowflake table into the data flow of your Job based on an SQL
query.

tSnowflakeInput Standard properties

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

The Standard
tSnowflakeInput component belongs to the
Cloud family.

The component in this framework is generally available.

Basic settings

Property Type

Select the way the connection details
will be set.

  • Built-In: The connection details will be set
    locally for this component. You need to specify the values for all
    related connection properties manually.

  • Repository: The connection details stored
    centrally in Repository > Metadata will be reused by this component. You need to click
    the […] button next to it and in the pop-up
    Repository Content dialog box, select the
    connection details to be reused, and all related connection
    properties will be automatically filled in.

This property is not available when other connection component is selected
from the Connection Component drop-down list.

Connection Component

Select the way you want this component to connect to Snowflake:

  • Use this component: define the
    Snowflake connection locally and only for this component.

  • tSnowflakeConnection: reuse the
    connection defined in a given tSnowflakeConnection.

Account

In the Account field, enter, within
double quotation marks, the name that has been assigned to you by Snowflake.

User Id and Password

Enter, within double quotation marks, your authentication information to
log in Snowflake.

  • In the User ID field,
    enter, within double quotation marks, your login name that has been defined
    in Snowflake using the LOGIN_NAME parameter of
    Snowflake. For details, ask the administrator of your Snowflake system.

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

Warehouse

Enter, within double quotation marks, the name of the Snowflake warehouse
to be used. This name is case-sensitive and is normally upper case in Snowflake.
Schema

Enter, within double quotation marks, the name of the database schema to
be used. This name is case-sensitive and is normally upper case in Snowflake.

Database

Enter, within double quotation marks, the name of the Snowflake database
to be used. This name is case-sensitive and is normally upper case in Snowflake.

Table

Click the […] button and in the
displayed wizard, select the Snowflake table to be used.

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. The schema is either Built-In or stored remotely in the Repository.

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.

If the Snowflake data type to be handled is
VARIANT,
OBJECT or ARRAY, while defining the schema in the
component, select String for the corresponding
data in the Type column of the schema editor
wizard.

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.

Note that if the input value of any non-nullable primitive
field is null, the row of data including that field will be rejected.

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.

Manual query

Select this check box and in the Full SQL query
string field, enter, within double quotation marks, the query
statement to be executed. For more explanations about the Snowflake SQL commands,
see SQL Command Reference.

Condition

Enter, within double quotation marks, the boolean expression to be used to
filter data from your Snowflake table. This expression is actually used as WHERE
clause in the SELECT query run by tSnowflakeInput.

Advanced settings

Login Timeout

Specify how long to wait for a response when connecting
to Snowflake before returning an error.

Tracing

Select the log level for the Snowflake JDBC driver. If enabled, a standard
Java log is generated.

Role

Enter, within double quotation marks, the default access control role to
use to initiate the Snowflake session.

This role must already exist and has been granted to the user ID you are
using to connect to Snowflake. If this field is left empty, the PUBLIC role is
automatically granted. For further information about the Snowflake access control
model, see Snowflake documentation at Understanding the Access Control
Model.

tStatCatcher Statistics

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

Global Variables

NB_LINE

The number of rows processed. This is an After variable and it returns an
integer.
ERROR_MESSAGE

The error message generated by the component when an error occurs. This
is an After variable and it returns a string.

Usage

Usage rule

This component is start component of a data flow in your Job. It sends data to
other components via a Row link.

Writing data into and reading data from a Snowflake table

Here is an example of using Talend Snowflake components to connect to Snowflake,
write data into a Snowflake table, then read data from this table, finally close the
connection to Snowflake.

use_case-snowflakes-job.png

Creating a Job for writing and reading Snowflake data

Create a Job to connect to Snowflake, write data into a Snowflake table and then
read data from the table, finally close the connection to Snowflake.

  • The Snowflake data warehouse to be used is started but not in the Suspended
    status, and the Snowflake role assigned to you has read and write
    permissions to the database to be connected. If you are not sure about these
    requirements, ask the administrator of your Snowflake system.

  • The table EMPLOYEE with four columns,
    ID of INT type, NAME of
    VARCHAR type, ONBOARD of DATE type, and
    SALARY of VARIANT type, has been created in
    Snowflake.

    snowflake-table_schema.png
  1. In the
    Integration
     perspective of the Studio, create a new Job from the Job Designs node in the Repository tree view.
  2. Add a tSnowflakeConnection component, a
    tFixedFlowInput component, a
    tSnowflakeOutput component, a
    tSnowflakeInput component, a
    tLogRow component, and a
    tSnowflakeClose component to the Job.
  3. Connect the tFixedFlowInput component to the tSnowflakeOutput component using a Row > Main connection.
  4. Do the same to connect the tSnowflakeInput component to
    the tLogRow component.
  5. Connect the tSnowflakeConnection component to the
    tFixedFlowInput component using a Trigger > On Subjob Ok connection.
  6. Do the same to connect the
    tFixedFlowInput component to the tSnowflakeInput component, and the
    tSnowflakeInput component to the
    tSnowflakeClose component.

Connecting to Snowflake

Configure the tSnowflakeConnection component to connect to
Snowflake.

  1. Double-click the tSnowflakeConnection component to open its Basic settings view.
  2. In the Account field, enter
    the account name assigned by Snowflake.
  3. In the User Id and the
    Password fields, enter the authentication
    information accordingly.

    Note that this user ID is your user login name. If you do not know your user login
    name yet, ask the administrator of your Snowflake system for details.

  4. In the Warehouse field,
    enter the name of the data warehouse to be used in Snowflake.
  5. In the Schema field, enter
    the name of the database schema to be used.
  6. In the Database field, enter
    the name of the database to be used.

Writing data into Snowflake

Configure the tFixedFlowInput component and the
tSnowflakeOutput component to write data into
Snowflake.

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

    use_case-snowflake-sample_data.png

  2. Click the […] button next
    to Edit schema and in the pop-up schema dialog
    box, define the schema by adding four columns, ID of int type,
    NAME and SALARY of String type, and
    ONBOARD of Date type.

    use_case-snowflake-sample_data_schema.png

  3. Click OK to validate these
    changes and accept the propagation prompted by the pop-up dialog box.
  4. In the Number of rows field, enter the number of records to
    be generated, 5 in this example.
  5. In the Mode area, select
    Use Single Table and specify the value for each
    column.

    • ID: the automatically incremented number generated by
      the routine Numeric.sequence("id",1,1).

    • NAME: the random first name generated by the routine
      TalendDataGenerator.getFirstName().

    • ONBOARD: the random date generated by the routine
      TalendDate.getRandomDate("2007-09-01","2017-09-01").

    • SALARY: the semi-structured JSON data with the format
      {"Salary": value}, where the salary value is
      generated by the routine
      Numeric.random(100000,200000).

  6. Double-click the tSnowflakeOutput component to open its Basic settings view.
  7. From the Connection
    Component     drop-down list, select the tSnowflakeConnection component to reuse the connection created by
    it.
  8. Click the […] button next
    to the Table field and in the pop-up dialog
    box, select the Use custom object check box and enter the name
    of the table into which the data will be written in the Object
    Name     field. In this example, it is EMPLOYEE.
    When done, click OK to close the dialog box.
  9. From the Output action
    drop-down list, select Insert to write data to
    the table EMPLOYEE.

Reading data from Snowflake

Configure the tSnowflakeInput component and the
tLogRow component to retrieve data from Snowflake and output
the data on the console.

  1. Double-click the tSnowflakeInput component to open its Basic settings view.
  2. From the Connection
    Component     drop-down list, select the tSalesforceConnection component to reuse the connection created
    by it.
  3. Click the […] button next to the
    Table field and in the pop-up dialog box, select the
    Use custom object check box and enter the name of the
    table from which the data will be retrieved in the Object
    Name     field. In this example, it is
    EMPLOYEE. When done, click OK
    to close the dialog box.
  4. Click the […] button
    next to Edit schema and in the pop-up
    schema dialog box, define the schema by adding four columns,
    ID of int type, NAME and
    SALARY of String type, and
    ONBOARD of Date type. This schema is the same as the
    schema of the tSnowflakeOutput component.
  5. Click OK to validate
    these changes and accept the propagation prompted by the pop-up dialog
    box.
  6. Double-click the tLogRow
    component to open its Component
    view.
  7. In the Mode area, select
    Vertical (each row is a key/value
    list)     for better readability of the results.

Closing the Snowflake connection

Configure the tSnowflakeClose component to close the
connection to Snowflake.

  1. Double-click the tSnowflakeClose
    component to open the Component
    tab.
  2. From the Connection Component drop-down list, select the
    component that opens the connection you need to close,
    tSnowflakeConnection_1 in this example.

Executing the Job to write and read data in Snowflake

After setting up the Job and configuring the components used in the Job for writing
and reading Snowflake data, you can then execute the Job and verify the Job execution
result.

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

    As shown below, the data is written into the table EMPLOYEE in
    Snowflake, then read from this table and outputted on the console in the
    Run view of the Studio.

    snowflake-write_read_data_job_result.png

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