ePrivacy and GPDR Cookie Consent by Cookie Consent Skip to main content

Loader Redshift

Redshift is a fully managed, petabyte-scale data warehouse service in the cloud designed to handle large-scale data analytics. It enables organizations to efficiently store and analyze vast amounts of data using a massively parallel processing (MPP) architecture. 

Business value in CDP

As a loader to export data from CDP to Redshift, users can leverage Redshift's native capabilities for data ingestion and transformation. CDP data can be seamlessly transferred to Redshift, allowing organizations to benefit from Redshift's high-performance analytics, scalability, and ease of use. This integration facilitates streamlined and efficient data movement, enabling businesses to derive valuable insights from their data housed in CDP within the powerful analytics environment of Amazon Redshift.


Setting up the loader in MI

Redshift loader enables the loading of the data from Meiro Integrations to Redshift. Files from Meiro are loaded to an AWS S3 bucket, and from there to Redshift. 

Data In/Data Out

Data In

In order to use a loader, files need to be stored in  /data/in/tables/ 

Data Out N/A

Learn more: about the folder structure here.  

Parameters

 

Access Key ID (required)

 

The AWS Access Key ID, looks like AKIA****

Create it in the Credential section of your AWS S3 account:

My_AWS -> My Security Credentials -> Access keys (access key ID and secret access key) -> Create New Access Key -> Download Key File 

 

Learn more: on how to create your Redshift Access Key can be found here.

Secret Access Key (required)

The AWS Secret Access Key is provided by the AWS when you create a new AWS Access Key:

My_AWS -> My Security Credentials -> Access keys (access key ID and secret access key) -> Create New Access Key -> Download Key File. 

 

Learn more: on how to create your Redshift Secret Access Key can be found here.

Bucket (required) ​Provide a Redshift bucket name which is a globally unique identifier and the region will be autodetected.

Prefix (optional)

 

Provide a prefix to key names of the files prefix/. If not indicated, the files will be uploaded without any prefix in the Redshift bucket.

 

For example, if you wish to upload data in the directory new_folder  in your Redshift bucket, please use new_folder/.

 

 

DB
Host (required) Name of the server that hosts the database.
Port (required) The port number you want to access. 
Database (required) Name of the database.
Schema (required) Name of the schema. Organization unit in the database.
User (required) Account name.
Password (required) Account password.

 

SSH
Enabled Checked if SSH required to connect to the database. 
Private  Private Key.
Public  Public Key.
SSH Host Name of SSH Host.

 

Table
Input Table (required) Name of the table you want to load to the database.
DB Table Name (required) Database table name.
Export True if you want this table to be loaded or not. By default, it is true.
Incremental Signifies if you want to load the data by overwriting whatever is in the database or incrementally.
Column (required)

Can be defined for a table either when using incremental load, or not. 

 

The primary key constraint can be defined on multiple columns or one, but cannot contain NULL values. If the incremental load is enabled, then only rows with new or updated records are inserted.

 

If the incremental load is disabled but the primary keys are defined: the full table is loaded (and will replace whatever is in the destination database). The primary key columns will still be primary keys in the destination table.

 

Column
Column Name (required) Refers to the column name in the input CSV file.
DB Column Name (required) Refers to how this column will be named in the destination database.
Data Type (required) The data type for the column.
Size The maximum number of digits used by the data type of the column or parameter.
Nullable

If checked, then the data with empty strings are loaded as NULL values in the destination database (in this case is_nullable = YES).

 

If unchecked and data contains empty strings, then the loader returns an error message. For example: Cannot insert the value NULL into column XY, the column does not allow nulls. INSERT fails.

 

Warning: All columns listed as primary keys should have Nullable unchecked (is_nullable = NO), otherwise the loader throws an error Cannot define a PRIMARY KEY constraint on nullable column”column”.

Default Value  The default value is inserted for empty values in the column. For example,   insert NULLs in the destination columns database for empty strings in CSV file. - inserts - for empty strings.