2

I have an external table, now I want to add partitions to it. I have 224 unique city id's and I want to just write alter table my_table add partition (cityid) location /path; but hive complains, saying that I don't provide anything for the city id value, it should be e.g. alter table my_table add partition (cityid=VALUE) location /path;, but I don't want to run alter table commands for every value of city id, how can I do it for all id's in one go?

This is what hive command line looks like: 

hive> alter table pavel.browserdata add partition (cityid) location '/user/maria_dev/data/cityidPartition';

FAILED: ValidationFailureSemanticException table is not partitioned but partition spec exists: {cityid=null}

leftjoin
  • 36,950
  • 8
  • 57
  • 116
pavel_orekhov
  • 1,657
  • 2
  • 15
  • 37

1 Answers1

2

Partition on physical level is a location (separate location for each value, usually looks like key=value) with data files. If you already have partitions directory structure with files, all you need is to create partitions in Hive metastore, then you can point your table to the root directory using ALTER TABLE SET LOCATION, then use MSCK REPAIR TABLE command. The equivalent command on Amazon Elastic MapReduce (EMR)'s version of Hive is: ALTER TABLE table_name RECOVER PARTITIONS. This will add Hive partitions metadata. See manual here: RECOVER PARTITIONS

If you have only not-partitioned table with data in it's location, then adding partitions will not work because the data needs to be reloaded, you need to:

Create another partitioned table and use insert overwrite to load partition data using dynamic partition load:

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

insert overwrite table2 partition(cityid) 
select col1, ... colN,
       cityid    
  from table1; --partitions columns should be last in the select

This is quite efficient way to reorganize your data.

After this you can delete source table and rename your target table.

leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • I've seen this before. And I've been getting errors for `insert overwrite`. It turns out that in my big data 201 course they messed something up, and instead of working with a 1 gb dataset, I was working with a 24 gb dataset, because of that I was running out of memory, and after I added new disks to hdfs, I started getting weird errors (hortonworks doesn't have any documentation about how to properly add disks to their platform). I will try this on a smaller dataset and let you know if it worked. – pavel_orekhov Dec 28 '18 at 11:51
  • Could you please take a look here? https://stackoverflow.com/questions/53959342/how-do-i-partition-tables-in-hive – pavel_orekhov Dec 28 '18 at 13:29