[jira] Updated: (DERBY-4962) Create a table function which efficiently migrates data out of foreign databases and which can be used for ongoing data integration

["Rick Hillegas (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/DERBY-4962?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Rick Hillegas updated DERBY-4962:
---------------------------------

    Attachment: ForeignTableVTI.java

Attaching ForeignTableVTI.java. This is a RestrictedVTI supporting the bulk-import and data integration use-cases. Here is the class header comment, which explains how to use the machinery. I have tested this VTI against some legacy Cloudscape databases.

-------------------------------------------------------------------------

To use this machinery, first declare the helper procedures which create and drop the foreign views:

    create procedure registerForeignTables
    (
        in foreignSchemaName varchar( 32672 ),
        in foreignDriverName varchar( 32672 ),
        in connectionURL varchar( 32672 ),
        in debug boolean
    )
    language java parameter style java modifies sql data
    external name 'ForeignTableVTI.registerForeignTables';
 
    create procedure deregisterForeignTables
    (
        in foreignSchemaName varchar( 32672 ),
        in foreignDriverName varchar( 32672 ),
        in connectionURL varchar( 32672 ),
        in debug boolean
    )
    language java parameter style java modifies sql data
    external name 'ForeignTableVTI.deregisterForeignTables';

Then create a schema for holding the foreign views and switch to that schema:

    create schema foreignschema;
    set schema foreignschema;

Register views against all of the tables in a foreign schema. In this example, the foreign database is a Cloudscape database and you register views and table functions against all of the tables in its APP schema:

    call app.registerForeignTables
    (
        'APP',
        'COM.cloudscape.core.JDBCDriver',
        'jdbc:cloudscape:olddbs/SalesDatabase',
        true
    );

Now bulk import data from the foreign views using INSERT INTO...SELECT statements. The following example bulk-copies all rows from a foreign table called "salesorders" into a local table by the same name:

    insert into app.salesorders select * from salesorders;

If you need to siphon data out of the foreign database on an ongoing basis, you can restrict the data you SELECT. Note that the local views are backed by RestrictedVTIs. That means that the actual query sent to the foreign database will only involve the columns you SELECT. In addition, the query will include the WHERE clause, provided that it is simple enough (see the javadoc for RestrictedVTI):

    insert into app.neworderids select orderid from salesorders where orderid > 54321;

When you are done with the foreign data, you can drop the views and table functions:

    call app.deregisterForeignTables
    (
        'APP',
        'COM.cloudscape.core.JDBCDriver',
        'jdbc:cloudscape:olddbs/SalesDatabase',
        true
    );


> Create a table function which efficiently migrates data out of foreign databases and which can be used for ongoing data integration
> -----------------------------------------------------------------------------------------------------------------------------------
>
>                 Key: DERBY-4962
>                 URL: https://issues.apache.org/jira/browse/DERBY-4962
>             Project: Derby
>          Issue Type: Improvement
>          Components: Tools
>            Reporter: Rick Hillegas
>         Attachments: ForeignTableVTI.java
>
>
> DDLUtils and other tools are good at copying the shape of foreign schemas into Derby. It would be good to have a tool which efficiently bulk-copies the foreign data into Derby. This would be faster than having to dump the foreign data into flat files and then import those files into Derby. In addition, many data integration applications need to siphon new data out of foreign database on an ongoing basis. A tool based around a RestrictedVTI would be able to push the column projection and WHERE clause into the foreign database in order to speed up the siphoning. I will attach a Restricted VTI which can be used for the following tasks:
> 1) Initial bulk-loading of Derby from a foreign database.
> 2) Efficient on-going data integration.

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.