[IMPALA-10172] Support Hive metastore managed locations for databases

• Type: Improvement
• Status: Resolved
• Priority: Major
• Resolution: Implemented
• Fix Version/s: Impala 4.0.0
• Epic Color: ghx-label-6
• Assignee: Csaba Ringhofer
• Reporter: Tristan Stevens
• Created: 17/Sep/20 12:11
• Updated: 16/Nov/20 18:56
• Resolved: 07/Oct/20 19:56

Description

In Hive 3 a database can have both managed and (unmanaged) locations.
Hive DDL syntax is as follows:

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

ALTER (DATABASE|SCHEMA) database_name SET MANAGEDLOCATION hdfs_path;

Right now, Impala does not appear to support this syntax.

Also the DESCRIBE FORMATTED and DESCRIBE EXTENDED statements should display both the LOCATION and MANAGEDLOCATION.

Example:

impala-shell -i host-2.user1-c5.my.example.com -d default -k --ssl --ca_cert=/opt/cloudera/security/pki/chain.pem
Starting Impala Shell using Kerberos authentication
Using service name 'impala'
SSL is enabled
Opened TCP connection to host-2.user1-c5.my.example.com:21000
Connected to host-2.user1-c5.my.example.com:21000
Server version: impalad version 3.4.0-SNAPSHOT RELEASE (build 25402784335c39cc24076d71dab7a3ccbd562094)
Query: use `default`
***********************************************************************************
Welcome to the Impala shell.
(Impala Shell v3.4.0-SNAPSHOT (2540278) built on Wed Aug  5 11:07:32 UTC 2020)

To see a summary of a query's progress that updates in real-time, run 'set
LIVE_PROGRESS=1;'.
***********************************************************************************
Query: use `default`
[host-2.user1-c5.my.example.com:21000] default> create database dbnew4 LOCATION 'hdfs://host-1.user1-c5.my.example.com:8020/data/dbnewnew/external' MANAGEDLOCATION 'hdfs://host-1.user1-c5.my.example.com:8020/data/dbnewnew/managed';
Query: create database dbnew4 LOCATION 'hdfs://host-1.user1-c5.my.example.com:8020/data/dbnewnew/external' MANAGEDLOCATION 'hdfs://host-1.user1-c5.my.example.com:8020/data/dbnewnew/managed'
ERROR: ParseException: Syntax error in line 1:
...0/data/dbnewnew/external' MANAGEDLOCATION 'hdfs://ccyc...
                             ^
Encountered: IDENTIFIER
Expected: AS, CACHED, PARTITION, TBLPROPERTIES, UNCACHED

CAUSED BY: Exception: Syntax error

[host-2.user1-c5.my.example.com:21000] default> alter database dbnewnew SET MANAGEDLOCATION='hdfs://host-1.user1-c5.my.example.com:8020/data/dbnewnew/managed';
Query: alter database dbnewnew SET MANAGEDLOCATION='hdfs://host-1.user1-c5.my.example.com:8020/data/dbnewnew/managed'
ERROR: ParseException: Syntax error in line 1:
...newnew SET MANAGEDLOCATION='hdfs://host-1.user1-...
                             ^
Encountered: =
Expected: ROLE, IDENTIFIER

CAUSED BY: Exception: Syntax error

[host-2.user1-c5.my.example.com:21000] default> describe database formatted db_cust_loc3 ;
Query: describe database formatted db_cust_loc3
+--------------+-------------------------------------------------------------------+---------+
| name         | location                                                          | comment |
+--------------+-------------------------------------------------------------------+---------+
| db_cust_loc3 | hdfs://host-1.user1-c5.my.example.com:8020/data/db_cust_loc3 |         |
| Owner:       |                                                                   |         |
|              | admin                                                             | USER    |
+--------------+-------------------------------------------------------------------+---------+
Fetched 3 row(s) in 0.03s
[host-2.user1-c5.my.example.com:21000] default> describe database extended db_cust_loc3 ;
Query: describe database extended db_cust_loc3
+--------------+-------------------------------------------------------------------+---------+
| name         | location                                                          | comment |
+--------------+-------------------------------------------------------------------+---------+
| db_cust_loc3 | hdfs://host-1.user1-c5.my.example.com:8020/data/db_cust_loc3 |         |
| Owner:       |                                                                   |         |
|              | admin                                                             | USER    |
+--------------+-------------------------------------------------------------------+---------+
Fetched 3 row(s) in 0.01s
[host-2.user1-c5.my.example.com:21000] default>

Issue Links

• relates to: IMPALA-462 ALTER DATABASE support to rename or move database