PostgreSQL
Bruin supports PostgreSQL as a data platform.
Connection
In order to have set up a Postgres connection, you need to add a configuration item to connections
in the .bruin.yml
file complying with the following schema.
connections:
postgres:
- name: "connection_name"
username: "pguser"
password: "XXXXXXXXXX"
host: "pghost.somedomain.com"
port: 5432
database: "dev"
ssl_mode: "allow"
schema: "schema_name" # optional
pool_max_conns: 5 # optional
NOTE
ssl_mode
should be one of the modes describe in the documentation.
PostgreSQL Assets
pg.sql
Runs a materialized Postgres asset or an sql script. For detailed parameters, you can check Definition Schema page.
Example: Create a table using table materialization
/* @bruin
name: events.install
type: pg.sql
materialization:
type: table
@bruin */
select user_id, ts, platform, country
from analytics.events
where event_name = "install"
Example: Run a Postgres script
/* @bruin
name: events.install
type: pg.sql
@bruin */
create temp table first_installs as
select
user_id,
min(ts) as install_ts,
min_by(platform, ts) as platform,
min_by(country, ts) as country
from analytics.events
where event_name = "install"
group by 1;
create or replace table events.install
select
user_id,
i.install_ts,
i.platform,
i.country,
a.channel,
from first_installs as i
join marketing.attribution as a
using(user_id)
pg.seed
pg.seed
are a special type of assets that are used to represent are CSV-files that contain data that is prepared outside of your pipeline that will be loaded into your postgres database. Bruin supports seed assets natively, allowing you to simply drop a CSV file in your pipeline and ensuring the data is loaded to the postgres database.
You can define seed assets in a file ending with .yaml
:
name: dashboard.hello
type: pg.seed
parameters:
path: seed.csv
Parameters:
path
: Thepath
parameter is the path to the CSV file that will be loaded into the data platform. path is relative to the asset definition file.
Examples: Load csv into a Postgres database
The examples below show how load a csv into a postgres database.
name: dashboard.hello
type: pg.seed
parameters:
path: seed.csv
Example CSV:
name,networking_through,position,contact_date
Y,LinkedIn,SDE,2024-01-01
B,LinkedIn,SDE 2,2024-01-01