Re: Hive Query - Issue
Hi
When you do a SELECT * , the partition columns are returned as last N columns
(if u have N partitions)
In this case the 63rd column in SELECT * is the partition column
Instead of SELECT *
Do a
SELECT
col1,
col2,
col3,
…..
Not to show the candle to the sun if u r a AWK/SED ninja :-) but to get all
column from hive u can do this
hive -e describe ur_table_name | awk '{print $1,}'|sed '1i SELECT'|less
Thanks
sanjay
From: Manickam P manicka...@outlook.commailto:manicka...@outlook.com
Reply-To: user@hive.apache.orgmailto:user@hive.apache.org
user@hive.apache.orgmailto:user@hive.apache.org
Date: Monday, September 2, 2013 4:32 AM
To: user@hive.apache.orgmailto:user@hive.apache.org
user@hive.apache.orgmailto:user@hive.apache.org
Subject: Hive Query - Issue
Hello Experts,
when i try to execute the below query i'm getting error. Please help me to
correct this.
insert overwrite table table_baseline partition (sourcedate='base_2013_08')
select * from (select * from table_a where sourcedate='tablea_2013_08' union
all select * from table_b where sourcedate='tableb_2013_08') final
My intention here is i want to populate the table_baseline by using the all
records from table_a and table_b with partition. I am getting the below error.
Error in semantic analysis: Line 1:23 Cannot insert into target table because
column number/types are different ''BASE_2013_08'': Table insclause-0 has 62
columns, but query has 63 columns.
I verified the column count and types everything is same but here it says some
difference. The same query works fine without having any partitions in all the
three tables but getting error while executing with partitions.
please help.
Thanks
Manickam P
CONFIDENTIALITY NOTICE
==
This email message and any attachments are for the exclusive use of the
intended recipient(s) and may contain confidential and privileged information.
Any unauthorized review, use, disclosure or distribution is prohibited. If you
are not the intended recipient, please contact the sender by reply email and
destroy all copies of the original message along with any attachments, from
your computer system. If you are the intended recipient, please be advised that
the content of this message is subject to access, review and disclosure by the
sender's Email System Administrator.
Hive Query - Issue
Hello Experts, when i try to execute the below query i'm getting error. Please help me to correct this. insert overwrite table table_baseline partition (sourcedate='base_2013_08') select * from (select * from table_a where sourcedate='tablea_2013_08' union all select * from table_b where sourcedate='tableb_2013_08') final My intention here is i want to populate the table_baseline by using the all records from table_a and table_b with partition. I am getting the below error. Error in semantic analysis: Line 1:23 Cannot insert into target table because column number/types are different ''BASE_2013_08'': Table insclause-0 has 62 columns, but query has 63 columns. I verified the column count and types everything is same but here it says some difference. The same query works fine without having any partitions in all the three tables but getting error while executing with partitions. please help. ThanksManickam P
Re: Hive Query - Issue
Hello, I think you are working with dynamic partition. Then you do not need to mention it's value.you only need to put partition like this::: try this:: insert overwrite table table_baseline partition (sourcedate) select * from (select * from table_a where sourcedate='tablea_2013_08' union all select * from table_b where sourcedate='tableb_2013_08') final If your problem will not resolve from this then you need to count the no.of columns in table i think problem is there then correct it if you not and try once again.I also found the same error in past.. On Mon, Sep 2, 2013 at 5:02 PM, Manickam P manicka...@outlook.com wrote: Hello Experts, when i try to execute the below query i'm getting error. Please help me to correct this. insert overwrite table table_baseline partition (sourcedate='base_2013_08') select * from (select * from table_a where sourcedate='tablea_2013_08' union all select * from table_b where sourcedate='tableb_2013_08') final My intention here is i want to populate the table_baseline by using the all records from table_a and table_b with partition. I am getting the below error. *Error in semantic analysis: Line 1:23 Cannot insert into target table because column number/types are different ''BASE_2013_08'': Table insclause-0 has 62 columns, but query has 63 columns.* I verified the column count and types everything is same but here it says some difference. The same query works fine without having any partitions in all the three tables but getting error while executing with partitions. please help. Thanks Manickam P -- Regards *Manish Dunani* *Contact No* : +91 9408329137 *skype id* : manish.dunani* *
Re: Hive Query Issue
Have you tried a re-start of the cluster. Hive is a JVM process. A hang in the JVM may cause issues like this. I have experienced a similar issue, however, I did not get any output at all. After typing the query and hitting enter, the prompt never returned and neither did I see any counter information. A restart resolved it. Though not a resolution, its a workaround worth trying. On Tue, Aug 6, 2013 at 7:10 AM, Manickam P manicka...@outlook.com wrote: Hi, Thanks for your reply. I have a small test table which has around 10 records. when i run select * from table.. it launched a job and that got completed successfully. When i run the same query in my actual table no job got initiated. it got struck. I did not get any error in my task tracker also. All my data nodes are up and running. I don't have any clue here. Thanks, Manickam P -- Date: Tue, 6 Aug 2013 16:24:38 +0530 Subject: Re: Hive Query Issue From: nitinpawar...@gmail.com To: user@hive.apache.org when you run select * from table .. it does not launch a mapreduce job, where are when you put some condition, it does need to process the data so it launches a mapreduce job now when you start this query, go to your jobtracker page and see how many jobs are running. Is it able to start your job? if not why ? that will tell you why its stuck On Tue, Aug 6, 2013 at 3:22 PM, Manickam P manicka...@outlook.com wrote: Hi, If i run a hive query like select * from table_name limit 10 it is working. If i run query like select column_name from table_name limit 10 it hangs. *Total MapReduce jobs = 1* *Launching Job 1 out of 1* *Number of reduce tasks is set to 0 since there's no reduce operator* It just prints the above after that nothing happening in the hive shell. Can you pls help me on this. Thanks, Manickam P -- Nitin Pawar
Re: Hive Query Issue
Hi Some quick checks Please don't mind if my questions sound trivial 1. Is your hdfs cluster or pseudo-distributed node up and running Can u see the HDFS at http://host:50070 ? 2. Is your mrV1 or Yarn (mrV2) up and running 2a)mrV1 http://host:50030 2b) YARN http://host:8088 3. Is your hive-default xml; pointing to the job tracker 4. Can u run a MR job on your cluster independent of Hive ? U can try the WordCount that comes standard with the examples Thanks sanjay From: Manickam P manicka...@outlook.commailto:manicka...@outlook.com Reply-To: user@hive.apache.orgmailto:user@hive.apache.org user@hive.apache.orgmailto:user@hive.apache.org Date: Tuesday, August 6, 2013 4:10 AM To: user@hive.apache.orgmailto:user@hive.apache.org user@hive.apache.orgmailto:user@hive.apache.org Subject: RE: Hive Query Issue Hi, Thanks for your reply. I have a small test table which has around 10 records. when i run select * from table.. it launched a job and that got completed successfully. When i run the same query in my actual table no job got initiated. it got struck. I did not get any error in my task tracker also. All my data nodes are up and running. I don't have any clue here. Thanks, Manickam P Date: Tue, 6 Aug 2013 16:24:38 +0530 Subject: Re: Hive Query Issue From: nitinpawar...@gmail.commailto:nitinpawar...@gmail.com To: user@hive.apache.orgmailto:user@hive.apache.org when you run select * from table .. it does not launch a mapreduce job, where are when you put some condition, it does need to process the data so it launches a mapreduce job now when you start this query, go to your jobtracker page and see how many jobs are running. Is it able to start your job? if not why ? that will tell you why its stuck On Tue, Aug 6, 2013 at 3:22 PM, Manickam P manicka...@outlook.commailto:manicka...@outlook.com wrote: Hi, If i run a hive query like select * from table_name limit 10 it is working. If i run query like select column_name from table_name limit 10 it hangs. Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks is set to 0 since there's no reduce operator It just prints the above after that nothing happening in the hive shell. Can you pls help me on this. Thanks, Manickam P -- Nitin Pawar CONFIDENTIALITY NOTICE == This email message and any attachments are for the exclusive use of the intended recipient(s) and may contain confidential and privileged information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message along with any attachments, from your computer system. If you are the intended recipient, please be advised that the content of this message is subject to access, review and disclosure by the sender's Email System Administrator.
Hive Query Issue
Hi, If i run a hive query like select * from table_name limit 10 it is working.If i run query like select column_name from table_name limit 10 it hangs. Total MapReduce jobs = 1Launching Job 1 out of 1Number of reduce tasks is set to 0 since there's no reduce operatorIt just prints the above after that nothing happening in the hive shell. Can you pls help me on this. Thanks, Manickam P
RE: Hive Query Issue
Hi, Thanks for your reply. I have a small test table which has around 10 records. when i run select * from table.. it launched a job and that got completed successfully. When i run the same query in my actual table no job got initiated. it got struck. I did not get any error in my task tracker also. All my data nodes are up and running. I don't have any clue here. Thanks, Manickam P Date: Tue, 6 Aug 2013 16:24:38 +0530 Subject: Re: Hive Query Issue From: nitinpawar...@gmail.com To: user@hive.apache.org when you run select * from table .. it does not launch a mapreduce job, where are when you put some condition, it does need to process the data so it launches a mapreduce job now when you start this query, go to your jobtracker page and see how many jobs are running. Is it able to start your job? if not why ? that will tell you why its stuck On Tue, Aug 6, 2013 at 3:22 PM, Manickam P manicka...@outlook.com wrote: Hi, If i run a hive query like select * from table_name limit 10 it is working. If i run query like select column_name from table_name limit 10 it hangs. Total MapReduce jobs = 1 Launching Job 1 out of 1Number of reduce tasks is set to 0 since there's no reduce operator It just prints the above after that nothing happening in the hive shell. Can you pls help me on this. Thanks, Manickam P -- Nitin Pawar
