Recently I wanted to test some Calcite functionality, and thought it might be nice to use a GUI instead of sqlline.
It turns out, it is:
I figured you could just use something like SquirreL, which lets you import arbitrary JDBC drivers. This was less straightforward than I thought.
The hangup was the dependencies and their recursive transitive dependencies. At first, I was manually downloading the proper versions from the Maven portal, but there turned out to be a much easier way.
All credit to this brilliant fellow for the idea: https://stackoverflow.com/a/68918147/13485494
We make a script which creates a dummy pom.xml just to download the closure of the dependency set:
Assuming we named this get-maven-deps.sh, we invoke it as:
./get-maven-deps.sh org.apache.calcite calcite-core 1.30.0
If successful, you should see:
user@MSI:~/tmp/maven-dep-downloader$ ./get-maven-deps.sh org.apache.calcite calcite-core 1.30.0
[INFO] Scanning for projects...
[INFO]
[INFO] -------------------------< com.temp.temp:temp >-------------------------
[INFO] Building temp 0.0.0
[INFO] --------------------------------[ jar ]---------------------------------
Downloading from central: https://repo.maven.apache.org/maven2/org/apache/calcite/calcite-core/1.30.0/calcite-core-1.30.0.pom
<... SNIPPED ...>
[INFO] --- maven-dependency-plugin:2.8:copy-dependencies (default-cli) @ temp ---
[INFO] Copying jsr305-3.0.2.jar to /home/user/tmp/maven-dep-downloader/libs/1.30.0/jsr305-3.0.2.jar
<... SNIPPED ...>
[INFO] Copying memory-0.9.0.jar to /home/user/tmp/maven-dep-downloader/libs/1.30.0/memory-0.9.0.jar
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 7.988 s
[INFO] Finished at: 2022-07-18T16:38:22-04:00
[INFO] ------------------------------------------------------------------------
Now we need to copy the libraries into our SQuirreL installation folder somewhere.
I use a directory called custom-drivers:
C:\squirrel-sql\custom-drivers\calcite\1.30.0
After copying the folder, start SQuirreL, and navigate to Drivers -> Create New from the left-hand side:
Fill out the information with:
- Name:
Calcite 1.30(or corresponding version) - Example URL:
jdbc:calcite:model=<pathToModelFile> - Class name:
org.apache.calcite.jdbc.Driver
And then from the Extra Class Path tab, press Add:
Navigate to the custom-drivers/calcite/1.30.0 directory, press Ctrl+A/Cmd+A to select all, and then add:
At the bottom of the screen, you should see
Driver class org.apache.calcite.jdbc.Driver successfully registered for driver definition: Calcite 1.30.0:
Now navigate to Aliases on the left-hand sidebar, and create a new one:
You'll need a model for a Calcite schema, if you have a JDBC database you can use that.
Set the URL to jdbc:calcite:model=/path/to/this/file.json where the file is a Calcite model, like the below:
{
"version": "1.0",
"defaultSchema": "test",
"schemas": [
{
"name": "test",
"type": "jdbc",
"jdbcUrl": "jdbc:postgresql://localhost:5432/postgres",
"jdbcSchema": "public",
"jdbcUser": "postgres",
"jdbcPassword": "Password123#",
"jdbcDriver": "org.postgresql.Driver"
}
]
}Trying to add this specific model will fail though, as we've not added the Postgres JDBC driver to our classpath:
This is easily solved by modifying the Calcite Driver we just added and including whatever DB drivers we need in the classpath. When first installing SQuirreL, you can choose to have it download many common DB JDBC drivers.
In case you've chosen Postgres, the driver will be at:
"squirrel-sql\plugins\postgres\lib\postgresql-42.2.9.jar"
We can add it by right-clicking on the Calcite 1.30.0 definition under Drivers and clicking Modify:
Now if we try to add the alias again, and test the connection it should be successful:
We can finally connect:
