Error in CREATE table with dbexecute in r

[#1]

I am trying to create a table using the CREATE TABLE statement, in hue it works perfectly but when I use the dbexecute function in r, I get an error. How could it be solved.

library(DBI)
library(odbc)
con_Hive <- dbConnect(odbc::odbc(), "Hive_produccion", database = "cld_ml_bi_eng", encoding = "latin1")
con_Hive
dbExecute(conn = con_Hive, statement = "CREATE EXTERNAL TABLE iris(sepal_length double,
                           sepal_width double,
                           petal_length double,
                           petal_width double,
                           species varchar(10))
LOCATION '/sandbox/CLD_ML_BI_ENG/iris'")

Error: nanodbc/nanodbc.cpp:1655: 42000: [Cloudera][Hardy] (80) Syntax or semantic analysis error thrown in server while executing query. Error message from server: Error while compiling statement: FAILED: HiveAccessControlException Permission denied: user [RDIAZ] does not have [CREATE] privilege on [default/iris] 
<SQL> 'CREATE EXTERNAL TABLE iris(sepal_length double,
                           sepal_width double,
                           petal_length double,
                           petal_width double,
                           species varchar(10))
LOCATION '/sandbox/CLD_ML_BI_ENG/iris'

[#2 · Accepted solution]

Hello @RafaelDiaz 

Thanks for using Cloudera Community. Based on the Post, Create Table via Hue works yet fails in R with "dbExecute" Function. While I haven't tried using "dbExecute" Function in R, the Error shows HiveAccessControlException denying the User on "default/iris". 

You haven't stated the CDH/CDP Version being used yet I assume your Team is using CDP with Ranger. First, Kindly check if passing the DB Name "cld_ml_bi_eng" with Table Name "iris" in "dbExecute" Function works. Since I haven't used "dbExecute" Function in R, Let us know if passing DB Name with Table Name like <DBName.TableName> is feasible. 

Secondly, Kindly check in Ranger for the Privilege of User "RDIAZ" for "CREATE" Privilege. Try including the DB Scope to "*" & Confirm the Table being Created with the Database ("default" or "cld_ml_bi_eng"). Accordingly, We can proceed further. 

Kindly review & share the Outcome from the above 2 suggestion. If your Team have Fixed the Issue already, We would appreciate your Team sharing the details for fellow Community Users as well.  

Regards, Smarak

[#3 · Accepted solution]

Hello @RafaelDiaz 

Thanks for using Cloudera Community. Based on the Post, Create Table via Hue works yet fails in R with "dbExecute" Function. While I haven't tried using "dbExecute" Function in R, the Error shows HiveAccessControlException denying the User on "default/iris". 

You haven't stated the CDH/CDP Version being used yet I assume your Team is using CDP with Ranger. First, Kindly check if passing the DB Name "cld_ml_bi_eng" with Table Name "iris" in "dbExecute" Function works. Since I haven't used "dbExecute" Function in R, Let us know if passing DB Name with Table Name like <DBName.TableName> is feasible. 

Secondly, Kindly check in Ranger for the Privilege of User "RDIAZ" for "CREATE" Privilege. Try including the DB Scope to "*" & Confirm the Table being Created with the Database ("default" or "cld_ml_bi_eng"). Accordingly, We can proceed further. 

Kindly review & share the Outcome from the above 2 suggestion. If your Team have Fixed the Issue already, We would appreciate your Team sharing the details for fellow Community Users as well.  

Regards, Smarak

[#4]

@RafaelDiaz, Has the reply helped resolve your issue? If so, please mark the appropriate reply as the solution, as it will make it easier for others to find the answer in the future. 

[#5]

Hello @RafaelDiaz 

We hope the Post was helpful for you & marking the same as Resolved. If your Team continue to face the issue with HiveAccessControlException, Do update the Post & we can check accordingly.

Regards, Smarak

[#6 · Accepted solution]

It is already solved, it was only missing in the CREATE TABLE to add the sandbox path

CLD_ML_BI_ENG.iris

dbExecute(conn = con_Hive,
statement = "CREATE EXTERNAL TABLE CLD_ML_BI_ENG.iris(sepal_length double, sepal_width double, petal_length double, petal_width double, species varchar(10))
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION '/sandbox/CLD_ML_BI_ENG/iris'")