Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 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
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
>
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB