Execute COPY INTO Table

Operation Name

Function Overview

Execute the COPY INTO <table> command (hereinafter referred to as COPY command) to load data from staged files into a table.

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

"COPY INTO <table>" - (https://docs.snowflake.com/en/sql-reference/sql/copy-into-table)

Data Model

Data model of this type of component is table model type.

Properties

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 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]: Add a new global resource. [Edit list]: Global resource settings can be edited in “Edit resource list” screen.
Stage name	Required	Available	Select or enter a stage name.	Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the COPY command. When a schema or stage name includes “.”, you can handle the name properly by enclosing it with quotation marks. The schema and stage name are “A.B” and “C.D” respectively, the proper way to enclose is ["A.B"."C.D"].
Enclose stage name with quotation marks	Optional	Not available	Select whether to enclose the stage name with quotation marks. [Checked]: Stage name displayed in [Stage name] is enclosed with quotation marks. [Not checked]: (default) Stage name displayed in [Stage name] is not enclosed with quotation marks. If the stage name includes characters like space, you need to enclose the stage name with quotation marks. For details on characters that need to be enclosed with quotation marks, contact the vendor of the destination product.	Disabled when [Enter COPY command directly] is checked. At the time of execution, the stage name input in [Stage name] is applied as is. If the stage name entered in [Stage name] is enclosed with quotation marks, a query will be issued with the stage name enclosed with quotation marks also at the time of execution. If the stage name entered in [Stage name] is not enclosed with quotation marks, this property will be checked at the time of execution, and if this property is enabled, a query will be issued with the stage name enclosed with quotation marks. When environment variables are used in [Stage name], the value is deployed when enabling/disabling this property.
File/Folder path on stage	Optional	Available	Enter a file path or folder path on the stage.	A path needs to start with "/". "/" represents the root folder of a stage. Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the COPY command.
Table name	Required	Available	Select or enter a table name.	Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the COPY command. When a schema or table name includes “.”, you can handle the name properly by enclosing it with quotation marks. The schema and table name are “A.B” and “C.D” respectively, the proper way to enclose is ["A.B"."C.D"].
Enclose table name with quotation marks	Optional	Not available	Select whether to enclose the table name with quotation marks. [Checked]: Table name displayed in [Table name] is enclosed with quotation marks. [Not checked]: (default) Table name displayed in [Table name] is not enclosed with quotation marks. If the table name includes characters like space, you need to enclose the table name with quotation marks. For details on characters that need to be enclosed with quotation marks, contact the vendor of the destination product.	Disabled when [Enter COPY command directly] is checked. At the time of execution, the table name input in [Table name] is applied as is. If the table name entered in [Table name] is enclosed with quotation marks, a query will be issued with the table name enclosed with quotation marks also at the time of execution. If the table name entered in [Table Name] is not enclosed with quotation marks, this property will be checked at the time of execution, and if this property is enabled, a query will be issued with the table name enclosed with quotation marks. When environment variables are used in [Table name], the value is deployed when enabling/disabling this property.
Enter COPY command directly	Optional	Not available	Select whether to enter the COPY command directly. [Checked]: Enter the COPY command directly. [Not checked]: (default) The COPY command is generated based on the values specified in the properties.
COPY command	Required	Available	Enter the COPY command that is issued to Snowflake.	Enabled when [Enter COPY command directly] is selected. The COPY command needs to start with "COPY INTO".

Property actions

Item name	Description	Remarks
Get stage list	Get the stage names from the schema and set them in [Stage name].	Enabled when [Destination] is selected. Disabled when [Enter COPY command directly] is selected.
Get table list	Get the table names from the schema and set them in [Table name].	Enabled when [Destination] is selected. Disabled when [Enter COPY command directly] is selected.
Launch table browser	Launch the table browser. For details on how to operate, refer to “Table Browser“.	Enabled when [Get table list] is succeeded. Disabled when [Enter COPY command directly] is selected. The table specified in [Table name] is displayed initially.

File specification settings

Item name	Required/Optional	Use of variables	Description	Remarks
Specification method for files	Optional	Not available	Select a method to specify files to be loaded. [Load all files]: (default) All the files in the path specified for [File/Folder path on stage] are loaded. [Load files that match regular expression]: Files that match the specified pattern in [Regular expression pattern] are loaded. [Load files specified by name]: Files that match the specified names in [File name list/File name] are loaded.	Disabled when [Enter COPY command directly] is selected.
Regular expression pattern	Optional	Available	Enter a regular expression pattern.	Displayed when [Load files that match regular expression] is selected in [Specification method for files]. Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the PATTERN parameter of the COPY command.
File name list	Optional	-	Specify the list of file names. Each file name can be operated with the following buttons. [Add]: Add a file name. [Up]: Move the selected file name upwards by one row. [Down]: Move the selected file name downwards by one row. [Delete]: Delete the file name.	Displayed when [Load files specified by name] is selected in [Specification method for files]. Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the FILES parameter of the COPY command.
File name list/File name	Optional	Available	Enter a file name.

File format settings

Item name	Required/Optional	Use of variables	Description	Remarks
Named file format	Optional	Available	Enter a named file format that is used to load files.	Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the FORMAT_NAME parameter of the COPY command. When a schema or named file format includes “.”, you can handle the name properly by enclosing it with quotation marks. The schema and named file format are “A.B” and “C.D” respectively, the proper way to enclose is ["A.B"."C.D"].
Enclose named file format with quotation marks	Optional	Not available	Select whether to enclose the named file format with quotation marks. [Checked]: Named file format displayed in [Named file format] is enclosed with quotation marks. [Not checked]: (default) Named file format displayed in [Named file format] is not enclosed with quotation marks. If the named file format includes characters like space, you need to enclose the named file format with quotation marks. For details on characters that need to be enclosed with quotation marks, contact the vendor of the destination product.	Disabled when [Enter COPY command directly] is checked. At the time of execution, the named file format input in [Named file format] is applied as is. If the named file format entered in [Named file format] is enclosed with quotation marks, a query will be issued with the named file format enclosed with quotation marks also at the time of execution. If the named file format entered in [Named file format] is not enclosed with quotation marks, this property will be checked at the time of execution, and if this property is enabled, a query will be issued with the named file format enclosed with quotation marks. When environment variables are used in [Named file format], the value is deployed when enabling/disabling this property.

Column matching settings

Item name	Required/Optional	Use of variables	Description	Remarks
Column matching method	Optional	Not available	Select a method for matching file columns to table columns. [Match by order]: (default) Match columns by order. [Match by column name]: Match columns by name.	Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the MATCH_BY_COLUMN_NAME parameter of the COPY command.
Case sensitive	Optional	Not available	Select whether column names are case-sensitive. [Not checked]: (default) Column names are case-insensitive. [Checked]: Column names are case-sensitive.	Displayed when [Match by column name] is selected in [Column matching method]. Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the MATCH_BY_COLUMN_NAME parameter of the COPY command.

Error settings

Item name	Required/Optional	Use of variables	Description	Remarks
Action on error	Optional	Not available	Select an action when error occurred. [Abort]: (default) Abort the load operation if an error occurs. [Skip file]: Skip the file if an error occurs and load the next file. [Continue]: Skip the row if an error occurs and load the next row.	Disabled when [Enter COPY command directly] is selected. The value entered in this property is set for the ON_ERROR parameter of the COPY command.

Data processing method

Item name	Required/Optional	Use of variables	Description	Remarks
Data processing method	Required	Not available	Select data processing method. [Apply script settings]: (default) Apply the mass data processing settings of the script property to the adapter. [Do not perform mass data processing]: Mass data processing will not be performed. [Perform mass data processing]: Mass data processing will be 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>file</column>
    <column>status</column>
    <column>rows_parsed</column>
    <column>rows_loaded</column>
    <column>error_limit</column>
    <column>errors_seen</column>
    <column>first_error</column>
    <column>first_error_line</column>
    <column>first_error_character</column>
    <column>first_error_column_name</column>
  </row>
  <row>
    :
  </row>
</table>

Element name	Label	Description	Remarks
row	-	Repeated for the number of loaded files.
	file	Outputs the file path on the stage.
	status	Outputs the result of loading the file.	Outputs "LOADED" if all the rows are loaded successfully, "LOAD_SKIPPED" if the file is already loaded, "LOAD_FAILED" if no row is loaded, or "PARTIALLY_LOADED" if some rows are loaded but the others are not.
	rows_parsed	Outputs the number of load target rows.
	rows_loaded	Outputs the number of successfully loaded rows.
	error_limit	Outputs the error limit. If the number of errors reaches this limit, the load is aborted.	If status is "LOAD_SKIPPED" then "0" is output.
	errors_seen	Outputs the number of rows that failed to load.
	first_error	Outputs the cause of the first load error.	If status is "LOADED" then the value is blank.
	first_error_line	Outputs the line number of the first load error.	If status is "LOADED" or "LOAD_SKIPPED" then "0" is output.
	first_error_character	Outputs the position of the error character in the first load error line.	If status is "LOADED" or "LOAD_SKIPPED" then "0" is output.
	first_error_column_name	Outputs the column name of the first load error.	If status is "LOADED" or "LOAD_SKIPPED" then the value is blank.

Mass Data Processing

Mass Data Processing is supported.

Transaction

Transaction is supported.

PSP Usage

PSP is supported.

Refer to “Parallel Stream Processing” for more details.

Available Component Variables

Component variable name	Description	Remarks
loaded_file_count	The number of successfully loaded files is stored.	Default value is null. Value is not stored in PSP.
load_skipped_file_count	The number of already loaded files is stored.	Default value is null. Value is not stored in PSP.
load_failed_file_count	The number of files that failed to load is stored.	Default value is null. Value is not stored in PSP.
partially_loaded_file_count	The number of files that are partially loaded is stored.	Default value is null. Value is not stored in PSP.
error_code	When an error (SnowflakeSQLException) occurs, the corresponding error code is stored.	Default value is null. 0 is stored when finishing normally. Error code is based on driver specifications.
message_category	When an error occurs, the corresponding message code's category is stored.	Default value is null.
message_code	When an error occurs, the corresponding message code is stored.	Default value is null.
message_level	When an error occurs, the corresponding message code's severity is stored.	Default value is null.
error_type	When an error occurs, the error type is stored.	Default value is null. The error type will be in the following format. Example: “java.io.FileNotFoundException” The contents that will be stored may vary depending on DataSpider Servista versions.
error_message	When an error occurs, the error message is stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions.
error_trace	When an error occurs, the error's trace information is stored.	Default value is null. The contents that will be stored may vary depending on DataSpider Servista versions and client applications.

Specification Limits

None.

Major Exceptions

Exception name	Cause	Solution
ResourceNotFoundException Resource definition is not found. Name: []	[Destination] is not specified.	Specify [Destination].
ResourceNotFoundException Resource definition is not found. Name: [<global resource name>]	The resource definition selected in [Destination] is not found.	Check the global resource specified in [Destination].
InvalidPropertyConfigurationException Required item <property name> is not entered.	[<Property name>] is not entered.	Enter a value in [<property name>].
InvalidPropertyConfigurationException File/Folder path on the stage does not start with "/".	The path specified for [File/Folder path on stage] does not start with "/".	Enter a path that starts with "/" in [File/Folder path on stage].
net.snowflake.client.jdbc.SnowflakeSQLException	The operation for Snowflake failed.	Check the SnowflakeSQLException message. SnowflakeSQLException message is based on the specifications of the driver. The cause of the error might be output in SnowflakeSQLException message. For more details on the message, contact the vendor of the destination product.