bdu
Description
The Bulk Data Utility (BDU) is a command line tool that allows you to load data from a delimited text file into a PSQL table. The table and database must already exist.
The BDU, the table, the database, and the PSQL database engine must all be located on the same machine. The delimited text file must be locally accessible by the database engine server through a local drive, mapped drive, mounted folder, or shared folder.
You may use a default delimiter or a user-specified delimiter. The delimiting character must not be contained in the data itself. The following tables list the permissible delimiters.
Table 14 Delimiters for Columns
Delimiter | Indicated By |
Tab | \t (default) |
Any single printable character (control characters are not printable, except null, tab, new line, and carriage return) | (*, A, t, l, and so forth) |
Note PSQL does not support the use of NULL terminator (\0) or double quote (") as column delimiters.
Table 15 Delimiters for Rows
Delimiter | Indicated By |
New line character | \n (default) |
Carriage return | \r |
Carriage return line feed (CR LF) | \r\n |
The BDU supports only the single quote (') and the double quote (") characters as text qualifiers. The data file may contain column values enclosed by single quotes or by double quotes. For example, the following column values are enclosed by double quotes and delimited by the TAB character: "Fred"\t"22"\t"2459"\t"Sales"\t.
The BDU treats consecutive column delimiters as NULL values. If the tool finds consecutive column delimiters, it inserts a NULL value into the column, provided the column is nullable.
No qualifiers are allowed for a NULL value. The following column data inserts a NULL value in the second column. Note that qualifiers are not included for that column: "Fred"\t\t"2459"\t"Sales"\t.
Synopsis
bdu {database_name} {table_name} {data_file}
[-<e|E> max_errors]
[-<r|R> reject_file]
[-<f|F> first_row]
[-<l|L> last_row]
[-<t|T> field_term]
[-<n|N> row_term]
[-<o|O> output_file]
[{-<u|U>
login_id} {-<p|P>
password}]
[-<c|C> encoding]
[-<h|H>]
Note When loading data with BDU into a secured database for which the Btrieve Security policy is set to Mixed, the supplied credentials (user name and password) must match those of a PSQL database user account and an operating system user account.
Parameters
Table 16 Bulk Data Utility Parameters
Parameter | Mandatory/Optional | Default Value | Description |
database_name | Mandatory | | Database name to connect to the local PSQL engine |
table_name | Mandatory | | Name of the table to be populated |
data_file | Mandatory | | Name and location of the delimited text file |
-<e|E> max_errors | Optional | 0 (zero) The BDU exits when the first error is encountered. | The maximum number of errors that the BDU ignores before exiting |
-<r|R> reject_file | Optional | stderr | Name of the file to which to write the rows that failed load. The specified directory must exist. However, if the file does not exist in the specified directory, it will be created. |
-<f|F> first_row | Optional | Row 1 | The first row in the delimited text file with which the load begins. This parameter allows you to skip a header row. For example, if your header row is row 1, set first_row to 2. |
-<l|L> last_row | Optional | End of the source file | Last row in source file (row will be included in load). The load will stop after the end row has been loaded |
-<t|T> field_term | Optional | A character, such as a comma or tab character | Column delimiter in the source file |
-<n|N> row_term | Optional | A new line character | Row delimiter in the source file |
-<o|O> output_file | Optional | stderr | Name of the file to which to write the information and error messages during load. The specified directory must exist. However, if the file does not exist in the specified directory, it will be created. |
-<u|U> login_id | Optional | | User name to connect to the Relational Engine |
-<p|P> password | Optional | | Password to connect to the Relational Engine |
-<c|C> encoding | Optional | The system code page or “ASCII” if the system code page cannot be determined | The valid values for encoding are: •ASCII •UTF-8 •UTF-16LE •UTF-16BE The encoding parameter is not case sensitive and does not require quotes. See
Examples. Note: If a data file contains a byte order mark (BOM), BDU uses the encoding specified by the BOM. That is, if a data file uses a BOM to indicate an encoding of UTF-8, UTF-16LE, or UTF-16BE, BDU uses that encoding regardless of what value you specify for the encoding parameter on the command line. If you provide an invalid value for encoding, the message returned is “Invalid value for command line argument ‘file_encoding.’” |
-<h|H> | Optional | | Displays the version of BDU and the usage help |
Notes
Configuration Settings
You are not required to change any PSQL configuration settings to use BDU.
BDU loads data into a table using the accelerated mode. During the load of data, the MicroKernel does not perform transaction logging.
If you use archival logging, back up your data files again.
Error Logging
By default, BDU logs all information and error messages to the standard error stream (stderr). You may specify a log file to which the tool writes the information or error messages.
Two types of errors are not logged: critical and recoverable. With critical errors, BDU exits because it cannot perform error recovery. For example, a missing delimited data file is a critical error.
With recoverable errors, BDU skips the error and continues processing. The tool keeps a count of such skipped errors and exits when it reaches a user-specified threshold. By default, the threshold is set to zero.
Constraints
The following constraints apply to loading data with BDU.
Constraint | Discussion |
Any Referential Integrity (RI) error is considered an RI violation | Row is rejected |
Any unique or primary key violations | Row is rejected |
No value specified for a non-NULL column1 | Row is rejected regardless of column’s default value |
No value specified for a nullable column1 | NULL inserted regardless of column’s default value |
Table into which data is being loaded contains insert triggers | BDU returns an error and does not attempt to load the table. Drop the insert triggers on the table then rerun BDU |
Table into which data is being loaded contains CLOB or BLOB columns (relational data types LONGVARCHAR, NLONGVARCHAR, or LONGVARBINARY) | BDU does not attempt to load the table and returns the message “The target table contains longvarchar, nlongvarchar, or longvarbinary data types. These data types are not supported.” |
Order of rows | BDU treats the delimited data file as unordered. The original order of rows may not be preserved. |
Date fields | The only supported format is yyyy-mm-dd |
Time fields | The only supported format is HH:MM:SS |
Timestamp fields | The only supported format is yyyy-mm-dd HH:MM:SS.MS |
White space and column delimiters | No white space must exist between the column delimiters and the data values, even if the data values are enclosed in quotes. |
1 BDU is not aware of default values for a column defined during table creation or update |
Best Practices
If possible, run BDU when the database load is minimal or when no concurrent sessions exist on the table being loaded.
If the table being loaded contains any indexes, drop the indexes before using BDU. Recreate the indexes after the load is complete.
If the table being loaded contains any columns with check constraints, drop the check constraints before using BDU. Respecify the constraints after the load is complete.
Sample Source File
The following content may be used to create a sample delimited text file. You may use the file to verify the usage examples. The examples refer to the sample file as data_file.txt.
Note that, because the following content is comma delimited, you must specify the -t parameter (-t ,) with BDU. The -t parameter is required for any delimiter except the TAB character.
psqlBDUsample_1,12345,psql,101,18446744073709551615
psqlBDUsample_2,12346,psql,102,18446744073709551614
psqlBDUsample_3,12347,psql,103,18446744073709551613
psqlBDUsample_4,12348,psql,104,18446744073709551612
psqlBDUsample_5,12349,psql,105,18446744073709551611
psqlBDUsample_6,12350,psql,106,18446744073709551610
psqlBDUsample_7,12351,psql,107,18446744073709551609
psqlBDUsample_8,12352,psql,108,18446744073709551608
psqlBDUsample_9,12353,psql,109,18446744073709551607
psqlBDUsample10,12354,psql,110,18446744073709551606
Note that in any data file being used as source input, no white space must exist between the column delimiters and the data values, even if the data values are enclosed in quotes.
Examples
The following examples assume that a table named BDU_Table is part of the Demodata sample database. To add such a table to Demodata, use the following query:
CREATE TABLE BDU_Table (Name CHAR(20) NOT NULL CASE, PhoneNo INTEGER,BuildingName CHAR(25) NOT NULL CASE, RoomNo UINT NOT NULL,HeadOfDept UBIGINT NOT NULL)
To run BDU with the default options:
bdu demodata BDU_Table C:\data_file.txt
Note The input data must be TAB delimited to use default options. If the input data is not TAB delimited, you must specify the delimiter with the -t parameter.
For example, to use the data from
Examples, which is comma-delimited, run BDU as follows:
bdu demodata BDU_Table C:\data_file.txt -t , ============
To run BDU for the Billing table in the sample database Demodata and load TAB-delimited data from a file that uses UTF-16LE encoding:
bdu demodata Billing D:\billing_data_import.txt -c UTF-16LE
============
To run the BDU for a database that requires username and password:
bdu demodata BDU_Table C:\data_file.txt -u <username> -p <password>
============
To run the BDU with max errors option:
bdu demodata BDU_Table C:\data_file.txt -e <no of errors user wants to allow>
For instance, for loading to continue until 100 errors have occurred:
bdu demodata BDU_Table C:\data_file.txt -e 100
============
To run the BDU with a specific column delimiter option:
bdu demodata BDU_Table C:\data_file.txt -t <column delimiter>
Example:
When the source file contains text in which each row is separated by ,
bdu demodata BDU_Table C:\data_file.txt -t ,
============
To run the BDU with a specific row delimiter option:
bdu demodata BDU_Table C:\data_file.txt -n <row delimiter>
For instance, when the source file contains text in which each row is separated by \n:
bdu demodata BDU_Table C:\data_file.txt -n \n
============
To run the BDU with a specific start row option:
bdu demodata BDU_Table C:\data_file.txt -f <line no. from which user wants loading to begin>
============
To run the BDU with a specific end row option:
bdu demodata BDU_Table C:\data_file.txt -l <line no. at which user wants loading to end>
============
You may combine parameters. To load the first 15 rows from the source file containing data that is separated by | and is enclosed in ':
bdu demodata BDU_Table C:\data_file.txt -f 1 -l 15 -t |