Argument Flags - Aster Client

Teradata Aster® Client Guide

Product
Aster Client
Release Number
7.00
Published
May 2017
Language
English (United States)
Last Update
2018-04-13
dita:mapPath
hki1475000360386.ditamap
dita:ditavalPath
Generic_no_ie_no_tempfilter.ditaval
dita:id
B700-2005
lifecycle
previous
Product Category
Software

In addition to the schemaname.tablename and filename/dirname arguments explained above, the Aster Loader Tool takes the following argument flags at the command line or in the map file. (Map files let you load from many input files in a single running of Aster Loader. See Troubleshoot Loading in the Aster Client Guide .)

In the table that follows, the argument flags are sorted based on the long-form, command-line flag:

  • The left column lists the flag you use at the command line.
  • The middle column lists the flag you can use in a map file. (See the section Rules for Passing Arguments in a Map File.) If no value appears in the middle column, then the argument is one that can only be passed at the command line.

Note that you can always specify a loader node using its IP address. If you wish to specify it by hostname, you first need to add the loader node to the Aster Database hosts file on all nodes through the Hosts tab in the AMC. In systems running the Aster Execution Engine, loader nodes cannot be added to the cluster. The queen node will be used for loading data if you specify an IP address for the loader node.

Argument Flags for Aster Loader Tool
Flag at Command Line Flag in Map File Description
-a [ --auto-partition] auto-partition Specifies that ncluster_loader will run in autopatitioning mode, which automatically routes each row to the appropriate child table in a parent/child table created through inheritance.
Parent/child inheritance is not supported in a system running Aster Execution Engine.
-B [ --begin-script ] arg begin-script Specifies the qualified path of the file containing SQL commands that should be executed when the transaction starts, i.e. immediately after the BEGIN command is issued to Aster Database.
Data-returning statements such as SELECT are not allowed in scripts executed by the ncluster_loader.
-c [ --csv ]   Pass this argument if you want ncluster_loader to interpret the input file as being in CSV format. By default ncluster_loader uses a text file with a tab-delimited format, often called “TSV”.
-C [ --columns ] arg columns An optional comma-separated list of the columns in the target table that will receive the data being loaded (the default is to assume that each input row contains exactly one data value for each column in the target table, in the order in which the table columns were defined).

By using -C, you can specify that the ordering of the columns in the input file is different from the ordering in the table. You can also use -C to specify that this input file contains values for only some of the columns in the table.

The input data is assumed to contain values for the columns in the order specified here. For example, to load data into columns 'col1' and 'col2', one could specify "col1, col2" as the value for this option. Column names not specified here are expected to get NULL values.

See Using the -C Option With Uppercase or Special Characters in the Aster Client Guide .

-d [ --dbname ] arg dbname The name of the database to connect to (default is "beehive").
-D [ --delimiter ] arg   The column delimiter character to use when interpreting the input file (must be a string that represents a valid single character, such as 'd' or '\n'). The default is a tab character ('\t') in text mode, a comma in CSV mode.
--date-style arg date-style Specifies the date format of the data being loaded. If you are using a map file, you must specify a single date-style per map file.

The following formats are supported:

  • ISO - This is the default. Uses ISO 8601-style dates and times (YYYY-MM-DD HH:MM:SS) like, for example, 
2010-12-17 07:37:16-08
  • SQL - Uses Oracle/Ingres-style dates and times. For example: 
12/17/2010 07:37:16.00 PST
  • POSTGRES - Uses traditional PostgreSQL format. For example: 
Wed Dec 17 07:37:16 2010 PST
  • German - Uses dd.mm.yyyy for numeric date representations. For example: 
17.12.2010 07:37:16.00 PST
  • SQL,DMY - For example: 
17/12/2010 15:37:16.00 CET
  • SQL,MDY - For example: 
12/17/2010 07:37:16.00 PST
  • Postgres,DMY - For example: 
Wed 17 Dec 07:37:16 2010 PST

-e [ --escape ] arg   Specifies the escape character for CSV input (must be a string that represents a valid single character, such as d or \n). The default escape character is the same as the default quote character - the double-quote (").

If an explicit quote character (-q) is specified, and no explicit escape character (-e) is specified, then the escape character will be set to be the same as the quote character specified.

You may only pass this option when loading a CSV-formatted file (--csv). Note that ncluster_loader will fail if you pass this argument when loading a tab-delimited text file.

-E [ --end-script ] arg end-script Qualified path of the file containing SQL commands that should be executed when the transaction is about to commit, i.e., immediately before the END/COMMIT command is issued to Aster Database.
Data-returning statements such as SELECT are not allowed in scripts executed by the Aster Loader Tool.
--el-discard-errors discard-errors Sub-flag that can accompany --el-enabled. If present, specifies that all errors (malformed rows) be discarded by Aster Database. By default, errors are not discarded.
--el-enabled errorlogging If present, turns on error logging for this invocation of the ncluster_loader. This needs to be enabled for any other error logging option to be accepted. The default is disabled. See the Error Logging section.
--el-label arg label Sub-flag that can accompany --el-enabled. The --el-label flag specifies the label to be used to tag the malformed rows. By default, Aster Database will use a statement identifier as the label value. (There’s one statement identifier per COPY command; if there is one map entry for many input files, then you’ll have a unique statement identifier per input file.) The label is useful for finding your failed rows in the error logging table and for finding statistics about the load attempt in the nc_all_errorlogging_stats table.
-F --el-errfile arg errfile Sub-flag that can accompany --el-enabled. The --el-errfile flag introduces the pathname of the optional error logging file. If you use error logging, you must have an error logging table, and you can have an error logging file. Upon completion of the load, ncluster_loader writes the contents of the error logging table’s rawdata column (and no other columns) to the error logging file. Only the contents of this column are written to the file. The filename will have a numeral appended to it in the form, “_0”.

For this option to work, you must have also specified an el-table.

To produce an error logging file in a system running Aster Execution Engine, use this sub-flag and set the el-table sub-flag as either nc_errortable_repl or nc_error_table_part, depending on whether the load-destination table is replicated or distributed.

Regardless of whether or not you specify that an error logging file should be used, the error logging table will still contain all error rows upon completion of each load.

-L [ --el-limit ] arg limit Sub-flag that can accompany --el-enabled. The --el-limit flag sets the maximum number of malformed rows allowed for each file being loaded. By default the limit is unbounded. If the error count during your load attempt exceeds the specified --el-limit, the transaction aborts and no rows are inserted into the destination table, and no rows are inserted into the error logging table.
--preprocess-script arg   [Linux only] This flag executes a shell script against the source file, and uses the output of the script as the new source file for loading. You can use this feature along with the provided script remove-null.sh to remove NULL(0x00) values from data to be loaded using Aster Loader Tool on Linux. For non-Linux operating systems, use the workaround Removing NULLs from Data in the Aster Client Guide .
-S [ --el-schema ] arg schema Sub-flag that can accompany --el-enabled. The --el-schema flag specifies the name of the schema to which the error logging table belongs. If not specified, the public schema is used.

Note that this sub-flag supports the use of custom error logging tables, which are not supported in a system running Aster Execution Engine. Teradata Aster recommends that Aster Execution Engine customers not use this sub-flag.

-T [ --el-table ] arg table Sub-flag that can accompany --el-enabled. The --el-table flag specifies the name of the error logging table that will capture malformed rows.

If you are loading to a distributed table, the error logging table must be a distributed table. Likewise, if you are loading to a replicated table, the error logging table must be a replicated table.

If this flag is not provided, Aster Database uses a default table: nc_errortable_repl is used if the load-destination table is a replicated table, or nc_errortable_part is used if the load-destination table is a distributed table.

Note that in systems running Aster Execution Engine, only the default tables nc_errortable_repl and nc_errortable_part are available. This sub-flag can be used with the appropriate choice of default tables to enable the use of the --el-errfile flag.

Important: The system does not perform automatic truncation of the error logging tables. You must VACUUM them regularly.
-f [ --force-loader ] force-loader Instructs the Aster Loader Tool to use the loader node specified with the -l or --loader option even if the IP address provided is not known to Aster Database. Note that if this option is specified, the Aster Loader Tool will only try that single IP address and return an error status if the connection fails for any reason. If this options is not used, Aster Loader Tool will attempt to choose the least busy loader node available.

This option is not supported by systems running the Aster Execution Engine.

filename

(Not a flag; you pass the file or directory name itself.)

file Qualified pathname of the file or directory containing data to be loaded. The contents of the file must be in either CSV or text format, as described for the COPY statement.

If you pass a directory name, then all data files found in the directory are loaded in a single transaction. Subdirectories are ignored. All files must be in the same format.

If you pass no file or directory name, Aster Loader loads from STDIN.

  • In a map file, use the file flag.
  • At the command line, there is no flag for this argument; instead, ncluster_loader assumes that you pass the named-flag arguments first, then the table name, and finally the source file name.
-? [ --help ]   Shows online help for Aster Loader Tool.
-h [ --hostname ] arg   Host name or IP address of the Aster Database queen. Default is 'localhost'. This always points to the queen, even if you’re loading using loader nodes. See the section, Multiple Loader Nodes.
--header-included   Indicates that the first line of the data file to be input is a comma delimited list of the column names. The number of skip-rows will be counted beginning after the header, if this option is specified. Cannot be combined with --columns.
-l [ --loader ] arg loader Preferred loader IP address. If a value is provided, the Aster Loader Tool tries to load to this IP address before trying any other loader node. Note that the Aster Loader Tool expects this IP address to be known by Aster Database and will ignore the address provided if this is not the case. To change this behavior one can use the -f or --force-loader flag.

This option is not supported by systems running the Aster Execution Engine.

-m [ --map-file ] arg   Name of the file containing mappings of data files or directories to target tables. This option allows you to load multiple directories into a table and also to load data into multiple tables within the same transaction. For details about the format used for the map file, see Troubleshoot Loading in the Aster Client Guide .
-n [ --null ] arg   String that is used to represent a null value. The default is \N in text (non-CSV) mode, and an empty value with no quotes in CSV mode.

When loading any non-CSV delimited format (e.g. TSV), you can easily load files that contain empty strings (that is, files that don’t use the typical \N to represent nulls). To do this, run ncluster_loader with the -n flag, followed by two double-quote characters. That is, you type:

    ncluster_loader -n ""

See also --null-backslash-escapes.

--null-backslash-escapes   Indicates that backslashes in the '-n/--null' flag should be treated as special characters, which will be processed according to the default rules for escaped strings.

You should use this flag whenever the null string contains a backslash, to ensure compatibility with future versions of Aster Database.

-p [ --port ] arg   TCP port on which Aster Database listens for connections. Default is 2406.
-P [ --data-prefix ] arg data-prefix String that should be used as a prefix for each row (each line in a data file) being loaded into Aster Database.
Important: The Aster Loader Tool will simply concatenate this value to each row without any parsing. You must insert the proper delimiter, quote, and escape characters in the prefix string.
-q [ --quote ] arg   Character to be used as the quoting character for CSV input (must be a string that represents a valid single character, such as d or \n). The default is the double-quote. This option is only valid when CSV input has been specified (-c or --csv) and will cause the load to fail otherwise.

If an explicit quote character (-q) is specified, and no explicit escape character (-e) is specified, then the escape character will be set to be the same as the quote character specified.

If using a single quote (') as the quoting character, you should escape it when specifying it with -q as -q"\'". Otherwise any input data that includes a comma will cause the load to fail.

--skip-rows arg   Specifies how many rows of the file to skip before starting to load data. If combined with --header-included, --skip-rows will not start counting until after the first line in the file. The default is 0.
-t [ --timeout ] arg timeout Timeout value in seconds for Aster Database connection attempts. Default is 30 seconds.
tablename

(Not a flag; you pass the table name itself.)

table Name of the destination table.
  • In a map file, use the table flag.
  • At the command line, there is no flag; instead, ncluster_loader assumes that you pass the named-flag arguments first, then the table name, and finally the source file name or directory name.
--truncate-table truncate-table Performs a TRUNCATE operation to empty the table before beginning the load. If child tables are present, they will be truncated, too. By default, this feature is disabled.
-U [ --username ] arg username If present, the Aster Loader Tool connects to the database with this username instead of the default ("beehive"). (The user account must have permission to do so.) Note that when the --enable-sso parameter is not specified, the user needs to specify the username and password in order to connect to Aster Database (this is the default mode of authenticating while using the tool).
-v [ --vacuum-table ]   Runs VACUUM on the target table if the load fails. If target child tables or partitions are present, they are VACUUMed, too. By default, this feature is disabled.
-V [ --version ]   Print the Aster Loader Tool version and exit.
--verbose   Run in verbose mode.
-w [ --password ] arg password Connect to the database using the specified password (as opposed to providing a password at the prompt). Note that when the --enable-sso parameter is not specified, the user needs to specify the username and password in order to connect to Aster Database (this is the default mode of authenticating while using the tool).
-W [--password-prompt]   Forces the Aster Loader Tool to prompt for a password before connecting to a database. It should automatically prompt for a password whenever the server requests password authentication. If no password prompt is issued and the server requires password authentication, the connection attempt will fail.
-z [ --auto-analyze ]   Runs ANALYZE on the table(s) after loading the data and sets the hint bits on the table(s). By default, this is disabled. If data was loaded to child tables via autopartitioning, they will be analyzed, as well.

Note that analyzing a columnar table may be slow if there are many columns. To improve the speed of statistics collection, execute a separate ANALYZE command after the load that only processes the columns involved in query row filters or grouping.

--enable-sso   Enables SSO support for Aster Loader Tool.
Log in to the client machine using your key distribution center (KDC) domain username and password.

The user needs to use the --enable-sso parameter in order to connect to Aster Database for authentication using SSO. When the --enable-sso parameter is not specified, the user needs to specify the username and password in order to connect to Aster Database (this is the default mode of authenticating while using the tool).

--gss-lib-path <path>   [Linux only] Sets the GSS shared library path (default on Linux is /opt/quest/lib32 or /opt/quest/lib64). Ignored on Windows.