DEV Community: Obrotoks

Fast API as EndPoint of Postgres

Obrotoks — Thu, 15 Jun 2023 10:22:26 +0000

Introduction

Recently, I've watch this video which a youtuber try to solve an interview step by step. But in this case, he didn't share here code.

In my case, I love to try to solve this kind of issues but I've never work with a backend solution as API. For this reason, I found intereseting tryng to repoduce his code (with some modification) to try to achive to get an API to get information about one table.

Here is my repository with the main code

Goal

Try to have a backend-structure (with FastAPI to trying get the information about one table from the database (in this case I'm going to use Postgrest).

Source data

For this purpouse it's going to be use this dataset. In there we could find two csv:

1st File: Travel Company Old Clients; Number of observations: 682
2nd File: Travel Company New Clients; Number of observations: 1303

We are going to use only Travel Company New Clients.

Instalation

Python Installation

All the libraries in the requirements.txt:

anyio==3.7.0
click==8.1.3
exceptiongroup==1.1.1
fastapi==0.97.0
greenlet==2.0.2
h11==0.14.0
httptools==0.5.0
idna==3.4
Jinja2==3.1.2
MarkupSafe==2.0.1
psycopg2==2.9.6
pydantic==1.10.9
python-dotenv==1.0.0
PyYAML==6.0
sniffio==1.3.0
SQLAlchemy==2.0.16
starlette==0.27.0
typing_extensions==4.6.3
uvicorn==0.22.0
uvloop==0.17.0
watchfiles==0.19.0
websockets==11.0.3

Create a virtual environment

We are going to use a virtual environment to try to keep the track of all the libraries we are going to need:

python -m venv venv

And also we are going to active the environment (Linux):

source venv/bin/activate

Finally to get the requirements.txt you could use this shell:

pip freeze >requirements.txt

Install FastAPI

We are going to need to install this library to deploy our python code as a server. They told us in there documentation how to install:

This library is to deploy our python code:

pip install fastapi

... and also we are going to need our system work as server for this reason they told us to use uvicorn.

pip install "uvicorn[standard]"

Example FastAPI

To try to check is it is working I've done this example, to try if everthing is working fine:

from fastapi import FastAPI

app = FastAPI()

@app.get("/")
async def root():
    return {"message": "Hello World"}

This script return only a message of hello world. I've save this script as example_1.py. To run in our bash:

uvicorn example_1:app --reload

If it is working properly in bash you are going to have a message like this:

Then, you could go to your favorite browser and in the ip http://127.0.0.1:8000/ you are going to se a message like this:

Also there are a links I could find usefull like:
http://127.0.0.1:8000/docs - where you could see the documentation about your project an test the dependecies

Finally to close your bash server simple Ctrl-C and your server is going to be down.

Install Jinja2

FastAPI could be use with Jinja2 template and we are going to use it to get a template of a query

'''shell
pip install Jinja2
'''

Install SQLAlchemy

We are going to use it to connect to our database and make a querys:

pip install SQLAlchemy

Install Pydantic

To help us to translate of our results in the query to format json

pip install pydantic

Install markupsafe

Library to implements a text object that escapes characters so it is safe to use in HTML and XML

pip install MarkupSafe==2.0.1

Install psycopg2

Adaptar of PostgresSQL to python

pip install psycopg2

Postgres Installation w/docker

Env variables

We are going to use this env variables:

PS_VERSION=15
PS_USER=ps
PS_PASSWORD=ps_2023
PS_DB=RAW

Dockerfile

We are going to create a file with name Dockerfile.postgres like this:

FROM postgres

# make directory to Docker
RUN mkdir -p /home/src

# Copy source data 
COPY ./src /home/src
COPY ./db_script/* /docker-entrypoint-initdb.d

# How to create a main table 
RUN chmod a+r /docker-entrypoint-initdb.d/*

# Expose the port
EXPOSE 6666

Steps to create FastAPI:

Models.py

First script we are going to create is how our model is going to be connected to our database based on the table of Newclients:


import os
from sqlalchemy import Column, String, Integer
from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base

# Use environment variable DB_CONNSTR
if os.environ["DB_CONNSTR"] is None:
    DB_CONNSTR='postgresql://ps:ps_2023@localhost:5432/RAW'
else:
    DB_CONNSTR = os.environ["DB_CONNSTR"]

# Create engine to connect our data base
engine = create_engine(DB_CONNSTR) if DB_CONNSTR else None
# Get all the metadata
meta = MetaData(DB_CONNSTR)
# declare metada has an object
Base = declarative_base(metadata=meta)

# Declare tables
TABLE_NAME='newclients'
class Newclients(Base):
    __tablename__ = TABLE_NAME
    num_age = Column(Integer, primary_key=True)
    des_employetype = Column(String, primary_key=True)
    is_graduate = Column(String, primary_key=True)
    imp_annualincome = Column(Integer, primary_key=True)
    num_familymembers = Column(Integer, primary_key=True)
    is_frequentflyer = Column(String, primary_key=True)
    is_evertravelledabroad= Column(String, primary_key=True)

Backend.py

In this script we are going to create a class, with the base of the paramenters:

select
where
order_by
group_by

This script is going to connect to our database and return a dictionary to get the result of our query.

To achive this point we are going to use Jinja2Templates.

Jinja2Templates

FastApi it could works native with Jinja2Templates. With this in main we could create an SQL script with the style of jinja. This is store in the folder of templates.

In this case, I've stored in github repo because dev.to doesn't let you to use double brackets

If someone knows how it could be copied in here without any problems it could be great.

Python Script

Use the Models.py and also the Jinja2Templates, we could use this to get the connection and the result of the query:

from fastapi.templating import Jinja2Templates
from sqlalchemy import text

from models import engine, TABLE_NAME

# Import templates of Jinja2
templates = Jinja2Templates(directory="templates")

class Sql_conn:

    def _parse(self, query_dict: dict[str]):
        # Use dict to pass variables throught jinja template
        query_dict['table']= TABLE_NAME
        render = templates.TemplateResponse(
                "query_template.j2",{'request': None, 'data': query_dict}
            )
        return render.body.decode('ascii')

    def _query(self, q):
       # Use connection to make a query
        print(q)
        with engine.connect() as con:
            rs = con.execute(text(q))
        return rs
    def _resultdict(self,result):
        # Convert result to dict 
        resultdic = dict()
        for idx, row in enumerate(result):
            row_as_dict = row._mapping
            resultdic.update({idx:row_as_dict})
        return resultdic

    def request_query(self, query_dict: dict[str]):
        q = self._parse(query_dict)
        data = self._query(q)
        datadic = self._resultdict(data)
        return datadic

Api.py

Finally in this script, we are going to use fastapi. In here based on the last script (backend.py), we are going to create our desired api:


from typing import Optional
from fastapi import FastAPI, HTTPException
from sqlalchemy.exc import ProgrammingError
from pydantic import BaseModel


from backend import Sql_conn


# Variables to the model
class Query(BaseModel):
    select: str
    where : Optional[str]=None
    order_by : Optional[str]=None
    group_by : Optional[str]=None
    sort_by : Optional[str]=None

# Create our object of FastAPI
app = FastAPI()

# Based on the method post of analytics..
@app.post("/analytics")
async def query_data(query: Query):
    # ... we create  async function to get the result of a query 
    return Sql_conn().request_query(query.dict())

Dockerfile.python

We are going to create an instance to run our desired dockerfile to run our api.


FROM python:3.9

WORKDIR /app

# Install OS dependencies
RUN apt-get update && apt-get install -qq -y \
    git gcc build-essential libpq-dev --fix-missing --no-install-recommends \ 
    && apt-get clean

# Make sure we are using latest pip
RUN pip install --upgrade pip

# Copy requirements.txt
COPY requirements.txt requirements.txt

# ... install of the requirements
RUN pip install -r requirements.txt

# ... copy all the script into the app
COPY ./py_script/ /app/

# ... select the variable of environment where is going to execute python
ENV PYTHONPATH "${PYTHONPATH}:/app/"

# ... and expose the desired port
EXPOSE 8000

CMD ["uvicorn","api:app","--host","0.0.0.0"]

Docker compose

Finally to get all the enviornment working together I've create this script:


version: '3.9'
services:
  post_db:
    build:
        context: .
        dockerfile: Dockerfile.postgres
    user: postgres
    image: postgres/test:v1
    environment:
      - POSTGRES_USER=${PS_USER}
      - POSTGRES_PASSWORD=${PS_PASSWORD}
      - PG_DATA:/var/lib/postgresql/data/pgdata
      - POSTGRES_DB=${PS_DB}
    healthcheck:
      test: ["CMD-SHELL","pg_isready -U ${PS_USER} ${PS_PASSWORD}"]
      interval: 10s
      timeout: 5s
      retries: 5
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data
    restart: unless-stopped
  api:
    build:
        context: .
        dockerfile: Dockerfile.python
    environment:
      - DB_CONNSTR=postgresql://${PS_USER}:${PS_PASSWORD}@post_db:5432/${PS_DB}
    ports:
      - 8000:8000
    links:
      - post_db
    restart: unless-stopped

volumes:
  db-data:

Now we could acces to our http://localhost:8000/docs and check if there is any in action:

Tests

To check if everything is working properly I've made this test. You could access to make this test with tryout button:

{
  "select": "num_age as Age, count(*) as Num_row",
  "group_by": "num_age",
"order_by": "count(*) desc"

}

And also there is our desired result:

Conclusion

FastAPI is a really good packacge to get the information about your database and also there is a lot of features I've not test it yet, but it looks great to have it as a server to get access to your data.

Have a nice day:)

DBT expectations w/postgres

Obrotoks — Sat, 10 Jun 2023 17:14:46 +0000

Goal

Create a process with dbt where it has been used great expectations, with this package. Here is the main repository

Description

Source

For this project, we are going to use as a source Soccer Events

Environment

Environment with python

For this project we are going to install a virtual enviorment under the name env_dbt_ge:

python3 -m venv env_dbt_ge

... and activate
source env_dbt_ge/bin/activate

With this enviorment we could export our requirements with this script:

python -m pip freeze > requirements.txt

Requirements library

For this project only we need these requirements:

dbt-core==1.5.1
dbt-extractor==0.4.1
dbt-postgres==1.5.1
markupsafe==2.0.1

Environment with docker

Actually, I'm working with a MacOs with TMUX for this reason, my docker's preference should be changed like this:

{
  "builder": {
    "gc": {
      "defaultKeepStorage": "20GB",
      "enabled": true
    }
  },
  "experimental": false,
  "features": {
    "buildkit": false
  }
}

Steps

0 - Create an env file

I've used this .env file with this project:

DBT_PROFILES_DIR=<YOUR_FOLDER>/projects/dbt_great_expectations
PS_VERSION=15
PS_USER=ps
PS_PASSWORD=ps_2023
PS_DB=RAW 
PY_VERSION=3.9-slim-buster

1 - Create our Postgres with dbt

As we've seen it in last article, we could create our docker file for postgres as this way:

1.1 Docker postgres

Dockerfile for postgres

# Dockerfile for postgres
ARG PS_VERSION
FROM postgres:${PS_VERSION}

1.2. Docker dbt

For this case, only we are going to add our new comand: dbt test (to run our desired test) & dbt deps (to download the last version of our package)

ARG PY_VERSION
FROM python:${PY_VERSION}

# Working directory
WORKDIR /app

# Install OS dependencies
RUN apt-get update && apt-get install -qq -y \
    git gcc build-essential libpq-dev --fix-missing --no-install-recommends \ 
    && apt-get clean

# Make sure we are using latest pip
RUN pip install --upgrade pip

# Create directory for dbt config
RUN mkdir -p /root/.dbt

# Copy requirements.txt
COPY requirements.txt requirements.txt

# Install dependencies
RUN pip install -r requirements.txt

# Copy dbt profile
COPY profiles.yml /root/.dbt/profiles.yml


# Copy source code
COPY dbt_project/ /app

WORKDIR /app/dbt_post


# Run 1- Install package + import data + check data 
CMD ["/bin/bash", "-c", "dbt deps;dbt seed;dbt test"]

1.3. Docker compose

Then we could have this docker compose to work with our environment:



version: '3.9'
volumes:
  db-data:
    name: "data_postres"
    driver: local

services:
  post_db:
    build: 
      context: .
      dockerfile: Dockerfile.postgres
      args:
        - PS_VERSION=${PS_VERSION}
    user: postgres
    image: postgres/test:v1
    environment:
      - POSTGRES_USER=${PS_USER}
      - POSTGRES_PASSWORD=${PS_PASSWORD}
      - PG_DATA:/var/lib/postgresql/data/pgdata
      - POSTGRES_DB=${PS_DB}
    healthcheck:
      test: ["CMD-SHELL","pg_isready -U ${PS_USER} ${PS_PASSWORD}"]
      interval: 10s
      timeout: 5s
      retries: 5
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data
    restart: unless-stopped
  dbt_python:
    build:
      context: .
      dockerfile: Dockerfile.python
      args:
        - PY_VERSION=${PY_VERSION}
    depends_on:
      - post_db

2 - DBT

2.1. - Import csv in our folder:

In this step we should incloude in our folder of dbt_project/seed our csv

2.2 - Add the package

In our folder of dbt_project, we should create a file with name "package.yml" with this code:

packages:
  - package: calogica/dbt_expectations
    version: [">=0.6.0", "<0.7.0"]

2.3. - Add schema in seed folder

In our folder of dbt_project/seed, we should create a file "schema.yml"

In there, we could include which type of column it is going to be (char, int, ...), and also we could include some test.

columns:
 - name: season
        tests:
          - unique:
              config:
                store_failures: true

If we choose the preset's test we could use the option of config, and also store_failures. These store_failure's checks it's going to be saved on our desired database under schema: "dbt_test__audit"."name_of_our_test"

The main purpouse is sees how its load in our table in case something goes wrong. For this reason this test should fail.

These tests could be working with great expectation's package:

columns:
      - name: id_odsp
        tests:
          - dbt_expectations.expect_column_values_to_be_unique

In this case, I couldn't see how canonical [the main package] could store faliures in case if something goes wrong.

Example

In here, we could see an example of this case of use:

version: 2

seeds:
  - name: ginf
    column_types:
      id_odsp: varchar(10)
      link_odsp: varchar(90)
      adv_stats: bool
      date: date
      league: varchar(4)
      season: int
      ht: varchar(30)
      at: varchar(30)
      odd_h: float
      odd_d: float
      odd_a: float
      odd_over: varchar(5)
      odd_under: varchar(5)
      odd_bts: varchar(5)
      odd_bts_n: varchar(5)
    columns:
      - name: id_odsp
        tests:
          - dbt_expectations.expect_column_values_to_be_unique
          - not_null
      - name: season
        tests:
          - unique:
              config:
                store_failures: true

2.4. - Save it in other schema without "_"

In case we want to create our tables in other schema, DBT as preset's behavior create an schema like this : "presetschema_createdschema". And we could avoid with this solution

2.4.1 - Add a macro

In the folder macro, we should create a sql file with this code (it doesn't care too much the name of the file):

{% macro generate_schema_name(custom_schema_name, node) -%}

    {%- set default_schema = target.schema -%}
    {%- if custom_schema_name is none -%}

        {{ default_schema }}

    {%- else -%}

        {{ custom_schema_name | trim }}

    {%- endif -%}

{%- endmacro %}

2.4.2 - Set new schema in dbt_project

We should add in dbt_project

seeds:
  +schema: src

Conclusion

It's really amazing the work of this dbt package, it brings a lot of features of great expecations to DBT. However, I've seen a little missing details to achive a greate feature as could be:

Lack of documentation: Currently if you use this package to try to document your database. You are going to see something like this:

It is not really clear to share with the main customere this kind of input.

Revision check: in case you use the presets factory of dbt, you could see the data's behavior. Otherwise, in case you are going to work with this great expecation package, you will need some help too see where is the error.
PySpark no compatibility: in the case of great expecations, you could work with a Pyspark's cluster. And this is little sad, because this could be helpful in dbt's world.

Also, I feel this package have a lot of cool features and in the future it is going to be an essential one.

References

Create a DBT w/Postgres

Obrotoks — Thu, 01 Jun 2023 13:24:23 +0000

Goal

Create a docker with postges. Then create another docker with dbt and launch an operation with dbt. Source code

Requirements

Docker
Postgres
Python (with DBT)

1 - Create a Server with Postgres

I've already create a post and how it could be done.

2 - Create a Server with Python

For this part, I've used this post to help about how to construct a python server with dbt.

However, there are a difference between these article, we want to launch this task only one time.

2.1 - Python Requirements

For this purpose, we will need this requirements of python:

dbt-postgres==1.0.0
markupsafe==2.0.1

DBT library to make the ETL process & markupsafe to avoid problems with the config profile yml

2.2 - Dockerfile

We should create this docker file to try to create our server to launch our DBT process


FROM python:3.8.1-slim-buster

# Working directory
WORKDIR /app

# Install OS dependencies
RUN apt-get update && apt-get install -qq -y \
    git gcc build-essential libpq-dev --fix-missing --no-install-recommends \ 
    && apt-get clean

# Make sure we are using latest pip
RUN pip install --upgrade pip

# Create directory for dbt config
RUN mkdir -p /root/.dbt

# Copy requirements.txt
COPY requirements.txt requirements.txt

# Install dependencies
RUN pip install -r requirements.txt

# Copy dbt profile
COPY profiles.yml /root/.dbt/profiles.yml


# Copy source code
COPY dbt_post/ /app

WORKDIR /app/dbt_post
# Start the dbt RPC server
CMD ["dbt","run"]

2.3 DBT Structure

To avoid have a lot of structure, and test all the structure, I've only change 3 files to try to test if the process works fine.

I've just made a query of a distinct num_age of one table which is already loaded in the postgres.

For this reason in models/raw, I've created two files

1- Schema.yml - to get all the information about the source file

version: 2

sources: 
  - name: InfoClients
    description: "Information about Clients"
    database: "RAW"
    schema: public
    tables:
      - name: newclients

2- Age - to get all the diferent values of a table:


with final as (
    select 
        distinct num_age
    from {{ source('InfoClients','newclients')}}
)
select * 
from final

Finally, I've already modify dbt_project.yml to try to get the model

name: 'dbt_post'
version: '1.0.0'
config-version: 2

# This setting configures which "profile" dbt uses for this project.
profile: 'dbt_post'

# These configurations specify where dbt should look for different types of files.
# The `model-paths` config, for example, states that models in this project can be
# found in the "models/" directory. You probably won't need to change these!
model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]
snapshot-paths: ["snapshots"]

clean-targets:         # directories to be removed by `dbt clean`
  - "target"
  - "dbt_packages"


# Configuring models
# Full documentation: https://docs.getdbt.com/docs/configuring-models

# In this example config, we tell dbt to build all models in the example/
# directory as views. These settings can be overridden in the individual model
# files using the `{{ config(...) }}` macro.
models:
  raw:

3 Docker compose

Finally, I've already merge all the information into one single structure:


version: '3.9'
services:
  post_db:
    build: . 
    user: postgres
    image: postgres/test:v1
    environment:
      - POSTGRES_USER=${PS_USER}
      - POSTGRES_PASSWORD=${PS_PASSWORD}
      - PG_DATA:/var/lib/postgresql/data/pgdata
      - POSTGRES_DB=${PS_DB}
    healthcheck:
      test: ["CMD-SHELL","pg_isready -U ${PS_USER} ${PS_PASSWORD}"]
      interval: 10s
      timeout: 5s
      retries: 5
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data
    restart: unless-stopped
  python:
    build:
      context: .
      dockerfile: Dockerfile.python
      args:
        PY_VERSION: ${PY_VERSION}
    depends_on:
      - post_db
volumes:
  db-data:

4 Checks if table works

When it runs all:

docker compose up

... and when you check it on the database. You could see there is a view with a name of the ages:

5 Conclusion

I've spend a lot of time trying to get the information between my machine and docker. The reason: I've already have the port ocupy for other project and I've couldn't see if postgres it was doing something.

If you had the same problem as me in Linux you could see your ports in here:

lsof -i :<your desired port>

If you could see anything try with sudo, because in my case i've couldn't see another project working with this port.

When you already found which one is your not desired project then :

(sudo) kill <PID>

Have a nice day:).

Create a cluster with pyspark

Obrotoks — Sat, 27 May 2023 18:34:47 +0000

Goal

Create a cluster with PySpark with python. Based on this repo & on this video

Here is the code repository

Description

We are going to create 4 enviroments:
1- Master: ports: 8080 & 7077
2- Worker-1: ports: 8081
3- Worker-2: ports: 8082

All of them are going to share same volumen on: /opt/workspace

And also they are going to have the same source file

For try to check if everything, it's going to be test this script:

from pyspark.sql import SparkSession


spark = SparkSession \
        .builder \
        .appName("TestCluster") \
        .master("spark://spark-master:7077") \
        .getOrCreate()
df = spark \
        .read.options(
                header='True'
                ,inferschema='True'
                ,delimiter=";") \
        .csv("src/Travel\ Company\ New\ Clients.csv")
df.count()
df.printSchema()
df.select("FamilyMembers").distinct().show()

def myFunc(s):
    return [ ( s["FamilyMembers"],1)]

lines=df.rdd.flatMap(myFunc).reduceByKey(lambda a, b: a + b)
famColumns =["FamilyMembers","Num_reg"]
dataColl=lines.toDF(famColumns)
dataColl.show(truncate=False)

Prequistes

Docker version 23.0.5
Openjdk:8-jre-slim
Python: 3
Spark version: 3.4.0
Hadoop version: 3

Steps:

Firstly, we should the bases of the ApacheSpark (with the same instalation, files, ...) . Three of all of these image are going to be based on this first one.

For this reason, we are going to separate our flow in four parts:
1- Create a script of a bash which is going to create the firts image
2- Create a Dockerfile for the master based on (1)
3- Create a Dockerfile for the workers based on (1)
4- Create a docker-compose to activate (2 & 3)

0 - Env variables

In futures steps, it has been used by version purposes or directory index.


# Version
## JDK
JDK_VERSION=8-jre-slim
## Hadoop
HAD_VERSION=3
## Spark
SPA_VERSION=3.4.0
# Directories
WORK_DIR=/opt/workspace

1 - Create Apache Spark

This could be the step a little harder. First, we should to know what tools we could use:

Openjdk : the basic framework to run pyspark.
Python3: we could use Scala, but in my case I prefer to use python.
Curl: we need it to download to Oficial spark repository
Vim : only in case if we need to edit something

Variables

jdk_version
spark_version
hadoop_version
shared_wk : where we are going to save our main files

Script Main DockerFile

For this reason our Dockerfile should be:

# Layer - Image
ARG jdk_version
FROM openjdk:${jdk_version}

# Layer  - Arguments in dockerfile
ARG spark_version
ARG hadoop_version
ARG shared_wk
ARG py_cmd


# Layer - OS + Directories 
RUN apt-get update -y
RUN mkdir -p ${shared_wk}/data 
RUN mkdir -p /usr/share/man/man1
RUN ln -s /usr/bin/python3 
RUN ln -s /urs/bin/python

# Layer - Prequistes
RUN apt-get -y install curl
RUN apt-get -y install vim 
RUN apt-get -y install python3

# Download the info about PySpark
RUN curl https://archive.apache.org/dist/spark/spark-${spark_version}/spark-${spark_version}-bin-hadoop${hadoop_version}.tgz -o spark.tgz  
RUN tar -xvzf spark.tgz
RUN mv spark-${spark_version}-bin-hadoop${hadoop_version} /usr/bin/ 
RUN echo "alias pyspark=/usr/bin/spark-${spark_version}-bin-hadoop${hadoop_version}/bin/pyspark" >> ~/.bashrc 
RUN echo "alias spark-shell=/usr/bin/spark-${spark_version}-bin-hadoop${hadoop_version}/bin/spark-shell" >> ~/.bashrc
RUN mkdir /usr/bin/spark-${spark_version}-bin-hadoop${hadoop_version}/logs
RUN rm spark.tgz
RUN echo "JAVA_HOME"

# Layer - Enviorment Out
ENV SPARK_HOME /usr/bin/spark-${spark_version}-bin-hadoop${hadoop_version}
ENV SPARK_MASTER_HOST spark-master    
ENV SPARK_MASTER_PORT 7077   
ENV PYSPARK_PYTHON python3   

# Layer - Move files to execute
## Data of the execution
RUN mkdir -p /usr/bin/spark-${spark_version}-bin-hadoop${hadoop_version}/src 
COPY ./src/*  /usr/bin/spark-${spark_version}-bin-hadoop${hadoop_version}/src/
## Script of the execution
RUN mkdir -p /usr/bin/spark-${spark_version}-bin-hadoop${hadoop_version}/script 
COPY ./script/*  /usr/bin/spark-${spark_version}-bin-hadoop${hadoop_version}/script/

# Layer - Volume & workdir 
VOLUME ${shared_wk}
WORKDIR ${SPARK_HOME}

Script bash

To create this image, we are going to use an script to bash to run it:

#!/bin/bash
# Load variables from .env 
set -o allexport;
source .env;
set +o allexport;


# Create Spark Base 
docker build \
        --build-arg jdk_version=${JDK_VERSION} \
        --build-arg hadoop_version=${HAD_VERSION} \
        --build-arg spark_version=${SPA_VERSION} \
        --build-arg shared_wk=${WORK_DIR} \
        -f Dockerfile \
        -t spark-base .;

2 - Create Apache Spark - Master

With this first image create, we should create a master's image:

FROM spark-base

CMD bin/spark-class org.apache.spark.deploy.master.Master >> logs/spark-master.out;bin/spark-submit --master spark://spark-master:7077 script/main.py

3 - Create Apache Spark - Worker

Then, we need to create another image of workers:

FROM spark-base

# Layer - Enviorment Out
ENV SPARK_MASTER_HOST spark-master    
ENV SPARK_MASTER_PORT 7077   



CMD bin/spark-class org.apache.spark.deploy.worker.Worker spark://${SPARK_MASTER_HOST}:${SPARK_MASTER_PORT} >> logs/spark-worker.out

4 - Enviroment

We need to create a docker-compose file to maintain all the structure. I'll use this one:


version: "3.9"
volumes:
  shared-workspace:
    name: "Pyspark"
    driver: local
services:
  spark-master:
    build:
      context: .
      dockerfile: Dockerfile.master
    container_name: spark-master
    ports:
      - "8080:8080"
      - "7077:7077"
    volumes:
      - shared-workspace:${WORK_DIR}

  spark-worker-1:
    build:
      context: .
      dockerfile: Dockerfile.worker
    container_name: spark-worker-1
    ports:
      - "8081:8081"
    volumes:
      - shared-workspace:${WORK_DIR}
    depends_on:
      - spark-master
  spark-worker-2:
    build:
      context: .
      dockerfile: Dockerfile.worker
    container_name: spark-worker-2
    ports:
      - "8082:8082"
    volumes:
      - shared-workspace:${WORK_DIR}
    depends_on:
      - spark-master

5 - Test it

Once everything is everthing is up and running. I'll use the same terminal of docker to run this shell.


./bin/spark-submit --master spark://spark-master:7077 script/main.py

And it looks like it works great:

Have a nice day:)!

Docker with Postgres

Obrotoks — Wed, 24 May 2023 08:45:40 +0000

Everyone has an start and I feel this is one of the ways. There is the code

Goal

Create an postgres with some data load in there.

Data

For this purpouse it's going to be use this dataset. In there we could find two csv:

1st File: Travel Company Old Clients; Number of observations: 682
2nd File: Travel Company New Clients; Number of observations: 1303

We are going to use only Travel Company New Clients.

Prequistes

Docker version 23.0.5
Postgres version 15

1- Create a docker-compose

For this objective we are going to create an image based on actual image

version: '3.9'
services:
  post_db:
    build: . 
    image: postgres/test:v1
    user: postgres
    environment:
      - POSTGRES_USER=${PS_USER}
      - POSTGRES_PASSWORD=${PS_PASSWORD}
      - PG_DATA:/var/lib/postgresql/data/pgdata
      - POSTGRES_DB=${PS_DB}
    healthcheck:
      test: ["CMD-SHELL","pg_isready -U ${PS_USER} ${PS_PASSWORD}"]
      interval: 10s
      timeout: 5s
      retries: 5
    ports:
      - "8080:8080"
    volumes:
      - db-data:/var/lib/postgresql/data
    restart: unless-stopped
volumes:
  db-data:

There are a healthcheck to view if there are something wrong with the connection.

2 - Create table(s)

If we want to create a table as init configuration. The scrips shouldbe place in docker-entrypoint-initdb.d.

There is an example of script in bash:

!/bin/bash

set -e


psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL

CREATE TABLE 
    newClients (
         Num_Age INT
        ,Des_EmployeType VARCHAR(50)
        ,Is_Graduate VARCHAR(3)
        ,Imp_AnnualIncome INT
        ,Num_FamilyMembers INT
        ,Num_ChronicDisease INT
        ,Is_FrequentFlyer VARCHAR(3)
        ,Is_EverTravelledAbroad VARCHAR(3)
    );
EOSQL

Also, it should be place in the Dockerfile.


FROM postgres${PS_VERSION}

# make directory to Docker
RUN mkdir -p /home/raw

# copy scripts
COPY ./raw/* /docker-entrypoint-initdb.d

# run all the scripts in initdb
RUN chmod a+r /docker-entrypoint-initdb.d/*

3- Load data

Now, with the folder of docker-entrypoint-initdb it could be use to load the data adding in the last script:


#!/bin/bash

set -e


psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL

CREATE TABLE 
    newClients (
         Num_Age INT
        ,Des_EmployeType VARCHAR(100)
        ,Is_Graduate VARCHAR(3)
        ,Imp_AnnualIncome INT
        ,Num_FamilyMembers INT
        ,Num_ChronicDisease INT
        ,Is_FrequentFlyer VARCHAR(3)
        ,Is_EverTravelledAbroad VARCHAR(3)
    );

COPY newClients FROM '/home/src/Travel Company New Clients.csv' DELIMITER ';' CSV HEADER;
EOSQL

And it's looks like it works.

If there are any kind of improvment or a better practise please let me know, and I will fix it.