Execute COPY Command

Operation Name

Execute COPY Command

Function Overview

Execute COPY command to Amazon Redshift.
For details on COPY command specifications of this component, refer to the following link page.

Data Model

Table Model type.

Properties

For information about using variables, refer to "variables".
Basic settings
Item name Required/Optional Use of Variables Description Remarks
Name Required Not available Enter the name to show on the script canvas.  
Required settings
Item name Required/Optional Use of Variables Description Remarks
Destination Required Not available Select a global resource.
  • [Add]:
    Adds a new global resource.
  • [Edit list]:
    Enables to edit the global resource settings in the "Edit resource list".
  
COPY command Required Not available Enter the COPY sentence that is issued to Amazon Redshift.
  • COPY command needs to start from "COPY".
  • The value after CREDENTIALS specified in COPY command will be output to executing log, masked with "******".
    • Secret access key[aws_secret_access_key]
    • Token[token]
    • Encryption master key[master_symmetric_key]
COMMIT if command successfully finishes Optional Not available Select whether to COMMIT if command successfully finishes.
  • [Checked]:
    Performs COMMIT. Process becomes successful finish.
  • [Not checked]:(default)
    Not performs COMMIT if error data exists. Process becomes abnormal finish. If error data does not exist, performs COMMIT.
  • Setup of allowance value for error data is available 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.
  • [Use script settings]:(default)
    Applies the setting of mass data processing set up in script property to the adapter.
  • [Disable]:
    Mass data processing is not performed.
  • [Enable]:
    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 adapter.
The description will be reflected in the specifications.		  

Schema

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 Remarks
row - Outputs a row by a record unit.  
STL_LOAD_ERRORS_userid Outputs the user ID that created entry.  
STL_LOAD_ERRORS_slice Outputs the slice that error occurred.  
STL_LOAD_ERRORS_tbl Outputs the table ID.  
STL_LOAD_ERRORS_starttime Outputs the start time of loading showed as 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 perfect path to the load input file.  
STL_LOAD_ERRORS_line_number Outputs the row number that error occurred in the load file.  
STL_LOAD_ERRORS_colname Outputs the field that 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 in advance analytic value of "colname" field raising analytic error.  
STL_LOAD_ERRORS_err_code Outputs error code.  
STL_LOAD_ERRORS_err_reason Outputs error description.  

Loading Schema on Mapper

Schema is loaded automatically.
For more details, refer to, "Edit Schema".

Mass Data Processing

Mass Data Processing is supported.

PSP Usage

PSP is not supported.

Available Component Variables

Component variable name Description Remarks
copy_count Stores data number that succeeded to COPY.
  • Default value is null.
copy_query_id Stores query ID.
  • Default value is null.
has_error_data Stores existence of error data.
  • Default value is null.
  • Stores "true" if error data exists, and stores "false" if not.
  • If "true", outputting data from output schema is recommended.
error_data_count Stores number of error data.
  • Default value is null.
command_message Stores result message of COPY command.
  • Default value is null.
message_category Stores the category of message code corresponding to the occured error.
  • Default value is null.
message_code Stores the code of message code corresponding to the occured error.
  • Default value is null.
message_level Stores the severity of message code corresponding to the occured error.
  • Default value is null.
error_type Returns error type when error occurred.
  • Default value is null.
  • Error is represented in the format depicted below.
    Example:java.io.FileNotFoundException
  • The message may vary depending on the DataSpider Servista version.
error_message Returns error message when error occurred.
  • Default value is null.
  • The message may vary depending on the DataSpider Servista version.
error_trace Returns trace information when error occurred.
  • Default value is null.
  • The message may vary depending on the DataSpider Servista version or the client application used.

Error Data

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

Specification Limits

None.

Main Exceptions

Exception name Cause Solution
ResourceNotFoundException
 Resource definition could not be found.Name: [] [Destination] is not specified. Specify [Destination].
ResourceNotFoundException
Resource definition could not be found.Name: [<name of Global Resources>]		 Resource definition selected in [Destination] is not found. Check the global resource specified in [Destination].
InvalidPropertyConfigurationException <Property name> is required. Required property is not entered. Specify [<Property name>].
COPY command does not start from 'COPY'[<COPY command>] COPY command specified in [COPY command] does not start from "COPY". Enter COPY command starting from "COPY".
java.sql.SQLException Failed to connect. Failed to connect to Amazon Redshift. Check global resource [URL].
Connection is denied. Search that host name and port number are correct, and post master allows TCP/IP connection. Cannot connect to Amazon Redshift. Check the global resource [URL] or setting of Amazon Redshift security group.
[Amazon](500310) Invalid operation: Invalid credentials. Must be of the format: credentials 'aws_iam_role=...' or 'aws_access_key_id=...;aws_secret_access_key=...[;token=...]' CREDENTIALS is in false format. Check CREDENTIALS specified in COPY command.
The AWS Access Key Id you provided does not exist in our records. Access key ID is false. Check the access key ID specified in CREDENTIALS of COPY command.
The request signature we calculated does not match the signature you provided. Secret access key is false. Check the secret access key specified in CREDENTIALS of COPY command.
Access Denied Access is denied. Check the access authorization to data source specified in FROM of Amazon Redshift and COPY command.
SSL Error: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target Failed in SSL communication. Check whether certification is imported to key store that DataSpiderServer refers.
For more details on certification import, refer to "Certification Import".
[Amazon](500310) Invalid operation: Cannot COPY into nonexistent table <Table name> Table does not exist. Check the table name specified in COPY command.
AmazonRedshiftIllegalStateException [<Number of error data>] number of error data exists. Check the data. Loading error occurs during COPY command execution. Check error data.

Notes