[
https://issues.apache.org/jira/browse/HIVE-24396?focusedWorklogId=572571&page=com.atlassian.jira.plugin.system.issuetabpanels:worklog-tabpanel#worklog-572571
]
ASF GitHub Bot logged work on HIVE-24396:
-----------------------------------------
Author: ASF GitHub Bot
Created on: 26/Mar/21 09:53
Start Date: 26/Mar/21 09:53
Worklog Time Spent: 10m
Work Description: vnhive commented on a change in pull request #2037:
URL: https://github.com/apache/hive/pull/2037#discussion_r602144439
##########
File path:
ql/src/java/org/apache/hadoop/hive/ql/ddl/dataconnector/alter/properties/AlterDataConnectorSetPropertiesOperation.java
##########
@@ -0,0 +1,49 @@
+/*
Review comment:
I verified here https://issues.apache.org/jira/browse/HIVE-24396 that
there are no issues with ALTER. In case I missed an already existing issue, I
am really sorry, you can ignore my comment.
I tried the following,
1. Created a connector with the wrong user name
2. Created a database USING the above connector
3. show tables
4. select from table (failed as expected)
5. altered the connector to use the correct user name
6. select from table (failed with same error)
7. created a new database USING the connector
8. show tables
9. select from table (failed with same error).
To ensure nothing was missing from my end, I tried creating a database
pointing to a connector having the username correct in the first place
and it worked fine.
Please find the detailed SQL queries below,
Connectors
----------
0: jdbc:hive2://> show connectors;
+--------------------+
| connector_name |
+--------------------+
| derby_connector_4 |
| derby_connector_5 |
+--------------------+
Connector Description
---------------------
0: jdbc:hive2://> describe connector extended derby_connector_4;
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
| name | type | url
| owner_name | owner_type | comment | parameters
|
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
| derby_connector_4 | derby | jdbc:derby:./target/tmp/junit_metastore_db
| narayanan | USER | | {hive.sql.dbcp.password=,
hive.sql.dbcp.username=APP} |
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
1 row selected (0.053 seconds)
0: jdbc:hive2://> describe connector extended derby_connector_5;
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
| name | type | url
| owner_name | owner_type | comment | parameters
|
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
| derby_connector_5 | derby | jdbc:derby:./target/tmp/junit_metastore_db
| narayanan | USER | | {hive.sql.dbcp.password=,
hive.sql.dbcp.username=root} |
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
1 row selected (0.056 seconds)
This Works
----------
0: jdbc:hive2://> CREATE REMOTE DATABASE db_derby_10 USING derby_connector_4
with DBPROPERTIES("connector.remoteDbName"="junit_metastore_db");
21/03/26 14:25:40 [HiveServer2-Background-Pool: Thread-344]: WARN
exec.DDLTask: metastore.warehouse.external.dir is not set, falling back to
metastore.warehouse.dir. This could cause external tables to use to managed
tablespace.
No rows affected (0.028 seconds)
0: jdbc:hive2://> use db_derby_10;
No rows affected (0.014 seconds)
0: jdbc:hive2://> show tables;
+-----------+
| tab_name |
+-----------+
| TBL1 |
| TBL2 |
+-----------+
2 rows selected (0.049 seconds)
0: jdbc:hive2://> select * from TBL1;
+---------+
| tbl1.i |
+---------+
| 2 |
+---------+
1 row selected (0.093 seconds)
This Does not Work
------------------
0: jdbc:hive2://> CREATE REMOTE DATABASE db_derby_11 USING derby_connector_5
with DBPROPERTIES("connector.remoteDbName"="junit_metastore_db");
21/03/26 14:26:39 [HiveServer2-Background-Pool: Thread-367]: WARN
exec.DDLTask: metastore.warehouse.external.dir is not set, falling back to
metastore.warehouse.dir. This could cause external tables to use to managed
tablespace.
No rows affected (0.03 seconds)
0: jdbc:hive2://> use db_derby_11;
No rows affected (0.014 seconds)
0: jdbc:hive2://> show tables;
+-----------+
| tab_name |
+-----------+
| TBL1 |
| TBL2 |
+-----------+
2 rows selected (0.047 seconds)
0: jdbc:hive2://> select * from TBL1;
21/03/26 14:26:54 [47bb12e6-ef2a-4f82-825e-d4d1f1d37148 main]: ERROR
dao.GenericJdbcDatabaseAccessor: Error while trying to get column names.
java.sql.SQLSyntaxErrorException: Schema 'ROOT' does not exist
0: jdbc:hive2://> alter connector derby_connector_5 set
DCPROPERTIES("hive.sql.dbcp.username"="APP","hive.sql.dbcp.password"="");
No rows affected (0.039 seconds)
0: jdbc:hive2://> describe connector extended derby_connector_5;
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
| name | type | url
| owner_name | owner_type | comment | parameters
|
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
| derby_connector_5 | derby | jdbc:derby:./target/tmp/junit_metastore_db
| narayanan | USER | | {hive.sql.dbcp.password=,
hive.sql.dbcp.username=APP} |
+--------------------+--------+---------------------------------------------+-------------+-------------+----------+----------------------------------------------------+
1 row selected (0.05 seconds)
0: jdbc:hive2://> describe database db_derby_8;
21/03/26 14:22:43 [47bb12e6-ef2a-4f82-825e-d4d1f1d37148 main]: WARN
lazy.LazyStruct: Extra bytes detected at the end of the row! Ignoring similar
problems.
+-------------+----------+-----------+------------------+-------------+-------------+--------------------+---------------------+
| db_name | comment | location | managedlocation | owner_name |
owner_type | connector_name | remote_dbname |
+-------------+----------+-----------+------------------+-------------+-------------+--------------------+---------------------+
| db_derby_8 | | | | narayanan | USER
| derby_connector_5 | junit_metastore_db |
+-------------+----------+-----------+------------------+-------------+-------------+--------------------+---------------------+
1 row selected (0.058 seconds)
0: jdbc:hive2://> use db_derby_8;
No rows affected (0.014 seconds)
0: jdbc:hive2://> show tables;
+-----------+
| tab_name |
+-----------+
| TBL1 |
| TBL2 |
+-----------+
2 rows selected (0.065 seconds)
0: jdbc:hive2://> select * from TBL1;
21/03/26 14:23:19 [47bb12e6-ef2a-4f82-825e-d4d1f1d37148 main]: ERROR
dao.GenericJdbcDatabaseAccessor: Error while trying to get column names.
java.sql.SQLSyntaxErrorException: Schema 'ROOT' does not exist
0: jdbc:hive2://> CREATE REMOTE DATABASE db_derby_9 USING derby_connector_5
with DBPROPERTIES("connector.remoteDbName"="junit_metastore_db");
21/03/26 14:23:56 [HiveServer2-Background-Pool: Thread-282]: WARN
exec.DDLTask: metastore.warehouse.external.dir is not set, falling back to
metastore.warehouse.dir. This could cause external tables to use to managed
tablespace.
No rows affected (0.021 seconds)
0: jdbc:hive2://> use db_derby_9;
No rows affected (0.023 seconds)
0: jdbc:hive2://> show tables;
+-----------+
| tab_name |
+-----------+
| TBL1 |
| TBL2 |
+-----------+
2 rows selected (0.05 seconds)
0: jdbc:hive2://> select * from TBL1;
21/03/26 14:24:25 [47bb12e6-ef2a-4f82-825e-d4d1f1d37148 main]: ERROR
dao.GenericJdbcDatabaseAccessor: Error while trying to get column names.
java.sql.SQLSyntaxErrorException: Schema 'ROOT' does not exist
##########
File path:
ql/src/java/org/apache/hadoop/hive/ql/ddl/dataconnector/alter/url/AlterDataConnectorSetUrlOperation.java
##########
@@ -0,0 +1,65 @@
+/*
Review comment:
I verified here https://issues.apache.org/jira/browse/HIVE-24396 that
there are no issues with ALTER. In case I missed an already existing
issue, I am really sorry, you can ignore my comment.
I tried the following,
1. Altered a connector to point to the wrong database URL.
2. show tables on an existing database still returns the original tables
3. created a new database with the connector
4. show tables on the new database still returns the original tables.
0: jdbc:hive2://> describe connector extended derby_connector_5;
+--------------------+--------+----------------------+-------------+-------------+----------+----------------------------------------------------+
| name | type | url | owner_name |
owner_type | comment | parameters |
+--------------------+--------+----------------------+-------------+-------------+----------+----------------------------------------------------+
| derby_connector_5 | derby | jdbc:derby:wrong_db | narayanan | USER
| | {hive.sql.dbcp.password=, hive.sql.dbcp.username=APP} |
+--------------------+--------+----------------------+-------------+-------------+----------+----------------------------------------------------+
1 row selected (0.045 seconds)
0: jdbc:hive2://> describe database db_derby_8;
21/03/26 14:30:38 [47bb12e6-ef2a-4f82-825e-d4d1f1d37148 main]: WARN
lazy.LazyStruct: Extra bytes detected at the end of the row! Ignoring similar
problems.
+-------------+----------+-----------+------------------+-------------+-------------+--------------------+---------------------+
| db_name | comment | location | managedlocation | owner_name |
owner_type | connector_name | remote_dbname |
+-------------+----------+-----------+------------------+-------------+-------------+--------------------+---------------------+
| db_derby_8 | | | | narayanan | USER
| derby_connector_5 | junit_metastore_db |
+-------------+----------+-----------+------------------+-------------+-------------+--------------------+---------------------+
1 row selected (0.048 seconds)
0: jdbc:hive2://> use db_derby_8;
No rows affected (0.013 seconds)
0: jdbc:hive2://> show tables;
+-----------+
| tab_name |
+-----------+
| TBL1 |
| TBL2 |
+-----------+
2 rows selected (0.048 seconds)
0: jdbc:hive2://> CREATE REMOTE DATABASE db_derby_15 USING derby_connector_5
with DBPROPERTIES("connector.remoteDbName"="junit_metastore_db");
21/03/26 15:19:33 [HiveServer2-Background-Pool: Thread-493]: WARN
exec.DDLTask: metastore.warehouse.external.dir is not set, falling back to
metastore.warehouse.dir. This could cause external tables to use to managed
tablespace.
No rows affected (0.018 seconds)
0: jdbc:hive2://> use db_derby_15;
No rows affected (0.015 seconds)
0: jdbc:hive2://> show tables;
+-----------+
| tab_name |
+-----------+
| TBL1 |
| TBL2 |
+-----------+
2 rows selected (0.056 seconds)
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
Issue Time Tracking
-------------------
Worklog Id: (was: 572571)
Time Spent: 8.5h (was: 8h 20m)
> [New Feature] Add data connector support for remote datasources
> ---------------------------------------------------------------
>
> Key: HIVE-24396
> URL: https://issues.apache.org/jira/browse/HIVE-24396
> Project: Hive
> Issue Type: Improvement
> Components: Hive
> Reporter: Naveen Gangam
> Assignee: Naveen Gangam
> Priority: Major
> Labels: pull-request-available
> Time Spent: 8.5h
> Remaining Estimate: 0h
>
> 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.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
