An extended version of SYSCS_UTIL.IMPORT_DATA that enables you to import data into
specific columns without locking the target table and/or to use a custom import process. Use
this procedure during long import operations when you require continued access to the table.
You specify the subset of columns either by providing the list of column names an
argument to the procedure, or by including the list of column names as the first
line of the input file. Columns that are provided as procedure arguments override
any columns specified in the input file. If a list of columns is not specified
either using arguments or the input file, then an exception is thrown.
If you execute this procedure on a connection that sets the skip-constraint-checks
property to true, then GemFire XD uses the PUT INTO
DML syntax instead of INSERT to import the data.
In addition, GemFire XD ignores all primary key, foreign key, and unique
Note: If you create a schema, table, or column name as a non-delimited identifier, you
must pass the name to the import procedure using all uppercase characters. If you
created a schema, table, or column name as a delimited identifier, you must pass the
name to the import procedure using the same case that was used when it was created.
SYSCS_UTIL.IMPORT_DATA_EX (IN schemaName VARCHAR(128),
IN tableName VARCHAR(128), IN insertColumnList VARCHAR(32672),
IN columnIndexes VARCHAR(32672), IN fileName VARCHAR(32672),
IN columnDelimiter CHAR(1), IN characterDelimiter CHAR(1),
IN codeset VARCHAR(128), IN replace SMALLINT,
IN lockTable SMALLINT, IN numThreads INTEGER, IN caseSensitiveNames SMALLINT,
IN importClassName VARCHAR(32672), IN errorFile VARCHAR(32672))
No result is returned from the procedure.
- An input argument of type VARCHAR(128) that specifies the schema of the
table into which the procedure imports data. Pass a NULL value to use the
default schema name.
- An input argument of type VARCHAR (128) that specifies the table name of the
table into which the procedure imports data. This table cannot be a system
table or a declared temporary table. Passing a null will result in an error.
- An input argument of type VARCHAR (32762) that specifies the individual
column names (separated by commas) of the table into which the procedure
imports data. Pass a NULL value if you instead specified the list of column
names as the first line of the input file.
Note: If you pass a NULL value for
insertColumnList and the input file does not contain the column list,
then procedure execution fails with an exception.
- An input argument of type VARCHAR (32762) that specifies the indexes
(numbered from 1 and separated by commas) of the input data fields to
import. Passing a NULL value uses all of the input data fields in the file.
- An input argument of type VARCHAR(32672) that specifies the file that
contains the data to import. If you do not specify a path, the current
working directory is used. Passing a NULL value results in an error.
Note: If you execute this procedure from a thin client connection, the
input file must directly accessible by GemFire XD member to which you
are connected. If you want to import a local file that is not available
to existing GemFire XD members, start a local peer client from which you
execute the import procedure.
- An input argument of type CHAR(1) that specifies a column delimiter. The
specified character is used in place of a comma to signal the end of a
column in the input file. Passing a NULL value will use the default value;
the default value is a comma (,).
- An input argument of type CHAR(1) that specifies a character delimiter. The
specified character is used in place of double quotation marks to enclose a
character string in the input file. Passing a NULL value will use the
default value; the default value is a double quotation mark (").
- Input argument of type VARCHAR(128) that specifies the code set
of the data in the input file. The name of the code set should be one of the
Java-supported character encodings. Data is converted from the specified code
set to the database code set (utf-8). Passing a NULL value interprets the data
file in the same code set as the JVM in which it is being executed.
- A input argument of type SMALLINT. A non-zero value runs the procedure in
REPLACE mode, while a value of zero runs the procedure in INSERT mode.
REPLACE mode uses the PUT INTO DML syntax to insert rows without first
checking primary key constraints. The table definition and the index
definitions are not changed. INSERT mode adds the imported data to the table
using the standard INSERT syntax, checking existing primary key values if
necessary. Passing a NULL value causes an error.
If you execute this
procedure over a connection that sets the skip-constraint-checks
property to true, then all inserts
use the PUT INTO
DML syntax regardless of what value
you set for the REPLACE option.
- Input argument of type SMALLINT. With a non-zero value, this
procedure operates similarly to
and it locks the table during the import operation. Specify a value of zero to
prevent GemFire XD from locking the table during the import operation.
- Specifies the number of threads to use for the import process.
Each thread processes a different portion of the file in parallel, and performs
a bulk insert to the GemFire XD system.
- Input argument of type SMALLINT. With a non-zero value, GemFire XD
treats the SCHEMANAME and TABLENAME arguments as case-sensitive. With a zero
value, GemFire XD treats the schema and table names as case insensitive.
- The argument can specify a custom class that extends com.pivotal.gemfirexd.load.Import. You must install
the class to the GemFire XD distributed system using install-jar before you execute this procedure; see
Storing and Loading JAR Files in GemFire XD. The specified class overrides the default import process, and can
perform on-the-fly data conversions from external formats to formats that
GemFire XD supports. If you specify NULL for this argument, then GemFire XD
uses the default Import class.
- Specifies an error file in which the procedure logs any data formatting
errors that may occur during the import operation. Keep in mind that the
import table operation only logs (and then skips) data formatting errors
that occur during the import. An import operation may still terminate before
completion due to system errors or table constraint errors. (You can
optionally skip over constraint errors by setting the skip-constraint-checks connection property.)
- You can use the contents of a generated error file to try to correct and
reapply the failed import operations at a later time.
The following example imports some of the data fields from a delimited data file called
into the APP.STAFF
table. The import
procedure uses the default import implementation with 2 threads, and does not lock
the target table:
('APP', 'STAFF', null, '1,3,4', 'data.del', null, null, null, 0, 0, 2, 0, null, null)