x

To import data into a Hyperstage table, use the following MySQL loading command:

LOAD DATA INFILE 'file_name' INTO TABLE tbl_name[FIELDS
[TERMINATED BY 'char']
[ENCLOSED BY 'char']
[ESCAPED BY 'char']
];

where:

file_name

Is the path to the file to be loaded.

tbl_name

Is the name of the table where the data will be loaded.

x

Using either the MySQL or the Hyperstage Loader, you can load data from a remote machine across the network using the LOAD DATA LOCAL INFILE syntax. This allows you to offload potentially heavy ETL processing to a separate server, keeping the Hyperstage server on a dedicated machine. This also allows you to save significant time when transferring large LOAD files over the network, which can typically limit load speed.

For more information, see LOAD DATA INFILE Syntax in the MySQL 5.x Reference Manual.

A few important notes:

• Before importing data using the LOAD DATA LOCAL syntax, be sure you fully understand the security issues. For more details, see Security Issues with LOAD DATA LOCAL in the MySQL 5.x Reference Manual.
• You can disable all LOAD DATA LOCAL statements from the server side by starting mysqld with the --local-infile=0 option.
• For the mysql command line client, enable LOAD DATA LOCAL by specifying the --local-infile[=1] option, or disable it with the --local-infile=0 option. For mysqlimport, local data file loading is off by default. Enable it with the --local or -L option. In any case, successful use of a local load operation requires that the server permits it.
• Some (but not all) Windows GUI tools may work with remote load, even with Linux servers.

To import data into a Hyperstage table from a remote machine across the network, use the following MySQL loading command (for more information about command options, see the Data Loading Guide):

LOAD DATA [LOCAL] INFILE 'file_name' INTO TABLE tbl_name 
[FIELDS 
[TERMINATED BY 'char'] 
[ENCLOSED BY 'char'] 
[ESCAPED BY 'char'] 
]; 

where:

file_name

Is the path to the file to be loaded.

tbl_name

Is the name of the table where the data will be loaded.

If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

Note: Network speeds may limit the load speed. Exceptions and errors in the transfer are handled by the MySQL client, and will behave the same as the MySQL client.

x

To export data from a Hyperstage table, use the following MySQL export command:

SELECT … INTO OUTFILE 'file_name'
FIELDS
[TERMINATED BY 'string']
[ENCLOSED BY 'char']
[ESCAPED BY 'char']]
FROM 'tbl_name';

where:

file_name

Is the path to the file where data will be exported.

tbl_name

Is the name of the table from which the data will be retrieved.

For more information on export syntax, see SELECT Syntax in the MySQL 5.x Reference Manual.

x

Several optional clauses exist for the MySQL LOAD command. All of these clauses are ignored by Hyperstage, with the exception of the FIELDS clause. You can also use the FIELDS clause when exporting data.

You can use the optional FIELDS clause to specify how values are provided in the input file. To use the FIELDS clause, the following must be true:

• The data import format must be defined as variable-length text.

The FIELDS clause is ignored while importing data in binary format.

Within the FIELDS clause, you can use the following subclauses:

• Use the TERMINATED BY subclause to specify the character recognized as the separator (delimiter) between values. By default, a semicolon (;) is assumed to separate values.
• Use the ENCLOSED BY subclause to specify the character that begins and ends each string representing a text value. By default, a double quotation mark (") is assumed to enclose each value. If the text values in the input file do not use any enclosing characters, use the value 'NULL' in the ENCLOSED BY subclause. Note that this is the same as using the empty string '' option in standard MySQL.
• Use the ESCAPED BY subclause to support special characters that may be embedded within text fields.

x

Hyperstage may abort a load when invalid values are found. Certain invalid values, however, can be loaded in Hyperstage. The following rules are used with invalid data:

• If a numeric, date, or time value is invalid, the value is replaced by 0.
• If a NULL value is imported into a column defined as NOT NULL (except for TIMESTAMP columns), it is replaced by 0 (for numerical, date, and time columns) or by an empty string (for string columns).