Skip to main content Skip to complementary content

Configuring the Snowflake external table Job

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.

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – please let us know!

Leave your feedback here