CODEWITHSUNDEEP
type-mismatchsnowflake-cloud-data-platform
Gabriel
Gabriel

Reputation: 600

Data type mismatch when creating pipe

I am creating a snowpipe which will load data from a S3 bucket into a table. My table contains a column of data type GEOGRAPHY.

When I am creating the snowpipe I get the following error message: "SQL compilation error: Expression type does not match column data type, expecting GEOGRAPHY but got VARCHAR(16777216) for column GEO_LOCATION"

Why does it assume that the incoming column is of type VARCHAR(16777216)? How do I change this assumptiom? Because I know that my input column will be in the correct format.

Upvotes: 0

Views: 278

Answers (2)

Gokhan Atil
Gokhan Atil

Reputation: 10079

As for now, snowpipe requires transformation when loading geo data. Here is the repro of the issue:

create or replace stage mystage;
create or replace table test ( a geography );
insert into test values ('POINT(-122.35 37.55)' );
copy into @mystage from test;
truncate table test;

copy into test from @mystage; -- works
truncate table test;

create or replace pipe test_pipe as -- fails with expecting GEOGRAPHY but got VARCHAR(16777216) for column A
copy into test from @mystage;

If you apply transformation, it should work:

create or replace pipe test_pipe as -- works
copy into test from (select to_GEOGRAPHY($1) from @mystage);

alter pipe test_pipe refresh;

select * from test;

This is a known issue, and it will be fixed in future.

Upvotes: 1

Dean Flinter
Dean Flinter

Reputation: 674

Try something like this in your snowpipe definition:

TO_GEOGRAPHY( <varchar_expression> )

Upvotes: 0

Related Questions