NuoDB Loader

Description

The NuoDB Loader (nuoloader) is a command line utility that is available in the NuoDB Client-only package. It can be used 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]...

If for security reasons, the password is omitted as a command line argument or environmental variable, the user will be prompted to enter the password via stdin.

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. Multiple connection properties may be specified on one command line. See Connection Properties for more information.

Import Options

--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

When specified, NuoDB Loader recognizes \n, \r, \t, \" and \' as special characters to represent newlines, tabulation, and quotes.

--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 (?,?,?)”.

  • When you use import format parameters with the --import option, separate each option with a comma only and no spaces.

  • The maximum number of columns for import is 32,000 (the same as the maximum number of columns in a table).

  • While importing a CSV file, column values with double quote character must be enclose in double quotes. For example, values such as ONE, T"WO, THREE,… must be ONE, "T"WO", THREE,….

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

When specified, NuoDB Loader recognizes \n, \r, \t, \" and \' as special characters to represent newlines, tabulation and quotes.

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 1MB.

--batch-count n

Where n specifies a size of a batch in the number of rows, default is 1000.

--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

Importing a CSV file filled with data into a newly created table

First, use NuoDB SQL to create a schema and an empty table (called myTable).

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

After creating myTable, use NuoDB Loader to import data into the table.

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

Importing data from a CSV file

Import data from foo.csv into another table (FOO) which has three columns.

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

Importing data with special characters

This example shows how data that has special characters, such as double quotes, embedded in the column values, can be imported. These special characters need to be escaped in the input data file using NuoDB Loader’s escape option 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

NuoDB Loader’s escape option is used so that NuoDB Loader recognizes \n, \r, \t, \", and \' as special characters to represent newlines, tabulation, and quotes. Otherwise, such characters are 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 (?,?,?)"

Importing data from standard input into the FOO table and remove all leading white space

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

The space after --import is required.

Exporting all columns from the FOO table to standard output

This example shows how NuoDB Loader can be used to export data, escaping double quotes, new lines, and tabulation.

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

Importing data selectively

This example shows how NuoDB Loader can be used to import data selectively by picking columns from a CSV file as follows:

"row1 col1", "row1 col2", "row1 col3"

"row2 col1", "row2 col2", "row2 col3"

nuoloader test@localhost --user dba --password goalie --schema test \
    --import /tmp/a.csv --to "insert into FOO.FOO values (?.[:2],?.[:3])"

Or

nuoloader test@localhost --user dba --password goalie --schema test \
    --import /tmp/a.csv --to 'insert into FOO.FOO values (?.":2",?.":3")'
Use single quotes around the insert statement if you use double quotes to denote column positions in the values phrase.

Importing and exporting data

This example shows how NuoDB Loader can be used to import data from a foo.csv file using 1K batches (and commit every batch), then export everything from a FOO table into a foo.out file.

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