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)