Azure Question posted in
The official documentation to get you started can be found here.

Azure – 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_CRM_DB"."POC_CCV".Account
 using template (select array_agg(object_construct(*))
     from table(infer_schema(location=>'@adf_copyparquetfile_stage_dev/Account', file_format=>'DEV_PARQUET_TYPE'
         )
     )
 );

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

Can someone help me?

enter image description here

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.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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')));


  2. 0

    "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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search