Contents x
    Salesforce Bulk Query
      • Dark
        Light
      Contents

      Salesforce Bulk Query

        • Dark
          Light
        Article Summary

        This article is specific to the following platforms - Snowflake.

        Salesforce Bulk Query

        The Salesforce Bulk Query component uses the Salesforce API to retrieve bulk data to load into a table. This action stages the data, so the table is reloaded each time. You can then use transformations to enrich and manage the data in permanent tables.

        Warning: This component is potentially destructive. If the target table undergoes a change in structure, it will be recreated. Otherwise, the target table is truncated. Setting the Load Option Recreate Target Table to Off will prevent both recreation and truncation. Do not modify the target table structure manually.


        Properties

        Snowflake Properties

        PropertySettingDescription
        NameStringA human-readable name for the component.
        VersionSelectA list of supported versions of Salesforce.
        AuthenticationSelectSelect a Salesforce Bulk OAuth entry to authenticate this component. OAuth entries should be set up in advance of configuring the component. To learn how to create and authenticate an OAuth entry to authorize Salesforce Bulk Query, read Salesforce Bulk Query Authentication Guide.
        SOQL QueryQuery ToolA query tool for Salesforce Object Query Language. Does not return an error upon validation, but does return errors if found during runtime. For more information, read Introduction to SOQL and SOSL.
        Page SizeIntegerSpecify the size of each page requested.
        Must be a positive integer. The default is 50,000. The max size is the max int size in Java.
        Max Wait MinutesIntegerAn integer value for the maximum time to wait (in minutes) for the bulk query to run before a failure is returned.
        Must be a positive integer.
        The default is 25 minutes.
        The maximum is 10,080 minutes (7 days).
        TypeSelectThe table type. Select external or standard. Below properties are determined by the chosen table type.
        WarehouseSelectSelect the Snowflake warehouse. The special value, [Environment Default], will use the warehouse defined in the Matillion ETL environment. For more information, read Virtual Warehouses.
        DatabaseSelectSelect the Snowflake database. The special value, [Environment Default], will use the database defined in the Matillion ETL environment. For more information, read Databases, Tables, & Views.
        SchemaSelectSelect the Snowflake schema. The special value, [Environment Default], will use the schema defined in the Matillion ETL environment. For more information, read Database, Schema, & Share DDL.
        New Table NameStringSpecify the name of the new table to be created.
        This property is only available when Type is set to External.
        Stage DatabaseSelect(Specify the stage database. The special value, [Environment Default], will use the database defined in the environment.
        This property is only available when Type is set to External.
        Stage SchemaSelectSpecify the stage schema. The special value, [Environment Default], will use the schema defined in the environment.
        This property is only available when Type is set to External.
        StageSelectSelect an external stage.
        This property is only available when Type is set to External.
        Target TableStringProvide a new table name.
        Warning: This table will be recreated and will drop any existing table of the same name.
        StageSelectSelect a managed stage. The special value, [Custom], will create a stage "on the fly" for use solely within this component. Selecting [Custom] provides all the properties typically seen in the Manage Stages dialog for your input.
        If you select a managed stage that has already been configured in Manage Stages, the additional properties are not provided, as they have already been configured.
        Manage Stages can be found by clicking the Environments panel in the lower-left, then right-clicking an environment. To learn more, read Manage Stages.
        Stage PlatformSelectSelect a staging setting.
        Snowflake Managed: Allow Matillion ETL to create and use a temporary internal stage on Snowflake for staging the data. This stage, along with the staged data, will cease to exist after loading is complete.
        Existing Amazon S3 Location: Activates the S3 Staging Area property, allowing users to specify a custom staging area on Amazon S3. The Stage Authentication property is also activated, letting users select a method of authenticating the data staging.
        Existing Azure Blob Storage Location: Activates the Storage Account and Blob Container properties, allowing users to specify a custom staging location on Azure. The Stage Authentication property is also activated, letting users select a method of authenticating the data staging.
        Existing Google Cloud Storage Location: Activates the GCS Staging Area property, allowing users to specify a custom staging area within Google Cloud Storage.
        Stage AuthenticationSelectSelect an authentication method for data staging.
        Credentials: Uses the credentials configured in the Matillion ETL environment. If no credentials have been configured, an error will occur.
        Storage Integration: Use a Snowflake storage integration to authentication data staging. A storage integration is a Snowflake object that stores a generated identity and access management (IAM) entity for your external cloud storage, along with an optional set of allowed or blocked storage locations. To learn more, read Create Storage Integration.
        Storage IntegrationSelectSelect a Snowflake storage integration from the dropdown list. Storage integrations are required to permit Snowflake to read data from and write to your cloud storage location (Amazon S3, Microsoft Azure, Google Cloud Storage) and must be set up in advance of selection.
        To learn more about setting up a storage integration for use in Matillion ETL, read Storage Integration Setup Guide.
        This property is only available when Stage Authentication is set to Storage Integration.
        S3 Staging AreaSelectSelect an S3 bucket for temporary storage. Ensure your access credentials have S3 access and permission to write to the bucket. Read Manage Credentials for details on setting up access. The temporary objects created in this bucket will be removed again after the load completes, they are not kept.
        Use Accelerated EndpointBooleanWhen True, data will be loaded via the s3-accelerate endpoint. Please consider the following information:
        • Enabling acceleration can enhance the speed at which data is transferred to the chosen S3 bucket. However, enhanced speed is not always guaranteed. Please consult Amazon S3 Transfer Acceleration Speed Comparison to compare S3 Direct versus S3 Accelerated Transfer speeds.
        • Users must manually set the acceleration configuration of an existing bucket. To learn more, see PutBucketAccelerateConfiguration in the API Reference, available at the AWS documentation.
        • This property is only available if the selected S3 bucket has Amazon S3 Transfer Acceleration enabled. For more information, including how to enable this feature, read Getting started with Amazon S3 Transfer Acceleration.
        • Cases may arise where Matillion ETL cannot determine whether the chosen S3 bucket has Amazon S3 Transfer Acceleration enabled. In these cases, Matillion ETL will reveal this property for user input on a "just in case" basis. In these cases, Matillion ETL may return a validation message that reads OK - Bucket could not be validated. You may also encounter cases where, if you do not have permission to get the status of the acceleration configuration (namely, the permission, GetAccelerateConfiguration) Matillion ETL will again show this property "just in case".
        • The default setting is False.
        Storage AccountSelectSelect a storage account with your desired blob container to be used for staging the data. For more information, read Storage account overview.
        Blob ContainerSelectSelect a Blob container to be used for staging the data. For more information, read Introduction to Azure Blob storage.
        GCS Staging AreaSelectThe URL and path of the target Google Storage bucket to be used for staging the queried data. For more information, read Creating storage buckets.
        EncryptionSelectDecide how the files are encrypted inside the S3 bucket. This property is available when using an existing Amazon S3 location for staging.
        None: No encryption.
        SSE KMS: Encrypt the data according to a key stored on KMS. Read AWS Key Management Service (AWS KMS) to learn more.
        SSE S3: Encrypt the data according to a key stored on an S3 bucket. Read Using server-side encryption with Amazon S3-managed encryption keys (SSE-S3) to learn more.
        Load OptionsMultiple SelectClean Staged Files: Destroy staged files after loading data. Default is On.
        String Null is Null: Converts any strings equal to "null" into a null value. This is case-sensitive and only works with entirely lower-case strings. Default is Off.
        Recreate Target Table: Choose whether the component recreates its target table before the data load. If Off, the component will use an existing table or create one if it does not exist. Default is On.
        File Prefix: Give staged file names a prefix of your choice. The default setting is an empty field.
        Trim String Columns: Remove leading and trailing characters from a string column. Default is On
        Compression Type: Set the compression type to either gzip or None. The default is gzip.
        Use Grid Variable: Check this checkbox to use a grid variable. This box is unchecked by default.
        What's Next