NuoDB Loader

Description

The NuoDB Loader is a command line utility to import and export data to and from a NuoDB database. It supports comma separated value (CSV) file formats. It runs as a client of NuoDB and requires a database name, user name and password.

Empty elements in a CSV file are treated as NULL. To specify an empty string use double quotation marks ( " ). For example, when importing:

CSV Input Behavior
1,2,3,,5 NULL is assigned to column #4 (because it is specified as an empty element)
1,2,3,"",5

Empty string is assigned to column #4 (because it is specified with double quotation marks)

Similarly, export operations specify empty strings with double quotation marks, and a value of NULL is exported as an empty element.

Syntax

nuoloader database_name  --user user_name --password password [options]...

Connection Options

Option Description
--schema schema_name Specifies the schema to be used for the nuoloader client connection to the server.
--connection-property x=y [--connection-property x=y]... Set connection property x to y. See Connection Properties for more information. Multiple connection properties may be specified on one command line.

Import Options

Note: When you use import format parameters with the --import option, be sure to separate each option with a comma only and no spaces.

--import csv_filename[,import_format_parameter]... --to sql_insert_statement
Option Description
--import csv_filename[,import_format_parameter]...

Provides a CSV file to be imported with optional import format parameters. If csv_filename is not provided, data is expected on stdin.

Import format parameters may include:

Parameter Description
separator='char'

A printable character or word ('tab' for horizontal tabulation) that represents the value separator in csv_filename. The default is separator=','

skip[=n] The number of lines to skip in the beginning of the file. The default is skip=1
trim={
leading |
trailing |
both |
none }
This controls stripping leading and trailing white spaces.
escape='char'

This designates the escape character. A backslash ('\') is used to escape double quotes, new lines and tabulation.

--to sql_insert_statement

The argument is a valid SQL INSERT or UPDATE statement. A question mark ('?') is used to specify the position of a parameter. For example:

--to "insert into FOO values (?,?,?)"

Export Options

--export sql_select_statement --to csv_filename[,export_format_parameter]...
Option Description
--export sql_select_statement Provides an SQL SELECT statement whose result set is exported into a file in CSV format.
--to csv_filename[,export_format_parameter]...

Specifies an output file name for the export operation. If no csv_filename is specified output is sent to stdout.

[export_format_parameters]... is an optional comma separated list of parameters to the output file format. These may include:

Parameter Description
separator="char" A printable character or word ("tab" for horizontal tabulation) that represents the value separator in csv_filename. The default is separator=","
titles If specified, column names are written in the first line.
escape="char" This designates the escape character. A backslash ('\') is used to escape double quotes, new lines and tabulation.
enclose Specify that all fields are enclosed in double quotes.

Batch Options

For better performance, data is imported using batches. The following options control the size of a single batch and whether or not each batch should be processed as a separate transaction. Batching can be turned off by setting either --batch-size or --batch-count to zero.

Option Description
--batch-size n Where n specifies a size of a batch in bytes, default is 64KB
--batch-count n Where n specifies a size of a batch in the number of rows, default is 5000.
--no-batch-commit This overrides the default of committing after each batch.

Reporting Options

Option Description
--verbose Reports progress for each executed batch.
--log log_filename Redirects error and verbose output to log_filename.

Miscellaneous Options

Option Description
--strict Halts execution on any error. By default, the erroneous line is reported and execution continues.
--help Prints a list of options.
--version Prints the version.

Examples

Create schema and empty table using the nuosql client:

$ nuosql test@localhost --user dba --password goalie
SQL> USE newSchema;
SQL> CREATE TABLE myTable (Number INT,Name STRING,Position STRING,Team STRING);
SQL> SHOW myTable;
       Found table MYTABLE in schema NEWSCHEMA
               NUMBER integer
               NAME string
               POSITION string
               TEAM string
SQL> quit;

Import .csv file filled with data, into the newly created table with nuoloader.

$ nuoloader test@localhost --user dba --password goalie \
    --import "/Path/filename",separator=":",skip \
    --to "INSERT INTO newSchema.myTable (number,name,position,team) VALUES (?,?,?,?);"
Imported 26 rows, failed 0 rows, size 715 bytes from /Path/filename

Other Examples

Import data from file foo.csv into table FOO which has three columns.

nuoloader test@localhost --user dba --password goalie --schema test \
    --import foo.csv --to "insert into FOO values (?,?,?)"

Example of importing data that has special characters, such as double quotes, embedded in the column values. These special characters need to be escaped in the input data file using a backslash ("\") or they will be ignored and not loaded as part of the inserted value.

1	row1	first row
2	row2	second\" row
3	row3	third r\"ow
4	row4	forth row

The escape option of the nuoloader command is used to define this escape character for these double quotes. Otherwise, the double quotes will be ignored and not loaded as part of the column value.

nuoloader test@localhost --user dba --password goalie --schema test \
    --import foo.csv,separator='tab',trim,escape='\' --to "insert into FOO values (?,?,?)"

Import data from standard input into table FOO and remove all leading white space.

Note: The space after --import is required.

nuoloader test@localhost --user dba --password goalie --schema test \
    --import ,trim=leading --to "insert into FOO values (?,?,?)"

Export all columns from table FOO to standard output, escaping double quotes, new lines and tabulation with backslash and enclosing in double quotes.

nuoloader test@localhost --user dba --password goalie --schema test \
    --export "select * from FOO" --to ,escape,enclose

Import data from file foo.csv using 1K batches and committing every batch. Then export everything from table FOO into a file foo.out.

nuoloader test@localhost --user dba --password goalie --schema test \
    --import foo.csv --to 'insert into FOO values (?,?,?)" \
    --batch-size 1K \
    --export 'select * from FOO' --to foo.out