Create a default directory for managed tables

You can specify a top level directory for managed tables when creating a Hive database in CDP Data Center.

Create a default directory for managed tables only after limiting CREATE DATABASE and ALTER DATABASE statements to users having the Admin role, which has hive service user permissions. Permissions to the managed directory must be limited to the hive service user. In addition to restricting permissions to the hive user, you can further secure managed tables using Ranger fine-grained permissions, such as row-level filtering and column masking.

As Admin, you specify a managed location within the default location specified by the hive.metastore.warehouse.dir configuration property to give managed tables a common location for governance policies. The managed location designates a single root directory for all tenant tables, managed and external.

Setting the metastore.warehouse.tenant.colocation property to true allows a common location for managed tables outside the warehouse root directory, providing a tenant-based common root for setting quotas and other policies. To set this property, in Cloudera Manager use the Hive on Tez safety valve for hive-site.xml as shown below.

Use the following syntax to create a database that specifies a location for managed tables:

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, ...)];

Do not set LOCATION and MANAGEDLOCATION to the same HDFS path.

Use the following syntax to set or change a location for managed tables.

ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION [managed_table_directory_path];

Create a database mydatabase that specifies a top level directory named sales for managed tables.
```
CREATE DATABASE mydatabase MANAGEDLOCATION '/warehouse/tablespace/managed/hive/sales';
```

Change the abc_sales database location to the same location as mydatabase.

ALTER DATABASE abc_sales SET MANAGEDLOCATION '/warehouse/tablespace/managed/hive/sales';

Configure a table location outside the warehouse root directory

In Cloudera Manager, click Clusters > Hive on Tez > Configuration.
In scope, click Hive on Tez (Service-Wide).
In Hive Service Advanced Configuration Snippet (Safety Valve) for hive-site.xml, click +.
In Name, enter metastore.warehouse.tenant.colocation.
In Value, enter true.
Save changes.
In Cloudera Manager Home, restart Hive on Tez.