SemanticException adding partiton Hive table

Question

Attempting to create a partition on a Hive table with the following:

> alter table stock_ticker add if not exists
> partition(stock_symbol='ASP')
> location 'data/stock_ticker_sample/stock_symbol=ASP/'

Which produces the following output

FAILED : SemanticException table is not partitioned but partition spec exists: {stock_symbol=ASP} 

There are no partitions on this table prior to this addition attempt

> show partitions stock_ticker;

which results in

FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. 
Table stock_ticker_sample is not a partitioned table

There is no question that the stock_symbol column exists and is of type string.

The query is what steps need to be taken in order to add this partition?

Beaver

Answer 1

Solution would be to add partitioning info into the definition of stock_ticker table:

CREATE EXTERNAL TABLE stock_ticker (
... 
)
PARTITIONED BY (stock_symbol STRING);

Then easily you can add external data to your table by:

> alter table stock_ticker add if not exists
> partition(stock_symbol='ASP')
> location 'data/stock_ticker_sample/stock_symbol=ASP/'

GL!

Elecampane

It would appear that partitioning must be done on external tables. – Beaver

Is not, I assumed that you wanted to specify the existing location for this partition so making it manageable table might endanger the source data in case of drop. – Elecampane

What I mean is that when performed on an external table pointing to the location of my internal table that the partition operation succeeds. Not that partitioning on internal tables is not possible per se. – Beaver

Specifying location for given partition for internal table would work as well. But generally is a bad idea. For managed tables automatic folder assign seems to be right way to follow. Gl with partitioning stuff! – Elecampane

I got similar error for a external table and couldn't find a reason of why it happens. Ended up removing the table and recreating the table again. Since it was external table it didn't matter much for me to delete and recreate it again. – Titi

Is there anyway of doing this WITHOUT re-creating the table? See also my question: #66822268 – Tinkle