RDS Databases

The Datalogz application uses OLTP and OLAP databases to optimize performance and scalability between the two workloads.

Database System Requirements

Option 1: Postgres RDS Only

  • Minimum

    • Memory Optimized

    • Compute: 2 vCores, 16 GiB Memory, 3200 IOPS

    • Storage: 512 GB

    Estimated Cost as of 2/1/2023: $500 / mo.

  • Recommended

    • Memory Optimized

    • Compute: 4 vCores, 32 GiB Memory, 6400 IOPS

    • Storage: 512 GB

    Estimated Cost as of 2/1/2023: $850 / mo.

Total Estimated Cost: $500 - $850 / mo.

Option 2: Postgres RDS + Snowflake

Postgres

  • Minimum/Recommended

    • Memory Optimized

    • Compute: 2 vCores, 8 GiB Memory, 3200 IOPS

    • Storage: 512 GB

    Estimated Cost as of 2/1/2023: $200 / mo.

Snowflake (See Snowflake Option Section)

Object Creation

AWS

  1. Go to RDS and click Create Database

  2. Choose Standard Create, Postgresand choose your desired size

  3. Settings:

    1. Name your database instance

    2. Provide the credentials to your database. Store these credentials somewhere secure. We will come to these credentials later in the Secrets Manager section

  4. Instance configuration:

    1. Leave all the settings as default

  5. Storage:

    1. Set Allocated Storage to 50 GiB

  6. Connectivity:

    1. Select the VPC and the subnets that we created in the VPC deployment section

    2. Select the security group that we created in the Security Group section

    3. Leave the rest as default

  7. Leave all the other sections as default

  8. Click Create database

Option 1: Postgres Only

  • App DB Name: datalogz_bi

---- Run the following commands from the master (postgres) database
---- Run these commands one at a time
CREATE DATABASE datalogz_bi;
CREATE ROLE datalogz_gateway_admin WITH PASSWORD '<password>' LOGIN; -- Add the password!
GRANT ALL PRIVILEGES ON DATABASE datalogz_bi TO datalogz_gateway_admin WITH GRANT OPTION;
CREATE DATABASE airflow; 
CREATE ROLE airflow WITH PASSWORD '<password>' LOGIN; -- Add the password!
GRANT ALL PRIVILEGES ON DATABASE airflow TO airflow;
--- Connect to the datalogz_bi database as datalogz_gateway_admin
---- Run the following commands from the datalogz_bi database
GRANT ALL PRIVILEGES ON SCHEMA public TO datalogz_gateway_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO datalogz_gateway_admin;

  • Warehouse DB Name: datalogz_wh

---- Run the following commands from the master (postgres) database

-- Setup the BI Warehouse Database (OLAP - App Workflows)
CREATE DATABASE datalogz_wh;

CREATE ROLE datalogz_diagnostics_admin WITH PASSWORD '<password>' LOGIN; -- Add the password!
CREATE ROLE datalogz_gateway_admin WITH PASSWORD '<password>' LOGIN CREATEROLE; -- Add the password!
CREATE ROLE datalogz_gateway_user WITH PASSWORD '<password>' LOGIN; -- Add the password!
CREATE ROLE write_user_role;
CREATE ROLE read_user_role;

GRANT ALL ON DATABASE datalogz_wh TO datalogz_diagnostics_admin WITH GRANT OPTION;
GRANT ALL ON DATABASE datalogz_wh TO datalogz_gateway_admin WITH GRANT OPTION;
GRANT ALL ON DATABASE datalogz_wh TO write_user_role;

---- Run the following commands from the datalogz_wh database
CREATE SCHEMA powerbi_ingest;
CREATE SCHEMA powerbi_source;
CREATE SCHEMA tableau_ingest;
CREATE SCHEMA tableau_source;
CREATE SCHEMA biops_general;
CREATE SCHEMA biops_int;
CREATE SCHEMA biops_marts;

GRANT USAGE ON SCHEMA powerbi_ingest TO datalogz_diagnostics_admin;
GRANT USAGE ON SCHEMA powerbi_source TO datalogz_diagnostics_admin;
GRANT USAGE ON SCHEMA tableau_ingest TO datalogz_diagnostics_admin;
GRANT USAGE ON SCHEMA tableau_source TO datalogz_diagnostics_admin;
GRANT USAGE ON SCHEMA biops_general TO datalogz_diagnostics_admin;
GRANT USAGE ON SCHEMA biops_int TO datalogz_diagnostics_admin;
GRANT USAGE ON SCHEMA biops_marts TO datalogz_diagnostics_admin;

GRANT USAGE ON SCHEMA biops_general TO datalogz_gateway_admin;
GRANT USAGE ON SCHEMA biops_marts TO datalogz_gateway_admin;
GRANT USAGE ON SCHEMA biops_general TO read_user_role;
GRANT USAGE ON SCHEMA biops_marts TO read_user_role;
GRANT ALL ON SCHEMA biops_general TO write_user_role;

ALTER SCHEMA powerbi_ingest OWNER TO datalogz_diagnostics_admin;
ALTER SCHEMA powerbi_source OWNER TO datalogz_diagnostics_admin;
ALTER SCHEMA tableau_ingest OWNER TO datalogz_diagnostics_admin;
ALTER SCHEMA tableau_source OWNER TO datalogz_diagnostics_admin;
ALTER SCHEMA biops_general OWNER TO datalogz_diagnostics_admin;
ALTER SCHEMA biops_marts OWNER TO datalogz_diagnostics_admin;
ALTER SCHEMA biops_int OWNER TO datalogz_diagnostics_admin;

GRANT read_user_role TO datalogz_gateway_user;
GRANT write_user_role TO datalogz_gateway_admin;

CREATE TABLE biops_general.entitlement(
    account_id int,
    environment_id int,
    connector_id int,
    account_connector_key varchar(128),
    user_name varchar(256),
    system_name varchar(256),
    entitlement_type varchar(256),
    entitlement_status varchar(256),
    created_at timestamp default current_timestamp,
    updated_at timestamp default current_timestamp
);

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA powerbi_ingest TO datalogz_diagnostics_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA powerbi_source TO datalogz_diagnostics_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA tableau_ingest TO datalogz_diagnostics_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA tableau_source TO datalogz_diagnostics_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA biops_general TO datalogz_diagnostics_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA biops_int TO datalogz_diagnostics_admin;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA powerbi_ingest TO datalogz_gateway_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA powerbi_source TO datalogz_gateway_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA tableau_ingest TO datalogz_gateway_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA tableau_source TO datalogz_gateway_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA biops_general TO datalogz_gateway_admin;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA biops_general TO datalogz_gateway_admin;

GRANT SELECT ON TABLE datalogz_wh.biops_general.entitlement TO read_user_role;
GRANT INSERT, SELECT, UPDATE ON TABLE datalogz_wh.biops_general.entitlement TO write_user_role;

-- Before running this step, ensure the extension has been enabled in Azure
-- In Azure Portal, go to Server parameters and include the following configuration:
-- Azure.extensions -> FUZZYSTRMATCH enabled
CREATE EXTENSION IF NOT EXISTS FUZZYSTRMATCH WITH SCHEMA biops_marts;
CREATE EXTENSION IF NOT EXISTS plpgsql;

Option 2: Postgres + Snowflake

Postgres

  • App DB Name: datalogz_bi

-- Setup the Application Database (OLTP - App Workflows)
CREATE DATABASE datalogz_bi;
CREATE ROLE datalogz_gateway_admin WITH PASSWORD '<password>' LOGIN; -- Add the password!

GRANT ALL PRIVILEGES ON DATABASE datalogz_bi TO datalogz_gateway_db_admin WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON SCHEMA public TO datalogz_gateway_db_admin;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO datalogz_gateway_db_admin;

-- Setup the Airflow Database (OLTP - API Connector Workflows)
CREATE DATABASE airflow; 
CREATE ROLE airflow WITH PASSWORD '<password>' LOGIN; -- Add the password!
GRANT ALL PRIVILEGES ON DATABASE airflow TO airflow;

  • BI Warehouse DB (See Snowflake Option Section) Name: datalogz_wh

PreviousS3 BucketNextIAM Roles

Last updated