[
https://issues.apache.org/jira/browse/IMPALA-2112?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16973505#comment-16973505
]
ASF subversion and git services commented on IMPALA-2112:
---------------------------------------------------------
Commit e0a98df3fabb84cae8c355d046c1aaa14e5bab25 in impala's branch
refs/heads/master from Anurag Mantripragada
[ https://gitbox.apache.org/repos/asf?p=impala.git;h=e0a98df ]
IMPALA-2112: Support primary key/foreign key constraints as part of
create table in Impala.
This is the first of several changes to use informational, unenforced
primary key(pk) and foreign key(fk) specifications in Impala.
The parent JIRA for this effort is IMPALA-3531.
This change adds support for adding pk/fk information during create
table DDLs. There is only limited SQL syntax support as of now and will
add various other SQL styles including ANSI syntax support in later
changes. Currently the only supported way of adding fk/pk information
is after the column definitions. Examples are:
CREATE TABLE pk(col1 INT, col2 STRING, PRIMARY KEY(col1, col2));
CREATE TABLE fk(id INT, col1 INT, col2 STRING, PRIMARY KEY(id),
FOREIGN KEY(col1, col2) REFERENCES pk(col1, col2));
In the current implementation, manual specification of constraint names
is not supported. Internally we use UUIDs for contraint name generation.
Additionally, three constraint states are supported to comply with
Hive's implementation which were taken from Oracle.
DISABLE (default true)
NOVALIDATE (default true)
RELY (default true)
More info here:
https://docs.oracle.com/database/121/DWHSG/schemas.htm#DWHSG9053
These constraints can be optionally specified after each PK/FK
specification like:
CREATE TABLE pk(id INT, PRIMARY KEY(id) DISABLE, NOVALIDATE, RELY);
However, a specification like this will throw an analysis errror:
CREATE TABLE pk(id INT, PRIMARY KEY(id) ENABLE, VALIDATE, RELY);
Notes:
- toSql support is not fully functional. Observability changes like showing
PK/FK information in DESCRIBE output will be done separately.
- Retrieval of primary keys and foreign keys is currently not supported
in Local Catalog Mode.
Tests:
Added tests to:
- AnalyzeDDLTest#TestCreateTable
- ParserTest#TestCreateTable
- ToSqlTest#TestCreateTable
- Built against both Hive-2 and Hive-3
Change-Id: Id03d8d4d41a2ac1b15e7060e2a013e334d044ee7
Reviewed-on: http://gerrit.cloudera.org:8080/14592
Reviewed-by: Impala Public Jenkins <[email protected]>
Tested-by: Impala Public Jenkins <[email protected]>
> Support primary key/foreign key constraint as part of create table in Impala
> ----------------------------------------------------------------------------
>
> Key: IMPALA-2112
> URL: https://issues.apache.org/jira/browse/IMPALA-2112
> Project: IMPALA
> Issue Type: Sub-task
> Components: Catalog, Frontend
> Affects Versions: Impala 2.2
> Reporter: Marcel Kinard
> Assignee: Anurag Mantripragada
> Priority: Critical
> Labels: planner
>
> These would be advisory, ie, Impala would not attempt to enforce them.
> However, they could be used for cardinality estimation during query planning.
> To be compatible with Hive:
> * We neither enforce or validate integrity constraints. Hence, DISABLE and
> NOVALIDATE options are mandatory.
> * RELY/NORELY is optional. The CBO is expected to use this information when
> a user specifies “RELY”. The default is NORELY.
> * Since we do not yet have UNIQUE in Hive, the FK mentioned must be Primary
> Key column in parent table.
> Support create table syntax like hive does:
> * {{create table pk(id1 integer, id2 integer, }}{{primary key(id1, id2)
> DISABLE NOVALIDATE);}}
> * {{create table fk(id1 integer, id2 integer, }}{{constraint c1 foreign
> key(id1, id2) references pk(id2, id1) DISABLE NOVALIDATE);}}
> * {{create table T1(id integer, name string, primary key(id) DISABLE
> NOVALIDATE RELY}}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
