apache / apache/sedona

Sedona parameterized queries

Open
#1,781 1 comment 1 reaction 0 assignees View on GitHub
Dominant language
Java
Stars
2.4k
Forks
784
Avg merge
1d 12h
Merged PRs (30d)
58

Description

Parameterized queries [were recently added to Spark](https://www.databricks.com/blog/parameterized-queries-pyspark) and allow for some really clean syntax when shifting from the Python API to the SQL API. We should consider adding this interface to Sedona.

## Status quo

Create a geometry object for Minnesota:

```python
minnesota = 'POLYGON((-96.4517 43.5008,-91.2195 43.5017,-91.3101 43.8226,…))’
```

Create a DataFrame:

```python
df = sedona.read.format("shapefile").option("charset", "UTF-8").load(user_uri + extract_dir)
```

Create a temporary view:

```python
df.createOrReplaceTempView('gauges')
```

Run a query with a f-string:

```python
query = sedona.sql(f'''
select geometry, Status, Waterbody, Observed
from gauges
where st_intersects(geometry, ST_GeomFromWKT('{minnesota}'))
''')
```

## How to make this more concise with a parameterized query

This could be nicer with a parameterized query:

```python
sedona.sql("""
select geometry, Status, Waterbody, Observed
from {df}
where st_intersects(geometry, ST_GeomFromWKT('{minnesota}'))
""", df=df, minnesota=minnesota)
```

The parameterized query saves you from creating the temporary view and using f-strings.

It would be great if the parameters were also sanitized to prevent SQL injection vulnerabilities.

## Another parameterized query example

This can be even cooler. Assume Minnesota is a geometry object as follows:

```python
minnesota_wkt = 'POLYGON((-96.4517 43.5008,-91.2195 43.5017,-91.3101 43.8226,…))’
minnesota = ST_GeomFromWKT(minnesota_wkt)
```

Then, we can pass the geometry object directly in the parameterized query:

```python
sedona.sql("""
select geometry, Status, Waterbody, Observed
from {df}
where st_intersects(geometry, minnesota)
""", df=df, minnesota=minnesota)
```

Contributor guide

Open the contributing guide

Research direction

Start with the sedona.sql interface and compare it with Spark's parameterized-query behavior described in the issue. Define how DataFrame, geometry, and string parameters should work, then verify that the examples run without a temporary view or f-string and that parameter values are sanitized against SQL injection.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, spark, sql
Domain
api, data, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
30/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.