Extractor Parameters (U-SQL)
Summary
| Parameters |
|---|
| ● delimiter ● encoding ● escapeCharacter ● nullEscape ● quoting ● rowDelimiter ● silent ● skipFirstNRows ● charFormat
|
The supported parameters and their defaults are:
| Parameter name | Parameter type | Default value |
|---|---|---|
delimiter |
char |
',' (comma) |
This parameter specifies the column separator character that separates columns in the file. The default column separator for is a comma (','). It can be any valid Unicode character including those that are represented with multi-byte encodings in any of the Unicode-Transfer-Formats (i.e. UTF-*). The delimiter parameter is not available for Extractors.Csv() and Extractors.Tsv().
Important
If the quoting parameter is set to false, the delimiter character inside a quoted string is being used as a column separator and may lead to incorrect or failing extractions.
| Parameter name | Parameter type | Default Value |
|---|---|---|
encoding |
System.Text.Encoding |
Encoding.UTF8 |
Per default, files are assumed to be stored in UTF-8 encoding. However, some files may be stored using a different encoding. The encoding parameter provides the option to specify the file’s actual encoding and also translate non-UTF-8 encoded files.
The supported encodings are:
| Encoding Property | Description |
|---|---|
Encoding.[ASCII] |
Assumes that the file is encoded using the ASCII (7-bit) character set. Since ASCII is an all uppercase keyword, it needs to be quoted to not conflict with U-SQL’s reserved keyword rule. |
Encoding.BigEndianUnicode |
Assumes that the file is encoded using the UTF-16 format that uses the big endian byte order. |
Encoding.Unicode |
Assumes that the file is encoded using the UTF-16 format using the little endian byte order. |
Encoding.UTF7 |
Assumes that the file is encoded using the UTF-7 format. |
Encoding.UTF8 |
Assumes that the file is encoded using the UTF-8 format. This is the default. |
Encoding.UTF32 |
Assumes that the file is encoded using the UTF-32 format using the little endian byte order. |
Important
System.Text.Encoding.Default is not supported, since it would use the processing node’s operating system’s current ANSI code page that cannot be controlled by the user and is not the same as the default of the extractor. For more details see System.Text.Encoding.
If the file contains a code point that is invalid for the specified encoding, a runtime error during extraction will occur regardless of the silent parameter setting that will indicate the offending data value.
| Parameter name | Parameter type | Default value |
|---|---|---|
escapeCharacter |
char? |
null |
The escapeCharacter parameter – if not set to null – specifies the character in the file that is used to escape itself and all delimiter values in the file.
If the escape character is followed by a value other than itself or any of the delimiter values, the escape character is dropped when reading the value.
If it set to null, there is no escape character specified.
The escapeCharacter parameter will be applied regarding of whether quoting is enabled or not. It however will not be used to escape the quoting character. The quoting character will get escaped with double-quotes in alignment with the Excel CSV behaviour.
| Parameter name | Parameter type | Default value |
|---|---|---|
nullEscape |
string |
null |
The nullEscape parameter specifies the string in a column that is used to represent the null value. It will be applied regardless of whether quoting is enabled or not.
If the textual input of the field is not exactly equal the nullEscape string sequence, then the string sequence will not be interpreted as a null value and it will be treated like any other input string.
If the textual input is the zero-length string and the target type is a nullable non-string type, then it will also be interpreted as NULL.
If nullEscape is set to null (the default), no special null value is being interpreted. A zero-length textual representation (before removing quotes) will be interpreted as a zero-length string for target type string, and null for all non-string nullable types. A zero-length string with quotes will be a zero-length string for both type categories.
If the target type of the extraction is not a nullable type and the value is interpreted as null, an E_RUNTIME_USER_EXTRACT_COLUMN_CONVERSION_NULL_ERROR error will be raised.
The following table gives some examples on how different inputs are being interpreted based on the target types for the fields (using int? as the representative type for nullable non-string types) and the value for null_escape (ERR indicates a runtime conversion error) using the row value
1,,"",\N,#NULL#,"Some \N fun"
| nullEscape | String | int? |
|---|---|---|
| "\N" | "1","","",null,"#NULL#","Some \N fun" | 1,null,ERR,null,ERR, ERR |
| "" | "1","",null,"\N","#NULL#","Some \N fun" | 1,null,null,ERR,ERR,ERR |
| null | "1",null,"","\N","#NULL#","Some \N fun" | 1,null,ERR,ERR,ERR,ERR |
| "#NULL#" | "1","","","\N","#NULL#","Some \N fun" | 1,null,ERR,ERR,null,ERR |
| Parameter name | Parameter type | Default value |
|---|---|---|
quoting |
bool |
true |
The quoting parameter if set to true indicates that the extractor should consider " (double-quote) as a column field quotation which inhibits the interpretation of the column delimiters inside the quoted field. A double-quote inside a quoted field needs to be escaped by doubling it as "" in the file.
Important
Due to the parallel processing and splitting of input files, quoting cannot inhibit the interpretation of the row delimiters. Thus for example an input Excel CSV file that contains an unescaped carriage return or linefeed will cause the extraction to fail even if these delimiters are quoted. They will have to be escaped. Currently no other quoting characters are supported.
Tip
If the input data does not use quoting, specifying false for the quoting parameter will be resulting in a faster extraction.
| Parameter name | Parameter type | Default values |
|---|---|---|
rowDelimiter |
string (max length=1) |
"\r\n" (carriage return, linefeed)"\r" (carriage return)"\n" (linefeed) |
This parameter specifies the row separator character sequence that separates rows in the file. If the rowDelimiter is set to null, the default values are being used which are carriage return followed by linefeed, or carriage return or linefeed.
If the length of the rowDelimiter is more than one UCS-4 characters, an error is raised**.**
The comparison of the rowDelimiter will be done using a byte-wise comparison after applying the encoding got applied.
Note that the rowDelimiter character inside a quoted string will not be escaped and will be used as a row separator which will lead to incorrect or failing extractions.
| Parameter name | Parameter type | Default Value |
|---|---|---|
silent |
bool |
false |
This parameter tells the extractor to ignore and skip rows that have a different number of columns than the requested number of columns. It also replaces corrupt column data with null when the datatype is nullable (for example, it replaces "int?" with null when there is a non-digit character in the column). If the column type is not nullable, an error is raised.
Rows are checked for the correct number of columns first. For example, if there is corrupt data in a column with the wrong number of rows, the extractor skips the row. There is no conversion. If the row has the correct number of columns, however, the extractor doesn't skip it. The conversion occurs (assuming that the column's type is nullable).
Important
silent does not ignore encoding errors since such errors often indicate that the file encoding and the specified encoding are not compatible and thus could lead to more severe data corruption.
| Parameter name | Parameter type | Default Value |
|---|---|---|
skipFirstNRows |
Int |
0 |
The parameter specifies the number of rows to skip. The rows being skipped do not need to conform to the column schema in either type or count of columns. Note, you can only skip rows that are in the first segment of a file; otherwise, an error will be raised.
| Parameter name | Parameter type | Default Value |
|---|---|---|
charFormat |
string |
"uint16" |
The supported values are:
| Value | Description |
|---|---|
| uint16 or null | This is the default value. Serializes the char value as an integral number (taking all other serialization options into account) and parses the input as the integral character code number to the corresponding character or errors if the input is not an integral character code (or mapable null if extracting it as char?). |
| string | Serializes the char value in its Unicode string representation (taking all other serialization options including encoding into account) and parses the input as the character codepoint using the specified encoding. |