tSnowflakeInput
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
This property is not available when other connection component is selected |
Connection Component |
Select the way you want this component to connect to Snowflake:
|
Account |
In the Account field, enter, within |
User Id and Password |
Enter, within double quotation marks, your authentication information to
|
Warehouse |
Enter, within double quotation marks, the name of the Snowflake warehouse |
Schema |
Enter, within double quotation marks, the name of the database schema to |
Database |
Enter, within double quotation marks, the name of the Snowflake database |
Table |
Click the […] button and in the |
Schema and Edit Schema |
A schema is a row description. It defines the number of fields (columns) to Built-In: You create and store the Repository: You have already created If the Snowflake data type to be handled is Click Edit schema to make changes to the schema.
Note that if the input value of any non-nullable primitive This component offers the This dynamic schema |
Manual query |
Select this check box and in the Full SQL query |
Condition |
Enter, within double quotation marks, the boolean expression to be used to |
Advanced settings
Login Timeout |
Specify how long to wait for a response when connecting |
Tracing |
Select the log level for the Snowflake JDBC driver. If enabled, a standard |
Role |
Enter, within double quotation marks, the default access control role to This role must already exist and has been granted to the user ID you are |
tStatCatcher Statistics |
Select this check box to gather the Job processing metadata at the Job level |
Global Variables
NB_LINE |
The number of rows processed. This is an After variable and it returns an |
ERROR_MESSAGE |
The error message generated by the component when an error occurs. This |
Usage
Usage rule |
This component is start component of a data flow in your Job. It sends data to |
Writing data into and reading data from a Snowflake table
write data into a Snowflake table, then read data from this table, finally close the
connection to Snowflake.
Creating a Job for writing and reading Snowflake data
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.
-
In the
Integration
perspective of the Studio, create a new Job from the Job Designs node in the Repository tree view. -
Add a tSnowflakeConnection component, a
tFixedFlowInput component, a
tSnowflakeOutput component, a
tSnowflakeInput component, a
tLogRow component, and a
tSnowflakeClose component to the Job. - Connect the tFixedFlowInput component to the tSnowflakeOutput component using a Row > Main connection.
-
Do the same to connect the tSnowflakeInput component to
the tLogRow component. -
Connect the tSnowflakeConnection component to the
tFixedFlowInput component using a Trigger > On Subjob Ok connection. -
Do the same to connect the
tFixedFlowInput component to the tSnowflakeInput component, and the
tSnowflakeInput component to the
tSnowflakeClose component.
Connecting to Snowflake
Snowflake.
- Double-click the tSnowflakeConnection component to open its Basic settings view.
-
In the Account field, enter
the account name assigned by Snowflake. -
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. -
In the Warehouse field,
enter the name of the data warehouse to be used in Snowflake. -
In the Schema field, enter
the name of the database schema to be used. -
In the Database field, enter
the name of the database to be used.
Writing data into Snowflake
tSnowflakeOutput component to write data into
Snowflake.
-
Double-click the tFixedFlowInput component to open its Basic settings view.
-
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. -
Click OK to validate these
changes and accept the propagation prompted by the pop-up dialog box. -
In the Number of rows field, enter the number of records to
be generated, 5 in this example. -
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).
-
- Double-click the tSnowflakeOutput component to open its Basic settings view.
-
From the Connection
Component drop-down list, select the tSnowflakeConnection component to reuse the connection created by
it. -
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. -
From the Output action
drop-down list, select Insert to write data to
the table EMPLOYEE.
Reading data from Snowflake
tLogRow component to retrieve data from Snowflake and output
the data on the console.
- Double-click the tSnowflakeInput component to open its Basic settings view.
-
From the Connection
Component drop-down list, select the tSalesforceConnection component to reuse the connection created
by it. -
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. -
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. -
Click OK to validate
these changes and accept the propagation prompted by the pop-up dialog
box. -
Double-click the tLogRow
component to open its Component
view. -
In the Mode area, select
Vertical (each row is a key/value
list) for better readability of the results.
Closing the Snowflake connection
connection to Snowflake.
-
Double-click the tSnowflakeClose
component to open the Component
tab. -
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
and reading Snowflake data, you can then execute the Job and verify the Job execution
result.
-
Press Ctrl + S to save the
Job. -
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.