When you select the format of a Google Cloud Storage file for a source or a target, you can configure the formatting options.
The following table describes the Google Cloud Storage V2 file formatting options that you can configure:
Option
Description
Schema Source
The schema of the source or target file. You can select one of the following options to specify a schema:
- Read from data file. Google Cloud Storage V2 Connector imports the schema from the file in Google Cloud Storage. Applicable only to mappings in advanced mode.
- Import from schema file. Imports schema from a schema definition file in your local machine.
Schema File
The schema definition file on the agent machine from where you want to upload the schema.
This property appears only if you select Import from schema file in the Schema Source property.
You can use the following file formats:
- Avro
- Flat
- JSON
- Parquet
You can't upload a schema file when you select the Create Target option to write data to Google Cloud Storage.
The following table describes the formatting options for delimited and fixed width flat file types:
Option
Description
Flat File Type
The type of flat file.
Select one of the following options:
- Delimited. Reads a flat file that contains column delimiters.
- Fixed Width. Reads a flat file with fields that have a fixed length. You must select the file format in the Fixed Width File Format option.
If you do not have a fixed-width file format, click New > Components > Fixed Width File Format to create one.
Delimiter
Character used to separate columns of data. You can configure parameters such as comma, tab, colon, semicolon, or others.
Default is comma (,).
If you specify a multibyte character as a delimiter in the source object, the mapping fails.
Note: To set a tab as a delimiter, you must type the tab character in any text editor. Then, copy and paste the tab character in the Delimiter field.
Escape Character
Character immediately preceding a column delimiter character embedded in an unquoted string, or immediately preceding the quote character in a quoted string.
Default is backslash (\).
Qualifier
Quote character that defines the boundaries of data. You can set the qualifier as a single quote or double quote.
Default is double quote (").
Qualifier Mode
Specify the qualifier behavior for the target object. You can select one of the following options:
- MINIMAL. Applies qualifier to data enclosed within a delimiter value or a special character.
- ALL. Applies qualifier to all data.
- NON_NUMERIC. Not applicable.
- ALL_NON_NULL. Not applicable.
Default is MINIMAL.
Disable escape char when a qualifier is set
Check to disable the escape character when a qualifier value is already set.
Code Page1
The code page that the Secure Agent must use to read or write data. You can select from the following code pages:
- UTF-8. Select for Unicode and non-Unicode data.
- MS Windows Latin 1. Select for ISO 8859-1 Western European data.
- Shift-JIS. Select for double-byte character data.
- ISO 8859-15 Latin 9 (Western European).
- ISO 8859-2 Eastern European.
- ISO 8859-3 Southeast European.
- ISO 8859-5 Cyrillic.
- ISO 8859-9 Latin 5 (Turkish).
- IBM EBCDIC International Latin-1.
Default is UTF-8.
Header Line Number
Specify the line number that you want to use as the header when you read data from the Google Cloud Storage. You can also read a file that doesn't have a header.
To read data from a file with no header, specify the value of the Header Line Number field as 0. To read data from a file with a header, set the value of the Header Line Number field to a value that is greater than or equal to one.
Default is 1.
Ensure that the value of the Header Line Number field is lesser than or equal to the value of the First Data Row field.
When you create a mapping in advanced mode, set the value of the header line number to 0, 1, or empty to run the mapping successfully.
This property is applicable during runtime and data preview to read a file.
First Data Row1
Specify the line number from where you want to read data. You must enter a value that is greater or equal to one.
To read data from the header, the value of the Header Line Number and the First Data Row fields must be the same.
Default is 2.
This property is applicable when you preview data in a read and write operation. Also applies when you run the mapping to read the data.
Target Header
Select whether you want to write data to a flat file with or without a header.
Default is With Header.
This property is not applicable when you read data from a Google Cloud Storage source.
Distribution Column1
Specify the name of the column that is used to create multiple target files during runtime.
This property is not applicable when you read data from a Google Cloud Storage source.
Maximum Rows To Preview
Not applicable.
Row Delimiter
Not applicable.
1 Applies only to mappings.
The following table describes the formatting options for JSON files:
Property
Description
Number of Rows to Sample1
Specify the number of rows to read to find the best match to populate the metadata.
Default is 1.
Memory available to process data (in MB)1
The memory that the parser uses to read the JSON sample schema and process it.
The default value is 2.
Set the value to the file size that you want to read. If the file size is more than 2 MB, you might encounter an error.
Read multiple-line JSON files
Not applicable.
1Applies only to mappings in advanced mode.
Fixed-width file formats
You can use a fixed-width flat file as a source or target in mappings and mapping tasks.
When you configure a Source or Target transformation and select the fixed-width flat file type, you must select the most appropriate fixed-width file format to use based on the data in the fixed-width flat file.
See the following exceptions before you use a fixed-width flat file:
•You cannot use a fixed-width flat file as a source or target for mappings in advanced mode and data transfer tasks.
•When you create a flat file target at runtime and select a fixed width file format, the Secure Agent ignores the fixed-width column boundaries that you specified for the fixed-width flat file format and applies the additional fixed width attributes for the new target object.
•When you use a fixed-width flat file as a source or target, you cannot edit the metadata for the fields.
•When you write a column of Numeric data type from fixed-width flat file source to an empty fixed-width flat file target that use the same fixed-width file format, the Secure Agent appends a null character to the value in Numeric column in the target.
•When you use a Secure Agent installed on a Linux machine and create a fixed-width file format, ensure that the sample file uses the \n character as the new line symbol, and that source files use the same symbol.
•When you use a Secure Agent installed on a Windows machine and create a fixed-width file format, ensure that the sample file uses the \r\n character as the new line symbol, and that source files use the same symbol.
•When you create a fixed-width file format, ensure that the sample flat file only uses UTF-8 character set encoding.
Rules and guidelines for file formatting options
You must set the appropriate formatting options when you select the Flat, Avro, JSON, ORC, or Parquet format types. Use the following guidelines when you configure file formatting options:
•You must use the same qualifier and delimiter in all the fields and columns of a schema file.
•If you select the delimited format type and select Import from schema file as the value of the Schema Source formatting option, you can only upload a schema file in the JSON format.
•When you use a flat file, use the following format for the schema file:
•If you select the Avro, JSON, or Parquet format type and select Read from data file as the value of the Schema Source formatting option, you cannot view the schema of the file.
•When you read data from or write data to a file of the Avro or Parquet format, you must increase the Java heap size based on the amount of data you want to read or write. Increase the Java heap size in the JVM options for type DTM in the System Configuration Details section of the Secure Agent. Otherwise, the mapping task fails.
•When you select a file of JSON format that contains a large amount of data, you cannot preview data.
•When you read data from a flat file that contains a newline character, you must enclose the data within double quotation marks. Otherwise, the Secure Agent displays incorrect number of success rows in the session log.
•When you run a mapping that uses the None file format and set the Is Directory advanced property, the mapping reads only one file from the directory.
•When you import Avro, JSON, or Parquet format in a mapping task, you might encounter name collision errors with the credential file. To prevent this issue, configure the following property in the System Configuration Details section of the Secure Agent: