Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Unable to load the data into hive table through script.

Highlighted

Unable to load the data into hive table through script.

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

5 REPLIES 5
Highlighted

Re: Unable to load the data into hive table through script.

Expert Contributor

@kotesh banoth What is the error?

Highlighted

Re: Unable to load the data into hive table through script.

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

Highlighted

Re: Unable to load the data into hive table through script.

Expert Contributor

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

Highlighted

Re: Unable to load the data into hive table through script.

@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

Re: Unable to load the data into hive table through script.

Expert Contributor

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

Don't have an account?
Coming from Hortonworks? Activate your account here