Skip to main content
Version: Python

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 Deephaven tables from the global query scope as named Tables that can be accessed and queried via the default Flight SQL catalog.

note

SELECT queries are supported; INSERT, UPDATE, and DELETE queries are not currently supported.

While Flight SQL may be an easy jumping-off point, the Flight SQL APIs are not Live Dataframe APIs. If you need to receive real-time updates, the Deephaven-native clients are the best option.

Examples​

Each of the following examples demonstrates:

  1. Connecting to an insecure Deephaven Flight SQL server on localhost with port 10000.
  2. Using Anonymous authentication.
  3. Executing the SQL command SELECT 42 as Foo.
  4. Printing out the results.
note

Additional configuration options for TLS and authentication will likely be needed when connecting Flight SQL clients to production servers.

If the Flight SQL client expects to use cookies for authentication, the x-deephaven-auth-cookie-request header must be set to true.

ADBC Flight SQL​

Arrow Database Connectivity (ADBC) is an Arrow-first interface for efficiently fetching large datasets from a database. It's a great choice when paired with the Deephaven Flight SQL server given that the Flight protocol itself is Arrow-first.

There are a variety of ADBC client implementations that have Flight SQL drivers.

ADBC Python​

The ADBC Python library has a Flight SQL driver, and is simple to use from Python. Installation is as simple as pip install adbc-driver-flightsql pyarrow.

import adbc_driver_flightsql.dbapi
from adbc_driver_flightsql import DatabaseOptions

with adbc_driver_flightsql.dbapi.connect(
    "grpc://localhost:10000",
    db_kwargs={
        DatabaseOptions.AUTHORIZATION_HEADER.value: "Anonymous",
    },
) as conn:
    with conn.cursor() as cursor:
        cursor.execute("SELECT 42 as Foo")
        pa_table = cursor.fetch_arrow_table()
        print(pa_table)

ADBC Java​

The ADBC Java library has a Flight SQL driver, and is simple to use from Java. Installation requires a dependency on org.apache.arrow.adbc:adbc-driver-flight-sql.

Map<String, Object> options = new HashMap<>();
AdbcDriver.PARAM_URI.set(options, "grpc://localhost:10000");
FlightSqlConnectionProperties.WITH_COOKIE_MIDDLEWARE.set(options, true);
options.put(FlightSqlConnectionProperties.RPC_CALL_HEADER_PREFIX + "Authorization", "Anonymous");
options.put(FlightSqlConnectionProperties.RPC_CALL_HEADER_PREFIX + "x-deephaven-auth-cookie-request", "true");
try (
        BufferAllocator allocator = new RootAllocator();
        AdbcDatabase database = new FlightSqlDriverFactory().getDriver(allocator).open(options);
        AdbcConnection connection = database.connect()) {
    try (AdbcStatement statement = connection.createStatement()) {
        statement.setSqlQuery("SELECT 42 as Foo");
        try (QueryResult queryResult = statement.executeQuery()) {
            ArrowReader reader = queryResult.getReader();
            VectorSchemaRoot vectorRoot = reader.getVectorSchemaRoot();
            while (reader.loadNextBatch()) {
                System.out.println(vectorRoot.contentToTSVString());
            }
        }
    }
}

Other ADBC clients​

Any ADBC client that has a Flight SQL driver should work with Deephaven Flight SQL:

Flight SQL JDBC Driver​

The Flight SQL JDBC Driver can be used directly from Java, or from other applications that support Java Database Connectivity (JDBC). When already working in the context of JDBC, the Flight SQL JDBC Driver can be used to access the Deephaven Flight SQL server. When the client is not required to use JDBC, it is advisable to use ADBC or the Flight SQL client directly.

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​

The Flight SQL Client can be used directly from Java. This is a good choice when the user needs explicit control over Flight SQL RPCs or needs direct access to Flight APIs.

FlightClient client;
{
    client = FlightClient.builder()
        .allocator(new RootAllocator())
        .location(Location.forGrpcInsecure("localhost", 10000))
        .intercept(new ClientCookieMiddleware.Factory())
        .build();
    {
        CallHeaders headers = new FlightCallHeaders();
        headers.insert("Authorization", "Anonymous");
        headers.insert("x-deephaven-auth-cookie-request", "true");
        client.handshake(new HeaderCallOption(headers));
    }
}
try (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());
        }
    }
}

Tips​

Try experimenting with other queries once you have these simple examples running. For example, create some named Deephaven tables on the server via 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"])
)

And execute the Flight SQL commands:

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

What's next​

Open Database Connectivity (ODBC) support via Flight SQL is coming soon.