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)