Error in CREATE table with dbexecute in r

Question

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'

smdas · Accepted Answer

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

RafaelDiaz · Answer

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'")

Sign up

Please use your Cloudera account.

Login to the community

Please use your Cloudera account.