Insert records into Redshift table

This example demonstrates how to use the Redshift - Insert Snap to insert new records into a Redshift table. The Snap can also create the table automatically if it doesn't exist when the Create table if not present option is enabled.

The pipeline uses a JSON Generator to provide sample employee data, which is then inserted into a Redshift table using the Insert Snap.

  • A valid Redshift account
  • A target Redshift table (or the Snap can create it if it doesn't exist)
  • Appropriate permissions to insert records into the target table
  1. Configure the JSON Generator Snap to provide input data.

    Create JSON data with employee records. For example:

    [
  { "empno": 101, "ename": "John", "job": "Manager", "sal": 144000, "deptno": 10 },
  { "empno": 102, "ename": "Thomas", "job": "Marketing", "sal": 84000, "deptno": 20 },
  { "empno": 103, "ename": "Williams", "job": "Technical", "sal": 96000, "deptno": 30 },
  { "empno": 104, "ename": "Scott", "job": "Sales", "sal": 84000, "deptno": 20 },
  { "empno": 105, "ename": "Richard", "job": "Accounts", "sal": 72000, "deptno": 40 },
  { "empno": 106, "ename": "James", "job": "Sales", "sal": 84000, "deptno": 20 }
]
  2. Configure the Redshift - Insert Snap.

    Set the following properties:

    • Schema name: Specify the database schema (for example, public).
    • Table name: Specify the table name (for example, EMPLOYEE_DATA).
    • Create table if not present: Select this option to automatically create the table if it doesn't exist in the database.

    The Snap uses the document's keys as the columns to insert to and the document's values as the values to insert into the columns. If the table doesn't exist and Create table if not present is selected, the table will be created with columns and data types matching the first input document.

  3. Execute the pipeline.

    When the pipeline runs, the Snap inserts all records from the JSON Generator into the Redshift table.

The Snap successfully inserts all records into the Redshift table. The output shows:

  • Status of each insert operation
  • The original input data for each record
Warning: According to Amazon's Redshift documentation, Amazon strongly recommends using the BULK insert functionality for large datasets. Consider using the Redshift - Bulk Load Snap for better performance with large volumes of data.