Parent topic: Using Apache DdlUtils to Import Data
Exporting and Importing Data with GemFire XD / Using Apache DdlUtils to Import Data

Export, Alter, and Import a Database Schema Using DdlUtils

Use DdlUtils to export, modify, and import data.

Procedure
  1. Download and install Apache Ant if it is not already available on your system.
  2. Ensure that your CLASSPATH includes the JAR file that contains the third-party database's JDBC driver.
  3. Read the available DdlUtils documentation to understand the various Ant tasks. Documentation is installed in the ddlutils/DdlUtils-1.0-doc.zip file and is also available at the DdlUtils project site. This step is not required if you only want to export DDL from a schema and then apply it to GemFire XD.
  4. Make sure that you can connect to the external database and to GemFire XD servers. You will need to supply the connection URL and properties for both databases when using DdlUtils.
  5. Edit the build.xml file in the ddlutils/example directory to specify the JDBC connection information for both GemFire XD and the database from which you are importing data. The example build file contains sample entries for GemFire XD and MySQL. You will need to change each occurrence of the JDBC URLs. The build.xml contents are shown in the Example build.xml File.
  6. By default, the usedelimitedsqlidentifiers property is set to false, which means that tables, columns, and other object names are simple references with no need to place the names in quotation marks. However, if your schema contains table or column names with embedded spaces, change this property to "true" and use single quotation marks around the table and column names each time you reference them.
  7. 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 schemapattern property to exclude schemas that contain incompatible data types.
  8. In the ddlutils/example directory, run the following command to run the three Ant tasks that generate a SQL DDL script that is compatible with GemFire XD syntax: 
    ant writeDDLToXML createDBFromXML writeDDLToSQL
  9. Edit the CREATE TABLE statements in the resulting script to use GemFire XD-specific clauses. For example use syntax to use colocation for partitioning, persist tables, associate tables with gateways, and so forth.
  10. Use the gfxd utility to run the modified script in GemFire XD. For example: 
    gfxd
connect client 'localhost:1527';
run 'db-schema1.sql';
exit;
  11. To import data from the database, use the ImportDataToDB target in the example build.xml file.
  12. If necessary, manually add triggers, views, and Java stored procedures in the GemFire XD database.
Parent topic: Using Apache DdlUtils to Import Data