Export, Alter, and Import a Database Schema Using GFXD

Use gfxd commands to export the schema and data from a third-party database, and then import the schema and data to GemFire XD.

Procedure
  1. To use the gfxd export commands with a third-party database, you require a JDBC driver and connection URL for the database. Use a Java client application such as SQuirreL SQL to verify that you can connect to the database.

    This procedure uses MySQL as an example datasource. The components necessary to establish a JDBC connection the example server are:
    • Driver JAR file: mysql-connector-java-5.1.18-bin.jar
    • Driver class: com.mysql.jdbc.Driver
    • Connection URL: jdbc:mysql://localhost:3306/gfxddb?user=username&password=password
    To ensure that gfxd can access the JDBC driver class, add the JAR location to your CLASSPATH. For example, open a new command prompt and enter:
    export CLASSPATH=$CLASSPATH:/path/mysql-connector-java-5.1.18-bin.jar
  2. Add the GemFire XD /bin directory to your path if you have not already done so. For example:
    export PATH=$PATH:~/Pivotal_GemFireXD_10_bNNNNN/bin
  3. Use gfxd to export the schema of the third-party data source to a schema SQL file in a format that is compatible with GemFire XD. For example:
    gfxd write-schema-to-sql -file=mysql-schema.sql -to-database-type=gemfirexd
        -driver-class=com.mysql.jdbc.Driver 
        -url=jdbc:mysql://localhost:3306/gfxddb?user=username&password=password
    Note: Databases such as Oracle 11g contain system tables with data types that are incompatible with the DdlUtils 1.1 API. To export schemas or data from these databases, you must use the -schema-pattern option with the gfxd command to exclude schemas that contain incompatible data types. See gfxd write-schema-to-xml.
  4. Edit the CREATE TABLE statements in the resulting schema SQL file to use GemFire XD-specific clauses. For example use syntax to specify colocation for partitioning, persist tables, associate tables with gateways, and so forth.
  5. After editing the SQL script file, use an interactive gfxd session to execute the script in GemFire XD:
    gfxd
    connect client 'localhost:1527';
    run 'mysql-schema.sql';
  6. Pre-allocate buckets for partitioned table data before you import import the data, and exit gfxd:
    call sys.create_all_buckets;
    exit;
  7. To import the data from the third-party datasource, first use these gfxd commands to export both the data and schema to XML files:
    gfxd write-schema-to-xml -file=mysql-schema.xml 
        -driver-class=com.mysql.jdbc.Driver 
        -url=jdbc:mysql://localhost:3306/gfxddb?user=username&password=password
    gfxd write-data-to-xml -file=mysql-data.xml 
        -driver-class=com.mysql.jdbc.Driver 
        -url=jdbc:mysql://localhost:3306/gfxddb?user=username&password=password
  8. Use the gfxd write-data-to-db command and specify both the data XML file and the schema XML file to import the data to GemFire XD:
    gfxd write-data-to-db -files=mysql-data.xml -schema-files=mysql-schema.xml 
        -client-bind-address=localhost -client-port=1527
  9. If necessary, manually add triggers, views, and Java stored procedures in the GemFire XD database.