Use DdlUtils to export, modify, and import data.
Procedure
- Download and install
Apache Ant if
it is not already available on your system.
- Ensure that your CLASSPATH
includes the JAR file that contains the third-party database's JDBC driver.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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
- 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.
- Use the
gfxd utility to run the modified script in GemFire XD.
For example:
gfxd
connect client 'localhost:1527';
run 'db-schema1.sql';
exit;
- To import data from the
database, use the
ImportDataToDB target in the example
build.xml file.
- If necessary, manually add
triggers, views, and Java stored procedures in the GemFire XD database.