Greenplum Connector > Mappings and mapping tasks with Greenplum Connector > Greenplum targets in mappings
  

Greenplum targets in mappings

To write data to Greenplum, configure a Greenplum object as a target in the mapping.
Specify the name and description of the Greenplum target. Configure the target and advanced properties for the target object.
You can parameterize the target object name.
The following table describes the target properties that you can configure in a Target transformation:
Property
Description
Connection
Name of the target connection.
Target Type
Type of the target object. Select Single Object or Parameter.
Object
Target object for a single target. If you select parameter, define a name and type of data object.
Operation
The target operation.
The following table describes the Greenplum advanced target properties:
Property
Description
Method
Determines how the gpload utility processes the data from the named pipe.
You can select from the following methods:
  • - Insert. Inserts rows into the target.
  • - Update. Updates rows in the target.
  • - Merge. If the rows exist in the target, updates the existing rows. If the rows do not exist in the target, inserts the rows into the target.
Match Columns
Matches rows based on the comma-separated list of column names. Enclose the column names in single quotation marks and ensure that there are no leading or trailing spaces between the column names.
Update Columns
Updates the columns specified in the comma-separated list of column names. Enclose the column names in single quotes and ensure that there are no leading and trailing spaces between the column names.
Update Condition
Updates a row based on the condition that you specify. The gpload utility performs an update or merge operation based on the update condition.
Format
The Secure Agent writes data in a format that is compatible with the gpload utility.
Select one of the following values:
  • - Text. In the text format, the Secure Agent separates data using the delimiter character that you specify in the task properties.
    • If the data contains the delimiter or escape characters specified in the task properties, you can choose to ignore the escape character or specify delimiter and escape character values that are not a part of the data.
  • - CSV. In the CSV format, the Secure Agent encloses the data with the quote character that you specify in the task properties. The Secure Agent also separates the data using the delimiter character specified in the task properties.
    • If the data contains the quote or escape characters specified in the task properties, you can choose to ignore the escape character or specify quote and escape character values that are not a part of the data.
Default is Text.
Note: If the data contains newline characters, you must use the CSV format. If you use the text format and the data contains newline characters, the data after the newline character is treated as a new record. In such situations, the gpload utility might reject or insert incorrect data into the tables.
Delimiter
Separates successive input fields.
For data in the text format, use any 7-bit ASCII value except a-z, A-Z, and 0-9.
For data in the CSV format, use any 7-bit ASCII value except \n, \r, and \\.
Default is pipe (|).
You can also specify a non-printable ASCII character through an escape sequence by using the decimal representation of the ASCII character. For example, \014 represents the shift out character.
Escape
Character that treats special characters in the data as regular characters.
In the text format, special characters comprise delimiter and escape characters.
In the CSV format, special characters comprise quotes and escape characters.
Use any 7-bit ASCII value as an escape character.
Default is backslash (\).
Note: You can improve the task performance if the data does not contain escape characters.
Skip Escaping
Skips escaping special characters in the data.
Clear this option to treat special characters in the data as regular characters.
Null As
String that represents a null value.
In the source data, any data item that matches the string is treated as a null value.
Default is backslash N (\N).
Quote
Character that encloses the data in the CSV format.
The Secure Agent encloses data by the specified character and passes the data to the gpload utility. The quote character is ignored for data in the text format.
Use any 7-bit ASCII value that is not equal to the delimiter or null value.
Default is double quotes (“).
Error Limit
For each Greenplum segment, across all partitions if pass-through partitioning is used, the number of rows that the gpload utility discards or logs in the error table because of format errors.
The gpload utility fails the session if the error limit is reached for any Greenplum segment.
Default is zero. The maximum error limit is 2,147,483,647.
Error Table
Name of the error table where the gpload utility logs rejected rows when reading data that is processed by the Secure Agent.
The naming convention for the table name is <schema name>.<table name>, where schema name is the name of the schema that contains the table.
Note: For Greenplum server versions 5.0 and later, LOG_ERRORS are generated instead of an error table. For more information, see the Greenplum documentation.
Greenplum Pre SQL
The SQL command to run before loading data to the target.
Greenplum Post SQL
The SQL command to run after loading data to the target.
Reuse Table
Determines if the gpload utility drops the external table objects and staging table objects it creates. The gpload utility reuses the objects for future load operations that use the same load specifications.
Default is unchecked.
Truncate Target Option
Truncates the Greenplum target table before loading data to the target.
Greenplum Target Table
Overrides the default target table name.
Greenplum Loader Logging
Sets the logging level for the gpload utility.
You can select one of the following values:
  • - None
  • - Verbose
  • - Very Verbose
Default is None.
Greenplum gpfdist Timeout
The number of seconds that elapse before the gpfdist process times out when attempting to connect to the target.
The default value is 30 seconds.
Window Pipe Buffer Size
The size (in kilobytes) that the Secure Agent allocates to buffer data before writing to the Greenplum bulk loader.
Enter a value between 1 and 2048. The default value is 2048 KB.
You might need to test different settings for optimal performance. This attribute is applicable for Informatica servers that run on Windows.
Delete Control File
Determines if the Secure Agent must delete the gpload control file after the task is complete.
Default is selected.
Gpload Log File Location
The file system location where the gpload utility generates the gpload log file.
Default is $PMRootDir/Temp.
Gpload Control File Location
The file system location where the Secure Agent generates the gpload control file.
Default is $PMRootDir/Temp.
Encoding
Character set encoding of the source data.
Greenplum Connector supports only the UTF-8 character set encoding.
Pipe Location
The file system location where the pipes that are used for data transfer are created.
Default is $PMRootDir/Temp.
Schema Override
Overrides the schema specified in the Greenplum connection object.
If you do not configure this property, the Data Secure Agent uses the schema value specified in the Greenplum connection object.
Max_Line_Length
The integer that specifies the maximum length of a line in the XML transformation data that is passed to gpload.
Success File Directory
Not applicable.
Error File Directory
Not applicable.
Forward Rejected Rows
Not applicable.

Match and update columns

Before you run a session that loads data to a Greenplum target, you can configure the match and update columns.

Match Columns

You can specify the columns to use as the join condition for the update. The attribute value in the specified target columns must be equal to that of the corresponding source data columns in order for the row to be updated in the target table. You must specify the match columns if the method to process data from the named pipe is update or merge.

Update Columns

You can specify the columns to update for the rows that meet the criteria for match columns and the update column property. Update columns cannot be columns that are used for the Greenplum distribution key for the table. You must specify the match columns if the method to process data from the named pipe is update or merge.
The following table describes the session properties you can configure for match and update columns:
Target Property
Description
Match Column
Matches rows based on the comma-separated list of column names. Enclose the column names in double quotes and ensure that there are no leading and trailing spaces between the column names.
Update Column
Updates the columns specified in the comma-separated list of column names. Enclose the column names in double quotes and ensure that there are no leading and trailing spaces between the column names.

Error handling for Greenplum targets

You can set the error limit for a Greenplum segment to specify the number of rows that the gpload utility can discard before it fails a session. If you specify an error table, the gpload utility logs the discarded rows in the error table.
The error limit includes rows with format errors. The default value is 0. By default, the gpload utility stops a session when it encounters a row with format errors.
Use the following naming convention for the error table name: <schema name>.<table name>
If you do not specify a schema name, the gpload utility creates the error table in the public schema. The error table format is predefined in the Greenplum database.
For more information about the error tables, see the Greenplum documentation.
You can view load statistics in the session log. The gpload utility writes the error messages to the gpload log. The Secure Agent reads the gpload log and writes the errors to the session log. The gpload utility writes the error messages to the gpload log at the following location:
$INFA_HOME/server/infa_shared/temp