Arrow Flight SQL

deql server keeps the query and optimization engine alive behind Arrow Flight SQL. Clients send SQL statements and receive Arrow results.

Start the server

./deql server \
  --register-files inventory.csv \
  --candidates candidates.deql

Decisionhouse server listening on 127.0.0.1:6134

The server supports the same data options as the CLI:

--config
--connection
--candidates
--register-files

There is no positional query argument. Clients send queries after connecting. The address comes from the [server] configuration and defaults to 127.0.0.1:6134.

Connect from Python

Install the Flight SQL ADBC driver and PyArrow in your Python environment.

pip install adbc-driver-flightsql pyarrow

Keep the Decisionhouse server running in one terminal, then connect from another.

import adbc_driver_flightsql.dbapi as flight_sql

connection = flight_sql.connect("grpc://localhost:6134")
cursor = connection.cursor()

cursor.execute("""
CREATE OR REPLACE CANDIDATES items
DECISION KEY (id)
AS (
  SELECT *
  FROM inventory
)
""")

with open("query.deql") as query_file:
    cursor.execute(query_file.read())

solution = cursor.fetch_arrow_table()

One connection can execute CREATE CANDIDATES, DECIDE, and ordinary SQL. CREATE CANDIDATES returns an OK response.

Candidate-set scope

Candidate sets belong to the server process and are shared by all clients. Define stable sets once with --candidates, then let clients send only decision queries.

Flight result shape

Flight SQL differs from the CLI in two ways.

A decision column bounded to 0 or 1 acts as a filter. Only rows where every such decision was chosen are returned, and the 0/1 decision columns are removed.
Decision columns with other bounds remain in the table and do not filter rows.

For the knapsack example, Flight SQL returns the selected rows as id, weight, and value, rather than every candidate with is_selected.

Solver metadata

The solver report is stored as JSON in the Arrow schema metadata.

import json

info = json.loads(solution.schema.metadata[b"deql.solution_info.v1"])
print(info)

{
  "objective_value": 389.79,
  "objective_sense": "maximize",
  "status": "optimal",
  "solver": "highs",
  "algorithm": "mixed_integer_programming",
  "warm_start": "not_attempted"
}

Discovery and bulk ingest

GetCatalogs, GetDbSchemas, and GetTables are implemented, so Flight SQL clients can inspect registered data.

A client that already holds Arrow data can bulk ingest candidate rows instead of running CREATE CANDIDATES. Name the decision key columns in the deql.candidate_keys ingest option as a JSON array, for example ["id"].