As a part of Helinski city effort to encourage the usage of sustainable transportation methods, and to reduce the dependency on cars, the city is trying to promote the usage of bike share solutions for last mile trips. To monitor the adoption and the sucess of such solutions in Helinsky, the bearu of transportation needs a dashboard that summarizes the trends of using bike share solutions, based on trips data for years 2016-2020, before covid lockdown.

In order to have a better understanding of the solution adoption, the following is required:

• A graph showing the total trips year-to-year
• A map showing the popularity of each station: how much trips were made from each station.

• The pipe line takes the raw data from CSV in a tar.gz file
• Then using spark, it reads the data and cleans it, adding missing departure_id based on station coordinates.
• Then it saves the cleaned data as parquet files to GCS, partitioned by year.
• Similarly, it will save the data into big-query table
  • The table is partitioned by year to reduce per-year query costs
  • And the clustering is done on departure_id, to facilate the map dashboard, which count trips by departure station.
• Data looker is used to create the dashboard as shown in the image
• You can view the dashboard from here
  • unfortunately I couldn't find an easy way to automate the dashboard creation

• Google cloud account
• gcloud cli
• terraform

• The Helsinki City bikes data set from kaggle is available on this url. The pipeline will load it automatically.

• In Google-Cloud-Services console, create a new project.

• Init your shell, gcloud tool and terraform

# Set the project name in shell
export GCS_PROJECT=<your-gcp-project-id>;
export GCS_REGION=<your-gcp-project-region>;

#login to gcs using gcloud tool
gcloud auth login;

#Set the current project to the one created
gcloud config set project $GCS_PROJECT;

#set the project for terraform in the current shell
export TF_VAR_project=$GCS_PROJECT;
export TF_VAR_location=$GCS_REGION;

• init terraform state file

terraform init

• Check changes to new infra plan. Notice that terraform commands must run in the same shell you used to login to GCS, in order to have credentials to access the cloud.

terraform plan

• Create new infra

terraform apply

• run the notebook using dataproc

# copy the pipeline script to GCS
export SPARK_JOB=gs://$GCS_PROJECT-datalake/process_bikes-trips.py;
gsutil cp ./process_bikes-trips.py $SPARK_JOB;

gcloud dataproc jobs submit pyspark $SPARK_JOB --cluster=$GCS_PROJECT-dataproc --region=$GCS_REGION --jars=gs://hadoop-lib/bigquery/bigquery-connector-hadoop2-latest.jar,gs://spark-lib/bigquery/spark-bigquery-with-dependencies_2.12-0.30.0.jar; 

• When done, take down the infrastructure.

terraform destroy

• BigQuery python client documentation
• Creating dataproc cluster using terraform