CODEWITHSUNDEEP
azuresnowflake-cloud-data-platformazure-data-factory
Fernando Basaez
Fernando Basaez

Reputation: 3

ADF Script Creating a Table with Parquet format using INFER_SCHEMA in Snowflake

I'm trying to copy data from Salesforce to Snowflake using ADF avoiding use SAS Token, because of that I going to use integration storage on Snowflake.

I'm trying to follow this example: https://medium.com/snowflake/seamless-migration-to-snowflake-using-adf-script-activity-schema-detection-25475ea86a09.

I already create the integration storage, format type (parquet) and a Stage.

The problem is in the script activity: when I try to create a table using INFER_SCHEMA, I always get the same error:

Operation on target Script1 failed: ERROR [22000] Cannot perform operation. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.

When I run the same query in Snowflake it works!

--This is the query that I'm trying to run from script activity

CREATE OR REPLACE TABLE "DEV_LANDING_XXX"."POC_XXX".Account
 using template (select array_agg(object_construct(*))
     from table(infer_schema(location=>'@adf_copyparquetfileXXX_stage_dev/Account', file_format=>'DEV_PARQUET_TYPE_XXX'
         )
     )
 );

I realize when I run the query without "infer_schema" it works.

Can someone help me?

[![enter image description here][1]][1]

I already try to put another scripts Use Role; Use Warehouse; Use Database; Use Schema; each in a different script (in the same script activity).

I put the same query into a stored procedure and call but I got the same error:

Error creating or replacing table: Cannot perform operation. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name.

Upvotes: 0

Views: 291

Answers (2)

Fernando Basaez
Fernando Basaez

Reputation: 3

I finally got the solution...the problem was I was referencing to the stage of the integration storage in wrong manner:

...
table(infer_schema(location=>'@adf_copyparquetfile_stage_dev/Account', file_format=>'DEV_PARQUET_TYPE')));

But I realize that the route for the stage was different in snowflake and I chaged it (The best way to find the route o name go to snowflake in worksheets go to databases and find the stage press 3 dots and "place name in Editor"):

...
table(infer_schema(location=>'@DEV_LANDING_CRM_DB.POC_CCV.ADF_COPYPARQUETFILE_STAGE_DEV', file_format=>'DEV_PARQUET_TYPE')));

Upvotes: 0

Pratik Lad
Pratik Lad

Reputation: 8392

"Error creating or replacing table: Cannot perform operation. This session does not have a current schema. Call 'USE SCHEMA', or use a qualified name."

The cause of error is the issue with the stagging you created with Blob storage.

When you are configuring a Snowflake storage integration you have to Grant Snowflake Access to the Storage Locations by adding Storage Blob Data Contributor role to service principal created by Snowflake in your tenant.

  • The role possessing the OWNERSHIP privilege on the stage must also have the USAGE privilege on the storage integration.
  • Also check the user have appropriate USAGE privilege on the storage integration.

Follow this document to create Snowflake storage integration

Upvotes: 0

Related Questions