What
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.
Why
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.
How
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.jarfor 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/liband 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/"
Example:
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)}{0.0.0.0:8765}
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
Testing
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'.
SQLExecDirect:
In: hstmt = 0x0000000004261AE0, szSqlStr = "use Demo", cbSqlStr = -3
Return: SQL_SUCCESS=0
SQLTables:
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
Return: SQL_SUCCESS=0
Get Data All:
"TABLE_CAT", "TABLE_SCHEM", "TABLE_NAME", "TABLE_TYPE", "REMARKS"
"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.
SQLExecDirect:
In: hstmt = 0x0000000004261AE0, szSqlStr = "select top 5 * from Orders", cbSqlStr = -3
Return: SQL_SUCCESS=0
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.
Miscellaneous
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:
ARG AVATICA_VERSION="1.23.0"
FROM apache/calcite-avatica:$AVATICA_VERSION
MAINTAINER Apache Avatica <dev@calcite.apache.org>
ARG HSQLDB_VERSION="2.4.1"
# Dependencies
ADD https://repo1.maven.org/maven2/net/hydromatic/scott-data-hsqldb/0.1/scott-data-hsqldb-0.1.jar /home/avatica/classpath/
ADD https://repo1.maven.org/maven2/org/hsqldb/hsqldb/${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.