Data Load into Snowflake table - Geometry data

0
votes

I have a requirement to load a csv file which contains geometry data into a Snowflake table. I am using data load option which is available in the Snowflake WebGUI.

The sample geometry data is as below.

LINESTRING (-118.808186210713 38.2287933407744, -118.808182249848 38.2288155788245, -118.807079844554 38.2293234553217, -118.806532314702 38.229961732287, -118.80625724007 38.2306350645631, -118.805071970015 38.231849721603, -118.804097093763 38.2325380450286, -118.803504299857 38.2328501734747, -118.802726055048 38.2332839062976, -118.802126140311 38.2334442483131, -118.801758172942 38.233542312624)

Since commas are present in the geometry data, the data load option is treating them as a separate column and throwing a error.

I tried using updating the csv file with "to_geography" function as below, but still no luck.

TO_GEOGRAPHY(LINESTRING (-118.808186210713 38.2287933407744, -118.808182249848 38.2288155788245, -118.807079844554 38.2293234553217, -118.806532314702 38.229961732287, -118.80625724007 38.2306350645631, -118.805071970015 38.231849721603, -118.804097093763 38.2325380450286, -118.803504299857 38.2328501734747, -118.802726055048 38.2332839062976, -118.802126140311 38.2334442483131, -118.801758172942 38.233542312624))

So any pointers on this would be appreciated, the full content of the csv file is as below.

ID,"    GEOGRAPHIC_ROUTE",Name
12421,"LINESTRING (-118.808186210713 38.2287933407744, -118.808182249848 38.2288155788245, -118.807079844554 38.2293234553217, -118.806532314702 38.229961732287, -118.80625724007 38.2306350645631, -118.805071970015 38.231849721603, -118.804097093763 38.2325380450286, -118.803504299857 38.2328501734747, -118.802726055048 38.2332839062976, -118.802126140311 38.2334442483131, -118.801758172942 38.233542312624)",Winston
1
snowflake-cloud-data-platform
Can you share the definition of your FILE FORMAT? – Mike Walton
{ "TYPE": "CSV", "RECORD_DELIMITER": "\n", "FIELD_DELIMITER": ",", "FILE_EXTENSION": null, "SKIP_HEADER": 1, "DATE_FORMAT": "AUTO", "TIME_FORMAT": "AUTO", "TIMESTAMP_FORMAT": "AUTO", "BINARY_FORMAT": "HEX", "ESCAPE": "NONE", "ESCAPE_UNENCLOSED_FIELD": "\\", "TRIM_SPACE": true, "FIELD_OPTIONALLY_ENCLOSED_BY": "NONE", "NULL_IF": ["\\N"], "COMPRESSION": "AUTO", "ERROR_ON_COLUMN_COUNT_MISMATCH": true, "VALIDATE_UTF8": true, "SKIP_BLANK_LINES": false, "REPLACE_INVALID_CHARACTERS": false, "EMPTY_FIELD_AS_NULL": true, "SKIP_BYTE_ORDER_MARK": true, "ENCODING": "UTF8" } – sudarshan
Above is the file format – sudarshan
Hi - if you have fields containing your delimiter character then you will need to enclose your fields with quote marks (or some other appropriate character) and update your file format to reflect this i.e change this line: "FIELD_OPTIONALLY_ENCLOSED_BY": "NONE", – NickW

1 Answers

3
votes

As I see, the fields are enclosed by double quotes to prevent misinterpretion of comma characters of geographic data (which is good!)

Could you set FIELD_OPTIONALLY_ENCLOSED_BY to '"' (double quote) for your file format, try to re-import the file?

https://docs.snowflake.com/en/sql-reference/sql/create-file-format.html#format-type-options-formattypeoptions

I am able to ingest the sample data using the following COPY command:

copy into TEST_TABLE from @my_stage
FILE_FORMAT = (type = csv, FIELD_OPTIONALLY_ENCLOSED_BY='"', skip_header =1 );