wxunloader - Kognitio bulk data unloading tool

SYNOPSIS

wxunloader -s server -u user [options] query

wxunloader -s server -u user [options] -t table

DESCRIPTION

wxunloader is Kognitio’s bulk data unloading tool. It takes a database query, table or view, executes it using Kognitio’s bulk export command, and outputs the results. By default the results are written to standard output as CSV. A destination file can be specified with the -o option.

Text formats other than CSV can be specified using the -F option, which takes the name of a format string file. The syntax of the format string is described in greater detail in wxunloader-format.

Conversion between Kognitio’s native binary format and the target format is performed by the server in parallel. The aim is to maximise unload speed.

OPTIONS

Conversion options

-\-always-quote-blank

Always put non-NULL empty CSV strings in quotes, even if -N is set.

-c, -\-character-set charset

Desired character encoding of the output file. This character encoding must be specified in the IPE_CHARACTER_SET system table on the database server. If this option is not given, wxunloader uses the character encoding specified by the current locale.

-D, -\-date-format format

Specify how DATE columns should be formatted. Format letters that can be used are described in the DATE/TIME FORMAT section below. The default is YYYY-MM-DD.

-F, -\-field-format-file file

File containing the format string to use. If this option is not given, wxunloader will automatically generate a format string suitable for formatting the results of the given query as CSV (modified as necessary according to any other command line options given), and use that. The syntax of the format string is described in wxunloader-format. If a format string file is supplied, it overrides all other conversion options.

-f, -\-field-separator char

Field separator character. The default is a comma (,).

-\-implied-dp

Don’t include decimal points in DECIMAL or NUMERIC fields, and scale the result accordingly. For example, the value 2.34 as a DECIMAL(10, 5) would be formatted as 234000. The value 0.00008 in the same column would be formatted as 8.

-\-include-header

Include a CSV header row containing the names of the columns in the result set.

-N, -\-naive-delimiting

Do not put CSV fields in quotes unless necessary. If this option is not given, all string fields are enclosed in quotes, as is any other field which contains the field separator character, a record terminator character, or the quote character itself.

-\-null-value value

Specify how NULL values are to be represented. By default, fields corresponding to NULL values are left empty.

-\-percent-encode

Fields are not quoted. Instead, any field separator, record terminator, quote or percent characters in fields are percent-encoded. A percent-encoded character consists of a percent sign followed by the two-digit hex character code. For example, a comma would be encoded as %2C.

-\-quote char

Use this character to quote strings. The default is a double quote character (“).

-\-record-terminator char

Record terminator character. The default is \rn on Windows and \n on other platforms. char must be a single character or character code, or the sequence ‘\rn’.

-S, -\-timestamp-format format

Specify how TIMESTAMP columns should be formatted. Allowable format letters are listed in the DATE/TIME FORMAT section below. The default is YYYY-MM-DD HH:NN:SS.

-T, -\-time-format format

Specify how TIME columns should be formatted. Allowable format letters are listed in the DATE/TIME FORMAT section below. The default is HH:NN:SS.

-\-trim-char

Remove trailing spaces from fields corresponding to CHAR columns.

-\-trim-varchar

Remove trailing spaces from fields corresponding to VARCHAR columns.

-w

Export WCB and not CSV. This overrides all the CSV-specific conversion options above. This is the default if the destination file name ends with .wcb.

Behavioural options

-a, -\-append

Append to the output file. If this option is not supplied, the output file is truncated before being written to.

-o, -\-output-file file

Output data to file. If file is “-“, or if this option is not supplied, data is sent to standard output.

-s, -\-server dsn

Data source name of the Kognitio database. This option is required.

-t, -\-table tablename

Name of the table or view to export from. If this is not given, query must be given, and vice versa. It is an error to specify both. In the case of this option, the argument is appended to select * from to produce the query. For example, -t mytable is equivalent to a query argument of select * from mytable. This means anything that can legally come after the table name in an SQL export statement, such as WHERE or ORDER BY, can be put here. For example, to export all orders from the CUSTORDER table made by customer 806 in date order, the command might be: wxunloader -s server -u user -o orders.csv \         -t “custorder where id = 806 order by order_date”

-\-text

Do not export a WCB file even if the destination filename ends with .wcb.

-u, -\-username username

User name to log on as. This option is required.

-p, -\-password password

Password. If this is not given or if the password is blank, wxunloader will try to authenticate using public/private key pair authentication. If this fails, and wxunloader is run from a terminal, wxunloader prompts for a password on the command line.

Reporting options

-l, -\-log-file file

Destination file for diagnostic messages. By default these are sent to standard error.

-\-no-progress

Don’t show the progress indicator. This is the default if -q is given or if wxunloader is not run interactively.

-P, -\-show-progress

Show the progress indicator. This is the default unless -q is given wxunloader is not run interactively.

-q, -\-quiet

Be less verbose.

-\-show-timings

Show timing information.

-v, -\-verbose

Be more verbose.

DATE/TIME FORMAT

The arguments to -D, -T and -S represent a date/time format string.

Date/time format specifiers

Format

Meaning

YY

year without the century

YYYY

year with the century

M

month number, one or two characters

MM

month number, two characters

MMM

month name as a three-letter abbreviation

D

day of month, one or two characters

DD

day of month, two characters

DDD

day of the year (001-366)

H

hour, one or two characters

HH

hour, two characters

N

minute, one or two characters

NN

minute, two characters

S

second, one or two characters

SS

second, two characters

I

hour, minute and second run together as an integer

T

subsecond; number of Ts is the number of characters

P

A or P

PP

AM or PM

!

Next character is a literal (e.g. !M matches an M)

?

Skip this character

The 24-hour clock is used unless P or PP appear in the format, in which case the 12-hour clock is used.

EXAMPLES

These examples will prompt for a password unless one is supplied with -p or key authentication is available. The examples assume the existence of a DSN or hostname mysvr and the Kognitio user joe.

Export all rows from a table MYTABLE to a CSV file:

  wxunloader -s mysvr -u joe -o mytable.csv -t mytable

Export the results of a query to a CSV file:

  wxunloader -s mysvr -u joe -o part.csv “select id, name from part”

Export the table MYTABLE to a tab-delimited file, without unnecessarily quoting strings:

  wxunloader -s mysvr -u joe -f ’t’ -N -o mytable.txt -t mytable

Export the table MYTABLE as WCB, suitable for loading into another Kognitio system:

  wxunloader -s mysvr -u joe -w -o mytable.wcb -t mytable

Export the table MYTABLE as a compressed WCB file, suitable for loading into another Kognitio system on version 7.9 or newer.

  wxunloader -s mysvr -u joe -z -o mytable.zwcb -t mytable

EXIT STATUS

wxunloader returns 0 on success, and 1 on failure.

AUTHOR

Kognitio Ltd.