Amazon Redshift

Amazon Redshift is a data warehouse product which forms part of the larger cloud-computing platform Amazon Web Services.

Amazon Redshift-Specific Features and Limitations

Skyvia has the following limitations for Amazon Redshift:

Amazon Redshift Connections

To connect to Redshift, you need to specify the server host name or IP address, the port the server is running on, the user name and password to log in with, and the database name. If you need to import data to Redshift via Skyvia or perform replication, you will need to specify additional advanced parameters described below.

Connection Editor window

You need to specify the following parameters for Amazon Redshift connection:

  • Name — connection name that will be used to identify the connection in the objects list and when selecting a connection for a package.
  • Server — name or IP address of the Redshift host to connect to.
  • Port — Redshift connection port; default value is 5432.
  • User Id — user name to log in with.
  • Password — password to log in with.
  • Database — name of the Redshift database you want to connect to.
  • Schema — name of the Redshift schema you want to connect to.

If you want to execute Import packages, inserting data to Redshift, or Replication packages via this connection, you need to click Advanced Settings and set parameters for connecting to Amazon S3 storage service. This is necessary, because for these operations Skyvia uses Redshift data import from Amazon S3. It loads data as CSV files to Amazon S3, tells Redshift to import data from it, and deletes the CSV file after the import. Thus, you need to specify the S3 region to use and either AWS Security Token or AWS Access Key ID and AWS Secret Key. You may also optionally specify S3 Bucket Name to upload file to. Here are the descriptions of these parameters:

  • AWS Access Key ID — first part of your Amazon Web Services access key.
  • AWS Secret Key — second part of your Amazon Web Services access key. Read more about AWS access keys…
  • AWS Security Token — alternative to AWS Access Key ID and AWS Secret Key pair, Amazon Web Services Security token is a temporary limited-privilege credential.
  • S3 Region — AWS region, where your S3 storage is hosted.
  • S3 Bucket Name — here you may specify the name of your S3 bucket to temporarily load the file with imported or replicated data to. If you leave it empty, Skyvia will create a new bucket when importing or replicating data to Redshift and delete it after the operation is finished.

SSL Connections

If you want to use SSL connection, click Advanced Settings and then, under Protocol click SSL:

  • SSL Mode — this mode determines the priority of using secure SSL connection. You can select any of the following modes:
    • Allow — try first a non-SSL connection, then if that fails, try an SSL connection.
    • Disable — establish only an unencrypted SSL connection. If this mode is selected, SSL is not used, and other SSL parameters are not available. This mode is selected by default.
    • Prefer — try first an SSL connection, then if that fails, try a none-SSL connection.
    • Require — establish only a secure SSL connection.
  • SSL CA Cert — authority certificate. Click the button with three dots to open a multiline editor and paste the content of the certificate file there.

SSH Connections

If you want to use SSH connection, click Advanced Settings and then, under Protocol click SSH:

  • SSH Authentication Type — type of SSH authentication to use: Password or PublicKey. When using the password authentication, you need to specify the SSH password. For public key authentication, you need to specify the passphrase for the private key and the private key.
  • SSH Host — name or IP address of the SSH server.
  • SSH Port — TCP/IP port to connect to the SSH Server. By default, it is 22.
  • SSH User — user name on the machine where the SSH Server is running. It is a Windows user, not a user of the MySQL Server.
  • SSH Password — password of a user account on the SSH Server. It is available if Password SSH Authentication Type is selected.
  • SSH Passphrase — passphrase for a private key. You can set it while generating public and private key files through a key generator tool, for example PuTTygen. It is available if PublicKey SSH Authentication Type is selected.
  • SSH Private Key — private key. Click the button with three dots to open a multiline editor and paste the content paste the content of the key file there. It is available if PublicKey SSH Authentication Type is selected.

Other Advanced Parameters

  • Unicode — select this checkbox to use Unicode encoding for the connection.
  • Command Timeout — this parameter specifies the wait time before terminating an attempt to execute a command and generating an error. Note that it is the time to wait for any server reply since the command was sent to a server, and it doesn’t include the time necessary to fetch data if the command selects some data.
  • Connection Timeout — this parameter determines the time (in seconds) to wait while trying to establish a connection before terminating the attempt and reporting an error. Generally you don’t need to modify it, but in some specific cases when a connection to the database server is not good you may try increasing its value.

  • Use Bulk Import — this parameter affects import packages with the INSERT, UPDATE and DELETE operations and with Amazon Redshift as a target.

    By default, such packages import data in the following way: Skyvia writes data into multiple temporary CSV files, upload them to Amazon S3 and then tells Redshift to import data from these CSV files. These actions are performed simultaneously, providing the best performance. After the CSV files are imported, they are deleted. However, when data are imported this way, it’s not possible to obtain a per-record error log.

    If you disable bulk import, Skyvia will use usual INSERT, UPDATE, and DELETE statements for importing data. This allows you to obtain a per-record error log, but provides far less performance. Thus, disabling bulk import is not recommended and should only be considered if you need to import not that many records and need to have a per-record error log or you do not want to use Amazon S3 for temporary files.

Supported Actions and Actions Specifics

Amazon Redshift connector supports the following actions: