Example showing how to provide "flight call options" when making a connection against a (quackpy) Flight SQL server

[mskyttner]

Trying to figure out how to use adbi to get a connection to a Flight SQL server and while able to successfully get a connection against Voltron's sqlflite server - reproducable w containers here, I am stuck on authenticating against another containerized server quackpy.

From python the following can be used to establish a connection against the quackpy Flight SQL server:

from pyarrow.flight import FlightClient, Ticket, FlightCallOptions 
sql="""SELECT version()"""  
flight_ticket = Ticket(sql)

token = (b"authorization", bytes(f"user:persistence".encode('utf-8')))
options = FlightCallOptions(headers=[token])
client = FlightClient(f"grpc://localhost:8815")
reader = client.do_get(flight_ticket, options)
arrow_table = reader.read_all()

From R, using adbi, how do I provide the equivalent "FlightCallOptions" when initiating the connection?

Attempting to provide authentication details through the uri (I might be doing this wrong obviously) I get a server response like below:

./fly.R grpc://user:persistence@localhost:8815/ "from lineitem limit 5;"
Registered S3 methods overwritten by 'adbi':
  method                      from
  infer_nanoarrow_schema.AsIs nanoarrow
  infer_nanoarrow_schema.list nanoarrow
Error in force(code) :
  IO: [FlightSQL] write tcp 127.0.0.1:52716->127.0.0.1:8815: write: broken pipe (Unavailable; AuthenticateBasicToken)
Calls: <Anonymous> ... <Anonymous> -> with_adbc -> force -> stop_for_error
Execution halted

[krlmlr]

Thanks. The adbi package is mostly a wrapper around adbcdrivermanager. Can you establish a connection from there?

Copying @paleolimbot.

[paleolimbot]

I wonder if you are mixing up Flight and FlightSQL here? I think FlightSQL uses a different mechanism...if you need Flight specifically I think the arrow package has an option: https://arrow.apache.org/docs/r/reference/index.html#flight

[mskyttner]

I understand that Arrow Flight provides the foundational transport layer, while Flight SQL builds upon it to offer a specialized interface for SQL database interactions.

I can establish a connection against one of the Flight SQL servers - see reproducible source here - but it looks like I'm stuck perhaps on properly providing the necessary authentication options against the other one - see reproducible source here.

I am using the adbi mechanism in both cases and I understand that adbcdrivermanager is a dependency as well as the adbcflightsql package.

[paleolimbot]

I am still not sure that quackpy provides FlightSQL (and so even if you are able to connect, I am not sure you'll be able to do anything).

I wonder if https://github.com/voltrondata/flight-duckdb-example is a way to make this work?

[mskyttner]

The quackpy claims to support FlightSQL at https://github.com/quackscience/quackflight?tab=readme-ov-file#-take-python-flights and also provides a python example showing usage. It works and looks like this:

from pyarrow.flight import FlightClient, Ticket, FlightCallOptions 
import json
import pandas
import tabulate

sql="""SELECT version()"""
  
flight_ticket = Ticket(sql)

token = (b"authorization", bytes(f"user:persistence".encode('utf-8')))
options = FlightCallOptions(headers=[token])
client = FlightClient(f"grpc://localhost:8815")

reader = client.do_get(flight_ticket, options)
arrow_table = reader.read_all()

I am struggling though to do the same thing using R when using adbi + friends (using an approach which reproducibly works against the sqlflite server).

I attempted R code like this, which I thought would be equivalent:

b64creds <- 
	charToRaw(paste0(u$username, ":", u$password)) |> 
	base64enc::base64encode()

header <- paste("authorization", b64creds)

con <- adbi::dbConnect(
	drv = adbi::adbi("adbcflightsql"), 
	uri = adbi_uri,
	# see https://arrow.apache.org/adbc/current/driver/flight_sql.html
	adbc.flight.sql.authorization_header = header
	) |> suppressMessages()

on.exit(DBI::dbDisconnect(con))

# print and exit
con |> DBI::dbGetQuery(sql) |> tibble::as_tibble()

I was thinking maybe I send the autentication details incorrectly, but couldn't find a good example documenting how to do it? Or perhaps the quackpy isn't "fully compliant"? But it works from python, so I was thinking it should work from R too.