I see hive-31 supposedly supports this, but when mimicking the syntax in
the jira i get errors

https://issues.apache.org/jira/browse/HIVE-31

hive> create table   dem select demographics_local.*  from
demographics_local;
FAILED: Parse Error: line 1:19 cannot recognize input near 'select'
'demographics_local' '.' in create table statement

I tried some variants on the above  with similar results:

hive> insert overwrite table dem select * from demographics_local;
FAILED: Error in semantic analysis: Line 1:23 Table not found 'dem'
If i create the target table first, then things are ok.  But is that
necessary?

hive> create table dem(uid int, age string, gender string, dlocation string,
    > children string, home_market_value string, home_owner_status string,
home_property_type string,
    > household_income string, length_of_residence string, marital_status
string)
    > ;
OK
hive> insert overwrite table dem select * from
demographics_local;
Total MapReduce jobs = 2
Launching Job 1 out of 2
...
Table default.dem stats: [num_partitions: 0, num_files: 1, num_rows: 0,
total_size: 9946167, raw_data_size: 0]
301399 Rows loaded to dem
OK

You really do not want to mimic the syntax in Jira. In jira the issue
bounces back and forth and people are fairly loose in describing
syntax.

You want to use the hive language manual
https://cwiki.apache.org/confluence/display/Hive/LanguageManual

Or if you are are feeling brave look in the clientpositive directory
of the source code.

Edward

On Thu, Mar 29, 2012 at 1:15 PM, Stephen Boesch <[EMAIL PROTECTED]> wrote:
> I see hive-31 supposedly supports this, but when mimicking the syntax in the
> jira i get errors
>
> https://issues.apache.org/jira/browse/HIVE-31
>
> hive> create table   dem select demographics_local.*  from
> demographics_local;
> FAILED: Parse Error: line 1:19 cannot recognize input near 'select'
> 'demographics_local' '.' in create table statement
>
> I tried some variants on the above  with similar results:
>
> hive> insert overwrite table dem select * from demographics_local;
> FAILED: Error in semantic analysis: Line 1:23 Table not found 'dem'
>
>
> If i create the target table first, then things are ok.  But is that
> necessary?
>
> hive> create table dem(uid int, age string, gender string, dlocation string,
>     > children string, home_market_value string, home_owner_status string,
> home_property_type string,
>     > household_income string, length_of_residence string, marital_status
> string)
>     > ;
> OK
> hive> insert overwrite table dem select * from
> demographics_local;
> Total MapReduce jobs = 2
> Launching Job 1 out of 2
> ...
> Table default.dem stats: [num_partitions: 0, num_files: 1, num_rows: 0,
> total_size: 9946167, raw_data_size: 0]
> 301399 Rows loaded to dem
> OK
>
>

Hi Stephen
      You are missing AS in your statement, try this out
Create table dem AS select *  from demographics_local; 
Regards
Bejoy KS

________________________________
 From: Stephen Boesch <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED]
Sent: Thursday, March 29, 2012 10:45 PM
Subject: How to use create .. as select
 

I see hive-31 supposedly supports this, but when mimicking the syntax in the jira i get errors

https://issues.apache.org/jira/browse/HIVE-31
hive> create table   dem select demographics_local.*  from demographics_local;
FAILED: Parse Error: line 1:19 cannot recognize input near 'select' 'demographics_local' '.' in create table statement

I tried some variants on the above  with similar results:
hive> insert overwrite table dem select * from demographics_local;
FAILED: Error in semantic analysis: Line 1:23 Table not found 'dem'
If i create the target table first, then things are ok.  But is that necessary?
hive> create table dem(uid int, age string, gender string, dlocation string,
    > children string, home_market_value string, home_owner_status string, home_property_type string,
    > household_income string, length_of_residence string, marital_status string)
    > ;
OK

hive> insert overwrite table dem select * from demographics_local;                                   
Total MapReduce jobs = 2
Launching Job 1 out of 2
...
Table default.dem stats: [num_partitions: 0, num_files: 1, num_rows: 0, total_size: 9946167, raw_data_size: 0]
301399 Rows loaded to dem
OK

Thanks v much Edward, I am looking at those resources now. The queries in
clientpositive are quite instructive.

Here is the correct way to do it so it seems

hive> create  table dem2 like demographics_local;
OK
Time taken: 0.188 seconds
hive> hive> insert overwrite table dem2 select * from demographics_local;

...
301399 Rows loaded to dem2

2012/3/29 Edward Capriolo <[EMAIL PROTECTED]>

> You really do not want to mimic the syntax in Jira. In jira the issue
> bounces back and forth and people are fairly loose in describing
> syntax.
>
> You want to use the hive language manual
> https://cwiki.apache.org/confluence/display/Hive/LanguageManual
>
> Or if you are are feeling brave look in the clientpositive directory
> of the source code.
>
> Edward
>
> On Thu, Mar 29, 2012 at 1:15 PM, Stephen Boesch <[EMAIL PROTECTED]> wrote:
> > I see hive-31 supposedly supports this, but when mimicking the syntax in
> the
> > jira i get errors
> >
> > https://issues.apache.org/jira/browse/HIVE-31
> >
> > hive> create table   dem select demographics_local.*  from
> > demographics_local;
> > FAILED: Parse Error: line 1:19 cannot recognize input near 'select'
> > 'demographics_local' '.' in create table statement
> >
> > I tried some variants on the above  with similar results:
> >
> > hive> insert overwrite table dem select * from demographics_local;
> > FAILED: Error in semantic analysis: Line 1:23 Table not found 'dem'
> >
> >
> > If i create the target table first, then things are ok.  But is that
> > necessary?
> >
> > hive> create table dem(uid int, age string, gender string, dlocation
> string,
> >     > children string, home_market_value string, home_owner_status
> string,
> > home_property_type string,
> >     > household_income string, length_of_residence string, marital_status
> > string)
> >     > ;
> > OK
> > hive> insert overwrite table dem select * from
> > demographics_local;
> > Total MapReduce jobs = 2
> > Launching Job 1 out of 2
> > ...
> > Table default.dem stats: [num_partitions: 0, num_files: 1, num_rows: 0,
> > total_size: 9946167, raw_data_size: 0]
> > 301399 Rows loaded to dem
> > OK
> >
> >
>

Hi Bejoy,
    The syntax you suggested does work.

    I have many years of oracle (as well as other rdbm's) so it would have
been more natural to have assumed the AS were present: but instead I
 followed the ayntax in the jira that came up (and which lacks the AS
clause).    But as Edward mentions, the correct way to do this is to use
the hive manual, and that is what I will do going forward.

cheers
stephenb

2012/3/29 Bejoy Ks <[EMAIL PROTECTED]>

> Hi Stephen
>       You are missing AS in your statement, try this out
> Create table dem AS select *  from demographics_local;
>
> Regards
> Bejoy KS
>   ------------------------------
> *From:* Stephen Boesch <[EMAIL PROTECTED]>
> *To:* [EMAIL PROTECTED]
> *Sent:* Thursday, March 29, 2012 10:45 PM
> *Subject:* How to use create .. as select
>
> I see hive-31 supposedly supports this, but when mimicking the syntax in
> the jira i get errors
>
> https://issues.apache.org/jira/browse/HIVE-31
>
> hive> create table   dem select demographics_local.*  from
> demographics_local;
> FAILED: Parse Error: line 1:19 cannot recognize input near 'select'
> 'demographics_local' '.' in create table statement
>
> I tried some variants on the above  with similar results:
>
> hive> insert overwrite table dem select * from demographics_local;
> FAILED: Error in semantic analysis: Line 1:23 Table not found 'dem'
>
>
> If i create the target table first, then things are ok.  But is that
> necessary?
>
> hive> create table dem(uid int, age string, gender string, dlocation
> string,
>     > children string, home_market_value string, home_owner_status string,
> home_property_type string,
>     > household_income string, length_of_residence string, marital_status
> string)
>     > ;
> OK
> hive> insert overwrite table dem select * from
> demographics_local;
> Total MapReduce jobs = 2
> Launching Job 1 out of 2
> ...
> Table default.dem stats: [num_partitions: 0, num_files: 1, num_rows: 0,
> total_size: 9946167, raw_data_size: 0]
> 301399 Rows loaded to dem
> OK
>
>
>
>
>