0

I need to access data in my crate database via Apache Drill. Far as i have read, crate has a PostgreSQL interface, but that is not compatible with the PostgreSQL interface exposed by drill. There isn't much information on this available on the internet. One way to go about it is implementing a custom storage plugin. But did not find helpful information about that either. i went through this, but not to much avail. Any insights, references, links, information is appreciated.

Another way i tried connecting to crate via apache drill was using the storage configuration:

{
type: "jdbc",
enabled: true,
driver: "io.crate.client.jdbc.CrateDriver",
url:"jdbc:crate://localhost:5432/"
}

and jars: crate-jdbc-1.9.1.jar and crate-jdbc-standalone-2.2.0.jar

This did not work for me either.

Cœur
  • 37,241
  • 25
  • 195
  • 267
wingjammer1990
  • 1
  • 1
  • i'd be happy to explore this deeper and help debugging. did you try with our JDBC driver? what's the error message? i'm `jodok.crate` on the CrateDB community slack channel https://crate.io/docs/support/slackin/ – Jodok Batlogg Aug 21 '17 at 10:07
  • Hey jodok, thank you for replying. Apache drill accepts a PostgreSQL storage plugin in the following format: { type: "jdbc", enabled: true, driver: "org.postgresql.Driver", url:"jdbc:postgresql://1.2.3.4/mydatabase", username:"user", password:"password" } where as, for crate, we need to provide the connection URL like: jdbc:crate://localhost:5432/, Apache drill does not recognise this and is unable to create the storage plugin. The only error fired by Apache Drill is : Please retry: error (unable to create/ update storage), No further information is available in the logs too – wingjammer1990 Aug 21 '17 at 12:32
  • the first syntax should work as well: https://crate.io/docs/clients/jdbc/#jdbc-url-format can you try? – Jodok Batlogg Aug 22 '17 at 21:41
  • Please check my post edit, it did not work for me. I had started Apache drill and crate before going forward with those steps. Please let me know if something is amiss. – wingjammer1990 Aug 23 '17 at 05:32

1 Answers1

0

I could successfully connect to crate via Apache Drill using the following configuration:

1. Storage configuration to connect to crate via Apache Drill (embedded mode):

{
"type": "jdbc",
"driver": "io.crate.client.jdbc.CrateDriver",
"url": "jdbc:crate://localhost:5432/",
"username": "crate",
"password": null,
"enabled": true
}

  1. Use the jar crate-jdbc-standalone-2.2.0.
    Add it in drill-installation-dir/jars/3rdparty
    In drill-installation-dir/conf/drill-override.conf add line

    drill.exec.sys.store.provider.local.path = "crate-jdbc-standalone-2.2.0.jar"

Query Sample:

select * from cratestorageplugin.doc.test

where:
cratestorageplugin: storage plugin created for crate
doc: schema name
test: table name

Kudos and big big thanks to crate team for all their help and prompt support!

Note: This method can prove useful, if your tables do not have dynamic data type. We can only query tables with simple data types with this connection.

wingjammer1990
  • 1
  • 1