Unable to load the data into hive table through script.

[#1]

@Jay SenSharma,

Hi Iam loading data from external table to ORC table through acript in automation process.

But unable to load into ORC Table.

ssh XXXXXXX@172.16.0.XXX "/usr/hdp/current/hive-client/bin/hive -e 'insert into default.XXXXXXXX select * from bmlandingzone.XXXXXXXX WHERE DateViewed >= '2017-10-08 00:00:00' and DateViewed<= '2017-10-08 23:59:59' ';"

log4j:WARN No such property [maxFileSize] in org.apache.log4j.DailyRollingFileAppender. Logging initialized using configuration in file:/etc/hive/2.6.0.3-8/0/hive-log4j.properties Query ID = medwadmin_20171009193105_17e7fa40-defe-41f7-b452-75687fe721c3 Total jobs = 1 Launching Job 1 out of 1 Status: Running (Executing on YARN cluster with App id application_1506220114274_1568)

Loading data to table default.dl_his_viewprofiles Table default.dl_his_viewprofiles stats: [numFiles=0, numRows=0, totalSize=0, rawDataSize=0]

CREATE TABLE `default.XXXXX`( `matriid` string, `viewerid` string, `dateviewed` timestamp, `syssource` int, `pagesource` string, `activityposition` int, `dumptime` timestamp, `processid` int, `batchid` int, `processname` string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 'hdfs://HACluster/apps/hive/warehouse/XXXX' TBLPROPERTIES ( 'COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', 'numFiles'='0', 'numRows'='0', 'rawDataSize'='0', 'totalSize'='0', 'transient_lastDdlTime'='1507557678')

[#2]

@kotesh banoth What is the error?

[#3]

@Manish Gupta

There is no error query is running without error through script but data is not getting loaded to target hive table.

Source also hive table.

[#4]

@kotesh banoth can you run the select statement only using the same command but, remove the insert statement and check whether it returns some data or not. It is a possibility that the select statement is not returning any data.

[#5]

@Manish Gupta

can able to get records if i login to hive.

but through script while selecting records are not coming.

ssh XXXXXXX@XXXXXXX"/usr/hdp/current/hive-client/bin/hive -e 'select * from bmlandingzone.XXXXXXXWHERE DateViewed >='2017-10-08 00:00:00' and DateViewed<='2017-10-08 23:59:59' limit 10';"

log4j:WARN No such property [maxFileSize] in org.apache.log4j.DailyRollingFileAppender. Logging initialized using configuration in file:/etc/hive/2.6.0.3-8/0/hive-log4j.properties

OK Time taken: 1.818 seconds

hive> select * from bmlandingzone.XXXXXWHERE DateViewed >='2017-10-08 00:00:00' and DateViewed<='2017-10-08 23:59:59' limit 10;

OK

A279622 A385468 2017-10-08 00:00:54 104 0 0 1824 NULL NULL NULL

M4937334 A277781 2017-10-08 00:02:03 104 0 0 1824 NULL NULL NULL

[#6]

@kotesh banoth the problem is the single quotes, you are using in the command. You are having a pair of single quotes inside single quote. The first single quote which starts@ 'select, actually is ending at DateViewed >='

You need to escape inside pair of single qoute using this example:

https://stackoverflow.com/questions/8254120/how-to-escape-a-single-quote-in-single-quote-string-in-b...