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

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.

Wherobots SQL Driver TypeScript SDK

This is the TypeScript SDK for interacting with WherobotsDB. This package implements a Node.js client that programmatically connects to a WherobotsDB runtime and executes Spatial SQL queries.

Prerequisites

The following resources are needed to run the Wherobots SQL Driver's TypeScript SDK:

1. Node.js version 18 or higher
2. TypeScript version 5.x (if using TypeScript)

Installation

To complete the installation, run the following command:

npm install wherobots-sql-driver

Usage

Example: Executing SQL statement and printing results

This example:

• Establishes the connection to WherobotsDB with an async function
• Calls async methods to execute SQL queries through this connection.

import { Connection, Runtime } from "wherobots-sql-driver";

(async () => {
  const conn = await Connection.connect({
    // replace "YOUR-WHEROBOTS-API-KEY" with the key created above
    // or alternatively the key can be set with the `WHEROBOTS_API_KEY` environment variable
    apiKey: "YOUR-WHEROBOTS-API-KEY",
    runtime: Runtime.SEDONA,
  });
  const results = await conn.execute("SHOW SCHEMAS IN wherobots_open_data");
  console.log(JSON.stringify(results.toArray(), null, 2));
  conn.close();
})();

Running this example returns the results of the query as JSON:

[
  {
    "namespace": "overture"
  },
  {
    "namespace": "overture_2024_02_15"
  },
  {
    "namespace": "overture_2024_05_16"
  },
  {
    "namespace": "overture_2024_07_22"
  },
  {
    "namespace": "test_db"
  }
]

Code example explanation

1. Calling Connection.connect() asynchronously establishes a SQL Session connection in Wherobots Cloud and returns a Connection instance.
2. Calling the connection's execute() methods runs the given SQL statement and asynchronously returns the result as an Apache Arrow Table instance.
3. The Arrow Table instance is converted to a primitive by calling toArray(), and then printed to the console as formatted JSON with JSON.stringify().
4. Calling the connection's close() method tears down the SQL Session connection.

Running the example

JavascriptTypeScript

1. Paste the contents of the above code example into a file called wherobots-example.js
2. Run the example with: node wherobots-example.js

1. Paste the contents of the above code example into a file called wherobots-example.ts
2. Run the example with: npx tsx wherobots-example.ts

Runtime and region selection

Select your desired Wherobots runtime using the runtime parameter and specifying a runtime enum value. See the Wherobots product documentation for guidance on runtime sizing and selection.

Additional parameters to connect()

The Connection.connect() function can take the following additional options:

• resultsFormat: one of the ResultsFormat enum values; Arrow encoding is the default and most efficient format for receiving query results.

Note

currently only Arrow encoding is supported

• dataCompression: one of the DataCompression enum values; Brotli compression is the default and the most efficient compression algorithm for receiving query results.

Note

currently only Brotli compression is supported

• geometryRepresentation: one of the GeometryRepresentation enum values; selects the encoding of geometry columns returned to the client application. The default is EWKT (string) and the most convenient for human inspection while still being usable by geospatial data manipulation libraries.

• region: Currently, the only supported Wherobots compute region is aws-us-west-2, in AWS's Oregon (us-west-2) region.

Additional parameters to execute()

The Connection#execute method can take an optional second argument, options:

• options.signal: an AbortSignal which can be used to cancel the execution (optional)

---

Last update: October 9, 2024 19:02:38