Try Transformer for Snowflake
This tutorial covers the steps needed to try Transformer for Snowflake. You will learn how to work with the Control Hub user interface, build a basic Snowflake pipeline, and preview the pipeline actions.
Although the tutorial provides a simple use case, keep in mind that StreamSets is a powerful platform that enables you to build and run large numbers of complex pipelines.
Complete Prerequisite Tasks
- Verify Snowflake requirement for network policies
- If you are just starting to use Transformer for Snowflake, and your Snowflake account uses network policies, complete the Snowflake requirement.
- Verify user permissions
-
Make sure that the user account for the tutorial has the following permissions on the database where you create the tables:
- Read
- Write
- Create Table
- Create source tables
- Use the following SQL queries to create and populate two tables in Snowflake.
Build a Snowflake Pipeline
With these steps, you build a Snowflake pipeline that uses two Snowflake Table origins to read from the two source tables that you created, a Union processor to merge the data, and a Snowflake Table destination to write to a new output table.
You also preview the pipeline to verify how the stages process data.
-
Use the following URL to log in to StreamSets: https://cloud.login.streamsets.com/
Control Hub displays the Getting Started view.
- Click Create a Pipeline. . Or, in the navigation panel, click , then click
- Enter the following pipeline name: Snowflake Tutorial.
- For Engine Type, select Transformer for Snowflake, start with the default blank pipeline, and then click Next.
-
In the Share Pipeline step, click Save &
Open in Canvas.
A blank pipeline opens in the canvas.
-
On the General tab of the pipeline properties, specify the
following Snowflake properties:
General Property Description Snowflake URL Enter the URL of your Snowflake instance. For example: https://<yourcompany>.snowflakecomputing.com
Role Enter the name of the role used to access your Snowflake account. Note: For this tutorial, the role must have the following permissions:- Read
- Write
- Create Table
Warehouse Enter the name of the warehouse where you created the tutorial tables. Default Schema Specify the database and schema where you created the tutorial tables. This schema becomes the default schema for the origins and destinations in the pipeline. You can override this property in those stages when necessary.
Enter the database and schema name, or click the Select Schema icon to explore your Snowflake data for the database and schema to use.
You should have read, write, and create table permissions for the database.
-
In the canvas, click the Add Stage icon (
) to open the stage selector.
-
Select Snowflake Table.
The origin is added to the canvas.
- In the properties panel below the canvas, click the General tab, then name the origin Reads WAREHOUSE_EAST.
- Click the Table tab.
-
Click the Select Table icon (
). Navigate to the schema and database where you created the tutorial tables, then select the WAREHOUSE_EAST table.
We want to read all data in the table, so no other properties are needed.
-
To add another origin, click the Add Stage icon in the
toolbar above the canvas, click Origins, and then select
Snowflake Table.
Select the second origin and drag it below the first origin in the pipeline canvas.
- Click the General tab for the origin, then name the origin Reads WAREHOUSE_WEST.
- Click the Table tab.
-
Click the Select Table icon (
). Navigate to the schema and database where you created the tutorial tables, then select the WAREHOUSE_WEST table.
We want to read all data in the table, so no other properties are needed.
- Click the Add Stage icon connected to the second origin, click Processors in the stage selector, and then select the Union processor.
-
Click the General tab for the processor, then name the
processor
Union All
.Using the stage selector connects the new stage with the selected stage. Connect the other origin by clicking on the output of the origin, then drag and drop it over the processor.The input order is irrelevant in this case, but to change the order, select the processor, then in the pop-up menu select the Reorder icon: -
Click the Union tab, then configure the following
properties:
Union Property Value Operation Union Column Handling Pass all Columns This passes all columns from both origins and ensures that all rows have the superset of columns passed into the processor. That is, any columns that exist only in one set of rows are added to the other rows. These new columns are populated with null values.
- Click the Add Stage icon connected to the processor, click Destinations in the stage selector, and then select the Snowflake Table destination.
-
Click the General tab for the destination, then name the
stage
Tutorial Output
. - Click the Table tab.
-
Click the Table text box.
The Overwrite Database, Schema, and Table dialog box displays. By default, the destination uses the database and schema that you defined in the pipeline properties. You can optionally override the database and schema to write the output to another schema.
- Enter TUTORIAL_OUTPUT for the table, and then click Save.
-
Configure the following additional properties:
Snowflake Table Value Write Mode Overwrite Table This ensures that if you run this pipeline again, you receive the expected results.
Overwrite Mode Drop Table -
Use the default values for all remaining properties.
Note that Create Table is enabled by default so that the destination creates the TUTORIAL_OUTPUT table before writing to it.
Now that the pipeline is complete, you can preview how the pipeline works. Preview a pipeline to help you develop pipeline logic. When you preview the pipeline, you can view how a sample set of rows change as they pass through the pipeline. -
In the toolbar above the pipeline canvas, click the
Preview icon:
.
-
If you have not yet stored Snowflake credentials with your account, enter the
following information in the Snowflake Credentials dialog box and click
Save:
If you have already specified Snowflake credentials, the Snowflake Credentials dialog box does not appear.
Snowflake Credentials Property Description Username Snowflake user name. Authentication Method Authentication method to use: password or private key. Password Password for the Snowflake account. Available when using password authentication.
Private Key Private key for the Snowflake account. Enter a PKCS#1 or PKCS#8 private key and include the key delimiters. For example, when entering a PKCS#8 private key, include the
-----BEGIN PRIVATE KEY-----
and-----END PRIVATE KEY-----
key delimiters.Available when using private key authentication.
Role Optional role to use. Use to limit access to the Snowflake account. Note: For this tutorial, the role must have the following permissions:- Read
- Write
- Create Table
The first origin is selected in the pipeline canvas, and preview displays several rows of output data read by the origin.
-
In the canvas, select the processor so that you can review the output
data.
Notice how the two input streams have been merged into one. Rows from WAREHOUSE_EAST have a new
Bin
column with null values, because theBin
column existed in the WAREHOUSE_WEST data. -
Click Close Preview to close the preview.
Now we know how the pipeline processes the data, but let's create and run a job to see the pipeline in action.
Run a Job
Jobs are the execution of a dataflow that is represented in a pipeline.
When pipeline development is complete, you check in the pipeline to indicate that the pipeline is ready to be added to a job and run. When you check in a pipeline, you enter a commit message. StreamSets maintains the commit history of each pipeline.
Since the Snowflake engine is hosted on StreamSets DataOps Platform, job configuration is simple, compared to Data Collector or Transformer pipelines. As a result, you can use the default values when creating the job.
-
With the pipeline open in the canvas, click the Check In
icon:
-
Enter a commit message. You can use the default: New
Pipeline.
As a best practice, state what changed in this pipeline version so that you can track the commit history of the pipeline.
-
Click Publish and Next.
The Share Pipeline step displays. You can skip this step for now. When additional users join your organization, you must share the pipeline to grant them access to it.
-
Click Save & Create New Job.
The Create Job Instances wizard appears.
- Use the defaults in the Define Job step, and click Next.
- In the Select Pipeline step, click Next.
-
In the Review & Start step, click Start
& Monitor Job.
The job displays in the canvas, and Control Hub indicates that the job is active. When the job completes, the job has an Inactive status and displays the time that the job started and stopped.
-
Click the Summary tab to view the input and output
row count for the completed job.
-
To view the results of the pipeline, go to your Snowflake console and navigate
to the database, warehouse, and schema that you used for the tutorial. Notice
that the pipeline created a new
TUTORIAL_OUTPUT
table.When you preview the data in the table, it should look like this:
Notice the table includes a
Bin
column that only existed in the WAREHOUSE_WEST table. And as you saw when previewing the pipeline processing, rows that did not previously haveBin
data, now have nulls in that column.Congratulations! You have built and run your first Snowflake pipeline.
Next Steps
- Modify the tutorial pipeline
- Add a couple processors to the tutorial pipeline to see how easily you can
add a wide range of processing to the pipeline:
- Add a Filter to remove the
multitool
rows from the data set. - Use a Snowflake SQL Evaluator to double the inventory values, and overwrite the existing values.
- To see how the data drift feature works, change the destination
Overwrite Mode property from
Drop Table
toTruncate Table
. Then, add a Column Renamer processor to rename theBin
column.
- Add a Filter to remove the
- Create a new pipeline
- Create a new pipeline using your own Snowflake data or the Snowflake sample
data. You might explore some of the following functionality:
- If you have a Snowflake query that you want to enhance, use the Snowflake Query origin to generate data for the pipeline, then add processors to perform additional processing.
- Use the Join processor to join data from two Snowflake tables or views.
- As you develop the pipeline, use the Trash destination with data preview to see if the pipeline processes data as expected. For more information about data preview, see the Control Hub documentation.
- Explore advanced features
-
- Try using an existing user-defined-function (UDF) or define one in the pipeline.
- If you have an entire pipeline that you want to run with small changes, use runtime parameters to easily reuse and adapt pipeline logic.
- If you have a series of stages that you want to reuse in multiple pipelines, try creating a pipeline fragment.
- Configure Snowflake pipeline defaults to make configuring pipelines easier when you use the same Snowflake details in all or most of your pipelines.
- Learn more about Control Hub
-
- Become more familiar with the Control Hub pipeline canvas.
- Learn how Control Hub tracks pipeline version history and gives you full control of the evolving development process.
- Need to import data to Snowflake? You can use pipelines that run on Data Collector or Transformer engines to make that happen.
- Add users to your organization
-
- Invite other users to join your organization and collaboratively manage pipelines as a team.
- To create a multitenant environment within your organization, create groups of users. Grant roles to these groups and share objects within the groups to grant each group access to the appropriate objects.