Hive insert command is failed to run and throwing an error


#1

Hello Team,
insert overwrite local directory ‘/home/dineshbughade9557/onlycmc’ select * from nyse where symbol = ‘CMC’;

data is present in nyse table but above commands gives me below error. please help me out

/**********************************************************************************************/

Query ID = dineshbughade5997_20191225052919_8ff9f46d-a5c8-42f9-bd11-7f5fb802543a
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there’s no reduce operator
Starting Job = job_1573919454381_4570, Tracking URL = http://cxln2.c.thelab-240901.internal:8088/proxy/application_1573919454381_4570/
Kill Command = /usr/hdp/2.6.2.0-205/hadoop/bin/hadoop job -kill job_1573919454381_4570
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 0
2019-12-25 05:29:28,568 Stage-1 map = 0%, reduce = 0%
2019-12-25 05:29:34,859 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 4.61 sec
MapReduce Total cumulative CPU time: 4 seconds 610 msec
Ended Job = job_1573919454381_4570
Moving data to local directory /home/dineshbughade9557/onlycmc
Failed with exception Unable to move source hdfs://cxln1.c.thelab-240901.internal:8020/tmp/hive/dineshbughade5997/5d0a0969-9913-43a1-a9ec-c30304f928c1/hive_2019-12-25_05-29-19_808_4463325759378467311-1/-mr-10000 to destination /home/dineshbughade9557/onlycmc
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.MoveTask

MapReduce Jobs Launched:
Stage-Stage-1: Map: 1 Cumulative CPU: 4.61 sec HDFS Read: 3199724HDFS Write: 14404 SUCCESS


#2

Hi, Shrikar.

Here is the steps to load the nyse file to the local directory :- ( Replace the satyajitdas114a57801 by your username). Rest the steps are all same.

  1. local user> hadoop fs -copyToLocal /data/NYSE_daily

  2. hive> use satyajit1991; # database name

  3. hive> load data local inpath ‘NYSE_daily’ overwrite into table nyse;

  4. hive> SELECT symbol1, AVG(price_open) AS avg_price FROM nyse GROUP BY symbol1;

  5. hive> insert overwrite local directory ‘/home/satyajitdas114a57801/onlycmc’

    select * from nyse where symbol1 = ‘CMC’; # this will move to the local directory.

  6. hive> insert overwrite directory ‘onlycmc’ select * from nyse where symbol1=‘CMC’ # this will overwrite the nyse table

  7. go to local user> ls

  8. local user > cd onlycmc

  9. local user> cat 000000_0

Now you will be able to do it.

All the best!