Create a Snowpipe in Snowflake (AWS)

Learn how to create a Snowpipe that copies data to the target table from the external stage in Snowflake.

Create a Snow pipe to copy data from the external stage in Snowflake to the target table.

To create a pipe in your Snowflake environment:

In the worksheet area, enter the pipe name. For example: create or replace pipe create pipe {database}.{schema}.{pipe} auto_ingest=true as.
Copy data to the target table. For example: copy into {database}.{schema}."dataTable".
Enter the pipe details. For example: from (select $1:uri::string, $1:version::string, $1:timestamp::string, $1:deleted::boolean, $1:linked::boolean, $1:objectType::string, $1:type::string, $1, current_timestamp() from @{database}.{schema}.{stage_name}).
Enter the file format name. For example: file_format = zippedJson;

Here' the complete code:

create pipe {database}.{schema}.{pipe} auto_ingest=true as
  copy into {database}.{schema}."dataTable"
  from (select $1:uri::string, $1:version::string, $1:timestamp::string, $1:deleted::boolean, $1:linked::boolean, $1:objectType::string, $1:type::string, $1, current_timestamp() from @{database}.{schema}.{stage_name})
  file_format = zippedJson;

After you've created a pipe, you can check the pipe status. The SYSTEM$PIPE_STATUS( '<pipe_name>' ) command displays the current status in a JSON format. For more information, see topic https://docs.snowflake.com/en/sql-reference/functions/system_pipe_status.html.

Use the lastReceivedMessageTimestam and lastForwardedMessageTimestamp fields in the pipe output to check the current status of the Snowpipe.

The lastReceivedMessageTimestamp field specifies the timestamp of the last event message received from the message queue. The path associated with the message must match the path in the pipe definition. In addition, only messages triggered by created data objects are consumed by auto-ingest pipes.
If event messages are getting received from the message queue but are not sent to the pipe, verify the paths specified in the stage and pipe definitions. There is likely a mismatch between the blob storage path where the new data files are created and the combined path specified in the Snowflake stage and pipe definitions.
Note: A path specified in the pipe definition is appended to any path in the stage definition.

Accelerate the Value of Data

Create a Snowpipe in Snowflake (AWS)

Footer