RDS Bulk Output
    • Dark
      Light

    RDS Bulk Output

    • Dark
      Light

    Article Summary

    This article is specific to the following platforms - Snowflake - Redshift.

    RDS Bulk Output

    Matillion ETL for Snowflake: this feature is only available for instances hosted on AWS.

    RDS Bulk Output lets users load the contents of a table (or view) into a table in an Amazon RDS database.


    Properties

    Snowflake Properties

    PropertySettingDescription
    NameTextA human-readable name for the component.
    RDS TypeSelectSelect the database. Available options include:
    aurora: Amazon Aurora (see the AWS Documentation).
    mariadb: MariaDB (see the AWS Documentation).
    mysql: MySQL (see the AWS Documentation).
    postgresql: PostgreSQL (see the AWS Documentation).
    SQL Server (Microsoft Driver): Microsoft SQL Server (see the AWS Documentation).
    RDS EndpointTextThis is the RDS Database Endpoint. If the IAM role attached to the instance (or the manually entered credentials associated with the current environment) has the permissions granted to query the RDS endpoints, you may select the RDS endpoint from a list. Otherwise, you must enter it manually - it can be found in the RDS console and is a long dotted-name and port number, separated by a colon.
    Database NameTextThis is the name of the database within your RDS instance.
    UsernameTextThis is your RDS connection username.
    PasswordTextThis is your RDS connection password. The password is masked, so it can be set, but not read.Users have the option to store their password inside the component, but we highly recommend using the Password Manager option.
    JDBC OptionsParameterA JDBC parameter supported by the Database Driver. The available parameters are determined automatically from the driver, and may change from version to version.
    They are usually not required as sensible defaults are assumed.
    ValueThe parameter value.
    DatabaseTextThe name of the database you wish to source data from.
    SchemaSelectSelect the table schema. The special value, [Environment Default] will use the schema defined in the environment. For more information on using multiple schemas, see this article.
    Source TableSelectThe table (or view) on your cluster to copy to RDS.
    Target TableTextProvide a new table name.
    Load ColumnsSelectChoose the columns to load into RDS. This parameter can be empty, which means that all columns will be loaded.
    Table MaintenanceSelectNone - assume the RDS database already has the table defined with the correct structure.
    Create if not exists - Only create the table if it doesn't already exist.
    Replace - Always drop and re-create the table. Use with care!
    Primary KeySelectSpecify a set of columns to be used as the primary key for the target table. This is optional, but if specified allows you to UPSERT existing data in the target table.
    Truncate Target TableSelectWhether or not to truncate the target table before loading data.
    On WarningsSelectChoose whether to Continue with the load if an error is raised or to Fail the run.
    Additional Copy OptionsTextAny additional options that you want to apply to the copy. Some of these may conflict with the options the component already sets—in particular, care is taken to escape the data to ensure it loads into the target database even if the data contains row and/or column delimiters, so you should never override the escape or delimiter options.
    Options are documented here:
    Batch SizeIntegerThis is optional, and specifies the number of rows to load to the target between each COMMIT. On a very large export, this may be desirable to keep the size of the Amazon RDS log files from growing very large before the data is committed.

    Redshift Properties

    PropertySettingDescription
    NameTextA human-readable name for the component.
    RDS TypeSelectSelect the database. Available options include:
    aurora: Amazon Aurora (see the AWS Documentation).
    mariadb: MariaDB (see the AWS Documentation).
    mysql: MySQL (see the AWS Documentation).
    postgresql: PostgreSQL (see the AWS Documentation).
    SQL Server (Microsoft Driver): Microsoft SQL Server (see the AWS Documentation).
    RDS EndpointTextThis is the RDS Database Endpoint. If the IAM role attached to the instance (or the manually entered credentials associated with the current environment) has the permissions granted to query the RDS endpoints, you may select the RDS endpoint from a list. Otherwise, you must enter it manually - it can be found in the RDS console and is a long dotted-name and port number, separated by a colon.
    Database NameTextThis is the name of the database within your RDS instance.
    UsernameTextThis is your RDS connection username.
    PasswordTextThis is your RDS connection password. The password is masked, so it can be set, but not read.Users have the option to store their password inside the component, but we highly recommend using the Password Manager option.
    JDBC OptionsParameterA JDBC parameter supported by the Database Driver. The available parameters are determined automatically from the driver, and may change from version to version.
    They are usually not required as sensible defaults are assumed.
    ValueThe parameter value.
    SchemaSelectSelect the table schema. The special value, [Environment Default] will use the schema defined in the environment. For more information on using multiple schemas, see this article.
    Source TableSelectThe table (or view) on your cluster to copy to RDS.
    Target TableTextProvide a new table name.
    Load ColumnsSelectChoose the columns to load into RDS. This parameter can be empty, which means that all columns will be loaded.
    Table MaintenanceSelectNone - assume the RDS database already has the table defined with the correct structure.
    Create if not exists - Only create the table if it doesn't already exist.
    Replace - Always drop and re-create the table. Use with care!
    Primary KeySelectSpecify a set of columns to be used as the primary key for the target table. This is optional, but if specified allows you to UPSERT existing data in the target table.
    Update strategySelectReplace - Existing rows with the same Primary Key values will be replaced.
    Ignore - Existing rows with the same Primary Key values will be ignored.
    Truncate Target TableSelectWhether or not to truncate the target table before loading data.
    On WarningsSelectChoose whether to Continue with the load if an error is raised or to Fail the run.
    Additional Copy OptionsTextAny additional options that you want to apply to the copy. Some of these may conflict with the options the component already sets—in particular, care is taken to escape the data to ensure it loads into the target database even if the data contains row and/or column delimiters, so you should never override the escape or delimiter options.
    Options are documented here:
    Batch SizeIntegerThis is optional, and specifies the number of rows to load to the target between each COMMIT. On a very large export, this may be desirable to keep the size of the Amazon RDS log files from growing very large before the data is committed.

    Strategy

    A select query is issued against the source table. The output is formatted in an appropriate way to load into the target database, and data is streamed in.


    Video


    What's Next