Support GEOMETRY in Spark SQL table creation DDL

[melikasajadian]

Hi,

I am using Sedona in Databricks with Delta table and UC. In version 1.7.0 of Sedona, this DDL works:

%sql
CREATE OR REPLACE TABLE mycatalog.myschema.mytable (
id STRING,
geometry GEOMETRY
)
USING DELTA;

But when I use version 1.7.1 of Sedona I get :
[DELTA_UNSUPPORTED_DATA_TYPES] Found columns using unsupported data types: [geometry: GeometryType]. You can set 'spark.databricks.delta.schema.typeCheck.enabled' to 'false' to disable the type check. Disabling this type check may allow users to create unsupported Delta tables and should only be used when trying to read/write legacy tables. SQLSTATE: 0AKDC

With both versions of 1.7.0 and 1.7.1, CTAS and saveAsTable, it works perfectly fine with Sedona Geometry type. I understand the geometry type of Sedona is not supported in Delta format, but actually I like the idea of not transforming the geometry column to WKT every time I am writing the dataframe into a Delta table.

So my question is: will this feature be back in the next versions or is it better for me to stick to transforming the geometry column to the known types by Delta?

Thank you.

Cheers,
Melika

[jiayuasu]

Honestly you should not be able to write Sedona Geometry to Delta in both 1.7.0 and 1.7.1. DeltaLake standard must support native geo types in order to bypass this. We recently added the geometry type to Iceberg standard and are working on implementing Iceberg reader / writer.

Before we implement this into DeltaLake, which may take a few more month, I recommend you save the geometry column into DeltaLake binary format using ST_AsEWKB and read it back using ST_GeomFromEWKB. To achieve predicate pushdown, save 4 more columns xmin, xmax, ymin, ymax using ST_Xmin, ... WKB format has great encoding speed and less storage overhead.

If you just want to save Sedona tables into Parquet files, I recommend you save it directly as geoparquet files. This gives you native geometry type experience and spatial filter pushdown: https://sedona.apache.org/latest/tutorial/files/geoparquet-sedona-spark/

[melikasajadian]

Thank you for the explanation, I thought that was the intended behavior for Delta tables as well. Then I will do the conversion to WKB. Yesterday, I looked into how the geometry column is being saved. In the Catalog tab, if I check the table column it shows binary for the geometry column. If I do "DESCRIBE TABLE mytable" in the notebook, I get geometry as geometry type. I thought in the notebook the binary implicitly is getting deserialized and then the geometry type is recognized. Please see the images below:

[melikasajadian]

I forgot to mention, I just checked, I cannot write Sedona Geometry to Delta in the default schema of a catalog. Meaning that the below code throws the same "unsupported data types" error:

%sql
CREATE OR REPLACE TABLE geospatial.default.mytable (
id STRING,
geometry GEOMETRY
)
USING DELTA;

But if it is not in the default schema, it creates the table.

[MrPowers]

You'd need to use the BINARY type for the geometry column here. And before writing, you'd need to convert to WKB explicitly.

[melikasajadian]

true, but in version 1.7.0 the above code worked perfectly fine in any schema other than the default schema, which is weird :D But thanks.

[james-willis]

delta claims they support sedona geometry types: https://delta.io/blog/apache-sedona/

Anecdotal, but parquet has good support for UDTs in my experience. I'm not surprised that delta tends to work out of the box with them as well.

There's one caveat where the UDT support in spark/parquet is not great: https://issues.apache.org/jira/browse/SPARK-48942

[melikasajadian]

You are right, since it is not a very stable behavior, I would still go with Jia's recommendation for production codes.

[MrPowers]

I just tried this on my end and this works:

But this doesn't actually store the data as a geometry column - it stores it as a binary column.

It's just automatically doing the conversions under the hood.

It's probably better to just explicitly store the data as WKB.

[james-willis]

It depends on your usecase. If all readers are going to be sedona it is easier and more performant to leave it as a sedona geometry.

[melikasajadian]

Thank you for checking. You are right, the below code works fine for me as well (I think regardless of Sedona version):

df =spark.sql("SELECT ST_Point(1, 1) AS geometry")
display(df)
df.write.saveAsTable("geospatial.default.mygeom")

But with CTAS like below, it will throw an error with sedona 1.7.1 but works fine with sedona 1.7.0:

%sql
CREATE TABLE geospatial.some_schema_other_than_default.mygeom (
id int,
geometry geometry
)

Just a little bit of background: my original question in this discussion was about the CTAS specifically in the two versions of Sedona, because the CTAS I wrote with version 1.7.0 did not work when I upgraded to version 1.7.1. I initially thought it was a bug with the new version but Jia's response made me realized that there is no active development for Delta tables and geometry types of Sedona. So it seems the CTAS that worked on version 1.7.0 was probably an accidental behavior and it is not a stable way of working with Sedona geometry type and Delta tables.

But thank you both for the further investigation. 👍

[jiayuasu]

Maybe it is relevant to this issue: #1868

From your cluster configuration (Cluster -> Edit -> Configuration -> Advanced options -> Spark), add this line:

spark.sedona.enableParserExtension false