Using Apache Avatica with Virtuoso


Apache Avatica is a framework for building JDBC and ODBC drivers for databases, and an RPC wire protocol. Avatica’s Java binding has very few dependencies, and depends only on JDK 8+ and Jackson.


As a Relational database with available JDBC and ODBC drivers, Virtuoso can be used as the backend storage layer for Apache Avatica, as can other relational databases.


The simplest way is using the Apache Avatica Docker container image on machine with docker installed, as follows:

  • Locate the Virtuoso JDBC to be user for connecting to Virtuoso ie . /opt/virtuoso/lib/virtjdbc4_3.jar for a default Linux installation.
  • Pull the Apache Avatica docker container image:
  docker pull apache/calcite-avatica:1.23.0
  • Run the following Docker command to start the Apache Avatica server with Virtuoso as backend database, only having to set the location of the Virtuoso JDBC Driver ie /opt/virtuoso/lib and ensuring a Virtuoso instance is accessible on the JDBC connect string URL ie "jdbc:virtuoso://localhost:1111/UID=dba/PWD=dba/" :
docker run --rm -p 8765:8765 \
    -v /opt/virtuoso/lib/:/mydb-jars --entrypoint="" -it apache/calcite-avatica:1.23.0 \
    /usr/bin/java -cp "/home/avatica/classpath/*:/mydb-jars/*" \
    org.apache.calcite.avatica.standalone.StandaloneServer -p 8765 \
    -u "jdbc:virtuoso://localhost:1111/UID=dba/PWD=dba/"   


ubuntu@ip-172-30-0-77:~/docker/avatica-virtuoso$ sudo docker run --rm -p 8765:8765     -v /opt/virtuoso/lib/:/mydb-jars --entrypoint="" -it apache/calcite-avatica:1.23.0     /usr/bin/java -cp "/home/avatica/classpath/*:/mydb-jars/*"     org.apache.calcite.avatica.standalone.StandaloneServer -p 8765     -u "jdbc:virtuoso://localhost:1111/UID=dba/PWD=dba/"
Not configuring Avatica authentication
2023-07-18T11:59:10,194 [main] INFO  metrics.MetricsSystemLoader - No metrics implementation available on classpath. Using No-op implementation
2023-07-18T11:59:10,207 [main] INFO  util.log - Logging initialized @831ms to org.eclipse.jetty.util.log.Slf4jLog
2023-07-18T11:59:10,493 [main] INFO  server.Server - jetty-9.4.44.v20210927; built: 2021-09-27T23:02:44.612Z; git: 8da83308eeca865e495e53ef315a249d63ba9332; jvm 1.8.0_212-b04
2023-07-18T11:59:10,539 [main] INFO  server.AbstractConnector - Started ServerConnector@3b6ddd1d{HTTP/1.1, (http/1.1)}{}
2023-07-18T11:59:10,539 [main] INFO  server.Server - Started @1164ms
2023-07-18T11:59:10,544 [main] INFO  server.HttpServer - Service listening on port 8765.
2023-07-18T11:59:10,546 [main] INFO  standalone.StandaloneServer - Started Avatica server on port 8765 with serialization PROTOBUF


Apache Avatica does not have an ODBC driver but does have a JDBC Driver, to which a connection can readily be made with the OpenLink ODBC-JDBC Bridge driver and test connection made with the ODBCTest (Gator) tool on Windows or other ODBC application:

	Successfully connected to DSN 'jdbclu-avatica'.
In: hstmt = 0x0000000004261AE0, szSqlStr = "use Demo", cbSqlStr = -3
In: StatementHandle = 0x0000000004261AE0, CatalogName = "Demo", NameLength1 = 4, SchemaName = SQL_NULL_HANDLE, NameLength2 = 0, 
TableName = SQL_NULL_HANDLE, NameLength3 = 0, TableType = SQL_NULL_HANDLE, NameLength4 = 0

Get Data All:
"Demo", "demo", "Artist", "TABLE", <Null>
"Demo", "demo", "Categories", "TABLE", <Null>
"Demo", "demo", "Countries", "TABLE", <Null>
"Demo", "demo", "Customers", "TABLE", <Null>
"Demo", "demo", "Employees", "TABLE", <Null>
"Demo", "demo", "Flags", "TABLE", <Null>
"Demo", "demo", "Order_Details", "TABLE", <Null>
"Demo", "demo", "Orders", "TABLE", <Null>

"Demo", "demo", "OrderCategoryPeriod", "VIEW", <Null>
"Demo", "demo", "OrdersPeriods", "VIEW", <Null>
19 rows fetched from 5 columns.

In: hstmt = 0x0000000004261AE0, szSqlStr = "select top 5 * from Orders", cbSqlStr = -3

Get Data All:
"OrderID", "CustomerID", "EmployeeID", "OrderDate", "RequiredDate", "ShippedDate", "ShipVia", "Freight", "ShipName", "ShipAddress", "ShipCity", "ShipRegion", "ShipPostalCode", "ShipCountry", "ShipCountryCode"
10248, "VINET", 5, 1994-08-04 00:00:00, 1994-09-01, 1994-08-16 00:00:00, 3, 32.380000000000003, "Vins et alcools Chevalier", "59 rue de l'Abbaye", "Reims", <Null>, "51100", "France", "fr"
10249, "TOMSP", 6, 1994-08-05 00:00:00, 1994-09-16, 1994-08-10 00:00:00, 1, 11.609999999999999, "Toms Spezialitäten", "Luisenstr. 48", "Münster", <Null>, "44087", "Germany", "gm"
10250, "HANAR", 4, 1994-08-08 00:00:00, 1994-09-05, 1994-08-12 00:00:00, 2, 65.829999999999998, "Hanari Carnes", "Rua do Paço, 67", "Rio de Janeiro", "RJ", "05454-876", "Brazil", "br"
10251, "VICTE", 3, 1994-08-08 00:00:00, 1994-09-05, 1994-08-15 00:00:00, 1, 41.340000000000003, "Victuailles en stock", "2, rue du Commerce", "Lyon", <Null>, "69004", "France", "fr"
10252, "SUPRD", 4, 1994-08-09 00:00:00, 1994-09-06, 1994-08-11 00:00:00, 2, 51.299999999999997, "Suprêmes délices", "Boulevard Tirou, 255", "Charleroi", <Null>, "B-6000", "Belgium", "be"
5 rows fetched from 15 columns.


Note Apache Avatica docker container can be start running against HyperSQL which seems to be the default backend database it uses, with the commands:

docker build -f Dockerfile.hypersql -t avatica-hsqldb-server .
  • command for run Docker
docker run --rm -p 8765:8765 -it avatica-hsqldb-server

where Dockerfile.hypersql contains:


FROM apache/calcite-avatica:$AVATICA_VERSION
MAINTAINER Apache Avatica <>


# Dependencies
ADD /home/avatica/classpath/
ADD${HSQLDB_VERSION}/hsqldb-${HSQLDB_VERSION}.jar /home/avatica/classpath/

# Add on to avatica-server's entrypoint
CMD ["-u", "jdbc:hsqldb:res:scott"]

and the same connection attributes used to connect.