You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@hive.apache.org by Adline Dsilva <ad...@mimos.my> on 2013/10/16 07:48:25 UTC
Create table as select
Hi All,
Does hive (0.12) support sql statement "create TABLE tmp_table AS select * from user_table limit 500". Currently I'm running 0.10 version, where its not supported.
Regards,
Adline
Re: Create table as select
Posted by Nitin Pawar <ni...@gmail.com>.
Hive 0.12 is just released today and I still have not got my hands on it.
But the query you are trying works on hive-0.11
Hive dev team works extremely hard to keep backward compatibility so it
should work on hive-0.12
If you face any issues do file a JIRA :)
On Wed, Oct 16, 2013 at 11:18 AM, Adline Dsilva <ad...@mimos.my>wrote:
> Hi All,
>
> Does hive (0.12) support sql statement "create TABLE tmp_table AS
> select * from user_table limit 500". Currently I'm running 0.10 version,
> where its not supported.
>
> Regards,
> Adline
--
Nitin Pawar
RE: Create table as select
Posted by Adline Dsilva <ad...@mimos.my>.
Really sorry guys, it seems error occurred due to something else. Everything is getting created now :-)
hive>create TABLE tmp_table1 AS select * from user_table limit 500;
Total MapReduce jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
set mapred.reduce.tasks=<number>
Starting Job = job_201310161331_0003, Tracking URL = http://cd-master:50030/jobdetails.jsp?jobid=job_201310161331_0003
Kill Command = /opt/cloudera/parcels/CDH-4.4.0-1.cdh4.4.0.p0.39/lib/hadoop/bin/hadoop job -kill job_201310161331_0003
Hadoop job information for Stage-1: number of mappers: 3; number of reducers: 1
2013-10-16 15:23:34,455 Stage-1 map = 0%, reduce = 0%
2013-10-16 15:23:39,520 Stage-1 map = 33%, reduce = 0%, Cumulative CPU 1.27 sec
2013-10-16 15:23:40,541 Stage-1 map = 67%, reduce = 0%, Cumulative CPU 2.55 sec
2013-10-16 15:23:41,563 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:42,589 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:43,613 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:44,644 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:45,667 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:46,730 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:47,755 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:48,780 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:49,801 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:50,823 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:51,847 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 3.5 sec
2013-10-16 15:23:52,869 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
2013-10-16 15:23:53,895 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
2013-10-16 15:23:54,919 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
2013-10-16 15:23:55,943 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
2013-10-16 15:23:56,969 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
2013-10-16 15:23:57,997 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
2013-10-16 15:23:59,025 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
2013-10-16 15:24:00,052 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
2013-10-16 15:24:01,075 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 7.48 sec
MapReduce Total cumulative CPU time: 7 seconds 480 msec
Ended Job = job_201310161331_0003
Moving data to: hdfs://cd-master:8020/user/hive/warehouse/tmp_table1
Table default.tmp_table1 stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 352, raw_data_size: 0]
19 Rows loaded to hdfs://cd-master:8020/tmp/hive-hdcloud/hive_2013-10-16_15-23-26_240_2324696739286680344-1/-ext-10000
MapReduce Jobs Launched:
Job 0: Map: 3 Reduce: 1 Cumulative CPU: 7.48 sec HDFS Read: 1087 HDFS Write: 352 SUCCESS
Total MapReduce CPU Time Spent: 7 seconds 480 msec
Sorry for the trouble
Regards,
Adline
________________________________________
From: Adline Dsilva
Sent: 16 October 2013 15:24
To: David Morel; user@hive.apache.org
Subject: RE: Create table as select
Limit clause is creating the problem,
hive> create TABLE tmp_table AS select * from user_table limit 500;
FAILED: SemanticException 0:0 Error creating temporary folder on: hdfs://master:8020/user/hive/warehouse. Error encountered near token 'TOK_TMP_FILE'
I'm trying out hadoop with a BI tool, which creates temp tables. Without limit, huge data will roast BI :-)
Regards,
Adline
________________________________________
From: David Morel [dmorel@amakuru.net]
Sent: 16 October 2013 15:05
To: Adline Dsilva; user@hive.apache.org
Subject: Re: Create table as select
On 16 octobre 2013 at 07:49:05, Adline Dsilva (adline.dsilva@mimos.my<ma...@mimos.my>) wrote:
Hi All,
Does hive (0.12) support sql statement "create TABLE tmp_table AS select * from user_table limit 500". Currently I'm running 0.10 version, where its not supported.
Regards,
Adline
Er, I do "create table as select" all the time, what doesn't work exactly? the limit clause?
David
RE: Create table as select
Posted by Adline Dsilva <ad...@mimos.my>.
Limit clause is creating the problem,
hive> create TABLE tmp_table AS select * from user_table limit 500;
FAILED: SemanticException 0:0 Error creating temporary folder on: hdfs://master:8020/user/hive/warehouse. Error encountered near token 'TOK_TMP_FILE'
I'm trying out hadoop with a BI tool, which creates temp tables. Without limit, huge data will roast BI :-)
Regards,
Adline
________________________________________
From: David Morel [dmorel@amakuru.net]
Sent: 16 October 2013 15:05
To: Adline Dsilva; user@hive.apache.org
Subject: Re: Create table as select
On 16 octobre 2013 at 07:49:05, Adline Dsilva (adline.dsilva@mimos.my<ma...@mimos.my>) wrote:
Hi All,
Does hive (0.12) support sql statement "create TABLE tmp_table AS select * from user_table limit 500". Currently I'm running 0.10 version, where its not supported.
Regards,
Adline
Er, I do "create table as select" all the time, what doesn't work exactly? the limit clause?
David