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 <bejoy...@yahoo.com> > 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 <java...@gmail.com> > *To:* user@hive.apache.org > *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 > > > > >