CODEWITHSUNDEEP
azuresnowflake-cloud-data-platform
xandermonkey
xandermonkey

Reputation: 4412

Copying data in and out of Snowflake via Azure Blob Storage

I'm trying to copy into blob storage and then copy out of blob storage. The copy into works:

copy into 'azure://my_blob_url.blob.core.windows.net/some_folder/MyTable'
    from (select *
          from MyTable
          where condition = 'true')
    credentials = (azure_sas_token = 'my_token');

But the copy out fails:

copy into MyTable
    from 'azure://my_blob_url.blob.core.windows.net/some_folder/MyTable'
    credentials = (azure_sas_token = 'my_token');

the error is:

SQL Compilation error: Function 'EXTRACT' not supported within a COPY.

Weirdly enough, it worked once and hasn't worked since. I'm at a loss, nothing turns up details for this.

I know there's an approach I could take using stages, but I don't want to for a bunch of reasons and even when I try with stages the same error presents itself.

Edit:

The cluster key definition is:

cluster by (idLocal, year(_ts), month(_ts), substring(idGlobal, 0, 1));

where the idLocal and idGlobal are varchars and the _ts is a TIMESTAMPTZ

Upvotes: 1

Views: 1450

Answers (2)

Chris Hughes
Chris Hughes

Reputation: 51

Alex can you try with a different function in the cluster key on your target table like date_trunc('day',_ts)?

thanks Chris

Upvotes: 0

Suzy Lockwood
Suzy Lockwood

Reputation: 1170

I think I've seen this before with a cluster key on the table (which I don't think is supported with COPY INTO). The EXTRACT function (shown in the error) being part of the CLUSTER BY on the table.

This is a bit of a hunch, but assuming this isn't occurring for all your tables, hoping it leads to investigation on the table configuration and perhaps that might help.

Upvotes: 1

Related Questions