PostgreSQL
Preparation
To allow Shaped to connect to your Postgres database, you need to create a read-only user and pass the details into a Create Dataset request. You can create a read-only user on your Postgres DB with the following commands:
# 1. Create a new user.
CREATE USER read_only_user WITH PASSWORD 'secure_password1!';
# 2. Grant the user connection to the database.
GRANT CONNECT ON DATABASE database_name TO read_only_user;
# 3. Grant the user usage of the schema.
GRANT USAGE ON SCHEMA public TO read_only_user;
# 4. Grant the user read access to all the tables in the schema. Note you can also
# restrict this to your specific user, item and interaction views.
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only_user;
# 5. Grant the group access to future tables in the schema.
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only_user;
Dataset Configuration
Below are the fields required for the Postgres dataset connector:
Field | Example | Description |
---|---|---|
schema_type | POSTGRES | Specifies the connector schema type, in this case "Postgres". |
config.table | movies | The name of the table to sync. |
config.user | your_user | Access account username. |
config.password | pAssw0rd1! | Access account Password. |
config.host | my-postgres-db.xxxxxxx.us-east-2.rds.amazonaws.com | Database hostname. |
config.port | 5432 | Database port (the default for Postgres is 5432). |
config.database | movielens | The name of the database that contains table to sync. |
config.database_schema | public | Optional. The name of the schema that contains table to sync. |
config.replication_key | updated_at | The name of the column that contains a datetime key or ascending id for ordering data during incremental syncs. |
config.columns | ["productId", "color", "brand", "stockLevel"] | Optional, the name of the columns you wish to sync from Postgres into Shaped. If not specified, all columns will be synced. |
Dataset Creation Example
Below is an example of a Postgres dataset connector configuration:
dataset_name: your_postgres_dataset
schema_type: POSTGRES
config:
table: movies
user: your_user
password: pAssw0rd1!
host: my-postgres-db.xxxxxxx.us-east-2.rds.amazonaws.com
port: 5432
database: movielens
replication_key: updated_at
The following payload will create a Postgres dataset and begin syncing data from Shaped using the Shaped CLI.
shaped create-dataset --file dataset.yaml