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

[Alaa Salmo]

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

[Arina Yelchiyeva]

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

Post by Alaa Salmo
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