This is ETL/ELT script for Google Cloud Platform.
- Google Cloud Platform (GCP) including:
· GCP Instance
· GCP Bucket/Cloud Storage
· GCP BigQuery
· GCP DataFlow - Docker
- Airflow
- Python
- Search Engine Result (on Flight & Ticker keyword) datasets
- Login to your GCP account
- Go to IAM & Admin > Service Accounts
- Create a Service Account (Give minimum access to BigQuery (BigQuery Job User role) and Dataflow (Dataflow Worker role))
- Get and keep key-file.json
- Create a project
- Go to Compute Engine > VM Instances
- Create a Instance with spec: e2-standard-2 (2vCPU, 8GB memory), OS Debian 10, 50 GB HDD
- Go to Cloud Storage
- Create Bucket
- Upload the data files
- Go to VPC Network > Firewall
- Create airflow-port rule (IPv4 ranges 0.0.0.0/0, tcp 8080)
- On your instance, check all the firewall boxes, and add the airflow-port into the network tags)
- Go to your instance and start the instance
- Go to SSH > open the command window
- Install the docker in your GCP instance
- Install the docker compose
- Create (or download) the docker-compose (.yaml) file
- Initalizing the Airflow Environment (docker-compose up airflow-init)
- Start the services (docker-compose up -d), make sure that the airflow didn't have any trouble.
- Open the Airflow GUI on (gcp-external-ip):8080
- Create GCP connection in Airflow Connection menu (conn type: Google Cloud, keyfile json: copy paste from the gcp json key file)
- For Dataflow, go to Dataflow menu in Big Data section
- You can create job based on template or create it by code in your instance
- For Big Query, go to Big Query menu in Big Data section
- You can create your data sets and tables on your data sets
Main Goal: Create ETL (pipeline) process for uploading data .csv from google cloud stiorage (bucket) to google big query table
ETL (pipeline) process details:
- Read Data (from google cloud storage)
- Split Data (using delimiter ',')
- Format to Dict (convert to python dictionary/JSON like format)
- Delete Incomplete Data (remove row with NULL)
- Change Data Type (convert data type into suitable data type such as string to int, string to date)
- Delete Unwanted Data (drop unrelevant columns)
- Write to Big Query (create big query table and upload final data)
pipeline scripts are in /pipeline folder
How to implement the ETL:
- Upload data into Google cloud storage:
- Create new data set in Google Big Query (note: make sure that cloud storage and big query dataset is in same/relevant region)
- Create pipeline script
- Run the pipeline script and monetize it in Google Data Flow
- If it successly executed, create a DAG for airflow
- Test and schedule the Airflow DAG
another note: https://stackoverflow.com/questions/49302859/gsutil-serviceexception-401-anonymous-caller-does-not-have-storage-objects-list
DAG script is in /dags folder