How to copy a csv file from blob storage to snowflake table?

LIU, PETER 0

Trying to copy a csv file from blob storage to snowflake table. Been trying various ways but keep getting all sorts of errors.

Getting various errors including:

ErrorCode=SnowflakeInvalidStorageIntegration,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Snowflake invalid storage integration : The specified storage account is not permitted by the snowflake storage integration. Ensure that the storage account is included in the allow list of the storage integration.,Source=Microsoft.DataTransfer.Connectors.Snowflake,'

When not specifying Storage Integration
Operation on target Copy data1 failed: ErrorCode=GenericAdoNetReadError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Failed to execute the query command during read operation.,Source=Microsoft.DataTransfer.Connectors.GenericAdoNet,''Type=Apache.Arrow.Adbc.AdbcException,Message=[Snowflake] 003001 (42501): SQL access control error:

Insufficient privileges to operate on schema 'SDW_STAR_STAGE2',Source=Apache.Arrow.Adbc,'

Sina Salam 13,371 Reputation points

2024-11-18T22:09:04.03+00:00
Hello LIU, PETER,

Welcome to the Microsoft Q&A and thank you for posting your questions here.

I understand that you are trying to copy a csv file from blob storage to snowflake table and have been trying various ways but keep getting all sorts of errors.

There are two main issues while trying to copy a CSV file from blob storage to a Snowflake table. To resolve the Snowflake Invalid Storage Integration Error, ensure the specified storage account is included in the Snowflake storage integration's allow list by verifying and configuring the storage integration settings. - https://docs.snowflake.com/en/sql-reference/sql/copy-into-table For the SQL Access Control Error, check and grant the necessary privileges to the user or role on the schema 'SDW_STAR_STAGE2'. - https://docs.snowflake.com/en/user-guide/data-load-bulk-ts For more details, refer to the Snowflake documentation on COPY INTO and Troubleshooting Bulk Data Loads in the associated links.

You can also follow the steps below to troubleshoot and resolve these issues:

Make sure that your storage integration in Snowflake is correctly set up and includes the Azure storage account, use the following SQL command to check and update the storage integration:
ALTER STORAGE INTEGRATION <integration_name> SET STORAGE_ALLOWED_LOCATIONS = ('azure://<account>.blob.core.windows.net/<container>');

Make sure that the user has the required privileges on the schema. You can grant the necessary privileges using the following SQL commands:
GRANT USAGE ON SCHEMA <database_name>.<schema_name> TO ROLE <role_name>; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <database_name>.<schema_name> TO ROLE <role_name>;

Once the storage integration and privileges are correctly set up, use the COPY INTO command to load the CSV file into the Snowflake table:
COPY INTO <table_name> FROM 'azure://<account>.blob.core.windows.net/<container>/<file_name>.csv' STORAGE_INTEGRATION = <integration_name> FILE_FORMAT = (TYPE = 'CSV', FIELD_OPTIONALLY_ENCLOSED_BY = '"', SKIP_HEADER = 1);

I hope this is helpful! Do not hesitate to let me know if you have any other questions.

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful.
Ganesh Gurram 1,825 Reputation points Microsoft Vendor

2024-11-18T22:22:41.28+00:00
@LIU, PETER - Thanks for the question and using MS Q&A forum.
Below are the steps to copy a CSV file from blob storage to snowflake table:
Step 1: Create an Azure Data Factory Instance

Go to the Azure portal.

Click on "Create a resource" and search for "Data Factory".

Click "Create" and fill in the necessary details like Subscription, Resource Group, Region, and Data Factory Name.

Click "Review + Create" and then "Create".

Step 2: Create Linked Services

Blob Storage Linked Service

In the ADF portal, go to the "Manage" tab on the left pane.

Click on "Linked services" under the Connections section.

Click on "+ New".

Search for "Azure Blob Storage" and select it.

Fill in the necessary connection details such as the name, integration runtime (use default), and authentication method (e.g., account key or managed identity).

Test the connection and click "Create".

Snowflake Linked Service

In the same "Linked services" section, click on "+ New".

Search for "Snowflake" and select it.

Fill in the necessary connection details such as name, account, username, password, database, schema, and warehouse.

Test the connection and click "Create".

Step 3: Create Datasets

Blob Storage Dataset

Go to the "Author" tab on the left pane.

Click on "Datasets" and then "+ New".

Choose "Azure Blob Storage" and then "DelimitedText" (for CSV files).

Configure the dataset:

Select the linked service you created for Blob Storage.

Specify the file path in the container.

Set the file format settings (e.g., comma as the column delimiter).

Click "OK" to create the dataset.

Snowflake Dataset

In the "Datasets" section, click "+ New".

Choose "Snowflake".

Configure the dataset:

Select the linked service you created for Snowflake.

Specify the database, schema, and table name.

Click "OK" to create the dataset.

Step 4: Create a Pipeline

In the "Author" tab, click on "Pipelines" and then "+ New" to create a new pipeline.

Step 5: Add Copy Activity

In the new pipeline, drag a "Copy data" activity from the Activities pane to the pipeline canvas.

Click on the "Copy data" activity to configure it.

Source Configuration

In the "Source" tab of the copy activity, click on "Source dataset".

Select the Blob Storage dataset you created earlier.

Optionally, configure additional settings such as file format and compression type if necessary.

Sink Configuration

In the "Sink" tab of the copy activity, click on "Sink dataset".

Select the Snowflake dataset you created earlier.

Configure additional settings such as pre-copy script, post-copy script, or table options if necessary.

Mapping Configuration

In the "Mapping" tab, configure column mappings between the source CSV file and the target Snowflake table if necessary.

ADF can auto-map columns if the names are identical, but manual mapping might be needed for different column names or transformations.

Step 6: Validate and Debug

Use the "Validate" button to check for any configuration issues.

Use the "Debug" button to test the pipeline and ensure it works as expected.

Step 7: Publish the Pipeline

Once you have validated and tested the pipeline, click the "Publish All" button to publish the pipeline.

Step 8: Run the Pipeline

You can manually trigger the pipeline by clicking the "Add Trigger" button and then "Trigger Now".

Alternatively, you can set up a schedule trigger to run the pipeline on a regular basis.

By following these detailed steps, you should be able to successfully copy a CSV file from Azure Blob Storage to a Snowflake table using Azure Data Factory.

Follow these troubleshooting steps, for the errors you are facing:

Ensure that your Snowflake storage integration is correctly configured to allow access to your Azure Blob Storage account.

Make sure that the Azure Blob Storage account you are trying to access is included in the allow list of the Snowflake storage integration.

If the storage account is not included, update the storage integration to include the necessary Azure Blob Storage account.

Ensure that the Snowflake user account has the necessary permissions to access and perform operations on the specified schema.

If the user does not have the required privileges, you will need to grant them. This typically involves granting USAGE, SELECT, INSERT, UPDATE, and other necessary privileges on the schema and its objects.

Verify that the role assigned to the user has the necessary privileges. If not, you may need to adjust the role's permissions or assign a different role.

Hope this helps. Do let us know if you have any further queries!
Ganesh Gurram 1,825 Reputation points Microsoft Vendor

2024-11-19T19:28:48.49+00:00

@LIU, PETER - We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
shanewatson 0 Reputation points

2024-11-19T20:03:56.1533333+00:00

Hi ganesh,

Can you share a youtube video explaining that or a tutorial. It'll be a great help.
LIU, PETER 0 Reputation points

2024-11-19T20:37:06.7366667+00:00

I can check the access to the storage integration.

Is storage integration necessary? I saw in the documentation that it is possible to not involve storage integration if the storage is blob storage data lake gen v2. Can you comment / confirm?
Ganesh Gurram 1,825 Reputation points Microsoft Vendor

2024-11-20T17:47:43.2533333+00:00

@LIU, PETER - While storage integration is often used for enhanced security and management, it is not strictly necessary when copying data from Azure Blob Storage (Data Lake Gen2) to Snowflake.

You can perform the copy activity without storage integration by using shared access signature (SAS) authentication for the Azure Blob Storage connection. This method allows you to directly copy data to Snowflake without the need for an intermediate storage integration setup.

1 answer

Ganesh Gurram 1,825 Reputation points Microsoft Vendor

2024-11-20T17:44:37.6566667+00:00
@LIU, PETER - Thanks for the question and using MS Q&A forum.

I tried to repro the scenario, and the activity was completed successfully without any issues.

Follow these steps to copy a CSV file from blob storage to snowflake table:

Create an Azure Data Factory.

Create Blob storage and upload one CSV file in it.

Create Linked Service for blob storage as shown in below figure. Select Authentication type as SAS URI and copy paste the SAS URL from blob storage. Click on test connection, make sure the connection must be successful.

Create a Snowflake instance, then set up a database, warehouse and add a table to the database as illustrated in the figure.

Create linked service for Snowflake as shown in figure. Enter Account name, Database and Warehouse names correctly from the Snowflake created. Click on test connection and save.

Create Dataset for Blob storage.

Create Dataset for snowflake.

Create a pipeline and add a copy activity. Select source dataset as Blob storage.

Select sink dataset as snowflake, click on publish all and validate and click on debug.

Pipeline status is succeeded. Hope this helps. Do let us know if you have any further queries. ------------- If this answers your query, do click `Accept Answer` and `Yes` for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
LIU, PETER 0 Reputation points

2024-11-20T17:51:40.67+00:00

This look very similar to what I did. Let me follow you post step by step and see if I can replicate it.

Ganesh Gurram 1,825 Reputation points Microsoft Vendor

2024-11-21T07:07:14.82+00:00

@LIU, PETER - Just checking in to see if the above answer helped. If this answers your query, do click "Accept the answer” for the same, which might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.