squad-sagemaker-db-connector

======================== Auctm Database Connector

A python package that provides database connection to Bifrost database, Substrate database, and Decision Science database.

Modules #########

There are three modules - bifrost, substrate and decision_science.

All the modules provide the engine for their respective database.

bifrost

This module provides the database connection for the Bifrost database. It provides read-only access to the database.

The schema for tables in this database can be seen on the Bifrost Explorer <https://bifrost.squadplatform.com/explorer/play>__

substrate

This module provides the database connection for the Substrate database. It provides read-only access to the database.

decision_science

This module provides the database connection for the Decision Science database. It provides read and write access to the user's schema in the database.

This module provides the database connection for the Decision Science database.

SquadIQ

This module provides the database connection for the SquadIQ database. It provides read-only access to the database.

Usage

.. code-block:: python

from database_connector import bifrost, substrate, decision_science, squadiq, sagemaker

bifrost_engine = bifrost.engine
substrate_engine = substrate.engine
ds_engine = decision_science.engine
iq_engine = squadiq.engine
ds_sagemaker_engine = sagemaker.engine

# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with bifrost_engine.connect() as conn:
    data = conn.execute("Select * from fub_customer")

    # data is a dictionary with column name as key and row as value
    for row in data:
        print(row)
        
# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with substrate_engine.connect() as conn:
    data = conn.execute("Select * from substrate_customer")

    # data is a dictionary with column name as key and row as value
    for row in data:
        print(row)

# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with iq_engine.connect() as conn:
    data = conn.execute("Select * from voice_workflow_campaign")

    # data is a dictionary with column name as key and row as value
    for row in data:
        print(row)

# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with ds_sagemaker_engine.connect() as conn:
    data = conn.execute("Select * from voice_workflow_campaign")

    # data is a dictionary with column name as key and row as value
    for row in data:
        print(row)

# Database connection is automatically closed when connection is used in a context manager i.e. inside "with"
with ds_engine.connect() as conn:
    data = conn.execute("Select * from vedvasu.customers")

    # data is a dictionary with column name as key and row as value
    for row in data:
        print(row)

    conn.execute("insert into vedvasu.customers (id, name) values (1, 'Robert-Slack')")

How to install this module inside SageMaker Jobs started from JupyterHub

The following commands should be added in the DockerFile to install this module in the docker image. Preferably add these commands near the end before changing the working directory since all the steps these commands will be rebuild and cache won’t be used. Check the release here <https://discourse.squadstack.com/t/sagemaker-update-launch-training-jobs-inside-vpc/56>__.

.. code-block:: dockerfile

COPY auctm-database-connector-*.tar.gz /mnt/
RUN pip3 install /mnt/auctm-database-connector-*.tar.gz

UI for the Decision Science Database

The UI is available at Auctm PGAdmin <https://pgadmin.auctm.com/pgadmin4>__. It provides a MySQL Workbench like interface for our PostgreSQL database. Users can login here to view and make changes to their tables via a UI. The credentials for this can be taken from the Admins (currently AGB and abkunal)

How to Onboard a new user on Decision Science Database

• Login as the Admin Account at Auctm PGAdmin <https://pgadmin.auctm.com/pgadmin4>__.
• Create a User account for a new user and share the credentials with the user.
• Run the following SQL queries by replacing <role-name>, <strong-password>, and <schema-name>. Note down the role-name and strong-password.

.. code-block:: sql

-- Create a new role and GRANT all_read_only role to this new role
CREATE ROLE <role-name> WITH
    LOGIN
    NOSUPERUSER
    NOCREATEDB
    NOCREATEROLE
    INHERIT
    NOREPLICATION
    CONNECTION LIMIT -1
    PASSWORD '<strong-password>';

GRANT all_read_only TO <role-name>;

-- Grant this role to the Admin user
GRANT <role-name> TO decision_science;

-- Create schema for the role and make this role owner of this schema
CREATE SCHEMA <schema-name> AUTHORIZATION <role-name>;

-- Grant all_read_only role READ access to this new schema
GRANT USAGE ON SCHEMA <schema-name> TO all_read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA <schema-name> TO all_read_only;

• On JupyterHub, navigate to the user's home directory and create the credentials file.

.. code-block:: bash

sudo su
cd /home/<user>
nano decision_science_creds.csv

# Eg => cd /home/jupyter-kunal.yadav@squadr-319c0

• Put the role name and password in the csv file as shown below.

.. code-block:: bash

username,password
<role-name>,<strong-password>

• Change the ownership permissions for this file.

.. code-block:: bash

chown <user> decision_science_creds.csv
chgrp <user> decision_science_creds.csv

# Eg => chown jupyter-kunal.yadav@squadr-319c0 decision_science_creds.csv

Documentation

We use SQL Alchemy as an ORM because it provides additional features than raw psycopg2. You can check out its documentation here - SQL Alchemy docs <https://docs.sqlalchemy.org/en/13/core/connections.html>__

How to Modify this Package

• This package works with Python 3
• After making the necessary changes run the following commands to build the project and install the package

.. code-block:: bash

python setup.py sdist
sudo -E python -m pip install  dist/auctm-database-connector-0.1.tar.gz

3. You can uninstall the package by running the following command - sudo -E pip uninstall auctm-database-connector