datajunction-query

Pre-release

This release is a pre-release and may not be stable for production use.

DataJunction Query Service

This repository (DJQS) is an open source implementation of a DataJunction query service. It allows you to create catalogs and engines that represent sqlalchemy connections. Configuring a DJ server to use a DJQS server allows DJ to query any of the database technologies supported by sqlalchemy.

Quickstart

To get started, clone this repo and start up the docker compose environment.

git clone https://github.com/DataJunction/djqs
cd djqs
docker compose up

Creating Catalogs

Catalogs can be created using the POST /catalogs/ endpoint.

curl -X 'POST' \
  'http://localhost:8001/catalogs/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "name": "djdb"
}'

Creating Engines

Engines can be created using the POST /engines/ endpoint.

curl -X 'POST' \
  'http://localhost:8001/engines/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "name": "sqlalchemy-postgresql",
  "version": "15.2",
  "uri": "postgresql://dj:dj@postgres-roads:5432/djdb"
}'

Engines can be attached to existing catalogs using the POST /catalogs/{name}/engines/ endpoint.

curl -X 'POST' \
  'http://localhost:8001/catalogs/djdb/engines/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '[
  {
    "name": "sqlalchemy-postgresql",
    "version": "15.2"
  }
]'

Executing Queries

Queries can be submitted to DJQS for a specified catalog and engine.

curl -X 'POST' \
  'http://localhost:8001/queries/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "catalog_name": "djdb",
  "engine_name": "sqlalchemy-postgresql",
  "engine_version": "15.2",
  "submitted_query": "SELECT * from roads.repair_orders",
  "async_": false
}'

Async queries can be submitted as well.

curl -X 'POST' \
  'http://localhost:8001/queries/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "catalog_name": "djdb",
  "engine_name": "sqlalchemy-postgresql",
  "engine_version": "15.2",
  "submitted_query": "SELECT * from roads.repair_orders",
  "async_": true
}'

response

{
  "catalog_name": "djdb",
  "engine_name": "sqlalchemy-postgresql",
  "engine_version": "15.2",
  "id": "<QUERY ID HERE>",
  "submitted_query": "SELECT * from roads.repair_orders",
  "executed_query": null,
  "scheduled": null,
  "started": null,
  "finished": null,
  "state": "ACCEPTED",
  "progress": 0,
  "results": [],
  "next": null,
  "previous": null,
  "errors": []
}

The query id provided in the response can then be used to check the status of the running query and get the results once it’s completed.

curl -X 'GET' \
  'http://localhost:8001/queries/<QUERY ID HERE>/' \
  -H 'accept: application/json'

response

{
  "catalog_name": "djdb",
  "engine_name": "sqlalchemy-postgresql",
  "engine_version": "15.2",
  "id": "$QUERY_ID",
  "submitted_query": "SELECT * from roads.repair_orders",
  "executed_query": "SELECT * from roads.repair_orders",
  "scheduled": "2023-02-28T07:27:55.367162",
  "started": "2023-02-28T07:27:55.367387",
  "finished": "2023-02-28T07:27:55.502412",
  "state": "FINISHED",
  "progress": 1,
  "results": [
    {
      "sql": "SELECT * from roads.repair_orders",
      "columns": [...],
      "rows": [...],
      "row_count": 25
    }
  ],
  "next": null,
  "previous": null,
  "errors": []
}

Reflection

If running a [reflection service](https://github.com/DataJunction/djrs), that service can leverage the POST /table/{table}/columns/ endpoint of DJQS to get column names and types for a given table.

curl -X 'GET' \
  'http://localhost:8001/table/djdb.roads.repair_orders/columns/?engine=sqlalchemy-postgresql&engine_version=15.2' \
  -H 'accept: application/json'

response

{
  "name": "djdb.roads.repair_orders",
  "columns": [
    {
      "name": "repair_order_id",
      "type": "INT"
    },
    {
      "name": "municipality_id",
      "type": "STR"
    },
    {
      "name": "hard_hat_id",
      "type": "INT"
    },
    {
      "name": "order_date",
      "type": "DATE"
    },
    {
      "name": "required_date",
      "type": "DATE"
    },
    {
      "name": "dispatched_date",
      "type": "DATE"
    },
    {
      "name": "dispatcher_id",
      "type": "INT"
    }
  ]
}

DuckDB

DJQS includes an example of using DuckDB as an engine and it comes preloaded with the roads example database.

Create a djduckdb catalog and a duckdb engine.

curl -X 'POST' \
  'http://localhost:8001/catalogs/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "name": "djduckdb"
}'

curl -X 'POST' \
  'http://localhost:8001/engines/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "name": "duckdb",
  "version": "0.7.1",
  "uri": "duckdb://local[*]"
}'

curl -X 'POST' \
  'http://localhost:8001/catalogs/djduckdb/engines/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '[
  {
    "name": "duckdb",
    "version": "0.7.1"
  }
]'

Now you can submit DuckDB SQL queries.

curl -X 'POST' \
  'http://localhost:8001/queries/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "catalog_name": "djduckdb",
  "engine_name": "duckdb",
  "engine_version": "0.7.1",
  "submitted_query": "SELECT * FROM roads.us_states LIMIT 10",
  "async_": false
}'

Spark

DJQS includes an example of using Spark as an engine. To try it, start up the docker compose environment and then load the example roads database into Spark.

docker exec -it djqs /bin/bash -c "python /code/docker/spark_load_roads.py"

Next, create a djspark catalog and a spark engine.

curl -X 'POST' \
  'http://localhost:8001/catalogs/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "name": "djspark"
}'

curl -X 'POST' \
  'http://localhost:8001/engines/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "name": "spark",
  "version": "3.3.2",
  "uri": "spark://local[*]"
}'

curl -X 'POST' \
  'http://localhost:8001/catalogs/djspark/engines/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '[
  {
    "name": "spark",
    "version": "3.3.2"
  }
]'

Now you can submit Spark SQL queries.

curl -X 'POST' \
  'http://localhost:8001/queries/' \
  -H 'accept: application/json' \
  -H 'Content-Type: application/json' \
  -d '{
  "catalog_name": "djspark",
  "engine_name": "spark",
  "engine_version": "3.3.2",
  "submitted_query": "SELECT * FROM roads.us_states LIMIT 10",
  "async_": false
}'

Release files for datajunction-query 0.0.1a31

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for datajunction-query 0.0.1a31
File	Size	Uploaded
datajunction_query-0.0.1a31.tar.gz	142.1 kB	Oct 20, 2023	Details

Built distribution (wheel)

Table of built distributions (wheels) for datajunction-query 0.0.1a31
File	Interpreter	ABI	Platform	Reset
datajunction_query-0.0.1a31-py3-none-any.whl 27.5 kB Oct 20, 2023	Python 3	none	any	Details

Total release size: 169.7 kB

Release files / datajunction_query-0.0.1a31.tar.gz

Download URL	datajunction_query-0.0.1a31.tar.gz
Size	142.1 kB
Tags	Source
SHA-256 checksum How to use checksums	`8aac89179e66edf21c585ca26494c0d94532c668ea4520367ba081713875014d`
BLAKE2b-256 checksum How to use checksums	`2e60dd0a737e9ac4053e4284ccb27de3bf5e4e1c3b8b9768ce81b39bc25ba4b8`
Upload date	Oct 20, 2023
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`python-httpx/0.25.0`

Release files / datajunction_query-0.0.1a31-py3-none-any.whl

Download URL	datajunction_query-0.0.1a31-py3-none-any.whl
Size	27.5 kB
Tags	Python 3
SHA-256 checksum How to use checksums	`efe808ae0ceb95f94b9fbfb5c692a433b5a2f7f1b2398d43c5cda56ebf3c2ba0`
BLAKE2b-256 checksum How to use checksums	`6785453abe6263b3d5e87d3305bdf30202ca3c787b0d43fc4b0c6e37c1a0a10c`
Upload date	Oct 20, 2023
Uploaded using Trusted Publishing? What is trusted publishing?	No
Uploaded via	`python-httpx/0.25.0`