question Re: Error in CREATE table with dbexecute in r in Support Questions

Error in CREATE table with dbexecute in r

RafaelDiaz — Tue, 21 Apr 2026 08:03:51 GMT

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'

Re: Error in CREATE table with dbexecute in r

smdas — Thu, 11 Nov 2021 05:38:59 GMT

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

Re: Error in CREATE table with dbexecute in r

VidyaSargur — Mon, 15 Nov 2021 05:03:52 GMT

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

Re: Error in CREATE table with dbexecute in r

smdas — Wed, 01 Dec 2021 08:46:43 GMT

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

Re: Error in CREATE table with dbexecute in r

RafaelDiaz — Fri, 10 Dec 2021 22:14:07 GMT

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