Wherobots Spatial SQL API and SDKs¶

The Wherobots Spatial SQL API is a programmatic solution to support integrations with Wherobots Cloud, with direct SDK access to execute Spatial SQL queries.

Wherobots Cloud and the Wherobots Spatial SQL API are powered by WherobotsDB, with Apache Sedona at its core: a distributed computation engine that can horizontally scale to handle computation and analytics on any dataset. Wherobots Cloud automatically manages the infrastructure and compute resources of WherobotsDB to serve your use case based on how much computation power you need.

By establishing a connection to the Wherobots Spatial SQL API, a SQL session is started backed by your selected WherobotsDB runtime. Queries submitted through this connection are securely executed against your runtime, with compute fully managed by Wherobots.

We provide client SDKs in Java and in Python to easily connect and interact with WherobotsDB through the Spatial SQL API, as well as an Airflow Provider to build your spatial ETL DAGs; all of them are open-source and available on package registries, as well as on Wherobots’ GitHub page.

Note

Currently the API has a hard limit of 1000 rows on returned results. This doesn't affect computations on the clusters, just the amount of data the API or the drivers send back on the wire.

API keys¶

To authenticate and use the Spatial SQL APIs and SDKs, we often require a API key to be set up and be available. You can find more information here.

Python DB-API driver¶

To access Wherobots DB, we provide a Python DB-API implementation. The driver is a PEP-0249 compatible driver to programmatically connect to a Wherobots DB runtime and execute Spatial SQL queries.

Installation¶

If you use Poetry in your project, add the dependency with poetry add:

poetry add wherobots-python-dbapi

Otherwise, just pip install it:

pip install wherobots-python-dbapi

Usage¶

Usage follows the typical pattern of establishing the connection, acquiring a cursor, and executing SQL queries through it:

import logging

from wherobots.db import connect
from wherobots.db.region import Region
from wherobots.db.runtime import Runtime

# Optionally, setup logging to get information about the driver's
# activity.
logging.basicConfig(
    stream=sys.stdout,
    level=logging.INFO,
    format="%(asctime)s.%(msecs)03d %(levelname)s %(name)20s: %(message)s",
    datefmt="%Y-%m-%d %H:%M:%S",
)

# Get your API key, or securely read it from a local file.
api_key = '...'

with connect(
    host="api.cloud.wherobots.com",
    api_key=api_key,
    runtime=Runtime.SEDONA,
    region=Region.AWS_US_WEST_2) as conn:
        curr = conn.cursor()
        sql = """
            SELECT
                id,
                names['primary'],
                geometry,
                population
            FROM
                wherobots_open_data.overture_2024_02_15.admins_locality
            WHERE localityType = 'country'
            SORT BY population DESC
            LIMIT 10
        """
        curr.execute(sql)
        results = curr.fetchall()
        results.show()

The Cursor supports the context manager protocol, so you can use it within a with statement when needed:

with connect(...) as conn:
    with conn.cursor() as curr:
        curr.execute(...)
        results = curr.fetchall()
        results.show()

It also implements the close() method, as suggested by the PEP-2049 specification, to support situations where the cursor is wrapped in a contextmanager.closing().

Runtime and region selection¶

You can chose the Wherobots runtime you want to use with the runtime parameter, passing in one of the Runtime enum values. For more information on runtime sizing and selection, please consult the Wherobots product documentation.

The only supported Wherobots compute region for now is aws-us-west-2, in AWS's Oregon (us-west-2) region. It can be overridden with the region parameter.

Harlequin-wherobots SQL IDE for your terminal¶

As an example usage for our Python DB-API driver, we also integrated with Harlequin.

Quick start guide¶

Detailed usage guide is available as part of Harlequin's Wherobots Adapter documentation.

# Install Harlequin with Wherobots adapter
pip install harlequin-wherobots

# Start harlequin
harlequin -a wherobots --api-key <api-key> --runtime SEDONA --region AWS_US_WEST_2

Harlequin-wherobots Example

JDBC (type 4) driver¶

We provide an open-source Java library that implements a JDBC (Type 4) driver for connecting to WherobotsDB.

Installation¶

To start building Java applications around the Wherobots JDBC driver, add the following line to your build.gradle file’s dependency section:

implementation "com.wherobots:wherobots-jdbc-driver"

Usage¶

In your application, you only need to work with Java’s JDBC APIs from the java.sql package:

import com.wherobots.db.Region;
import com.wherobots.db.Runtime;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

// Get your API key, or securely read it from a local file.
String apiKey = "...";

Properties props = new Properties();
props.setProperty("apiKey", apiKey);
props.setProperty("runtime", Runtime.SEDONA);
props.setProperty("region", Region.AWS_US_WEST_2);

try (Connection conn = DriverManager.getConnection("jdbc:wherobots://api.cloud.wherobots.com", props)) {
    String sql = """
        SELECT
            id,
            names['primary'] AS name,
            geometry,
            population
        FROM
            wherobots_open_data.overture_2024_02_15.admins_locality
        WHERE localityType = 'country'
        SORT BY population DESC
        LIMIT 10
    """;
  Statement stmt = conn.createStatement();
  try (ResultSet rs = stmt.executeQuery(sql)) {
    while (rs.next()) {
      System.out.printf("%s: %s %f %s\n",
        rs.getString("id"),
        rs.getString("name"),
        rs.getDouble("population"),
        rs.getString("geometry"));
    }
  }
}

For more information and future releases, see our driver on GitHub.

Last update: May 20, 2024 19:29:12