Establishing a Connection
Redshift Connection String
The following connection string is required to establish Redshift database connection with Lyftron
Connecting to Redshift
The following connection properties are required in order to connect to data.
- Server: The host name or IP of the server hosting the Redshift database.
- Port: The port of the server hosting the Redshift database.
You can also optionally set the following:
- Database: The default database to connect to when connecting to the Redshift Server. If this is not set, the user's default database will be used.
You can obtain these values in the AWS Management Console:
- Open the Amazon Redshift console (http://console.aws.amazon.com/redshift).
- On the Clusters page, click the name of the cluster.
- On the Configuration tab, obtain the properties from the Cluster Database Properties section. The connection property values will be the same as the values set in the ODBC URL.
Connect Using Standard Authentication
To authenticate using standard authentication, set the following:
- User: The user which will be used to authenticate with the Redshift server.
- Password: The password which will be used to authenticate with the Redshift server.
Connect Using SSL Authentication
You can leverage SSL authentication to connect to Redshift data via a secure session. Configure the following connection properties to connect to data:
- SSLClientCert: Set this to the name of the certificate store for the client certificate. Used in the case of 2-way SSL, where truststore and keystore are kept on both the client and server machines.
- SSLClientCertPassword: If a client certificate store is password-protected, set this value to the store's password.
- SSLClientCertSubject: The subject of the TLS/SSL client certificate. Used to locate the certificate in the store.
- SSLClientCertType:; The certificate type of the client store.
- SSLServerCert: The certificate to be accepted from the server.
Connect Using SSO Authentication
Alternatively, you can connect to Redshift data using single sign-on (SSO). Configure the following connection properties to connect to data:
- SSOProvider: The name of the SSO provider you are trying to authenticate. The following options are available: OKTA, OneLogin, and ADFS.
- SSOLoginUrl: Set this to the login url associated with the SSO provider.
- SSOUser: The username of the SSO Provider used to authenticate the user.
- SSOPassword: The password of the SSO Provider used to authenticate the user.
Redshift QuickStart Steps
Step1: Go to Connections --> Click on Add Connection --> Add the connection name
Step2: Provide the connection details --> Click on test connection --> if no error then connection. is successful
Step3: Go to the Database --> Click on add Database --> Give Database name --> Choose the existing connection that we have made in Step2 --> Click next to Import Metadata--> Choose the tables to import --> Click create --> Now your Redshift database is ready for the pipeline
Step4: Click on the migrate data button --> Choose your source dataset to import --> Click next --> Choose the tables to migrate --> Choose import --> Choose the tables that you want to replicate to Redshift --> Click on Replicate to a target table --> Choose schema --> Choose table name -->Click Update --> Click on the management tasks --> Click on load data
Steps5: Query your data by dragging and dropping the tables that's been loaded by your pipeline
Connection String Parameters
|Connection String Name||Description|
|AllowPreparedStatement||Prepare a query statement before its execution.|
|LifeTime||The maximum lifetime of a connection in seconds. Once the time has elapsed, the connection object is disposed.|
|Database||The name of the Redshift database.|
|FirewallPassword||A password used to authenticate to a proxy-based firewall.|
|FirewallPort||The TCP port for a proxy-based firewall.|
|FirewallServer||The name or IP address of a proxy-based firewall.|
|FirewallType||The protocol used by a proxy-based firewall.|
|FirewallUser||The user name to use to authenticate with a proxy-based firewall.|
|IdPPassword||The password required for authentication with the Identity Provider.|
|IdPType||Type of authentication service to run by Identity Provider.|
|IdPUrl||The url value to connect Identity Provider.|
|IdPUser||The username used to authenticate with the Identity Provider IdPUrl .|
|IgnoredSchemas||Visibility restriction filter which is used to hide the list of schemas by metadata quering. For example, 'information_schema, pg_catalog'. Schema names are case sensitive.|
|Location||A path to the directory that contains the schema files defining tables, views, and stored procedures.|
|Logfile||A path to the log file.|
|MaxLogFileCount||A string specifying the maximum file count of log files. When the limit is hit, a new log is created in the same folder with the date and time appended to the end and the oldest log file will be deleted.|
|MaxLogFileSize||A string specifying the maximum size in bytes for a log file (for example, 10 MB). When the limit is hit, a new log is created in the same folder with the date and time appended to the end.|
|MaxRows||Limits the number of rows returned rows when no aggregation or group by is used in the query. This helps avoid performance issues at design time.|
|Other||These hidden properties are used only in specific use cases.|
|Password||The password required for authentication with the Amazon Redshift server.|
|PoolIdleTimeout||The allowed idle time for a connection before it is closed.|
|PoolMaxSize||The maximum connections in the pool.|
|PoolMinSize||The minimum number of connections in the pool.|
|PoolWaitTime||The max seconds to wait for an available connection.|
|Port||The port number of the Amazon Redshift server.|
|QueryPassthrough||This option passes the query to the Redshift server as is.|
|Readonly||You can use this property to enforce read-only access to Redshift from the provider.|
|RTK||The runtime key used for licensing.|
|Server||The host name or IP address of the Redshift cluster.|
|SSLClientCert||The TLS/SSL client certificate store for SSL Client Authentication (2-way SSL).|
|SSLClientCertPassword||The password for the TLS/SSL client certificate.|
|SSLClientCertSubject||The subject of the TLS/SSL client certificate.|
|SSLClientCertType||The type of key store containing the TLS/SSL client certificate.|
|SSLServerCert||The certificate to be accepted from the server when connecting using TLS/SSL.|
|SSOLoginURL||The SSOProvider login URL.|
|SSOPassword||The password of the SSOProvider used to authenticate the user.|
|SSOProvider||The name of the SSO provider you are trying to authenticate.|
|SSOUser||The SSOProvider user account used to authenticate.|
|Tables||This property restricts the tables reported to a subset of the available tables. For example, Tables=TableA,TableB,TableC.|
|Timeout||The (nonzero) value in seconds until the connection timeout error is thrown.|
|TimeZone||Set time zone for the current session.|
|UseConnectionPooling||This property enables connection pooling.|
|User||The username used to authenticate with the Amazon Redshift Server .|
|UseSSL||This field sets whether SSL is enabled.|
|Verbosity||The verbosity level that determines the amount of detail included in the log file.|
|Views||Restricts the views reported to a subset of the available tables. For example, Views=ViewA,ViewB,ViewC.|
|Visibility||Visibility restrictions used to filter exposed metadata for tables with privileges granted to them for current user. For example 'SELECT,INSERT' filter is restricting metatdata visibility only for those tables which may be accessed by current user for SELECT and INSERT opearations. Supported privilege values are SELECT, INSERT, UPDATE, DELETE, REFERENCES.|