REST-Jdbc — OpenWeatherMap

OpenWeatherMap provides weather data. This example uses the 5-day forecast (3-hour steps) and shows parameterized queries with FILTER — the city name is passed as query parameter q, the API key via connection properties (auth=apikey).

Quick start with the JDBC client

java -jar /path/to/restjdbc.jar openweather.sql

The file openweather.sql contains the connection and sample queries. Steps:

1. Obtain the driver JAR (absolute path to restjdbc.jar)
2. Create an API key at openweathermap.org/api (free registration)
3. Replace apiKey=XXXX in openweather.sql with your key
4. Run the command in the folder containing openweather.sql and the spec

connect 'jdbc:rest:https://api.openweathermap.org|spec=openweather-spec.json,auth=apikey,apiKey=Your_Key,apiKeyLocation=query,apiKeyParam=appid'

1. JDBC URL

jdbc:rest:https://api.openweathermap.org

The API base URL is set in the JDBC URL.

Part	Value
Driver prefix	jdbc:rest:
API base URL	https://api.openweathermap.org
SQL schema	public (default)

Connection example (Java):

Properties props = new Properties();
props.setProperty("spec", "/path/to/openweather-spec.json");
props.setProperty("auth", "apikey");
props.setProperty("apiKey", System.getenv("OPENWEATHER_API_KEY"));
props.setProperty("apiKeyLocation", "query");
props.setProperty("apiKeyParam", "appid");

Connection conn = DriverManager.getConnection(
    "jdbc:rest:https://api.openweathermap.org", props);

2. Connection properties

Property	Value	Required
spec	Path to spec	Yes
auth	apikey	Yes
apiKey	OpenWeather API key	Yes
apiKeyLocation	query (default, explicit for OpenWeather)	No
apiKeyParam	Query parameter name (appid for OpenWeather)	Yes

Spec path: absolute path to your local copy of the spec file.

Spec file: openweather-spec.json

3. Spec file

File: openweather-spec.json

Excerpt:

{
  "entities": [
    {
      "name": "forecast",
      "path": "/data/2.5/forecast?units=metric",
      "dataPath": "/list",
      "pagination": { "type": "none" },
      "write": false,
      "columns": [
        { "name": "dt", "type": "BIGINT", "primaryKey": true },
        { "name": "temp", "type": "DOUBLE", "jsonPath": "/main/temp" },
        { "name": "humidity", "type": "BIGINT", "jsonPath": "/main/humidity" },
        { "name": "description", "type": "VARCHAR", "jsonPath": "/weather/0/description" }
      ]
    }
  ]
}

Table forecast

SQL table	REST path	Pagination
forecast	/data/2.5/forecast?units=metric	none (one response with up to 40 entries)

• units=metric: Temperatures in °C (fixed in spec).
• dataPath /list: Forecast entries are in the list array.
• jsonPath: Temperature and description are nested under main and weather.

4. Authentication

OpenWeather expects the API key as query parameter appid, not as a bearer header. Use the apikey auth method:

props.setProperty("auth", "apikey");
props.setProperty("apiKey", "Your_OpenWeather_Key");
props.setProperty("apiKeyLocation", "query");
props.setProperty("apiKeyParam", "appid");

The driver appends appid=… to every request URL. The city name is added separately via FILTER q=….

5. SQL examples

SELECT

SELECT dt, temp, humidity, description FROM forecast FILTER q=Berlin;

SELECT dt, temp, humidity FROM forecast FILTER q=London;

SELECT dt, temp, description FROM forecast FILTER q=New York;

SQL	HTTP
SELECT … FILTER q=Berlin	GET …/forecast?appid=…&units=metric&q=Berlin
SELECT … FILTER q=London	GET …/forecast?appid=…&units=metric&q=London

FILTER q=… sets the city name as a query parameter — a typical pattern for parameterized REST queries.

Quote values with spaces if needed: FILTER q='New York'.

INSERT, UPDATE, DELETE

Not supported ("write": false).

6. System tables

SELECT table_name FROM system.table_list;

SELECT column_name, type_name FROM system.column_list WHERE table_name = 'forecast';

7. Notes

• Activation: New API keys may take a few minutes to become active.
• Free tier: Limited calls per minute/day — see OpenWeather documentation.
• Current weather: /data/2.5/weather returns a single JSON object (not an array) — the driver expects arrays. Use /data/2.5/forecast with dataPath: "/list" for forecasts.
• Multiple parameters: Multiple query parameters with FILTER param1=… AND param2=… — see the Webmetic example. Fixed parameters like units are in the spec path; the API key via auth=apikey.