SAS EG to Hive table

[tomnorra]

I have a technical issue in that when an Insert statement is invoked to load data to an existing table in the HIVE from SAS it fails with Error HiveAccessControlException Permission denied: user does not have [CREATE] privilege. This is because SAS EG does a create to the tmp space before it actually does a load to the actual schema where the data needs to land. Why does SAS try to create then load instead of just load directly to the table that exists? A solution to allow the create permission is not an acceptable solution because it would violate Controls Procedures within the organization. We need SAS EG just to load the data directly into the existing table not try to create a table in tmp space to load it into the existing table. 

Simple code example

LIBNAME SASData "/path/to/my/sas/data";

Libname HIVECON hadoop server="xxx.yyy.zzz" port = ##### schema=myschema;

RUN;
Options set=SAS_HADOOP_CONFIG_PATH='/path/to/Hadoop/Conf/';
Options set=SAS_HADOOP_JAR_PATH='/path/to/Hadoop/jars';
RUN;
PROC SQL;

/*PROC APPEND; /*Does the same thing as PROC SQL*/*/
INSERT INTO HIVECON.tblZip (branch_zip)
(SELECT ZIP FROM SASData.ZipInfo);

quit;

run;

[SuryaKiran]

Hello,

Welcome to SAS Community!

Try PROC APPEND, this will skip the Create Table statement as the base table already exists. 

[tomnorra]

Actually PROC APPEND does the same thing as PROC SQL. Or at least we get the same error.

[SASKiwi]

Well, SAS has to move the SAS table Zipinfo into the Hive environment somehow so use of temporary storage seems quite appropriate to me.

If your database admin is OK with you loading SAS data into Hive permanent tables then why not into temporary storage too? Most databases provide temporary storage for processes to use as part of constructing a permanent result set as it can be more efficient to do it that way. You can't use temporary storage without creating tables. 

[tomnorra]

SASKiwi, you don't get the security aspect of this evidently. The Hive controls in place are preventing CREATES not Inserts. The problem is that SAS translates everything into a Create first in Temp space then inserts into the Hive table. SAS is the one that is backwards and should have a solution to treat an insert as exactly that, just an insert and not a create than an insert. If the table already existed in the temp space the SAS script would fail because the table already existed ad wouldn't be able to be created again. SAS is attemting to create a unique table, something like sasdata-2019-01-16-15-02-57-414-e-00001.dlv. So it is doing a create regardless of what code the developer writes to perform the insert. Users won't know what the table in temp space needs to be to ensure it exists already as SAS will just use a different name to avoid overwrites. SAS just needs to change to not do Creates and allow inserts directly to existing tables.