For organizations using Snowflake, it is well-established that Internal Stages offer a good approach for storing files. Snowflake's Internal Stages are secure, fully managed storage areas inside your Snowflake account. They serve as a holding space for data files, which is necessary for efficient data loading and unloading. By keeping data within the Snowflake environment, these stages offer advantages, such as faster processing and automatic data encryption. However, the critical task of transferring files; be it uploading for Snowflake users to pick them, or downloading them for downstream systems, can often introduce complexity into established ETL/ELT workflows. This friction point can hinder the efficiency of data pipeline automation. This is precisely the challenge that KingswaySoft's Snowflake components address.
In this blog post, we will demonstrate how to bypass those manual procedures. You will learn to leverage KingswaySoft's Snowflake components to establish seamless and robust file management capabilities directly on your Snowflake Internal Stage. We will outline the straightforward process for both uploading and downloading files, enabling you to fully integrate Snowflake staging operations into your automated data pipelines with maximum efficiency.
For this demonstration, we will use KingswaySoft's Snowflake Command Task, which is available to download as part of our SSIS Productivity Task.
Setting up Internal Stage in Snowflake
As a first step, you would need to create an internal stage, if you already do not have one. For this, log into your Snowflake instance, and in your workspace, click on the "+" sign. This will give an option called "Stage", from which you can choose "Snowflake Managed".
On the page that opens up, configure details such as Stage name, choose the Schema, etc. Then, click on "Create" to create the internal stage.
Once you have created the internal stage, you will view it in your instance using the Database Explorer, as shown below. You could create more than one internal stage as well.
Using such built-in internal stages makes moving your data much smoother, and since your files are already inside the Snowflake system, they work perfectly and instantly with your queries. Plus, you get implicit security because Snowflake offers encryption options, as seen before in the Create Stage step.
Now let's see how to read and write files from/to the stages using KingswaySoft.
Configuring KingswaySoft Snowflake Command Task
As per Snowflake API documentation, you can use a GET command to download files from an internal stage into a local file system, and a PUT command to upload the files from the local file system to the stage. For this, you need to specify the following required parameters.
- <absolute_path_to_file> : The local directory path to the files to upload/download.
- <[namespace.]int_stage_name[/path]> : The internal stage details for files to upload/download.
- <filename> : The name of the file to upload. You can use wildcard characters (*, ?) to upload multiple files (Mandatory only for PUT commands).
File Download
With the parameters in the right place and format, the GET command would look as shown below.
In here, as you can see, the filename is not required, and just the absolute file path is specified along with the stage details. Apart from this, there are optional parameters for filtering (PATTERN) and parallel processing (PARALLEL), which you can find more details in the API documentation.
File Upload
For uploading files, the command will look as shown below, and in this case, the file name is required. The optional parameters are PARALLEL, AUTO_COMPRESS, SOURCE_COMPRESSION and OVERWRITE, details for which can be found in the API documentation.
One thing to note here is that the Snowflake command task also supports parameterization. You can easily drag and drop variables to replace hard-coded static values to build dynamic commands.
Conclusion
As we have shown above, you can easily upload or download your data instantly, replacing complicated manual steps with reliable automation using simple configuration. This gives you all the security and speed of Snowflake's storage without any of the traditional hassle, letting you have a worry-free file management system.