Apache Drill not able to set default workspace

Question

At the moment I am only able to work on dfs.tmp. workspace, which is quite annoying. So I tried to change the default workspace to a new (existing) folder (owned by the drill user):

"workspaces": {
    "default": {
      "location": "/var/drill",
      "writable": true,
      "defaultInputFormat": null
    },
    "root": {
      "location": "/",
      "writable": false,
      "defaultInputFormat": null
    },
...

But it does not work:

CREATE TABLE `test` as SELECT 'Test' FROM (VALUES(1))

Returns the following error, which indicates that the modified settings get ignored.

org.apache.drill.common.exceptions.UserRemoteException: PARSE ERROR: Root schema is immutable. Creating or dropping tables/views is not allowed in root schema.Select a schema using 'USE schema' command.

I also tried it with prefix (without success)

CREATE TABLE dfs.default.`test` as SELECT 'Test' FROM (VALUES(1))

PARSE ERROR: Encountered ". default" at line 1, column 17.

Also also tried to restart drill and make root writable.

lampShadesDrifter lampShadesDrifter · Accepted Answer · 2018-01-12T01:41:22

Adding this answer just to combine the two existing ones by ColemanTO and devツ and show an example.

So, like the other answers so far have said, the word "default" is reserved in a drill query. You correctly refer to docs that say to create a new default workspace in order to, say, define a writable root(/) workspace. However, the documentation also gives an example where, in order to actually reference the custom "default" workspace, you need to add backticks.

So if you added a workspace

{
  "type": "file",
  "enabled": true,
  "connection": "hdfs:///",
  "config": null,
  "workspaces": {
    "default": {
      "location": "/some/path",
      "writable": true,
      "defaultInputFormat": null
    }
  ...
  }
 ...
}

you would refer to in in a query like:

SELECT * FROM dfs.`default`.`path/relative/to/custom/default/location` LIMIT 10;

In the case of your original posted problem, you could also create a new workspace called, say "var_drill", so that you don't have to escape from the keyword "default" in your queries.

Apache Drill not able to set default workspace

4 Answers