Skip to main content
Version: Next

Arrow Flight SQL API

Arrow Flight SQL is a protocol for interacting with SQL databases using the Arrow in-memory format and the Flight RPC framework.

Spice implements the Flight SQL protocol, enabling querying of the datasets configured in Spice via tools that support connecting via one of the Arrow Flight SQL drivers, such as DBeaver, Tableau, or Power BI.

arrow flight and spice

Authentication​

API Key authentication is supported for the Arrow Flight SQL endpoint. For more details, see API Key Authentication.

Correlation IDs​

Send a spice-trace-id gRPC metadata entry with a 32-character hexadecimal ID to set the trace ID for a query. The runtime records it in the trace_id column of runtime.task_history, and returns the query's trace ID in spice-trace-id response metadata.

With ADBC, set the header through the adbc.flight.sql.rpc.call_header. option prefix:

import adbc_driver_flightsql.dbapi as flightsql

conn = flightsql.connect("grpc://localhost:50051", db_kwargs={
    "adbc.flight.sql.rpc.call_header.spice-trace-id": "7c7c7c7c7c7c7c7c7c7c7c7c7c7c7c7c",
})
cur = conn.cursor()
cur.execute("SELECT count(*) FROM taxi_trips")
print(cur.fetchall())

With PyArrow Flight, pass it as a call header:

import pyarrow.flight as flight

client = flight.FlightClient("grpc://localhost:50051")
options = flight.FlightCallOptions(headers=[(b"spice-trace-id", b"5a5a5a5a5a5a5a5a5a5a5a5a5a5a5a5a")])
info = client.get_flight_info(flight.FlightDescriptor.for_command(b"SELECT count(*) FROM taxi_trips"), options)
print(client.do_get(info.endpoints[0].ticket, options).read_all())

Short queries​

For point lookups and other small Flight SQL responses, fixed per-query costs (planning, admission, and the network round trip) make up more of the total time than they do for large scans.

  • Prepared statements, for a lookup that runs often with different parameter values. PREPARE the statement, then EXECUTE it with bound parameters (prepared statements). Preparing saves parsing and planning work on each call, not queueing: EXECUTE still takes a max_concurrent_queries slot. Repeated SQL text also hits the logical plan cache.
  • A low target_partitions for a lookup that does not scan in parallel. Confirm the plan with EXPLAIN.

JDBC, ODBC, and ADBC clients connect to Spice over Flight SQL. Set the connection pool size to the sum of max_concurrent_queries across the Spice replicas behind the load balancer, divided by the number of application instances that share them — see Client connection pools.

Long DoGet streams​

runtime.query.timeout applies to Flight SQL for the whole query, including result streaming. If a query times out while receiving results, the DoGet stream ends with an error. Acceleration refreshes are exempt from that timeout.