How to Insert Bulk Data Using Snowflake Integration Service

I’m trying to insert bulk data using the new Snowflake Integration Service Connector. Am I just missing something or is that not an option with the current package? (v4.0.0-preview)…

If it is not an option, what is a feasible workaround?

image

Hi,

Chat GPT output(you can probably ask different questions):

The Snowflake Scope activity in UiPath is used to define a set of activities that will execute within a specific Snowflake connection. This allows you to perform multiple actions, such as executing queries and loading data, within a single transaction. It also allows you to manage the Snowflake connection more efficiently by automatically opening and closing the connection as needed.

Here is an example of how you can use the Snowflake Scope activity in UiPath:

  1. Drag the Snowflake Scope activity from the activities panel to the design area.
  2. Inside the Snowflake Scope activity, drag the activities that you want to execute within the Snowflake connection. For example, you can add the “Snowflake Execute Query” or “Snowflake Bulk Insert” activity.
  3. In the properties panel, configure the Snowflake connection by providing the required information such as the account, username, password, and warehouse.
  4. Run the workflow. The Snowflake Scope activity will automatically open the connection, execute the activities within the scope, and then close the connection.
  5. If you have multiple Snowflake Scopes in your workflow, you can set a transaction isolation level for each scope. The isolation level determines how the data is locked during the transaction.

You can also use the “Snowflake Transaction” activity, which allows you to start, commit or rollback a transaction.

It’s worth noting that the Snowflake Scope should be used when the actions that you want to perform requires to be in the same transaction, if not, it’s better to open and close the connection for each action

There are a few ways to insert bulk data into a Snowflake table using the Snowflake Integration Service in UiPath:

  1. Using the “Snowflake Bulk Insert” activity: This activity allows you to insert data into a Snowflake table from a CSV or Excel file. It supports loading data in bulk and can handle large data sets efficiently.
  2. Using the “Snowflake Execute Query” activity: You can use this activity to execute a SQL query that performs a bulk insert. You can use the “COPY INTO” statement to insert data into a Snowflake table from a CSV or JSON file.
  3. Using the “Snowflake Load Data” activity: This activity is designed to load data into a Snowflake table from a CSV or JSON file. It uses the “COPY INTO” statement to insert data in bulk and automatically handles large data sets.

Here is an example of how you can use the “Snowflake Execute Query” activity to insert bulk data from a CSV file into a Snowflake table:

sqlCopy code

COPY INTO my_table
FROM 's3://my-bucket/data.csv'
CREDENTIALS = (AWS_KEY_ID='access_key_id' AWS_SECRET_KEY='secret_access_key')
CSV 
HEADER 
QUOTE = '"'
ESCAPE = '\'

Note that above is an example, you should replace the table name, file path, and credentials with your own.

You should also make sure that the file format, column names, and data types match the table’s schema. Additionally, make sure that the Snowflake user has the correct permissions to access the table and the file location.

It’s important to note that before inserting the data, you should make sure that your data is well formatted and clean, this will help you to avoid errors and issues during the insertion process

What Snowflake Package version are you using? I don’t have those activities (Snowflake Execute Query, Snowflake Bulk Insert)

Looks like this is not possible with the current package. Also found other bugs in even the Insert Record activity.

We decided against utilizing the Snowflake Integration Service connector until it is improved. Instead, we used ODBC and the regular Database connections (see link below)

@Caleb_D How to get this snowflake activity ? and on which version of the UiPath studio will we get the snowflake activities to install. I am using 2021 version and its not giving any option for snowflake activity

Hi @sunitanabjya and @Caleb_D , just letting you know that Snowflake connector and activities from Integration Service has reached its official General Availability status, version 5.0.1. You should ideally be able to locate this version in those greater than 22.10. We’re pleased to inform you that complete Create, Read, Update, and Delete (CRUD) functionalities are now accessible for tables that allow these operations. However, we regret to inform that performing operations in bulk is not feasible at the moment, but we have plans to include this feature in our upcoming roadmap.
Here is the announcement.

Hi @Caleb_D , The Native Search can be potentially used to do a Bulk job since it should ideally accept any query that works with Snowflake, be it a Select or an Insert. But, you would need to construct the Insert query before passing it to Native Search as a variable. Can you try this and let me know if that works?