topic Re: The INSERT INTO table orignaltable from table sastmp_04_04_21_*** failed in New SAS User

The INSERT INTO table orignaltable from table sastmp_04_04_21_*** failed

SaiSankar116 — Fri, 05 Apr 2019 14:00:31 GMT

Dear All,

I am trying to ingest data from SAS Server to Hive using Data step. It was working till last week.

-> Takes file from SAS Server , move it to HDFS Temp as .dlv file

-> creates a temporary table on the schema we gave in hadoop configuration connect string.

-> Finally it runs The INSERT INTO table orignaltable from table sastmp_04_04_21_*** .

-> file inside /tmp will be deleted.

Recently i am facing an issue.

Temporary table is getting created on hive.

The INSERT INTO table orignaltable from table sastmp_04_04_21_*** failed, though original table is created with no rows.

and i am not finding the file under/tmp.

Any expert suggestions? greatly appreciated.

Here is the code i am using.

options set=SAS_HADOOP_RESTFUL=1;
options set=SAS_HADOOP_JAR_PATH="/hadoop/jars";
options set= SAS_HADOOP_CONFIG_PATH="/hadoop/conf";

OPTIONS nofmterr;
%let svr = %NRSTR('testing.domain.com');

%let stng = %NRSTR('STORED as PARQUET');

libname myhadoop hadoop server=&svr hdfs_tempdir='/tmp/sastmp' user=hive password=pxxxx
schema=schema1 port=10000
DBCREATE_TABLE_OPTS=&stng
subprotocol=hive2;

libname sai '/mydropzone';

data myhadoop.carshelp;
set sai.cars_temp;
run;

Re: The INSERT INTO table orignaltable from table sastmp_04_04_21_*** failed

LinusH — Fri, 05 Apr 2019 07:56:00 GMT

Any code/log will help to understand the details of your operation.

Do I get you right that you don't acutually execute in SAS (explcit SQL pass through?)

Re: The INSERT INTO table orignaltable from table sastmp_04_04_21_*** failed

SaiSankar116 — Sun, 07 Apr 2019 15:38:35 GMT

Here is the sample code i am using, please help me. Thanks for your time.

options set=SAS_HADOOP_RESTFUL=1;
options set=SAS_HADOOP_JAR_PATH="/hadoop/jars";
options set= SAS_HADOOP_CONFIG_PATH="/hadoop/conf";

OPTIONS nofmterr;
%let svr = %NRSTR('testing.domain.com');

%let stng = %NRSTR('STORED as PARQUET');

libname myhadoop hadoop server=&svr hdfs_tempdir='/tmp/sastmp' user=hive password=pxxxx
schema=schema1 port=10000
DBCREATE_TABLE_OPTS=&stng
subprotocol=hive2;

libname sai '/mydropzone';

data myhadoop.carshelp;
set sai.cars_temp;
run;

Re: The INSERT INTO table orignaltable from table sastmp_04_04_21_*** failed

LinusH — Fri, 05 Apr 2019 18:58:26 GMT

Ok, next step the log. Also include the following at the top:

Options sastrace =',,,d' sastraceloc =saslog nostsuffix msglevel=i;

Re: The INSERT INTO table orignaltable from table sastmp_04_04_21_*** failed

SaiSankar116 — Sun, 07 Apr 2019 23:00:06 GMT

HADOOP_8: Executed: on connection 2
LOAD DATA INPATH '/tmp/sastmp/sasdata-2019-04-04-16-30-00-841-e-00001.dlv' OVERWRITE INTO TABLE sastmp_04_04_16_30_02_424_00002

HADOOP_9: Executed: on connection 2
set hive.exec.dynamic.partition.mode=nonstrict

HADOOP_10: Executed: on connection 2
set hive.exec.dynamic.partition=true

HADOOP_11: Executed: on connection 2
set mapred.max.split.size=536870912

HADOOP_12: Executed: on connection 2
INSERT INTO TABLE `cars_temp` SELECT * FROM sastmp_04_04_16_30_02_424_00002

HADOOP_13: Executed: on connection 2
DROP TABLE sastmp_04_04_16_30_02_424_00002

ERROR: Execute error:
ERROR: The INSERT INTO table cars_temp from table sastmp_04_04_16_30_02_424_00002 failed.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: DATA statement used (Total process time):
real time 8.21 seconds
cpu time 0.51 seconds

28
29

Extra Information :

*********************

Actual table cars_temp table is getting created with zero records. ERROR: The INSERT INTO table cars_temp from table sastmp_04_04_16_30_02_424_00002 failed. I checked all the folder permissions(777), everything looks good. Recently upgraded to HDP3.1.0.