sqoop-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Abraham Elmahrek (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (SQOOP-1823) Sqoop2: Fix Derby schema documenation
Date Tue, 02 Dec 2014 19:26:12 GMT

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

Abraham Elmahrek commented on SQOOP-1823:
-----------------------------------------

[~jarcec], here are a few commands that have helped me:

{code:type=SQL|title="Fetch foreign key column references"}
SELECT C.CONSTRAINTNAME,T.TABLENAME,M.DESCRIPTOR FROM SYS.SYSCONSTRAINTS C JOIN SYS.SYSFOREIGNKEYS
F ON F.CONSTRAINTID=C.CONSTRAINTID JOIN SYS.SYSCONGLOMERATES M ON M.CONGLOMERATEID=F.CONGLOMERATEID
JOIN SYS.SYSTABLES T ON T.TABLEID=C.TABLEID WHERE TYPE='F';
{code}

The above query will fetch foreign key column references. The references are serialized POJOs
with numeric references to column numbers. Use the query below to lookup the column numbers.

{code:type=SQL|title="Fetch a column by column number and table name"}
SELECT T.TABLENAME, COLS.COLUMNNAME FROM SYS.SYSCOLUMNS COLS JOIN SYS.SYSTABLES T ON T.TABLEID=COLS.REFERENCEID
WHERE COLS.COLUMNNUMBER=2 AND T.TABLENAME='SQ_CONNECTOR_DIRECTIONS';
{code}

A similar query is possible to retrieve the columns from which the constraint is sourced from:

{code:type=SQL|title="Fetch a constraint columns"}
SELECT C.CONSTRAINTNAME,T.TABLENAME,M.DESCRIPTOR FROM SYS.SYSCONSTRAINTS C JOIN SYS.SYSFOREIGNKEYS
F ON F.CONSTRAINTID=C.CONSTRAINTID JOIN SYS.SYSKEYS K ON K.CONSTRAINTID=F.KEYCONSTRAINTID
JOIN SYS.SYSCONGLOMERATES M ON M.CONGLOMERATEID=K.CONGLOMERATEID JOIN SYS.SYSTABLES T ON T.TABLEID=C.TABLEID
WHERE TYPE='F';
{code}

Though, I believe the above SQL is cut off in "IJ".

> Sqoop2: Fix Derby schema documenation
> -------------------------------------
>
>                 Key: SQOOP-1823
>                 URL: https://issues.apache.org/jira/browse/SQOOP-1823
>             Project: Sqoop
>          Issue Type: Bug
>          Components: sqoop2-derby-repository
>    Affects Versions: 1.99.4
>            Reporter: Abraham Elmahrek
>            Assignee: Abraham Elmahrek
>             Fix For: 1.99.5
>
>         Attachments: SQOOP-1823.0.patch
>
>
> There are a few places where there foreign keys are inaccurately listed. For example:
> {noformat}
>  *    +------------------------------+
>  *    | SQ_CONNECTOR_DIRECTIONS      |
>  *    +------------------------------+
>  *    | SQCD_ID: BIGINT PK AUTO-GEN  |
>  *    | SQCD_CONNECTOR: BIGINT       |FK SQCD_CONNECTOR(SQC_ID)
>  *    | SQCD_DIRECTION: BIGINT       |FK SQCD_DIRECTION(SQD_ID)
>  *    +------------------------------+
> {noformat}
> In the above snippet, FK SQCD_CONNECTOR(SQC_ID) should be FK _SQ_CONFIGURABLE(SQC_ID).



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)

Mime
View raw message