Create a default directory for managed tables

You can specify a top level directory for managed tables when creating a Hive database.

Create a default directory for managed tables only after limiting CREATE DATABASE and ALTER DATABASE statements to users having the Admin role. Only in the Admin role can you ensure that the managed directory has the following correct setup:

Restricts permissions to the directory for managed tables to the hive service user
Complies with other requirements, such as S3Guard authoritative mode for a file system like S3

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 metastore.warehouse.dir to give managed tables a common location for governance policies. You specify a MANAGEDLOCATION in the metastore.warehouse.dir to designate a single root directory for all tenant tables, managed and external.

When turned on, setting metastore.warehouse.tenant.colocation to true allows tables to be in a directory outside the warehouse root directory, providing a tenant-based common root for setting quotas and other policies.

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]
[MANAGEDLOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

ALTER (DATABASE|SCHEMA) database_name SET 
MANAGEDLOCATION
hdfs_path;

Create a database mydatabase that specifyies 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';