[jira] [Commented] (METAMODEL-1194) Stop supporting primary keys with Hive tables

["Gerard Dellemann (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/METAMODEL-1194?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16594722#comment-16594722 ] 

Gerard Dellemann commented on METAMODEL-1194:
---------------------------------------------

During my investigation I've researched 2 possible solutions:
 # We can't change how 'Create table' works. We could add a method called usesPrimaryKeys to HiveQueryRewriter (false) and DefaultQueryRewriter (true).
JdbcCreateTableBuilder.createSqlStatement(...). method should check that new method.
 # We could also update the current version of Hive from 1.2.1 to 2.1.0 or higher in MetaModel. Hive allows for non-validated primary and foreign keys since that version:
> StackOverflow explaining this:
[https://stackoverflow.com/questions/50141476/how-does-primary-key-and-foreign-key-constraints-works-in-hive] 
_Note that the primary keys would still not be supported. The key SQL statement part would just be accepted, but ignored._
> DDL of Hive:
[https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-CreateTableCreate/Drop/TruncateTable]

I've first tried updating the version of Hive (option 2). Unfortunately, this didn't work. The key part of the statement is probably still not according to the HQL standard. So I've implemented the first solution. It would have been better if it you couldn't adjust the MutableColumn's class variable _primaryKey to true with Hive datastores, but this adjustment is way to big for this issue.

> Stop supporting primary keys with Hive tables
> ---------------------------------------------
>
>                 Key: METAMODEL-1194
>                 URL: https://issues.apache.org/jira/browse/METAMODEL-1194
>             Project: Apache MetaModel
>          Issue Type: Bug
>            Reporter: Gerard Dellemann
>            Priority: Minor
>
> Hive tables don't have primary (or foreign) keys. Hive tables get created using a JDBC driver. Other types of datastores, that also use JDBC, do support this key. Right now it's not prevented that a MetaModel user can try to create a Hive table containing a primary key. This will result in a Exception being thrown:
> {code:java}
> ERROR 14:58:33 JdbcUtils - Could not execute create table statement: CREATE TABLE default.mytable (ID INT, myfield STRING, PRIMARY KEY(ID)): Error while compiling statement: FAILED: ParseException line 1:69 cannot recognize input near ')' '<EOF>' '<EOF>' in enable specification, Error code=40000, SQL state=42000
> org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 1:69 cannot recognize input near ')' '<EOF>' '<EOF>' in enable specification
> 	at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:256)
> 	at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:242)
> 	at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:254)
> 	at org.apache.hive.jdbc.HiveStatement.executeUpdate(HiveStatement.java:406)
> 	at org.apache.metamodel.jdbc.JdbcCreateTableBuilder.execute(JdbcCreateTableBuilder.java:61)
> {code}
> Basically the exception means that it doesn't understand the primary key part of the statement.
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)