Redshift
Connect & Ingest data from / to a Redshift database
Setup
The following credentials keys are accepted:
host
(required) -> The hostname / ip of the instanceuser
(required) -> The username to access the instancedatabase
(required) -> The database name of the instanceaws_bucket
(required) -> The name of the S3 Bucket for Bulk Loading / Unloadingaws_access_key_id
(required) -> The AWS Access Key ID to access the bucket for Bulk Loading / Unloadingaws_secret_access_key
(required) -> The AWS Secret Key to access the bucket for Bulk Loading / Unloadingaws_session_token
(optional) -> The AWS Session token to access the bucket for Bulk Loading / Unloadingschema
(optional) -> The default schema to use when loadingpassword
(optional) -> The password to access the instanceport
(optional) -> The port of the instance. Default is5439
.ssh_tunnel
(optional) -> The URL of the SSH server you would like to use as a tunnel (examplessh://user:[email protected]:22
)ssh_private_key
(optional) -> The private key to use to access a SSH server (raw string or path to file).ssh_passphrase
(optional) -> The passphrase to use to access a SSH server.
Using sling conns
sling conns
Here are examples of setting a connection named REDSHIFT
. We must provide the type=redshift
property:
$ sling conns set REDSHIFT type=redshift host=<host> user=<user> database=<database> password=<password> port=<port> aws_bucket=<aws_bucket> aws_access_key_id=<aws_access_key_id> aws_secret_access_key=<aws_secret_access_key>
# OR use url
$ sling conns set REDSHIFT url="redshift://myuser:[email protected]:5439/mydatabase" aws_bucket=<aws_bucket> aws_access_key_id=<aws_access_key_id> aws_secret_access_key=<aws_secret_access_key>
Environment Variable
export REDSHIFT='redshift://myuser:[email protected]:5439/mydatabase'
export AWS_BUCKET='<aws_bucket>'
export AWS_ACCESS_KEY_ID='<aws_access_key_id>'
export AWS_SECRET_ACCESS_KEY='<aws_secret_access_key>'
export REDSHIFT='{ type: redshift, url: "redshift://myuser:[email protected]:5439/mydatabase", aws_bucket: "<aws_bucket>", aws_access_key_id: "<aws_access_key_id>", aws_secret_access_key: "<aws_secret_access_key>" }'
Sling Env File YAML
See here to learn more about the sling env.yaml
file.
connections:
REDSHIFT:
type: redshift
host: <host>
user: <user>
password: <password>
port: <port>
database: <database>
schema: <schema>
aws_bucket: <aws_bucket>
aws_access_key_id: <aws_access_key_id>
aws_secret_access_key: <aws_secret_access_key>
REDSHIFT_URL:
url: "redshift://myuser:[email protected]:5439/mydatabase"
aws_bucket: <aws_bucket>
aws_access_key_id: <aws_access_key_id>
aws_secret_access_key: <aws_secret_access_key>
Database user creation
To allow Sling to access your database, we need to create a user with the proper privileges. Please follow the steps below:
Create a user
sling
(or whatever you prefer) by running:CREATE USER sling WITH PASSWORD '<password>';
If you are planning to load data into this connection, grant the following privileges to that user:
GRANT CREATE ON DATABASE <database_name> TO sling;
If you are planning to extract data from this connection, you need to give permission to read the tables you'd like Sling to extract.
-- Need this to read table & column names GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO sling; GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO sling; -- run this to grant SELECT permission to all tables in schema `marketing` to user sling GRANT SELECT ON ALL TABLES IN SCHEMA marketing TO sling;
If you are facing issues connecting, please reach out to us at [email protected], on discord or open a Github Issue here.
Last updated