Skip to main content

Pipe

Configuration can be provided to Snowflake Object Lifecycle Engine for the following operation with Pipe:

  • Manage Lifecycle of new and existing Pipe
  • Manage Grants of Pipe

Supported Parameters​

The engine supports the parameters listed below.

  • COPY_STATEMENT: Specifies the copy statement for the pipe.
    • REQUIRED
    • Configuration Key: copy_statement
    • Data Type: Object . See COPY INTO table for copy_statement parameters.
      COPY INTO Table

      Refer to the Snowflake Docs for COPY INTO table copy options and file format .

  • AUTO_INGEST: Specifies an auto_ingest param for the pipe.
    • Configuration Key: auto_ingest
    • Data Type: Boolean
  • AWS_SNS_TOPIC_ARN: Specifies the Amazon Resource Name (ARN) for the SNS topic for your S3 bucket.
    • Configuration Key: aws_sns_topic_arn
    • Data Type: String
  • COMMENT: Specifies a comment for the Pipe.
    • Configuration Key: comment
    • Data Type: String
  • ERROR_INTEGRATION: Specifies the name of the notification integration used to communicate with Amazon SNS.
    • Configuration Key: error_integration
    • Data Type: String
      info

      Required only when configuring Snowpipe to send error notifications using Amazon Simple Notification Service (SNS). For more information refer to:

    • Enabling Error Notifications for Snowpipe. :::
  • INTEGRATION: Specifies the existing notification integration used to access the storage queue.
  • MANAGE_MODE: Configures what properties to manage for the Pipe.
    • Configuration key: manage_mode
    • Data Type: String
    • Possible Values:
      • none
      • grants
      • all(Default)
  • GRANTS: List of Privileges and Roles to which privileges are granted to on the current Pipe.
    • Configuration key: grants
    • Data Type: Map
caution

During subsequent pipeline runs a force-replacement behavior might be observed in PLAN and APPLY phase for few parameters like copy_statement, auto_ingest, aws_sns_topic_arn, etc.

Basic syntax​

databases:
  <database-name>:
    schemas:
      <schema-name>:
        pipes:
          <pipe-name>:
            <configuration-key>: <value>
            grants:
              <privilege>:
                - <role-name>
                - <role-name>

Supported Pipe Grants to Roles​

Following is the list of Privileges Grant to Roles that can be specified in the pipe definition

  • ALL PRIVILEGES
  • MONITOR
  • OPERATE
  • OWNERSHIP

Examples​

Below are some example for configuring pipes

Pipe configuration with TYPE parameter​

databases:
  SALES_RECORD:
    comment: "product sales record"
    error_integration: "NOTIFICATION_INTEGRATION_SNS"
    schemas:
      comment: "sales records for year 2021"
      pipes:
        PIPE_1:
          comment: A pipe.
          auto_ingest: false
          copy_statement:
            into:
              database: "DATABASE_3"
              schema: "SCHEMA_1"
              table: "TABLE_3"
            from:
              database: "DUMMY_DATABASE"
              schema: "PUBLIC"
              stage: "LIFECYCLE_STAGE"
            pattern: ".*[.]csv"
            file_format:
              type: "JSON"
              compression: "AUTO"
              binary_format: "utf8"
              trim_space: false
              enable_octal: false
              strip_outer_array: false
              strip_null_values: false
              replace_invalid_characters: false
              ignore_utf8_errors: true
              skip_byte_order_mark: false
            enforce_length: false
            truncate_columns: true
          grants:
            MONITOR:
              - SYSADMIN
              - ACCOUNTADMIN

Pipe configuration without TYPE parameter​

databases:
  SALES_RECORD_FEB:
    comment: "product sales record"
    integration: "NOTIFICATION_INTEGRATION_GCP"
    schemas:
      comment: "sales records for year 2021 of feb month"
      PIPE_2:
        comment: A pipe.
        auto_ingest: false
        copy_statement:
          into:
            database: "DATABASE_3"
            schema: "SCHEMA_1"
            table: "TABLE_2"
          from:
            database: "DUMMY_DATABASE"
            schema: "PUBLIC"
            stage: "LIFECYCLE_STAGE_GCP"
          pattern: ".*[.]csv"
          file_format:
            format_database: "DUMMY_DB"
            format_schema: "PUBLIC"
            format_name: "DMY_FORMAT"
        grants:
          OPERATE:
            - SYSADMIN
Last updated on