search

PacktPublishing / Serverless-Analytics-with-Amazon-Athena

Serverless Analytics with Amazon Athena, published by packt
MIT License
25 stars 12 forks source link

ST_CONTAINS ERROR Unexpected parameters (varbinary, Geometry) #3

Open ajcastany opened 1 year ago

ajcastany commented 1 year ago

When running the query on page 84, either on bash or on the athena query editor I have the following error:

SYNTAX_ERROR: line 4:7: Unexpected parameters (varbinary, Geometry) for function st_contains. Expected: st_contains(Geometry, Geometry) , st_contains(varchar, varchar) , st_contains(Geometry, varchar) , st_contains(varchar, Geometry) This query ran against the "packt_serverless_analytics" database, unless qualified by the query

The datasets were downloaded with the script in this repository. I have tried to use ST_GeomFromBinary(varbinary) to convert it to Geometry, but it complains about the WKB representation? (this is with both Athena Engine 2 and 3)

ajcastany commented 1 year ago

I managed to make it work, updating the engine to lvl 3 and converting the counties.boundaryshape with geometry_from_hadoop_shape so where st_contains(geometry_from_hadoop_shape(counties.boundaryshape)