KaliAnt created HIVE-18223:
------------------------------
Summary: Hive JDBC driver support for retrieval of foreign keys
Key: HIVE-18223
URL: https://issues.apache.org/jira/browse/HIVE-18223
Project: Hive
Issue Type: Wish
Environment: Hortonworks Data Platform 2.6.3
hive-jdbc-2.1.0.2.6.3.0-235-standalone.jar(comes bundled with the HDP
distribution)
Reporter: KaliAnt
I created two sample Hive tables.
Department table:
{code:java}
+-------------------------------+------------------------------------------------------------+-----------------------------+--+
| col_name | data_type
| comment |
+-------------------------------+------------------------------------------------------------+-----------------------------+--+
| # col_name | data_type
| comment |
| | NULL
| NULL |
| id | int
| Surrogate PK is not fun |
| description | string
| |
| code | string
| |
| | NULL
| NULL |
| # Detailed Table Information | NULL
| NULL |
| Database: | ojoqcu
| NULL |
| Owner: | OJOQCU
| NULL |
| CreateTime: | Mon Dec 04 14:29:58 UTC 2017
| NULL |
| LastAccessTime: | UNKNOWN
| NULL |
| Retention: | 0
| NULL |
| Location: |
hdfs://devhadoop/apps/hive/warehouse/ojoqcu.db/department | NULL
|
| Table Type: | MANAGED_TABLE
| NULL |
| Table Parameters: | NULL
| NULL |
| | COLUMN_STATS_ACCURATE
| {\"BASIC_STATS\":\"true\"} |
| | numFiles
| 0 |
| | numRows
| 0 |
| | rawDataSize
| 0 |
| | totalSize
| 0 |
| | transient_lastDdlTime
| 1512397798 |
| | NULL
| NULL |
| # Storage Information | NULL
| NULL |
| SerDe Library: | org.apache.hadoop.hive.ql.io.orc.OrcSerde
| NULL |
| InputFormat: |
org.apache.hadoop.hive.ql.io.orc.OrcInputFormat | NULL
|
| OutputFormat: |
org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat | NULL
|
| Compressed: | No
| NULL |
| Num Buckets: | -1
| NULL |
| Bucket Columns: | []
| NULL |
| Sort Columns: | []
| NULL |
| Storage Desc Params: | NULL
| NULL |
| | serialization.format
| 1 |
| | NULL
| NULL |
| # Constraints | NULL
| NULL |
| | NULL
| NULL |
| # Primary Key | NULL
| NULL |
| Table: | ojoqcu.department
| NULL |
| Constraint Name: | pk_106546857_1512397798103_0
| NULL |
| Column Names: | id
| |
+-------------------------------+------------------------------------------------------------+-----------------------------+--+
{code}
Employee table:
{code:java}
+-------------------------------+----------------------------------------------------------+-----------------------------+--+
| col_name | data_type
| comment |
+-------------------------------+----------------------------------------------------------+-----------------------------+--+
| # col_name | data_type
| comment |
| | NULL
| NULL |
| id | int
| Surrogate PK isn't fun |
| firstname | string
| |
| lastname | string
| |
| dob | date
| |
| departmentid | int
| |
| | NULL
| NULL |
| # Detailed Table Information | NULL
| NULL |
| Database: | ojoqcu
| NULL |
| Owner: | ojoqcu
| NULL |
| CreateTime: | Wed Nov 15 12:37:13 UTC 2017
| NULL |
| LastAccessTime: | UNKNOWN
| NULL |
| Retention: | 0
| NULL |
| Location: |
hdfs://devhadoop/apps/hive/warehouse/ojoqcu.db/employee | NULL
|
| Table Type: | MANAGED_TABLE
| NULL |
| Table Parameters: | NULL
| NULL |
| | COLUMN_STATS_ACCURATE
| {\"BASIC_STATS\":\"true\"} |
| | numFiles
| 0 |
| | numRows
| 0 |
| | rawDataSize
| 0 |
| | totalSize
| 0 |
| | transient_lastDdlTime
| 1510749433 |
| | NULL
| NULL |
| # Storage Information | NULL
| NULL |
| SerDe Library: | org.apache.hadoop.hive.ql.io.orc.OrcSerde
| NULL |
| InputFormat: |
org.apache.hadoop.hive.ql.io.orc.OrcInputFormat | NULL
|
| OutputFormat: |
org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat | NULL
|
| Compressed: | No
| NULL |
| Num Buckets: | -1
| NULL |
| Bucket Columns: | []
| NULL |
| Sort Columns: | []
| NULL |
| Storage Desc Params: | NULL
| NULL |
| | serialization.format
| 1 |
| | NULL
| NULL |
| # Constraints | NULL
| NULL |
| | NULL
| NULL |
| # Primary Key | NULL
| NULL |
| Table: | ojoqcu.employee
| NULL |
| Constraint Name: | pk_133634893_1510749433083_0
| NULL |
| Column Names: | id
| |
+-------------------------------+----------------------------------------------------------+-----------------------------+--+
{code}
I wrote a test code snippet to retrieve the constraints:
{code:java}
DatabaseMetaData databaseMetaData = con.getMetaData();
//PK
ResultSet primaryKeysSet =
databaseMetaData.getPrimaryKeys("ojoqcu","ojoqcu","employee");
while (primaryKeysSet.next()){
System.out.println("PK : "+primaryKeysSet.getString("COLUMN_NAME"));
}
primaryKeysSet.close();
//FK
ResultSet foreignKeysSet =
databaseMetaData.getImportedKeys("ojoqcu","ojoqcu","employee");
while(foreignKeysSet.next()){
System.out.println("primary key column name being imported :
"+foreignKeysSet.getString("PKCOLUMN_NAME"));
System.out.println("foreign key column name :
"+foreignKeysSet.getString("FKCOLUMN_NAME"));
}
foreignKeysSet.close();
//FK
ResultSet fkSet =
databaseMetaData.getExportedKeys("ojoqcu","ojoqcu","department");
while(fkSet.next()){
System.out.println("primary key column name :
"+fkSet.getString("PKCOLUMN_NAME"));
System.out.println("foreign key column name being exported :
"+fkSet.getString("FKCOLUMN_NAME"));
}
fkSet.close();
con.close();
{code}
The output:
{code:java}
PK : id
Exception in thread "main" java.sql.SQLException: Method not supported
at
org.apache.hive.jdbc.HiveDatabaseMetaData.getExportedKeys(HiveDatabaseMetaData.java:330)
at com.my.App.connectHiveUsingZookeeper(App.java:125)
at com.my.App.main(App.java:25)
{code}
I took a quick look at the Hive
[implementation|https://github.com/apache/hive/blob/master/jdbc/src/java/org/apache/hive/jdbc/HiveDatabaseMetaData.java]
of the
[DatabaseMetaData|https://docs.oracle.com/javase/8/docs/api/java/sql/DatabaseMetaData.html]
and I found that:
# The getImportedKeys(...) returns an empty ResutSet
# As obvious from the exception, getExportedKeys(...) is not supported
It would be great if both the primary and foreign keys pertaining to a Hive
table can be retrieved.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
