Create postgres tables from CSV files
This first section is only relate to creating tables from CSV files using postgres container alone. Just one of my experiments. If you interest, you can just follow these steps (only if your working environment support bash):
sh scripts/prep.sh
The prep.sh will handle everything for you by doing follwing:
# Start postgres db container
docker-compose -f postgres.yaml up -d
# Sleep to make sure the container is fully up running
sleep 3
# I have problem with mouting csv files via docker compose, so here we go
# Copy csv and setup.sql to create required tables
docker cp ./csv/ my_postgres:
docker cp ./scripts/setup.sql my_postgres:setup.sql
# Execute the script in postgres db
docker exec -it my_postgres psql -p5432 --dbname=postgres --username=postgres --file=setup.sql
# Shutdown the container
docker-compose -f postgres.yaml down --remove-orphans
I had problem with mount volumn that I can't mount the files under csv and scripts folders. Which still can be improved with a proper mount. But let's skip it for now to save time.
Initial Setup/Start Airflow container
This section will use a separate docker-compose.yaml than the above test. It will be relate due to the fact that we want to use airflow to schedule the tasks above (create table and load data). To do so, do the following. First prepare folders. You can call a new folder specifically for this if you want.
# (optional) mkdir airflow && cd airflow
mkdir ./dags ./logs ./plugins
Next we need the airflow docker-compose.yaml in our airflow directory
curl -O https://airflow.apache.org/docs/apache-airflow/stable/docker-compose.yaml
Next make sure we will have a proper permission to initial Airflow
echo -e "AIRFLOW_UID=$(id -u)\nAIRFLOW_GID=0" > .env
Then we must initial Airflow instance
docker-compose up airflow-init
Wait until the initial finished then (you can use -d to detach if you want)
docker-compose up
Now you will be able to connect to Airflow GUI via http://localhost:8080/
Create Airflow DAG task
First thing, you need to setup connection for postgres database. Go to tab Admin > Connection > +, wow you have to fill details of the connection:
Connection Id: postgres_default
Connection Type: 'Postgres'
Host:
Schema: postgres (default)
Login:
Password:
Port:
Click "Test" button to check your connection then save. Now click at the Airflow icon to return to home page. You should see task name "create_postgres_tables". Try to run by clicking start button select "Trigger DAG".
1 Nov 1, 2021
1 Dec 3, 2021
5 Sep 28, 2022
1 Feb 11, 2022
1 Dec 26, 2021
79 Sep 20, 2022
293 Dec 29, 2022
1 Feb 27, 2022
3 Dec 7, 2022
14 Jan 03, 2023
1 Jan 28, 2022
179 Dec 21, 2022
876 Jan 04, 2023
863 Jan 08, 2023
2 Oct 23, 2021
3.9k Dec 31, 2022
762 Jan 07, 2023
1 Nov 07, 2021
0 Dec 16, 2022
250 Jan 03, 2023
96 Jan 01, 2023
6 Oct 16, 2022
2 May 26, 2022
107 Jan 04, 2023
2k Dec 29, 2022
8 May 17, 2022
1 Jan 19, 2022
12 Aug 30, 2022