SQL Query Error: VALIDATION ERROR: Unable to create or drop tables/views. Schema [hdfs] is immutable.

[Alaa Salmo <al...@gmail.com>]

Hi All,

I added my plugin to the storage. see it below. When I am trying to create
table (csv format), I am getting the error message. Any comment or advice!

**************************** Command
*****************************************
0: jdbc:drill:schema=hdfs> CREATE TABLE new_table_name as  select * from
`hdfs://mgelbana-machine:54310/myhdfs/people.csv`;

*************************** Error
************************************************

Error: VALIDATION ERROR: Unable to create or drop tables/views. Schema
[hdfs] is immutable.



*************************************** hdfs schema
*******************************************
{
  "type": "file",
  "connection": "hdfs://mgelbana-machine:54310",
  "config": null,
  "workspaces": {
    "root": {
      "location": "/",
      "writable": true,
      "defaultInputFormat": null,
      "allowAccessOutsideWorkspace": false
    }
  },
  "formats": {
    "psv": {
      "type": "text",
      "extensions": [
        "tbl"
      ],
      "delimiter": "|"
    },
    "csv": {
      "type": "text",
      "extensions": [
        "csv"
      ],
      "delimiter": ","
    }
,
  "enabled": true
}


Regards,
Alaa

Re: SQL Query Error: VALIDATION ERROR: Unable to create or drop tables/views. Schema [hdfs] is immutable.

[Arina Yelchiyeva <ar...@gmail.com>]

When workspace is not indicated, `default` workspace is used, it is added implicitly and usually is not show in the storage config.
To see that such workspace exists use `show schemas` command that will list all workspaces.
In your case, there are several options:
1. add root workspace to the create statement (create table hdfs.`root`.new_table …);
2. set workspace prior to CTAS command (use hdfs.`root`);
3. Add default workspace to the config (like root workspace is added).

Kind regards,
Arina

> On Dec 9, 2018, at 1:14 AM, Alaa Salmo <al...@gmail.com> wrote:
> 
> Hi All,
> 
> I added my plugin to the storage. see it below. When I am trying to create
> table (csv format), I am getting the error message. Any comment or advice!
> 
> **************************** Command
> *****************************************
> 0: jdbc:drill:schema=hdfs> CREATE TABLE new_table_name as  select * from
> `hdfs://mgelbana-machine:54310/myhdfs/people.csv`;
> 
> *************************** Error
> ************************************************
> 
> Error: VALIDATION ERROR: Unable to create or drop tables/views. Schema
> [hdfs] is immutable.
> 
> 
> 
> *************************************** hdfs schema
> *******************************************
> {
>  "type": "file",
>  "connection": "hdfs://mgelbana-machine:54310",
>  "config": null,
>  "workspaces": {
>    "root": {
>      "location": "/",
>      "writable": true,
>      "defaultInputFormat": null,
>      "allowAccessOutsideWorkspace": false
>    }
>  },
>  "formats": {
>    "psv": {
>      "type": "text",
>      "extensions": [
>        "tbl"
>      ],
>      "delimiter": "|"
>    },
>    "csv": {
>      "type": "text",
>      "extensions": [
>        "csv"
>      ],
>      "delimiter": ","
>    }
> ,
>  "enabled": true
> }
> 
> 
> Regards,
> Alaa