Access Through Arrow Flight SQL¶

Virtual DataPort supports the Arrow Flight SQL protocol, allowing connections from client applications interacting with this protocol independently of its underlying programming language.

Arrow Flight SQL is a protocol designed for high-performance, distributed database query execution using Apache Arrow’s columnar data format. It provides a standard interface, similar to ODBC or JDBC, for applications to interact with SQL databases. By leveraging Arrow’s in-memory format and the Flight RPC framework, it enables efficient data transport and processing, particularly in big data and cloud environments. It supports a variety of platforms and tools, ensuring compatibility across different systems and enhancing query speed and scalability.

Access Using Flight SQL JDBC driver¶

JDBC (Java Database Connectivity) is a Java API that allows executing statements on relational databases, regardless of the DBMS. The Flight SQL JDBC driver is a JDBC implementation that uses the Flight SQL protocol and can interact with any database supporting Flight SQL.

Get the Flight SQL JDBC driver from your installation of the Denodo Platform (<DENODO_HOME>/tools/client-drivers/jdbc/denodo-flight-sql-jdbc-driver.jar).

Java Version

The driver is compatible with JDK 8+. On JDK 9+, the following JVM parameter is required:

--add-opens=java.base/java.nio=ALL-UNNAMED

Driver Class Name

The class name of this driver is this:

org.apache.arrow.driver.jdbc.ArrowFlightJdbcDriver

Connection URL

Syntax of the database URL:

Syntax of the JDBC connection URL¶

jdbc:arrow-flight-sql://<hostname>[:<port>][?<paramName>=<paramValue>[&<paramName>=<paramValue>]*]

The URI scheme must be jdbc:arrow-flight-sql://.
<hostname>: hostname of the Virtual DataPort server
<port>: default port: 9994.

For example:

JDBC connection URL sample¶

jdbc:arrow-flight-sql://denodo-server.acme.com:9994?useEncryption=false&catalog=acme_db

Note

The values of the parameter must be URL-encoded if they contain characters such as !, @, $, etc. For example, if you are connecting to a database that has non-ASCII characters, encode the value of the parameter catalog.

Any URI parameters that are not handled by the driver are passed to the Flight SQL service as gRPC headers.

Connection Parameters¶

Additional options can be passed as query parameters. Parameter names are case-sensitive. The supported parameters are:

Parameters of the Flight SQL JDBC driver and their default value¶
Parameter of the URL	Description
autoCommit	If `true`, the invocations to the methods of the Flight SQL protocol of managing transactions are ignored. I.e. the driver ignores the invocations to the methods `setAutoCommit(...)`, `commit()` and `rollback()`. This is useful to make sure that an application does not start transactions inadvertently. Even with this parameter set to true, an application can start and finish transactions by executing the statements `BEGIN`, `COMMIT` and `ROLLBACK`. Take into account the limits on the duration of a transaction: By default, transactions cannot last for more than 30 minutes. Once the execution of a statement finishes, the client has to execute another statement in less than 30 seconds. See more about the limits on the duration of transaction in the section Transactions in Virtual DataPort of the VQL Guide. Default value: `true`.
catalog	Database against which the user is logged in. Default value: `N/A`.
database	Database against which the user is logged in. It is recommended to use the `catalog` parameter because it is directly defined as a known parameter in the JDBC Flight SQL driver. However, if you are using LDAP authentication at database level (not global server LDAP configuration), the parameter that must be used is `database`. Default value: `N/A`.
disableCertificateVerification	When TLS is enabled, whether to verify the server certificate. Default value: `false`.
password	The password for user/password authentication. Default value: `N/A`.
queryTimeout	Maximum time (in milliseconds) the execution of a query can take. After this period, it will throw an error. This parameter is optional. If it is not set, the query timeout has the default value (900000 milliseconds). If 0, the query does not have a timeout. This parameter sets the default timeout for all the queries. In addition, you can change the timeout for a single query by adding the parameter ‘QUERYTIMEOUT’ = ‘<value>’ to the CONTEXT clause of the query. See more about this in the section CONTEXT Clause of the VQL Guide. Default value: `900000` milliseconds (15 minutes)
timePrecision	Precision used for columns with time type. Valid values are: `milliseconds`, `microseconds` and `nanoseconds`. Default value: `microseconds`.
timeStampPrecision	Precision used for columns with timestamp and timestamptz types. Valid values are: `milliseconds`, `microseconds` and `nanoseconds`. Default value: `microseconds`.
trustStore	When TLS is enabled, the path to the certificate store. If not provided, the default Java truststore is used (usually `$JAVA_HOME/lib/security /cacerts`) and the trustStorePassword parameter is ignored. Default value: `N/A`.
trustStorePassword	Password to the certificate truststore. Default value: `N/A`.
useEncryption	Wheter to use TLS (the default is an encrypted connection). Default value: `true`.
user	The username for user/password authentication. Default value: `N/A`.
userAgent	Sets the user agent of the connection. The section Setting the User Agent of an Application of the Administration Guide explains why we recommend setting this parameter. Default value: `N/A`.
useSystemTrustStore	When TLS is enabled, whether to use the system certificate store. Default value: `true`
workspace	The workspace name to connect. Default value: `N/A`.

Access Using Flight SQL Python Driver¶

Python Flight SQL driver allows to connect with Virtual DataPort from native Python applications.

This driver implements the specifications PEP 249 – Python Database API Specification v2.0, which is an API for accessing to databases, and the ADBC: Arrow Database Connectivity, a set of APIs for accessing to Arrow-native databases.

Requirements¶

The following Python packages are required:

adbc-driver-flightsql
pyarrow

Connection Parameters¶

The supported parameters are:

Parameters of the Python ABDC driver and their default value¶
Parameter	Description
adbc.flight.sql.client_option.tls_root_certs	Override the root certificates used to validate the server’s TLS certificate. Default value: `N/A`.
adbc.flight.sql.client_option.tls_skip_verify	Disable verification of the server’s TLS certificate. Default value: `false`.
adbc.flight.sql.rpc.call_header.autoCommit	If `true`, the invocations to the methods of the Flight SQL protocol of managing transactions are ignored. This is useful to make sure that an application does not start transactions inadvertently. Even with this parameter set to true, an application can start and finish transactions by executing the statements `BEGIN`, `COMMIT` and `ROLLBACK`. Take into account the limits on the duration of a transaction: By default, transactions cannot last for more than 30 minutes. Once the execution of a statement finishes, the client has to execute another statement in less than 30 seconds. See more about the limits on the duration of transaction in the section Transactions in Virtual DataPort of the VQL Guide. Default value: `true`.
adbc.flight.sql.rpc.call_header.database	Database against which the user is logged in. Default value: `N/A`.
adbc.flight.sql.rpc.call_header.queryTimeout	Maximum time (in milliseconds) the execution of a query can take. After this period, it will throw an error. This parameter is optional. If it is not set, the query timeout has the default value (900000 milliseconds). If 0, the query does not have a timeout. This parameter sets the default timeout for all the queries. In addition, you can change the timeout for a single query by adding the parameter ‘QUERYTIMEOUT’ = ‘<value>’ to the CONTEXT clause of the query. See more about this in the section CONTEXT Clause of the VQL Guide. Default value: `900000` milliseconds (15 minutes)
adbc.flight.sql.rpc.call_header.timePrecision	Precision used for columns with timestamp and timestamptz types. Valid values are: `milliseconds`, `microseconds` and `nanoseconds`. Default value: `microseconds`.
adbc.flight.sql.rpc.call_header.timeStampPrecision	Precision used for columns with time type. Valid values are: `milliseconds`, `microseconds` and `nanoseconds`. Default value: `microseconds`.
adbc.flight.sql.rpc.call_header.workspace	The workspace name to connect. Default value: `N/A`.
adbc.flight.sql.rpc.call_header.userAgent	Sets the user agent of the connection. The section Setting the User Agent of an Application of the Administration Guide explains why we recommend setting this parameter.
password	The password for user/password authentication.
user	The username for user/password authentication.

Additional information can be found at ADBC Flight SQL Driver documentation

OAuth 2.0 Authentication Providing Access Token¶

To authenticate using a pre-obtained OAuth 2.0 Access Token, you must configure the connection credentials as follows:

User Name: Use the fixed identifier _$#AUTHTYPE_OAUTH2#$_
Password: Specify the OAuth 2.0 Access Token (Bearer Token).

Notes¶

Better performance is obtained using ADBC methods from the driver such fetchallarrow or fetch_arrow_table. Note that these methods returns results which allow conversions to pandas data frames.
We recommend to specify the parameter adbc.flight.sql.rpc.call_header.timePrecision with milliseconds value for avoiding problems with time types. The type time with microseconds precision is not supported, returning an error when introspecting a view or executing a query involving columns with that type.

Examples¶

Opening a connection.

Python connection sample¶

from adbc_driver_flightsql.dbapi import connect

def get_connection():
    return connect(
        "grpc://denodo-server.acme.com:9994",
        db_kwargs={
            "username": 'user',
            "password": 'password',
            "adbc.flight.sql.rpc.call_header.database": 'acme_db',
            "adbc.flight.sql.rpc.call_header.timePrecision": 'milliseconds',
        }
        , autocommit=True)

Opening a connection with TLS skipping certificate validation.

Python connection with TLS sample¶

from adbc_driver_flightsql.dbapi import connect

def get_connection():
    return connect(
        "grpc+tls://denodo-server.acme.com:9994",
        db_kwargs={
            "username": 'user',
            "password": 'password',
            "adbc.flight.sql.client_option.tls_skip_verify": 'true',
            "adbc.flight.sql.rpc.call_header.database": 'acme_db',
            "adbc.flight.sql.rpc.call_header.timePrecision": 'milliseconds',
        }
        , autocommit=True)

Opening a connection with TLS specifying a custom certificate.

Python connection with TLS sample¶

from adbc_driver_flightsql.dbapi import connect

def get_connection():
    cert_file = open("/<certificate-route>/my-certificate.crt", "r")
    crt = cert_file.read()
    cert_file.close()
    return connect(
        "grpc+tls://denodo-server.acme.com:9994",
        db_kwargs={
            "username": 'user',
            "password": 'password',
            "adbc.flight.sql.client_option.tls_root_certs": crt,
            "adbc.flight.sql.rpc.call_header.database": 'acme_db',
            "adbc.flight.sql.rpc.call_header.timePrecision": 'milliseconds',
        }
        , autocommit=True)

Example executing a query and converting results to a pandas data frame.

Query execution¶

with conn.cursor() as cur:
    cur.execute("SELECT * FROM VIEW")
    data = cur.fetchallarrow()
    dataframe = data.to_pandas()
    print(dataframe.head)

Example of executing a parameterized batch insert.

Parameterized batch insert¶

# Define the SQL statement with '?' placeholders
sql = "INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?)"

# Prepare the data as a list of tuples
data_to_insert = [
    ('Value 1', 10, '2024-01-01'),
    ('Value 2', 20, '2024-01-02'),
    ('Value 3', 30, '2024-01-03')
]

with conn.cursor() as cur:
    try:
        # Execute the insert for all rows in the list efficiently
        cur.executemany(sql, data_to_insert)

        # If autocommit=False, ensure you commit the transaction
        # conn.commit()

        print(f"Successfully inserted {cur.rowcount} rows.")
    except Exception as e:
        # If autocommit=False, rollback on error
        # conn.rollback()

Restrictions¶

Parameterized queries are not currently supported.

Type Mapping: Virtual DataPort to Apache Arrow¶

In our system, we map Virtual DataPort data types to Apache Arrow types based on the information provided in the field’s data type and defined subtype (if applicable). This mapping ensures that data from Virtual DataPort is appropriately represented when transported in the Apache Arrow format for efficient in-memory processing.

Mapping: Virtual DataPort Types to Arrow Types¶

Virtual DataPort Type	Source Type	Arrow Type
BLOB	BINARY	BINARY
	BLOB	BINARY
	LONGVARBINARY	BINARY
	VARBINARY	BINARY
BOOLEAN	BIT	BOOLEAN
	BOOLEAN	BOOLEAN
DATE(DEPRECATED)	TIMESTAMP_W_TZ	TIMESTAMP(MICROSECOND, TZ)
	DATE	DATE(DAY)
	TIME	TIME(MICROSECOND, 32 bits)
	TIMESTAMP	TIMESTAMP(MICROSECOND)
DECIMAL	DECIMAL	DECIMAL(256 bits)
	DOUBLE	FLOATINGPOINT(DOUBLE)
	FLOAT	FLOATINGPOINT(SINGLE)
	NUMERIC	DECIMAL(256 bits)
	REAL	FLOATINGPOINT(SINGLE)
DOUBLE	DECIMAL	DECIMAL(256 bits)
	DOUBLE	FLOATINGPOINT(DOUBLE)
	FLOAT	FLOATINGPOINT(SINGLE)
	NUMERIC	DECIMAL(256 bits)
	REAL	FLOATINGPOINT(SINGLE)
FLOAT	DECIMAL	DECIMAL(128 bits)
	DOUBLE	FLOATINGPOINT(DOUBLE)
	FLOAT	FLOATINGPOINT(SINGLE)
	NUMERIC	DECIMAL(128 bits)
	REAL	FLOATINGPOINT(SINGLE)
INT	BIGINT	INT(64 bits)
	DECIMAL	DECIMAL(128 bits)
	INTEGER	INT(32 bits)
	NUMERIC	DECIMAL(128 bits)
	SMALLINT	INT(16 bits)
	TINYINT	INT(8 bits)
INTERVALDAYSECOND	INTERVAL_DAY_TO_SECOND	INTERVAL(MONTH_DAY_NANO)
INTERVALYEARMONTH	INTERVAL_YEAR_TO_MONTH	INTERVAL(MONTH_DAY_NANO)
LOCALDATE	DATE	DATE(DAY)
LONG	BIGINT	INT(64 bits)
	DECIMAL	DECIMAL(256 bits)
	INTEGER	INT(32 bits)
	NUMERIC	DECIMAL(256 bits)
	SMALLINT	INT(16 bits)
	TINYINT	INT(8 bits)
TEXT	CHAR	UTF8
	CLOB	UTF8
	DATALINK	UTF8
	LONGVARCHAR	UTF8
	LONGNVARCHAR	UTF8
	NCHAR	UTF8
	NCLOB	UTF8
	NVARCHAR	UTF8
TIME	TIME	TIME(MICROSECOND, 32)
TIMESTAMPTZ	TIMESTAMP_W_TZ	TIMESTAMP(MICROSECOND, TZ)
TIMESTAMP	TIMESTAMP	TIMESTAMP(MICROSECOND)
VECTOR	VECTOR<DOUBLE,DIMENSION>	FIXED SIZE LIST
	VECTOR<FLOAT,DIMENSION>	FIXED SIZE LIST
	VECTOR<INT,DIMENSION>	FIXED SIZE LIST
	VECTOR<LONG,DIMENSION>	FIXED SIZE LIST
XML	CLOB	UTF8
	SQLXML	UTF8
	LONGVARCHAR	UTF8
	LONGNVARCHAR	UTF8
	VARCHAR	UTF8
	NCLOB	UTF8
	NVARCHAR	UTF8
ARRAY	ARRAY	LIST
STRUCT	STRUCT	STRUCT

Note

Mapping for types TIME, TIMESTAMPTZ and TIMESTAMP can differ if connection parameters timePrecision or timeStampPrecision are specified.

USER MANUALS

Access Through Arrow Flight SQL¶

Access Using Flight SQL JDBC driver¶

Connection Parameters¶

Access Using Flight SQL Python Driver¶

Requirements¶

Connection Parameters¶

OAuth 2.0 Authentication Providing Access Token¶

Notes¶

Examples¶

Restrictions¶

Type Mapping: Virtual DataPort to Apache Arrow¶

Mapping: Virtual DataPort Types to Arrow Types¶