Configuring the Snowflake external table Job - Cloud - 8.0

Snowflake
Version
Cloud
8.0
Language
English
Product
Talend Big Data
Talend Big Data Platform
Talend Data Fabric
Talend Data Integration
Talend Data Management Platform
Talend Data Services Platform
Talend ESB
Talend MDM Platform
Talend Open Studio for Big Data
Talend Open Studio for Data Integration
Talend Open Studio for ESB
Talend Real-Time Big Data Platform
Module
Talend Studio
Content
Data Governance > Third-party systems > Cloud storages > Snowflake components
Data Quality and Preparation > Third-party systems > Cloud storages > Snowflake components
Design and Development > Third-party systems > Cloud storages > Snowflake components
Last publication date
2023-09-14

Procedure

  1. Configure tDBConnection_1 to establish a connection to Snowflake. In the Basic settings view of the component:
    1. Select Snowflake from the Database list and click Apply.
    2. Enter the following Snowflake credential items in the rest fields:
      • Snowflake account name in the Account field
      • Snowflake region
      • Snowflake user ID in the User Id field
      • Snowflake account password in the Password field
      • Snowflake warehouse
      • Snowflake schema
      • Snowflake database
  2. Configure tDBRow_1 to create a stage referencing the file S3://my-bucket/logs/log1.json. In the Basic settings view of the component:
    1. Select Snowflake from the Database list and click Apply;
    2. Select tDBConnection_1 from the Connection Component list;
    3. Enter the following code in double quotation marks in the Query field. 
      CREATE OR REPLACE STAGE mystage
	url='s3://my-bucket/logs/'
	credentials=(aws_key_id='your_AWS_key_ID' aws_secret_key='your_AWS_secret_key')
	file_format = (type = json);
    4. Leave other options as they are.
  3. Configure tDBRow_2 to create an external table for the stage. In the Basic settings view of the component:
    1. Select Snowflake from the Database list and click Apply;
    2. Select tDBConnection_1 from the Connection Component list;
    3. Enter the following code in double quotation marks in the Query field. 
      CREATE OR REPLACE EXTERNAL TABLE logs (
	id varchar as (value:id::varchar),
	name varchar as (value:name::varchar),
	city varchar as (value:address::varchar))
	location=@mystage
	auto_refresh = true
	file_format=(type=json);
    4. Leave other options as they are.
  4. Configure tDBRow_3 to refresh the external table using the S3://logs/log1.json file. In the Basic settings view of the component:
    1. Select Snowflake from the Database list and click Apply;
    2. Select tDBConnection_1 from the Connection Component list;
    3. Enter the following code in double quotation marks in the Query field. 
      ALTER EXTERNAL logs REFRESH;
    4. Leave other options as they are.
  5. Configure tDBInput_1 to query the external table. In the Basic settings view of the component:
    1. Select Snowflake from the Database list and click Apply;
    2. Select tDBConnection_1 from the Connection Component list;
    3. Enter the following code in double quotation marks in the Query field. 
      SELECT id,name,city FROM logs;
    4. Click the [...] button next to Edit schema. Add the following three columns and click OK to propagate the schema.
      • ID, type String and Db Column ID
      • Name, type String and Db Column NAME
      • City, type String and Db Column CITY
    5. Leave other options as they are.
  6. Configure tLogRow_1 to specify the output layout. In the Basic settings view of the component, select a preferred mode for the output.
  7. Configure tDBClose_1 to close the connection to Snowflake. In the Basic settings view of the component:
    1. Select Snowflake from the Database list and click Apply;
    2. Select tDBConnection_1 from the Connection Component list;
  8. Press Ctrl + S to save the Job.