Wittline

insert into {project}.{dataset}.{tabledestination}
(id, category, lastdate)
select id, category, lastdate
from {project}.{dataset}.{tablesource}
WHERE EXTRACT(YEAR FROM lastdate) = {year} and category = '{category}'

Wittline

insert into {project}.{dataset}.{tablefinal}
(id, category, lastdate)
select id, category, lastdate
from {project}.{dataset}.{table1}
union all
select id, category, lastdate
from {project}.{dataset}.{table2}

Wittline

version: '3.1'

services:
  db:
      container_name: pg_container
      image: postgres
      restart: always
      environment:
          POSTGRES_USER: "postgres"
          POSTGRES_PASSWORD: "pg12345"

Wittline

# DROP TABLES
users_table_drop = "DROP TABLE IF EXISTS users"
departments_table_drop = "DROP TABLE IF EXISTS departments"
companies_table_drop = "DROP TABLE IF EXISTS companies"
staging_table_drop = "DROP TABLE IF EXISTS staging"

# CREATE TABLES

staging_table_create = ("""
CREATE TABLE IF NOT EXISTS staging(

Wittline

import psycopg2
import pandas as pd
from sql_queries import create_table_queries, drop_table_queries, fill_table_queries, create_constraints


def create_connection(params):
    """
     create a new connection with the postgreSQL 
     database and return the cur and conn object
    :param params: connection string   

Wittline

from typing import Optional
from fastapi import FastAPI
from fastapi.responses import JSONResponse
import uvicorn
import pandas as pd
import psycopg2
import db_engine as dbe
import os
from pydantic import BaseModel
import configparser

Wittline

FROM python:3.8

WORKDIR /ingestion

COPY requirements.txt .

COPY wait-for-it.sh /usr/wait-for-it.sh

RUN pip install -r requirements.txt

Wittline

FROM docker.io/bitnami/spark:2

USER root
ENV LIVY_HOME /opt/bitnami/livy
WORKDIR /opt/bitnami/

RUN install_packages unzip \
    && curl "https://downloads.apache.org/incubator/livy/0.7.1-incubating/apache-livy-0.7.1-incubating-bin.zip" -O \
    && unzip "apache-livy-0.7.1-incubating-bin" \
    && rm -rf "apache-livy-0.7.1-incubating-bin.zip" \

Wittline

version: '3'

services:
  spark-master:
    image: docker.io/bitnami/spark:2
    environment:
      - SPARK_MODE=master
      - SPARK_RPC_AUTHENTICATION_ENABLED=no
      - SPARK_RPC_ENCRYPTION_ENABLED=no
      - SPARK_LOCAL_STORAGE_ENCRYPTION_ENABLED=no

Wittline