Hi, I was trying to connect to a local cratedb 4.5.3 as this seems to be a very interesting open source database for time-series data and seems to be (mostly) compatible with postgreSQL.
I am using the DB Connector and PostgreSQL Connector in Knime 4.4.0. I was able to connect, create tables and use the DB Insert. However, I cannot do any other basic operation on it. When I use the DB Table Selector I get various errors:
After connecting:org.postgresql.util.PSQLException: ERROR: line 1:1: mismatched input ‘ROLLBACK’ expecting {‘SELECT’, …
Subsequent calls: java.sql.SQLException: A local transaction already exists.
The DB connector works best as it allows me to browse the database in the table selector node. Database dialect SQL-92 or PostgreSQL does not seem to make a difference. Also I tried to use the latest postgreSQL jdbc driver (42.2.23) without success. Does anybody have success with CrateDB, did I miss anything?
Thanks Scott, their JDBC driver [1] works much better indeed! I can read now and use Row Filter, GroupBy, Sorter and Joiner at blazing speed (you need to use Columnar Storage Labs to have similar performance in Knime for e.g. GroupBy).
Unfortunately the beloved Pivot [2], Auto-Binner [3] and Partitioning [4] do not work (test not exhaustive!). It seems to have to do partly with limitations of CrateDB and perhaps some streamlining neccessary on Knime side?
[1] Getting started — CrateDB JDBC
[2] Pivot: “o.crate.shade.org.postgresql.util.PSQLException: ERROR: missing FROM-clause entry for relation ‘[doc.t]’”
[3] Auto-Binner: “Failure during query generation. Error: ERROR: Corresponding output columns at position: 48 must be compatible for all parts of a UNION” or “Database does not support “CASE”. Please choose only one column.” when including more than one column.
[4] Partitioning: “Partition is not supported by this database.”