LOCATION and MANAGEDLOCATION clauses

Before upgrading, your Hive version might have supported using the LOCATION clause in queries to create either managed or external tables or databases for managed and external tables. After upgrading, Hive stores managed and external tables in separate HDFS locations. CREATE TABLE limits the use of the LOCATION clause, and consequently requires a change to your queries. Hive in CDP also supports a new location-related clause.

External table limitation for creating table locations

Hive assigns a default location in the warehouse to managed tables. In CDP, Hive does not allow the LOCATION clause in queries to create a managed table. Using this clause, you can specify a location only when creating external tables. For example:

CREATE EXTERNAL TABLE my_external_table (a string, b string)  
ROW FORMAT SERDE 'com.mytables.MySerDe' 
WITH SERDEPROPERTIES ( "input.regex" = "*.csv")
LOCATION '/warehouse/tablespace/external/hive/marketing';

Table MANAGEDLOCATION clause

In CDP, Hive has been enhanced to include a MANAGEDLOCATION clause as shown in the following syntax:

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
  [COMMENT database_comment]
  [LOCATION external_table_path]
  [MANAGEDLOCATION managed_table_directory_path]
  [WITH DBPROPERTIES (property_name=property_value, ...)];

In the MANAGEDLOCATION clause, you specify a top level directory for managed tables when creating a Hive database. Do not set LOCATION and MANAGEDLOCATION to the same HDFS path.