[HIVE-24396] [New Feature] Add data connector support for remote datasources

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Hive
• Labels: pull-request-available
• Assignee: Naveen Gangam
• Reporter: Naveen Gangam
• Created: 17/Nov/20 00:45
• Updated: 17/Nov/22 08:47
• Resolved: 15/Apr/21 14:29

Description

This feature work is to be able to support in Hive Metastore to be able to configure data connectors for remote datasources and map databases. We currently have support for remote tables via StorageHandlers like JDBCStorageHandler and HBaseStorageHandler.

Data connectors are a natural extension to this where we can map an entire database or catalogs instead of individual tables. The tables within are automagically mapped at runtime. The metadata for these tables are not persisted in Hive. They are always mapped and built at runtime.

With this feature, we introduce a concept of type for Databases in Hive. NATIVE vs REMOTE. All current databases are NATIVE. To create a REMOTE database, the following syntax is to be used
CREATE REMOTE DATABASE remote_db USING <dataconnector> WITH DCPROPERTIES (....);

Will attach a design doc to this jira.

Issue Links

• breaks: HIVE-25571 Fix Metastore script for Oracle Database
• depends upon: HIVE-26245 Fix some issues for data connector authorization
• links to: GitHub Pull Request #2037

Sub-Tasks

• Create table in REMOTE db should fail: Closed
• Move create/drop/alter table to the provider interface: Closed
• Support case-sensitivity for tables in REMOTE database.: In Progress
• Implement connector provider for Derby DB: Closed
• Add schema changes for MSSQL: Closed
• Add a generic JDBC implementation that can be used to other JDBC DBs: Open
• Provide CachedStore implementation for dataconnectors: Open
• Evaluate the need to have directSQL implementation for data connectors: Open
• Throw error when respective connector JDBC jar is not present in the lib/ path.: Closed
• Add implementation for a 'hive' connector provider: Open
• [Evaluate] Dataconnector URL validation on create: Open
• [Evaluate] if ReplicationSpec is needed for DataConnectors.: Closed
• Consider use of lambda expressions in formatters.: Open
• Reject location and managed locations in DDL for REMOTE databases.: Closed
• Implement connector provider for MSSQL and Oracle: Closed
• Implement List<Table> getTables() for existing connectors.: Closed
• Add hive authorization support for Data connectors.: Closed
• Drop/Alter table in REMOTE db should fail: Closed
• Implement Connector Provider for Amazon Redshift: In Progress
• Detect timed out connections for providers and auto-reconnect: Closed
• MySQL's bit datatype is default to void datatype in hive: Open
• Schema upgrade for MSSQL fails when adding TYPE column in DBS table: Closed
• Drop data connector with argument ifNotExists(true) should not throw NoSuchObjectException: Closed
• HMSHandler get_all_tables method can not retrieve tables from remote database: Closed
• Improve data connector cache: Closed
• Implement JDBC Connector for HiveServer: Closed
• Support VIEWs in the metadata federation: Open
• [Postgres] Use schema names instead of db names: Open
• [Athena] Add connector for Amazon Athena: Open
• SHOW TABLES with invalid connector, giving 0 results, instead of failing: Resolved
• Drop remote database succeeds but fails while deleting data under: Resolved