Hive: Create Table and Partition By
hadoop, hive
Solution
This is precisely why I prefer using external tables in Hive. The table you created is not external (you used `create table` instead of `create external table`). With non-external tables, dropping the table, drops the metadata (name, column names, types, etc.) and the data of the table in HDFS. On the contrary, when an external table is dropped, only the metadata is removed, the data in HDFS sticks around.
You have a few options going forward:
If the cost of import is high and the data is already not partitioned. Keep this table around but create a new table say xyzlogTable_partitioned that will be a partitioned version of this table. You can use Dynamic Partitioning in Hive to populate this new table.
If the cost of import is high but the data is already partitioned; for example say you already have data in separate files for each partition in HDFS. Create a new partitioned table and have a bash script (or equivalent), move (or copy and later delete, if you are conservative) from the HDFS directory corresponding to the un-partitioned table to the directory corresponding to the appropriate partition of the new table.
If import is cheap: drop the entire table. Re-create a new partitioned table and re-import. Many times if the import process is not aware of the partitioning schema (in other words, if the import can't directly push data into appropriate partitions), it's a common use case to have an unpartitioned table (like the one you already have) as a staging table and then use a Hive query or dynamic partitioning to populate a new partitioned table which gets used in subsequent queries of the workflow.
Problem
I have a table with loaded data as following: ``` create table xyzlogTable (dateC string , hours string, minutes string, seconds string, TimeTaken string, Method string, UriQuery string, ProtocolStatus string) row format serde 'org.apache.hadoop.hive.contrib.serde2.RegexSerDe' with serdeproperties( "input.regex" = "(\\S+)\\t(\\d+):(\\d+):(\\d+)\\t(\\S+)\\t(\\S+)\\t(\\S+)\\t(\\S+)", "output.format.string" = "%1$s %2$s %3$s %4$s %5$s %6$s %7$s %8$s") stored as textfile; load data local inpath '/home/hadoop/hive/xyxlogData/' into table xyxlogTable; ``` total row count is found to be more than 3 million. some queries work fine and some get into infinite loop. after seeing that select, group by queries taking long time and sometimes not even returning results, decided to go for partitioning. But both the following statements are failing: ``` create table xyzlogTable (datenonQuery string , hours string, minutes string, seconds string, TimeTaken string, Method string, UriQuery string, ProtocolStatus string) partitioned by (dateC string); ``` FAILED: Error in metadata: AlreadyExistsException(message:Table xyzlogTable already exists) FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask ``` Alter table xyzlogTable (datenonQuery string , hours string, minutes string, seconds string, TimeTaken string, Method string, UriQuery string, ProtocolStatus string) partitioned by (dateC string); ``` FAILED: Parse Error: line 1:12 cannot recognize input 'xyzlogTable' in alter table statement Any idea whats the problem!