Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Threaded View
Hive, mail # user - Create table like with partitions


Copy link to this message
-
Re: Create table like with partitions
Owen O'Malley 2013-06-10, 14:06
You need to create the partitioned table and then copy the rows into it.

create table foo_staging (int x, int y);

create table foo(int x) partitioned by (int y) clustered by (x) into 16
buckets;

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.enforce.bucketing = true;

insert overwrite table partition (y) select * from foo_staging;
On Mon, Jun 10, 2013 at 6:38 AM, Nitin Pawar <[EMAIL PROTECTED]>wrote:

> If a table is not partitioned and then you want to partition the table on
> the data already written but data is not in partition format, that is not
> doable.
>
> Best approach would be, create a new table definition with the partition
> columns you want.
> turn on the dynamic partitioning system before you load data into new
> table
>
> set hive.exec.dynamic.partition=true;
> set hive.exec.dynamic.partition.mode=nonstrict;
>
> insert overwrite table partitioned(columns) select * from oldtable
>
>
> remove old table
>
> PS: wait for others to add more suggestions. I may be very well wrong in
> suggesting this
>
>
> On Mon, Jun 10, 2013 at 7:01 PM, Peter Marron <
> [EMAIL PROTECTED]> wrote:
>
>>  Hi,****
>>
>> ** **
>>
>> Using hive 0.10.0 over hadoop 1.0.4****
>>
>> ** **
>>
>> I have a (non-partitioned) table with loads of columns.****
>>
>> I would like to create a partitioned table with the same set of columns.*
>> ***
>>
>> So the approach that I have been taking is to use “CREATE TABLE copy LIKE
>> original;”****
>>
>> then I can use ALTER TABLE to change the location and the INPUTFORMAT****
>>
>> and the OUTPUTFORMAT and the SERDE and properties and pretty much****
>>
>> everything else. However I don’t seem to be able to make it partitioned.*
>> ***
>>
>> Sure I can add partitions if it’s already partitioned but I don’t seem***
>> *
>>
>> to be able to make it partitioned if it’s not already. I get errors like
>> this:****
>>
>> ** **
>>
>> hive> ALTER TABLE customerShortValues ADD PARTITION (aid='1') LOCATION
>> 'E7/phase2/values/aid=1';****
>>
>> FAILED: Error in metadata: table is not partitioned but partition spec
>> exists: {aid=1}****
>>
>> FAILED: Execution Error, return code 1 from
>> org.apache.hadoop.hive.ql.exec.DDLTask****
>>
>> ** **
>>
>> So, I guess that I could create the table I want by hand copying over all
>> the****
>>
>> column definitions. But is there an easier way?****
>>
>> ** **
>>
>> Z****
>>
>
>
>
> --
> Nitin Pawar
>