Reputation: 611

PostgreSQL connection in Apache-Drill

I am trying to get Apache Drill to connect to a PostgreSQL database using the following configuration

# storage-plugins-override.conf
    "postgresql": {
      "type": "jdbc",
      "driver": "org.postgresql.Driver",
      "url": "${POSTGRES_URL}",
      "username": "${POSTGRES_USER}",
      "password": "${POSTGRES_PASSWORD}",
      "enabled": true,
      "sourceParameters": {
        "minimumIdle": 0,
        "autoCommit": true,
        "connectionTestQuery": "select version() as postgresql_version",
        "dataSource.cachePrepStmts": true,
        "dataSource.prepStmtCacheSize": 250
      }

When running the following command:

docker run -it --name drill -p 8048:8047 -p 31011:31010 \
  -e GCS_KEY_FILE -e DATA_PATH -e POSTGRES_URL -e POSTGRES_USER -e POSTGRES_PASSWORD \
  -v /home/indio/drill/bq:/tmp/data \
  -v /home/indio/drill/drill-override.conf:/opt/drill/conf/drill-override.conf \
  -v /home/indio/drill/storage-plugins-override.conf:/opt/drill/conf/storage-plugins-override.conf \
  -v /home/indio/drill/postgres-sample-data:/opt/drill/sample-data/postgres \
    -v /home/indio/drill/postgresql-42.6.0.jar:/opt/drill/jars/3rdparty/postgresql-42.6.0.jar \
  apache/drill \
  /opt/drill/bin/sqlline -u "jdbc:drill:zk=local" -n admin -p admin

where the postgresql-42.6.0.jar plugin was downloaded using the instructions provided in https://drill.apache.org/docs/rdbms-storage-plugin/.

Two things are noticed:

first

apache drill> SHOW SCHEMAS;
+--------------------+
|    SCHEMA_NAME     |
+--------------------+
| dfs.default        |
| dfs.root           |
| dfs.tmp            |
| information_schema |
| sys                |
| cp.default         |
+--------------------+
6 rows selected (0.104 seconds)

and

apache drill> use pgdb;
Error: VALIDATION ERROR: Schema [pgdb] is not valid with respect to either root schema or current default schema.

Current default schema: No default schema selected

[Error Id: 3fffb76d-92f8-4c17-97e7-1d5f022a9ee1 ] (state=,code=0)
apache drill>