Support Questions
Find answers, ask questions, and share your expertise

Error in CREATE table with dbexecute in r

New Contributor

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.

 

HUE.png

 

 

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 REPLIES 2

Super Collaborator

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

Community Manager

@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. 



Regards,

Vidya Sargur,
Community Manager


Was your question answered? Make sure to mark the answer as the accepted solution.
If you find a reply useful, say thanks by clicking on the thumbs up button.
Learn more about the Cloudera Community: