azuresnowflake-cloud-data-platformazure-data-factory

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.

Solution

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