lt_bulkload is a high speed data loading tool for LightDB.
lt_bulkload is designed to load huge amount of data to a database. You can load data to table bypassing LightDB shared buffers.
lt_bulkload also has some ETL features; input data validation and data transformation.
lt_bulkload [ OPTIONS ] [ controlfile ]
lt_bulkload is designed to load huge amount of data to a database. You can choose whether database constraints are checked and how many errors are ignored during the loading. For example, you can skip integrity checks for performance when you copy data from another database to LightDB. On the other hand, you can enable constraint checks when loading unclean data.
lt_bulkload can convert the load data into the binary file which can be used as an input file of lt_bulkload. If you check whether the load data is valid when converting it into the binary file, you can skip the check when loading it from the binary file to a table.
You can use lt_bulkload by the following three steps:
1. Edit control file "sample_csv.ctl" or "sample_bin.ctl" that includes settigs for data loading. You can specify table name, absolute path for input file, description of the input file, and so on.
2. Assume there is a directory $LTDATA/lt_bulkload, in that load status files are created.
3. Execute command with a control file as argument. Relative path is available for the argument.
$ lt_bulkload sample_csv.ctl
NOTICE: BULK LOAD START
NOTICE: BULK LOAD END
0 Rows skipped.
8 Rows successfully loaded.
0 Rows not loaded due to parse errors.
0 Rows not loaded due to duplicate errors.
0 Rows replaced with new rows.
lt_bulkload has the following command line options:
-i INPUT
--input=INPUT
--infile=INPUT
Source to load data from. Same as "INPUT" in control files.
-O OUTPUT
--output=OUTPUT
Destination to load data to. Same as "OUTPUT" in control files.
-l LOGFILE
--logfile=LOGFILE
A path to write the result log. Same as "LOGFILE" in control files.
-P PARSE_BADFILE
--parse-badfile=PARSE_BADFILE
A path to write bad records that cannot be parsed correctly. Same as "PARSE_BADFILE" in control files.
-u DUPLICATE_BADFILE
--duplicate-badfile=DUPLICATE_BADFILE
A path to write bad records that conflict with unique constraints during index rebuild. Same as "DUPLICATE_BADFILE" in control files.
-o "key=val"
--option="key=val"
Any options available in the control file. You can pass multiple options.
Options to connect to servers.
-d dbname
--dbname dbname
Specifies the name of the database to be connected. If this is not specified, the database name is read from the environment variable LTDATABASE. If that is not set, the user name specified for the connection is used.
-h host
--host host
Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket.
-p port
--port port
Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections.
-U username
--username username
User name to connect as.
-W
--password
Force lt_bulkload to prompt for a password before connecting to a database.
This option is never essential, since lt_bulkload will automatically prompt for a password if the server demands password authentication. However, vacuumdb will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.
-e
--echo
Echo commands sent to server.
-E
--elevel
Choose the output message level from DEBUG, INFO, NOTICE, WARNING, ERROR, LOG, FATAL, and PANIC. The default is INFO.
--help
Show usage of the program.
--version
Show the version number of the program.
You can specify the following load options. Control files can be specifed with an absolute path or a relative path. If you specify it by a relative path, it will be relative to the current working directory executing lt_bulkload command. If you don't specify a control file, you should pass required options through command line arguments for lt_bulkload.
Following parameters are available in control files. Characters after "#" are ignored as comments in each line.
TYPE = CSV | BINARY | FIXED | FUNCTION
The type of input data. The default is CSV.
CSV : load from a text file in CSV format
BINARY | FIXED : load from a fixed binary file
FUNCTION : load from a result set from a function.
If you use it, INPUT must be an expression to call a function.
INPUT | INFILE = path | stdin | [ schemaname. ] function_name (argvalue, ...)
Source to load data from. Always required. The value is treated as following depending on the TYPE option:
A file in the server: This is a file path in server. If it is a relative path, it will be relative from the control file when specified in the control file, or will be relative from current working directory when specified in command line arguments. The user of LightDB server must have read permission to the file. It is available only when "TYPE=CSV" or "TYPE=BINARY".
Standard input to lt_bulkload command: "INPUT=stdin" means lt_bulkload will read data from the standard input of lt_bulkload client program through network. You should use this form when the input file and database is in different servers. It is available only when "TYPE=CSV" or "TYPE=BINARY".
A SQL function: Specify a SQL function with arguments that returns set of input data. It is available only when "TYPE=FUNCTION". You can use not only build-in functions but also any user-defined functions. The defined functions should return the type as SETOF sometype, or as RETURNS TABLE(columns). Note that you specify attributes types if you use sometype. Because there is a case that assignment cast to cstring type which lt_bulkload uses internally, doesn't be permitted. Note that you might need to develop those function with C language instead of PL/pgSQL because the function must use SFRM_ValuePerCall mode for streaming loading.
Here is an example of user-defined function.
$ CREATE TYPE sample_type AS (sum integer, name char(10));
$ CREATE FUNCTION sample_function() RETURNS SETOF sample_type
AS $$ SELECT id1 + id2, upper(name) FROM INPUT_TABLE $$
LANGUAGE SQL;
Here is an example of a control file.
TABLE = sample_table
TYPE = FUNCTION
WRITER = DIRECT
INPUT = sample_function() # if to use the user-defined function
#INPUT = generate_series(1, 1000) # if to use the build-in function, which generate sequential numbers from 1 to 1000
WRITER | LOADER = DIRECT | BUFFERED | BINARY | PARALLEL
The method to load data. The default is DIRECT.
DIRECT : Load data directly to table. Bypass the shared buffers and skip WAL logging, but need the own recovery procedure. This is the default, and original older version's mode.
BUFFERED : Load data to table via shared buffers. Use shared buffers, write WALs, and use the original LightDB WAL recovery.
BINARY : Convert data into the binary file which can be used as an input file to load from. Create a sample of the control file necessary to load the output binary file. This sample file is created in the same directory as the binary file.
PARALLEL : Same as "WRITER=DIRECT" and "MULTI_PROCESS=YES". If PARALLEL is specified, MULTI_PROCESS is ignored. If password authentication is configured to the database to load, you have to set up the password file. See Restrictions for details.
OUTPUT | TABLE = { [ schema_name. ] table_name | outfile }
Destination to load data to. Always required. The value is treated as following depending on the WRITER (or LOADER) option:
A table to load to: Specify the table to load to. If schema_name is omitted, the first matching table in the search_path is used. You can load data to a table only if WRITER is DIRECT, BUFFERED or PARALLEL.
A file in the server: Specify the path of the output file in the server. If it's a relative path, it will be interpreted in the same way as INPUT option. The OS user running LightDB must have write permission to the parent directory of the specified file. You can load (convert) data to a file only if WRITER is BINARY.
SKIP | OFFSET = n
The number of skip input rows. The default is 0. You must not specify both "TYPE=FUNCTION" and SKIP at the same time.
LIMIT | LOAD = n
The number of rows to load. The default is INFINITE, i.e., all of data will be loaded. This option is available even if you use TYPE=FUNCTION.
ENCODING = encoding
Specify the encoding of the input data. Check whether the specified encoding is valid, and convert the input data to the database encoding if needed. By default, the encoding of the input data is neither verified nor converted. If you can be sure that the input data is encoded in the database encoding, you can reduce the load time by not specifying this option, and by skipping encoding verification and conversion. Note that client_encoding is used as the encoding of the input data by default only if INPUT is stdin. You must not specify both "TYPE=FUNCTION" and ENCODING at the same time.
Here are option values and actual behaviors:
DB encoding
SQL_ASCII non-SQL_ASCII
not specified neither checked nor converted neither checked nor converted
SQL_ASCII neither checked nor converted only checked
non-SQL_ASCII, same as DB only checked only checked
non-SQL_ASCII, different from DB only checked checked and converted
FILTER = [ schema_name. ] function_name [ (argtype, ... ) ]
Specify the filter function to convert each row in the input file. You can omit definitions of argtype as long as the function name is unique in the database. If not specified, the input data are directly parsed as the load-target table. See also How to write FILTER functions to make FILTER functions.
You must not specify both "TYPE=FUNCTION" and FILTER at the same time. Also, FORCE_NOT_NULL in CSV option cannot be used with FILTER option.
CHECK_CONSTRAINTS = YES | NO
Specify whether CHECK constraints are checked during the loading. The default is NO. You must not specify both "WRITER=BINARY" and CHECK_CONSTRAINTS at the same time.
PARSE_ERRORS = n
The number of ingored tuples that throw errors during parsing, encoding checks, encoding conversion,
FILTER function, CHECK constraint checks, NOT NULL checks, or data type conversion. Invalid input
tuples are not loaded and recorded in the PARSE BADFILE. The default is 0. If there are equal or more
parse errors than the value, already loaded data is committed and the remaining tuples are not
loaded. 0 means to allow no errors, and -1 and INFINITE mean to ignore all errors.
DUPLICATE_ERRORS = n
The number of ingored tuples that violate unique constraints. Conflicted tuples are removed from the table and
recorded in the DUPLICATE BADFILE. The default is 0. If there are equal or more unique violations than the
value, the whole load is rollbacked. 0 means to allow no violations, and -1 and INFINITE mean
to ignore allviolations. You must not specify both "WRITER=BINARY" and DUPLICATE_ERRORS at the same time.
ON_DUPLICATE_KEEP = NEW | OLD
Specify how to handle tuples that violate unique constraints. The removed tuples are recorded in the BAD file. The default is NEW. You also need to set DUPLICATE_ERRORS to more than 0 if you enable the option. You must not specify both "WRITER=BINARY" and ON_DUPLICATE_KEEP at the same time.
NEW : Keep tuples in the input data, and remove corresponding existing tuples. When both violated tuples are in the data, keep the latter one.
OLD : Keep existing tuples and remove tuples in the input data.
LOGFILE = path
A path to write the result log. If specified by a relative path, it is treated as same as INPUT. The default is $LTDATA/lt_bulkload/timestamp_dbname_schema_table.log.
PARSE_BADFILE = path
A path to the BAD file logging invalid records which caused an error during parsing, encoding checks, encoding conversion, FILTER function, CHECK constraint checks, NOT NULL checks, or data type conversion. The format of the file is same as the input source file. If specified by a relative path, it is treated as same as INPUT. The default is $LTDATA/lt_bulkload/timestamp_dbname_schema_table.bad.extension-of-infile.
DUPLICATE_BADFILE = path
A path to write bad records that conflict with unique constraints during index rebuild. The format of the file is always CSV. If specified by a relative path, it is treated as same as INPUT. The default is $LTDATA/lt_bulkload/timestamp_dbname_schema_table.dup.csv. You must not specify both "WRITER=BINARY" and DUPLICATE_BADFILE at the same time.
TRUNCATE = YES | NO
If YES, delete all rows from the target table with TRUNCATE command. If NO, do nothing. The default is NO. You must not specify both "WRITER=BINARY" and TRUNCATE at the same time.
VERBOSE = YES | NO
If YES, write bad tuples also in server log. If NO, don't write them in serverlog. The default is NO.
MULTI_PROCESS = YES | NO
If YES, we do data reading, parsing and writing in parallel by using multiple threads. If NO, we use only single thread for them instead of doing parallel processing. The default is NO. If WRITER is PARALLEL, MULTI_PROCESS is ignored. If password authentication is configured to the database to load, you have to set up the password file. See Restrictions for details. Please make sure when enabling MULTI_PROCESS that no other LightDB backend process is trying to modify the schema of the table, because it may cause the schema of the table as seen by the reader and the writer processes to differ and cause problems.
DELIMITER = delimiter_character
The single ASCII character that separates columns within each row (line) of the file. The default is comma. When you load a tab-separated format file (TSV), you can set DELIMITER to a tab character. Then, you need to double quote the tab:
DELIMITER=" " # a double-quoted tab
You can also specify DELIMITER as a command-line -o option with $'\t' syntax.
$ lt_bulkload tsv.ctl -o $'DELIMITER=\t'
QUOTE = quote_character
Specifies the ASCII quotation character. The default is double-quotation.
ESCAPE = escape_character
Specifies the ASCII character that should appear before a QUOTE data character value. The default is double-quotation.
NULL = null_string
The string that represents a null value. The default is a empty value with no quotes.
FORCE_NOT_NULL = column
Process each specified column as though it were not a NULL value. Multiple columns are available as needed. FILTER cannot be used together with this option.
COL = type [ (size) ] [ NULLIF { 'null_string' | null_hex } ]
Column definitions of input file from left to right. The definitions consists of type name, offset, and length in bytes. CHAR and VARCHAR means input data is a text. Otherwise, it is a binary data. If binary, endian must match between server and data file.
CHAR | CHARACTER : a string trimmed trailing spaces. The length is always required.
VARCHAR | CHARACTER VARYING : a string keeping trailing spaces. The length is always required.
SMALLINT | SHOFT : signed integer in 2 bytes.
INTEGER | INT : signed integer in 2 or 4 or 8 bytes. The default is 4.
BIGINT | LONG : signed integer in 8 bytes.
UNSIGNED SMALLINT | SHORT : unsigned integer in 2 bytes.
UNSIGNED INTEGER | INT : unsigned integer in 2 or 4 bytes. The default is 4.
FLOAT | REAL : floating point number in 4 or 8 bytes. The default is 4.
DOUBLE : floating point number in 8 bytes.
The length and offset of the type can be specifed as follows:
TYPE : TYPE with default length follows.
TYPE(L) : TYPE with L bytes follows.
TYPE(S+L) : L bytes, offset S bytes from the beginning of the line
TYPE(S:E) : start at S bytes and end at E bytes.
The length and offset of the type can be specifed as follows:
NULLIF 'null_string' : Specify the string expressing NULL when the type is CHAR or VARCHAR. The length of the string must be the same as that of the type.
NULLIF null_hex : Specify the hex value expressing NULL when the type is other than CHAR and VARCHAR. The length of the hex value must be the same as that of the type.
In addition, "COL N" is available, that is same as COL CHAR(N), for backward compatibility.
PRESERVE_BLANKS = YES | NO
YES regards following "COL N" as "COL CHAR(N)" and NO as "COL VARCHAR(N)". Default is NO.
STRIDE = n
Length of one row. Use if you want to truncate the end of row. The default is whole of the row, which means the total of COLs.
OUT_COL = type [ (size) ] [ NULLIF { 'null_string' | null_hex } ]
Column definitions of output file from left to right. The definitions consists of type name, offset, and length in bytes. CHAR and VARCHAR means input data is a text. Otherwise, it is a binary data. If binary, endian must match between server and data file.
CHAR | CHARACTER : fixed-length string. The length must be specified. If the string to be stored is shorter than the declared length, values will be space-padded. "COL=CHAR(size)" will be output in the sample of control file.
VARCHAR | CHARACTER VARYING : fixed-length string. The length must be specified. If the string to be stored is shorter than the declared length, values will be space-padded. "COL=VARCHAR(size)" will be output in the sample of control file.
SMALLINT | SHOFT : signed integer in 2 bytes.
INTEGER | INT : signed integer in 2 or 4 or 8 bytes. The default is 4.
BIGINT | LONG : signed integer in 8 bytes.
UNSIGNED SMALLINT | SHORT : unsigned integer in 2 bytes.
UNSIGNED INTEGER | INT : unsigned integer in 2 or 4 bytes. The default is 4.
FLOAT | REAL : floating point number in 4 or 8 bytes. The default is 4.
DOUBLE : floating point number in 8 bytes.
The string expressing NULL can be specified as follows. If omitted but NULL is input, NULL is logged as an invalid data in PARSE_BADFILE.
NULLIF 'null_string' : Specify the string expressing NULL when the type is CHAR or VARCHAR. The length of the string must be the same as that of the type.
NULLIF null_hex : Specify the hex value expressing NULL when the type is other than CHAR and VARCHAR. The length of the hex value must be the same as that of the type.
lt_bulkload returns 0 when succesfully loaded. It also returns 3 with a WARNING message when there are some parse errors or duplicate errors even if loading itself was finished. Note that skipped rows and replaced rows (with ON_DUPLICATE_KEEP = NEW) are not considered as an error; the exit code will be 0.
When there is a non-continuable error, the loader raises an ERROR message. The return code will be often 1 because many errors occur in the database server during loading data. The following table shows the codes that lt_bulkload can return.
Return code Description
0 Success
1 Error occurred during running SQL in LightDB
2 Failed to connect to LightDB
3 Success, but some data could not be loaded
If you use direct load mode (WRITER=DIRECT or PARALLEL), you have to be aware below:
When lt_bulkload is crashed and some .loadstatus files are remained in $LTDATA/lt_bulkload,
database must be recovered by lt_bulkload own recovery with "lt_bulkload -r" command before
you invoke lt_ctl start. You must start and stop LightDB using LightDB script, which
invokes "lt_bulkload -r" and "lt_ctl start" correctly. We recommend not to use lt_ctl directly.
Because of bypassing WAL, archive recovery by PITR is not available. This does not mean that it can be done PITR without loaded tables data. If you would like to use PITR, take a full backup of the database after load via lt_bulkload. If you are using streaming replication, you need to re-create your standby based on the backup set which is taken after lt_bulkload.
You must not remove the load status file (*.loadstatus) found in $LTDATA/lt_bulkload directory. This file is needed in lt_bulkload crash recovery.
Do not terminate lt_bulkload command using "kill -9" as much as possible. If you did this,
you must invoke LightDB script to perform lt_bulkload recovery and restart LightDB to continue.
When MULTI_PROCESS=YES and password is required to connect from localhost to the database to load, the authentication will fail even if you enter the password correctly in the prompt. To avoid this, configure either of the followings.
Use "trust" method to authenticate the connection from localhost
In UNIX environment, the connection from localhost uses UNIX-domain socket. In UNIX, add the following line into lt_hba.conf.
# TYPE DATABASE USER CIDR-ADDRESS METHOD [for UNIX]
local all foo trust
Specify the password in .pgpass file
If you don't want to use "trust" method for security reasons, use "md5" or "password" as an authentication method and specify the password in .pgpass file. Note that the .pgpass file must be in the home directory of the OS user (typically "LightDB" user) who ran LightDB server. For example, if lt_bulkload connects to the server that is running on port 5432 as the DB user "foo" whose password is "foopass", the administrator can add the following line to the .pgpass file:
localhost:5432:*:foo:foopass
Specify the password in .pgpass file
Don't use "WRITER=PARALLE"
Use the loading method other than "WRITER=PARALLEL".
Only unique constraint and not-NULL constraint are enforced during data load in default. You can to set "CHECK_CONSTRAINTS=YES" to check CHECK constraints. Foreign key constraints cannot be checked. It is user's responsibility to provide valid data set.
There are some notes and warnings when you write FILTER functions:
Records in the input file are passed to the FILTER function one-by-one.
When an error occurs in the FILTER function, the passed record is not loaded and written into PARSE BADFILE.
The FILTER function must return record type or some composite type. Also, the actual record type must match with the target table definition.
If the FILTER function returns NULL, a record that has NULLs in all columns is loaded.
Functions with default arguments are supported. If the input data has fewer columns than arguments of the function, default values will be used.
VARIADIC functions are NOT supported.
SETOF funtions are NOT supported.
Functions that have generic types (any, anyelement etc.) are NOT supported.
FILTER functions can be implemented with any languages. SQL, C, PLs are ok, but you should write functions as fast as possible because they are called many times.
You can only specify one of FILTER or FORCE_NOT_NULL options. Please re-implement FORCE_NOT_NULL-compatible FILTER functions if you need the feature.
Here is an example of FILTER function.
CREATE FUNCTION sample_filter(integer, text, text, real DEFAULT 0.05) RETURNS record
AS $$ SELECT $1 * $4, upper($3) $$
LANGUAGE SQL;