Execute COPY Command

Operation name

Function overview

Executes COPY command to Amazon Redshift.

= Remarks =

For details on COPY command specifications of this component, refer to the following link page:

"Amazon Redshift document - Database Developer Guide - COPY" - (http://docs.aws.amazon.com/en_us/redshift/latest/dg/r_COPY.html)

Data model

The data model of this component is table model type.

Properties

= Remarks =

For details on use of variables, refer to Variables.

Basic settings

Item name	Required/Optional	Use of variables	Description	Remarks
Name	Required	Not available	Enter a name that is used on the script canvas.

Required settings

Item name	Required/Optional	Use of variables	Description	Remarks
connection destination	Required	Not available	Select a connection resource. Add A new connection resource can be added. Edit list Connection resource settings can be edited in > HULFT INTEGRATE > Connections.
COPY command	Required	Available	Enter the COPY sentence that is issued to Amazon Redshift.	Note COPY command needs to start from "COPY". The following values of CREDENTIALS specified in COPY command will be masked with "******" and output to executing log. Secret access key [aws_secret_access_key] Token [token] Encryption master key [master_symmetric_key]
COMMIT when command is successfully terminated.	Optional	Not available	Select whether to perform COMMIT if command successfully finishes. Selected Perform COMMIT. Process finishes successfully. Not selected (Default) Don't perform COMMIT if error data exists. Process finishes unsuccessfully. If error data doesn't exist, perform COMMIT.	You can set an allowance value for error data by specifying MAXERROR option of COPY command.

Item name

Required/Optional

Use of variables

Description

Remarks

connection destination

Required

Not available

Select a connection resource.

Add: A new connection resource can be added.
Edit list: Connection resource settings can be edited in > HULFT INTEGRATE > Connections.

COPY command

Required

Available

Enter the COPY sentence that is issued to Amazon Redshift.

Note

COPY command needs to start from "COPY".

The following values of CREDENTIALS specified in COPY command will be masked with "******" and output to executing log.
- Secret access key [aws_secret_access_key]
- Token [token]
- Encryption master key [master_symmetric_key]

COMMIT when command is successfully terminated.

Optional

Not available

Select whether to perform COMMIT if command successfully finishes.

Selected

Perform COMMIT. Process finishes successfully.

Not selected

(Default)

Don't perform COMMIT if error data exists. Process finishes unsuccessfully.

If error data doesn't exist, perform COMMIT.

You can set an allowance value for error data by specifying MAXERROR option of COPY command.

Data processing method

Item name	Required/Optional	Use of variables	Description	Remarks
Data processing method	Required	Not available	Select a data processing method. Apply script settings (Default) The setting for mass data processing in the script properties is applied to the connector. Do not perform mass data processing Mass data processing isn't performed. Perform mass data processing Mass data processing is performed.

Item name

Required/Optional

Use of variables

Description

Remarks

Data processing method

Required

Not available

Select a data processing method.

Apply script settings

(Default)

The setting for mass data processing in the script properties is applied to the connector.

Do not perform mass data processing

Mass data processing isn't performed.

Perform mass data processing

Mass data processing is performed.

Comment

Item name	Required/Optional	Use of variables	Description	Remarks
Comment	Optional	Not available	You can write a short description of this connector.

Schemas

Input schema

None.

Output schema

<?xml version="1.0"?>
<table>
  <row>
    <column>STL_LOAD_ERRORS_userid</column>
    <column>STL_LOAD_ERRORS_slice</column>
    <column>STL_LOAD_ERRORS_tbl</column>
    <column>STL_LOAD_ERRORS_starttime</column>
    <column>STL_LOAD_ERRORS_session</column>
    <column>STL_LOAD_ERRORS_query</column>
    <column>STL_LOAD_ERRORS_filename</column>
    <column>STL_LOAD_ERRORS_line_number</column>
    <column>STL_LOAD_ERRORS_colname</column>
    <column>STL_LOAD_ERRORS_type</column>
    <column>STL_LOAD_ERRORS_col_length</column>
    <column>STL_LOAD_ERRORS_position</column>
    <column>STL_LOAD_ERRORS_raw_line</column>
    <column>STL_LOAD_ERRORS_raw_field_value</column>
    <column>STL_LOAD_ERRORS_err_code</column>
    <column>STL_LOAD_ERRORS_err_reason</column>
  </row>
  <row>
    :
  </row>
</table>

Column name		Description
row	-	Outputs a row for each record.
	STL_LOAD_ERRORS_userid	Outputs the user ID that created entry.
	STL_LOAD_ERRORS_slice	Outputs the slice where the error occurred.
	STL_LOAD_ERRORS_tbl	Outputs the table ID.
	STL_LOAD_ERRORS_starttime	Outputs the start time of loading in UTC.
	STL_LOAD_ERRORS_session	Outputs the session ID of load executing session.
	STL_LOAD_ERRORS_query	Outputs the query ID.
	STL_LOAD_ERRORS_filename	Outputs the complete path to the load input file.
	STL_LOAD_ERRORS_line_number	Outputs the row number where error occurred in the load file.
	STL_LOAD_ERRORS_colname	Outputs the field where error occurred.
	STL_LOAD_ERRORS_type	Outputs the data type of field.
	STL_LOAD_ERRORS_col_length	If defined, outputs the column length.
	STL_LOAD_ERRORS_position	Outputs the position of error in the field.
	STL_LOAD_ERRORS_raw_line	Outputs the raw loading data including errors.
	STL_LOAD_ERRORS_raw_field_value	Outputs the pre-parsing value of the "colname" field that raises the parsing error.
	STL_LOAD_ERRORS_err_code	Outputs error code.
	STL_LOAD_ERRORS_err_reason	Outputs error description.

Loading schema in Mapper

The schema is loaded automatically.

= Remarks =

For details, refer to Edit Schema.

Mass data processing

Mass data processing is supported.

Parallel Stream Processing

PSP isn't supported.

Available component variables

Component variable name	Description	Remarks
copy_count	The number of data records for which COPY succeeded is stored.	The default value is null.
copy_query_id	Query ID is stored.	The default value is null.
has_error_data	Existence of error data is stored.	The default value is null. "true" is stored if error data exists, and "false" is stored if not. If "true", it's recommended to output data from the output schema.
error_data_count	The number of error data records is stored.	The default value is null.
command_message	Result message of COPY command is stored.	The default value is null.
message_category	When an error occurs, the category of the message code corresponding to the error is stored.	The default value is null.
message_code	When an error occurs, the code of the message code corresponding to the error is stored.	The default value is null.
message_level	When an error occurs, the severity of the message code corresponding to the error is stored.	The default value is null.
error_type	When an error occurs, the error type is stored.	The default value is null. The format of the error type is as follows. Example: java.io.FileNotFoundException
error_message	When an error occurs, the error message is stored.	The default value is null.
error_trace	When an error occurs, the trace information for the error is stored.	The default value is null.

Error data

If load error is output to STL table "STL_LOAD_ERRORS" of Amazon Redshift after executing COPY command, error data can be output.

The search key of STL table "STL_LOAD_ERRORS" is the query ID.

= Remarks =
For details on STL table "STL_LOAD_ERRORS", refer to the following link page:
- "Amazon Redshift document - Database Developer Guide - STL_LOAD_ERRORS" - (http://docs.aws.amazon.com/en_us/redshift/latest/dg/r_STL_LOAD_ERRORS.html)
If you prefer to get additional details of the accurate column and row of data where an error occurred after getting error data, check STL table "STL_LOADERROR_DETAIL" by using query ID as the key.

= Remarks =
For details on STL table "STL_LOADERROR_DETAIL", refer to the following link page:
- "Amazon Redshift document - Database Developer Guide - STL_LOADERROR_DETAIL" - (http://docs.aws.amazon.com/en_us/redshift/latest/dg/r_STL_LOADERROR_DETAIL.html)

Message codes, exception messages, and limitations

Connector	Message code	Exception message	Limitations
Messages and limitations of the Amazon Redshift connector			None

Notes

To execute COPY command, INSERT privilege is required for Amazon Redshift table.
Query ID is updated when executing COPY command. However, if this query ID exceeds the maximum value (2147483647), the next software maintenance of Redshift may fail.
In this case, query ID will be initialized by performing Resize of cluster before it exceeds the maximum value of query ID.