Cloudera Docs

Locating Hive tables and changing the location

You need to know where Hive stores tables on your object store or file system. Also, right after installing Hive-on-Tez, you might also want to change the warehouse location.

New tables that you create in CDP are stored in either the Hive warehouse for managed tables or the Hive warehouse for external tables. The following default warehouse locations are in the HDFS file system:
  • /warehouse/tablespace/managed/hive
  • /warehouse/tablespace/external/hive

Managed tables reside in the managed tablespace, which only Hive can access. By default, Hive assumes external tables reside in the external tablespace.

To determine the managed or external table type, you can run the DESCRIBE EXTENDED table_name command.

You need to set Ranger policies to access external tables in the object store or file system, such as HDFS.

The capability to change the location of the Hive warehouse is intended for use immediately after installing the service. You can change the location of the warehouse using the Hive Metastore Action menu in Cloudera Manager as described in the following steps:
  1. In Cloudera Manager, click Clusters > Hive > Action Menu > Create Hive Warehouse Directory.
  2. In Cloudera Manager, click Clusters > Hive (the Hive Metastore service) > Configuration, and change the hive.metastore.warehouse.dir property value to the path for the new Hive warehouse directory.
  3. Click Hive > Hive > Action Menu > Create Hive Warehouse External Directory.
  4. Change the hive.metastore.warehouse.external.dir property value to the path for the Hive warehouse external directory.
  5. Configure Ranger Hadoop SQL policy to access the URL of the directory on the object store, such as S3 or Ozone, or file system, such as HDFS.