Connecting to a Deephaven Flight SQL server

Flight SQL is a protocol on top of Arrow Flight that exposes SQL-like capabilities.

The Deephaven Flight SQL server exposes each table in the server's global query scope as a named table. A Flight SQL query refers to a table by its variable name, as in SELECT * FROM my_table. The Flight SQL server is a built-in part of the Deephaven server and listens on the same port as the rest of the server, which is 10000 by default.

Note

The Deephaven Flight SQL server supports SELECT queries. It doesn't support INSERT, UPDATE, or DELETE queries.

Flight SQL queries return static snapshots rather than live, ticking tables. If you need to receive real-time updates, use a Deephaven client API that supports ticking tables, such as the Java, JavaScript, or C++ client, or the Python client with the pydeephaven-ticking package.

Authenticate

The Deephaven server uses pre-shared key (PSK) authentication by default. If you don't set the authentication.psk property, the server generates a random PSK at startup and prints it to the server log.

Every example except the Flight SQL JDBC Driver example uses the PSK deephaven. The JDBC example uses anonymous authentication. To match the PSK examples, do one of the following:

Start the server with -Dauthentication.psk=deephaven.
Replace deephaven in the examples with the key from the server log.

To use anonymous authentication instead, start the server with -DAuthHandlers=io.deephaven.auth.AnonymousAuthenticationHandler. This replaces PSK authentication. To run every example on this page against one server, enable both methods: -DAuthHandlers=io.deephaven.auth.AnonymousAuthenticationHandler,io.deephaven.authentication.psk.PskAuthenticationHandler -Dauthentication.psk=deephaven.

Each example passes the authentication method differently:

The ADBC (Arrow Database Connectivity) examples send it in an authorization header, which they set with the adbc.flight.sql.authorization_header option.
The Flight SQL client (Java) example sends it in an Authorization header.
The Flight SQL JDBC Driver example sets it with the Authorization URL parameter.
The Python Flight SQL client example sets it with the client's token argument. That client adds the Bearer prefix itself, so pass the rest of the PSK header value without it: io.deephaven.authentication.psk.PskAuthenticationHandler deephaven.

The following table lists the authorization header value for each authentication method:

Authentication method	Authorization header value
PSK	`Bearer io.deephaven.authentication.psk.PskAuthenticationHandler deephaven`
Anonymous	`Anonymous`

Every example except ADBC Python also sets the x-deephaven-auth-cookie-request header to true. This header asks the server to return the session token as a cookie. The client then sends that cookie with each later request. Each example handles the cookie differently:

The ADBC Java example sets the adbc.flight.sql.rpc.with_cookie_middleware option.
The Flight SQL JDBC Driver handles cookies itself, so its example only sets the header with the x-deephaven-auth-cookie-request=true URL parameter.
The Flight SQL client (Java) example uses ClientCookieMiddleware.
The Python Flight SQL client example defines a CookieMiddleware class.

Some clients, such as the Flight SQL JDBC Driver, don't keep the session without the cookie request header. If your client fails to reuse its session after authenticating, set this header.

Connect with a client

Each example in this section connects to an insecure (without TLS) Deephaven Flight SQL server on localhost with port 10000, runs the SQL query SELECT 42 as Foo, and prints the results.

Note

Production servers typically require TLS and additional authentication configuration, so adjust the connection settings in these examples to match your server.

The Java examples use Apache Arrow. Run their JVM with --add-opens=java.base/java.nio=ALL-UNNAMED, which Arrow needs on Java 16 and later.

Choose a client:

ADBC is the recommended choice for new code in Python, Java, and other languages.
The Flight SQL JDBC Driver is for applications that require JDBC.
The Flight SQL client (Java) gives explicit control over Flight SQL RPCs.
The Python Flight SQL client is archived. Use it only if existing code already depends on it.

ADBC

Arrow Database Connectivity (ADBC) is a database interface that returns query results in the Arrow columnar format, which makes it efficient for fetching large datasets. It pairs well with the Deephaven Flight SQL server because Flight also transfers data in the Arrow format, so results don't need conversion.

The recommended way to get the Flight SQL driver is to install it with the dbc command-line tool:

dbc install flightsql

This installs the driver's shared library and its driver manifest to a location where an ADBC client library can find the driver by the name flightsql. Any language with an ADBC client library, also called a driver manager, can load the driver.

ADBC Python

The ADBC Python library (driver manager) loads the flightsql driver installed with dbc install flightsql. The library implements Python's standard database interface, DB API 2. Its DB API module needs either pyarrow or polars. To install the library with pyarrow, which this example uses, run:

pip install adbc-driver-manager pyarrow

from adbc_driver_manager import dbapi

with dbapi.connect(
    driver="flightsql",
    db_kwargs={
        "uri": "grpc://localhost:10000",
        "adbc.flight.sql.authorization_header": "Bearer io.deephaven.authentication.psk.PskAuthenticationHandler deephaven",
    },
) as connection:
    with connection.cursor() as cursor:
        cursor.execute("SELECT 42 as Foo")
        pa_table = cursor.fetch_arrow_table()
        print(pa_table)

This example doesn't set the x-deephaven-auth-cookie-request header. If the connection fails to reuse its session after authenticating, add the same two options that the ADBC Java example uses to the db_kwargs dictionary:

"adbc.flight.sql.rpc.with_cookie_middleware": "true"
"adbc.flight.sql.rpc.call_header.x-deephaven-auth-cookie-request": "true"

ADBC Java

The ADBC Java library (driver manager) loads the flightsql driver installed with dbc install flightsql through the Java Native Interface (JNI). Installation requires a dependency on org.apache.arrow.adbc:adbc-driver-jni.

final String DRIVER_FACTORY = "org.apache.arrow.adbc.driver.jni.JniDriverFactory";

Map<String, Object> params = new HashMap<>();
JniDriver.PARAM_DRIVER.set(params, "flightsql");
params.put("uri", "grpc://localhost:10000");
params.put(
        "adbc.flight.sql.authorization_header",
        "Bearer io.deephaven.authentication.psk.PskAuthenticationHandler deephaven");
params.put("adbc.flight.sql.rpc.with_cookie_middleware", "true");
params.put("adbc.flight.sql.rpc.call_header.x-deephaven-auth-cookie-request", "true");
try (
        BufferAllocator allocator = new RootAllocator();
        AdbcDatabase database =
                AdbcDriverManager.getInstance().connect(DRIVER_FACTORY, allocator, params);
        AdbcConnection connection = database.connect()) {
    try (AdbcStatement statement = connection.createStatement()) {
        statement.setSqlQuery("SELECT 42 as Foo");
        try (AdbcStatement.QueryResult queryResult = statement.executeQuery()) {
            ArrowReader reader = queryResult.getReader();
            while (reader.loadNextBatch()) {
                System.out.println(reader.getVectorSchemaRoot().contentToTSVString());
            }
        }
    }
}

Other ADBC clients

Other ADBC client libraries also work with Deephaven Flight SQL:

ADBC C / C++ (see its driver manager)
ADBC C# / .NET
ADBC Go (see its driver manager)
ADBC JavaScript / TypeScript (see its driver manager)
ADBC R
ADBC Ruby
ADBC Rust (see its driver manager)

Flight SQL JDBC Driver

Use the Flight SQL JDBC Driver to connect to the Deephaven Flight SQL server from Java or from any application that supports Java Database Connectivity (JDBC). If your client doesn't need JDBC, use ADBC or the Flight SQL client (Java) instead. To use the driver, add a dependency on org.apache.arrow:flight-sql-jdbc-driver.

This example uses anonymous authentication. The Authorization=Anonymous URL parameter selects it. To run it, start the server with anonymous authentication enabled, as Authenticate describes.

String url = "jdbc:arrow-flight-sql://localhost:10000?useEncryption=0&Authorization=Anonymous&x-deephaven-auth-cookie-request=true";
try (Connection connection = DriverManager.getConnection(url)) {
    try (
        Statement statement = connection.createStatement();
        ResultSet resultSet = statement.executeQuery("SELECT 42 as Foo")) {
        while (resultSet.next()) {
            System.out.println(resultSet.getLong("Foo"));
        }
    }
}

Flight SQL client (Java)

Use the Flight SQL client directly from Java when you need explicit control over Flight SQL RPCs or direct access to Flight APIs. To use the client, add a dependency on org.apache.arrow:flight-sql.

try (
        BufferAllocator allocator = new RootAllocator();
        FlightClient client = FlightClient.builder()
                .allocator(allocator)
                .location(Location.forGrpcInsecure("localhost", 10000))
                .intercept(new ClientCookieMiddleware.Factory())
                .build()) {
    CallHeaders headers = new FlightCallHeaders();
    headers.insert(
            "Authorization",
            "Bearer io.deephaven.authentication.psk.PskAuthenticationHandler deephaven");
    headers.insert("x-deephaven-auth-cookie-request", "true");
    client.handshake(new HeaderCallOption(headers));

    FlightSqlClient flightSqlClient = new FlightSqlClient(client);
    FlightInfo info = flightSqlClient.execute("SELECT 42 as Foo");
    try (FlightStream stream = flightSqlClient.getStream(info.getEndpoints().get(0).getTicket())) {
        while (stream.next()) {
            System.out.println(stream.getRoot().contentToTSVString());
        }
    }
}

Python Flight SQL client

The Python Flight SQL client offers both a DB API 2 interface and an Arrow Flight interface for interacting with Flight SQL. The flightsql-dbapi project is archived and no longer maintained, so prefer the ADBC Python client for new code. Use this client only if your existing code already depends on it.

This example authenticates with the PSK through the client's token argument. To install the client, run:

pip install flightsql-dbapi

import pyarrow.flight as flight
from flightsql import FlightSQLClient


class CookieMiddleware(flight.ClientMiddleware):
    """Middleware to handle cookie-based authentication."""

    def __init__(self):
        self.cookie_store = None  # Store the session cookie

    def sending_headers(self):
        """Attach cookie to outgoing requests."""
        headers = {}
        if self.cookie_store:
            headers["cookie"] = self.cookie_store
        return headers

    def received_headers(self, headers):
        """Capture and store the Set-Cookie header from the server response."""
        if "set-cookie" in headers:
            self.cookie_store = headers["set-cookie"]


class CookieMiddlewareFactory(flight.ClientMiddlewareFactory):
    """Factory that returns one shared middleware instance so the session cookie persists across calls."""

    def __init__(self):
        self.middleware = CookieMiddleware()

    def start_call(self, info):
        """Returns the same middleware instance for all calls to maintain cookies."""
        return self.middleware


middleware_factory = CookieMiddlewareFactory()

# Create the client with the middleware
client = FlightSQLClient(
    host="localhost",
    port=10000,
    insecure=True,
    token="io.deephaven.authentication.psk.PskAuthenticationHandler deephaven",
    metadata={"x-deephaven-auth-cookie-request": "true"},
    middleware=[middleware_factory],
)

flight_info = client.execute("SELECT 42 as Foo")

ticket = flight_info.endpoints[0].ticket

reader = client.do_get(ticket)

pa_table = reader.read_all()
print(pa_table)
client.close()

Query your own tables

Once one of the examples connects to your server, use the same client to query your own Deephaven tables. For example, create some named Deephaven tables on the server in the web UI:

from deephaven import time_table

my_table_1 = (
    time_table("PT1s").view(["Timestamp1 = Timestamp", "Id = ii % 11"]).last_by(["Id"])
)
my_table_2 = (
    time_table("PT5s").view(["Timestamp2 = Timestamp", "Id = ii % 11"]).last_by(["Id"])
)

Then replace SELECT 42 as Foo in your client example with one of these Flight SQL queries. Because these tables tick, each query returns a snapshot of their rows at the time it runs:

SELECT * FROM my_table_1

SELECT
  *
FROM
  my_table_2
ORDER BY
  Timestamp2

SELECT
  my_table_1.Id,
  my_table_1.Timestamp1,
  my_table_2.Timestamp2
FROM
  my_table_1
  INNER JOIN my_table_2 ON my_table_1.Id = my_table_2.Id