DEV Community: Aditya Gupta

How to set up my Yelposhere data engineering project on GCP?

Aditya Gupta — Mon, 17 Apr 2023 07:14:09 +0000

What is YelpoSphere?

YelpoSphere is a data pipeline that extracts, transforms, and loads data from Yelp's public dataset into a GCP environment and performs various analyses on the data. The pipeline combines GCS, BigQuery, Dataproc, Cloud Composer, Cloud Run and Cloud Build for data processing.

YelpoSphere helps users provide insights into the restaurant business.

It performs the following tasks:

Loads the Yelp data into the GCS bucket.
Clean, preprocess and ingest data from GCS to BigQuery using Dataproc.
Uses DBT to normalize the data and performs exploratory data analysis (EDA) to identify trends and insights.
Visualizes the results using Google Data Studio.
Orchestrates different tasks using Cloud Composer.

This is also my capstone project for the Data Engineering Zoomcamp 2023!

https://github.com/Aditya-Gupta1/yelposphere

Where's the data coming from?

The dataset is taken from the Official Yelp Open Dataset website. It is a subset of Yelp's businesses, reviews, and user data for personal, educational, and academic purposes. It is available in JSON files.

Here's a list of files available:

business.json: Contains business data including location data, attributes, and categories.
reviews.json: Contains full review text data including the user_id that wrote the review and the business_id the review is written for.
user.json: User data including the user's friend mapping and all the metadata associated with the user.
checkin.json: Checkins on a business.
tip.json: Tips are written by a user of a business. Tips are shorter than reviews and tend to convey quick suggestions.

For a detailed description of the schema and examples, please visit the official documentation website.

What is the tech stack?

Data Storage: Google Cloud Storage (GCS)
Data Processing: Dataproc
Data Transformations: Data Build Tool (DBT)
Workflow Orchestration: Cloud Composer
Infrastructure: Terraform
Programming Language: Python
Data Visualization: Google Data Studio
Extras: Cloud Build, Cloud Run

What does the pipeline look like?

Here's a complete overview of the pipeline:

When an airflow DAG starts:

It copies the data from a public GCS bucket into the project's data lake.
It will wait for files to come in the GCS bucket's data/raw folder.
Once the files are received, it submits Dataproc jobs to the cluster to ingest data into BigQuery raw tables.
Once the data is ingested into raw tables, it will send a GET request to Cloud Run which will execute DBT jobs.
DBT will perform transformations and load the data into a final dataset, making it ready for visualization.

This final dataset acts as a source for Google Data Studio which creates a dashboard using it. On the other hand:

When commits are pushed to the main branch of the repo, a new Cloud Build is triggered.
This creates a new docker image that gets deployed on Cloud Run.

This ensures that DBT commands perform up-to-date transformations.

Note: Cloud Build and Cloud Run are used to dockerize the DBT project and run DBT commands. This is required because Airflow does not have any convenient operators to run DBT core commands. And scheduling DBT cloud jobs is not supported in the FREE plan of DBT.

In short, it is a workaround to orchestrate DBT with Cloud Composer.

What do I need to run this pipeline?

Operating System

Prefer Linux for this data pipeline. This is because Terraform throws EOF errors on Windows unexpectedly, causing inconvenience.

You can use Google VM in case you're having a Windows machine.

Tools

Python
Terraform
Git

Refer to the following blog for installation instructions on Python (Anaconda) and Terraform:

https://itsadityagupta.hashnode.dev/setting-up-the-development-environment-on-google-virtual-machine

GCP Resources

A GCP Project
A service account
GCloud CLI

Setting up the service account

You can refer to the following blog for general instructions on creating a service account and managing keys:

https://itsadityagupta.hashnode.dev/setting-up-a-gcp-service-account

But, here are the roles that are required for this pipeline:

Cloud Run
Cloud Run Service Agent
Composer Worker
Compute Admin
Dataproc Administrator
Environment and Storage Object Administrator
Secret Manager Secret Accessor
Storage Admin
Storage Object Admin
Storage Transfer Admin

Additionally, you'll need to give one more access to this service account:

In your GCP console, go to IAM > Service Accounts and select your service account.
Go to Permissions > Grant Access
- You need to add your default compute service account as a new principal. It follows the format {your-project-number}-compute@developer.gserviceaccount.com and you can locate your project number in the project settings. Once you have identified your project number, simply add the default compute service account as a new principal and grant it the Cloud Composer v2 API Service Agent Extension role.
  
  `

Note: You should have an environment variable named GOOGLE_APPLICATION_CREDENTIALS which points to the location of the key file of the service account. This is necessary for Terraform to manage resources on your behalf.

Command: export GOOGLE_APPLICATION_CREDENTIALS=/path/to/keyfile.json

Setting up the GCloud CLI

Download the installer from here and install the CLI app.
Run the following command (assuming that you have the GOOGLE_APPLICATION_CREDENTIALS environment variable pointing to your service account key file):

gcloud auth activate-service-account --key-file $GOOGLE_APPLICATION_CREDENTIALS

If you're on Windows, you can provide the path to the key file instead of the environment variable.

Setup your pipeline

Tip: Throughout the process, enable any Google APIs that you're prompted to.

Step 0: Get the code

Fork the repository (This is important!)
Clone the forked repo.

git clone <forked-repo-git-url>
cd yelp-gcp-infrastructure/terraform

Step 1: Modify the DBT env variable

In the dbt/Dockerfile, modify the value of the GCP_PROJECT environment variable with the value of your project id. (Specifically, this line)

Commit and push the changes to your repository.

Step 2: Save the SA key file in Secret Manager

DBT will require the service account key file to connect to BigQuery and execute transformations. This key file can be stored as a secret in the Secret Manager and accessed inside DBT's profile.yml file from Cloud Run.

Go to Secret Manager > Create Secret. (You may need to enable the Secret Manager API)
Give dbt-service-keyfile as the name of the secret. (Name should exactly match this!)
Click on Browse and upload the service account key file as the Secret Value.
Click Create.

Here's the secret overview

Step 3: Setup Cloud Run and Cloud Build for DBT

Go to Cloud Run > Create a Service.
Select Continuously deploy new revisions from a source repository > Setup with Cloud Build.
Click on "Manage connected repositories", sign in to your GitHub account and install the Build Agent on the forked repo only. Then click on Next.
Select "Dockerfile" as the build type and provide the file path as /dbt/Dockerfile. Click Save.
Give your Cloud Run service a name. Pick a region. This region should be the same as that of your BigQuery dataset (to be configured in the further steps). And then select "Require Authentication".
Expand the "Container, Networking, Security" section. Inside the Container section, choose Maximum requests per container to be 1.
Under the Secrets section, select "Reference a Secret". Select the secret you created in the previous step. Reference method as "mounted as volume", and mount path as secrets.
Now, under the Security section, select your service account and click Create.
Your Cloud Run service is ready. Go inside your service and you'll find an endpoint for this service. Copy this as you'll need it in future steps.

Now you have set up your DBT code to run.

A drawback of this workaround is that you can't view the data lineage or documentation. If you want to view that, you can always set this up using the DBT cloud as described in the course video:

https://www.youtube.com/watch?v=rjf6yZNGX8I&list=PL3MmuxUbc_hJed7dXYoJw8DoCuVHhGEQb&index=37

(Optional) Understanding DBT Transformations

Let me walk you through the use of DBT in this pipeline. It is responsible for:

Partitioning stg_businesses table on the is_open column. This will help others in querying this data on the basis of open or closed businesses, as per the requirement.
Creating different data models to answer business questions that will be used to create the final dashboard.

You can go through the data model code to understand how exactly they look.

Step 4: Build your infrastructure using Terraform

Go to terraform/terraform.tfvars file and modify the fields marked with "TODO", with appropriate values.

As of now, all the resources are created in the asia-south1 region except for Cloud Composer which is created in us-central1 region (I suggest not changing the region for Cloud Composer). For the rest of the resources, assign the same region if you want to change it.

The GCS buckets should have a globally unique name which is why you may have to rename them in the file.

The terraform variable "datalake_name" will be the name of the GCS bucket for your pipeline which will contain all your data and spark jobs. You'll need it in the next step.

Also, make sure to change the service account for cloud composer (in that same file) to your service account's email.

After modifying the necessary fields, run the terraform command:

terraform apply

Enter "yes" when prompted and wait for completion.

Here's what Terraform is doing behind the scenes:

Enables the required GCP APIs to work with different resources.
Creates the project's data lake with Spark ingestion scripts inside the scripts folder.
Creates BigQuery dataset and tables with defined schema.
Creates a Dataproc cluster.
Creates a Cloud Composer environment and uploads the Airflow DAG into it.
Once the resources are created, it generates output with a list of resources created and some metadata that is required in the later steps. (All the output values are defined in this file.)

A note on Cloud Composer

Cloud Composer generally takes around 20-30 mins for successful creation. But during the development, I observed that it might fail randomly due to timeout issues at the backend and throw errors stating permission issues. When it fails, it takes around 45-50 mins to throw the error. 😤

Now, if you have correctly set up your service account as mentioned earlier, you have no issues with permissions. In such cases, you can try running Terraform once again by changing the name and region of the composer environment. I've found us-central1 to be the most reliable till now.

Or you can manually create Composer (Composer 2) in the console and give the name, region, and service account and click Create. Once the environment is created, go inside it and click on "Open DAGs Folder".

This will open a GCS bucket where the composer stores all the Airflow DAGs. Click on "Upload files" and upload the cloud_composer/ingest_data_dags.py file. This contains the DAG for this project.

Once the DAG is uploaded, you can move on to the next steps.

Step 6: Upload Airflow variables using Airflow UI

I've used Airflow variables to pass dynamic values to the DAG. These values are related to infrastructure like the BigQuery table names, Dataproc cluster names, the path of the spark jobs etc.

You can get these values from the terraform output. Run the following commands (inside the terraform folder) to get these variables in a JSON file to easily upload them to Airflow.

terraform output -json > ../tf_output.json
cd ..
python utils/get_variables.py --input tf_output.json --output  airflow_variables.json

You will get airflow_variables.json file that you can upload to the Airflow UI using the following steps:

In the composer environment, click on "Open Airflow UI". You will be redirected to an Airflow dashboard.
Go to Admin > Variables.
Click on "Choose File". Select the airflow_variables.json file and click on "Import Variables".
This will upload 16 variables to Airflow.
Similarly, select the data_paths_airflow_variables.json file already present in the repo and import variables from this file as well.
This uploads 5 more variables to Airflow.

Step 7: Create a Cloud Run connection in Airflow

As I mentioned in the overview section, Airflow needs to send HTTP requests to Cloud Run. So you need to create an Airflow HTTP connection for it to connect to Cloud Run's endpoint. Follow the steps to do so:

In the Airflow UI, under the Admin tab, click on Connections.
Click on the + Icon.
Select connection_id as "cloud_run", connection type as "HTTP" and host as your Cloud Run service endpoint. Click on Save.

At this point, you can refresh your Airflow UI until there are no import errors shown at the top of Airflow UI. (It may take a few seconds for errors to resolve)

Before:

After:

This means that your DAG is imported successfully to Airflow and now we just need to run it.

Step 8: Run the Airflow DAG

Finally, you get to run your DAG and visualize the progress. Click on the process_yelp_data DAG followed by the play button and then "Trigger DAG".

This will start the DAG as you can see on the UI:

You can also see the DAG under the "Graph" tab:

Here's what's happening:

It gets the data from a public bucket into the data lake.
It checks if the files are present in the location given by airflow variables, which is the data lake URL.
If yes, it will submit a spark job to the Dataproc cluster to load that particular file into BigQuery.
Once the spark job succeeds, the JSON files are deleted.
And finally, a GET request is sent to the Cloud Run endpoint to execute DBT transformations and tests.

You can see all the DBT models created under the yelp_dbt dataset in BigQuery.

Note that a task may fail, but that is probably because of the less memory in Airflow. In this case, you just have to wait until it retries itself.

And here it gets completed:

Step 9 (Optional): Create a dashboard with Google Data Studio

I have used Google Data Studio to create a dashboard on top of the DBT models. You can either create your dashboard or simply view my dashboard here:

https://lookerstudio.google.com/reporting/03290c4d-e20a-4e61-9978-f4e0ae893ff5

Here's a screenshot of my dashboard in case you're having any trouble viewing it:

Pretty cool, isn't it? 😉

PS: I'll leave out the steps to create a dashboard on google data studio as I believe it is very convenient and self-explanatory since it is a drag-and-drop tool.

Clean up your GCP Resources!

Well, this is important because:

And if you don't clean it, then ...

So better clean it up as soon as you're done checking out the project. Here are the steps to do it:

Inside the terraform folder, run the terraform destroy command and enter yes when prompted. It'll take a few minutes.
Once done, check your GCS buckets and delete any remaining buckets.

(Generally, Composer's GCS bucket doesn't get deleted with Terraform destroy and has to be manually deleted.)

Then go to Cloud Build, Cloud Run, Artifact Registry and Secret Manager to delete everything present there.

That's it.

Now What?

Since now you got to build the data pipeline (and destroy it 😂), here are a few things that I think can be improved:

Better code structure for Terraform.
More data transformations on DBT.
Adding more data quality tests on DBT models.
Research about any easier way to orchestrate DBT with Airflow (although I did a LOT of research and asked a lot of different communities about this)
CI/CD Pipeline

Conclusion

This was my capstone project for the Data Engineering Zoomcamp.

I hope you like it. If you have any questions about the project, face any problems while following along or have a suggestion for me, feel free to comment or drop me a DM on Twitter or Linkedin.

This article is published w/ Scattr ↗️

Storage Transfer Service in GCP

Aditya Gupta — Thu, 30 Mar 2023 06:34:19 +0000

Introduction

The Storage Transfer Service is a data migration service provided by Google Cloud Platform (GCP) that allows users to move large amounts of data between on-premises data centres, cloud storage solutions, and other cloud providers.

The Storage Transfer Service can transfer data from sources such as Amazon S3, Microsoft Azure, and other third-party storage providers, as well as from file servers and other on-premises storage systems. Users can set up transfers that are one-time or recurring, with customizable scheduling and filtering options.

The service supports various transfer options, including parallel transfer and resumable transfer, which helps to ensure data integrity and efficient data transfer. Additionally, it provides features like data validation, notification, and logging that enable users to monitor the transfer process and detect issues.

The Storage Transfer Service can be managed using the GCP Console or through APIs, and it is charged based on the amount of data transferred and the number of operations performed.

In this blog post, I'll guide you through the process of creating a storage transfer service using Terraform to transfer data from one Google Cloud Storage (GCS) bucket to another. To follow along with this tutorial, you'll need to have the following prerequisites in place:

Terraform installed on your local machine
A Google Cloud Platform (GCP) project created in the Google Cloud Console
A GCP service account with a JSON private key file and the necessary permissions

If you haven't created a service account yet, check out my previous blog post on how to create one.

https://itsadityagupta.hashnode.dev/setting-up-a-gcp-service-account

Assumptions

I have a Google Cloud Storage (GCS) bucket named "gcs_capstone_dezoomcamp" that contains approximately 9 gigabytes of data. My goal is to transfer all the files from this bucket to the "data" folder within a new bucket called "destination".

Note: The source bucket is already created whereas the destination bucket has to be created.

Initializing a Terraform Project

Create a new directory for your Terraform project and create a new file named "main.tf". In the "main.tf" file, define the provider by specifying "google" as the provider and your project credentials.

https://gist.github.com/Aditya-Gupta1/4f30855c9a70477d662fe5a4325e81db

Note: If the environment variable GOOGLE_APPLICATION_CREDENTIALS is set to the path of the service account key file, then the credentials fields is not necessary.

Run the terraform init command to initialize the project.

Create the destination GCS bucket

Use the "google_storage_bucket" resource to define the destination bucket. Specify the "name" parameter to set the name of the bucket (globally unique), and the "location" parameter to set the location of the bucket.

https://gist.github.com/Aditya-Gupta1/8a785b0c503bf07f4aab7a1b6e888ae1

Grant permissions to GCS buckets

Storage Transfer Service uses a default service account for data transfer which doesn't have required accesses by default. Hence, we have to configure access to the source GCS bucket and the destination GCS bucket before creating a data transfer job.

Roles for source GCS bucket

One of:
- Storage Object Viewer (roles/storage.objectViewer) if the transfer is to another Cloud Storage bucket.
- Storage Object Creator (roles/storage.objectCreator) if the transfer is to a file system.
Plus one of the:
- Storage Legacy Bucket Writer (roles/storage.legacyBucketWriter) if the object deletes permission is required.
- Storage Legacy Bucket Reader (roles/storage.legacyBucketReader) if the object deletes permission is not required.

Assigning roles to the source GCS bucket

To assign roles and permissions to the storage transfer service, it's important to first identify the default service account being used. This can be achieved by utilizing the "google_storage_transfer_project_service_account" data block.

Once the service account has been identified, it can be granted the necessary roles and added as a member to the relevant Google Cloud Storage (GCS) bucket. Let's look at the terraform code to assign roles to the source GCS bucket:

https://gist.github.com/Aditya-Gupta1/a49dd76be109f5c242b285fc3641c792

Note that the legacyBucketWriter role is not assigned to the source bucket because, in the data transfer job, I'll not delete the source files.

Roles for destination GCS bucket

Storage Legacy Bucket Writer (roles/storage.legacyBucketWriter)

Assigning roles to the destination GCS bucket

https://gist.github.com/Aditya-Gupta1/8b6fc66fd3ff91f76befa86e254fcf93

Define the data transfer job

Use the "google_storage_transfer_job" resource to define the data transfer job. Specify the source and destination bucket names as well as the desired transfer options.

https://gist.github.com/Aditya-Gupta1/536facac85cf99eea1398f3ecb90ca64

Overview

Here's a complete "main.tf" file:

https://gist.github.com/Aditya-Gupta1/3db7dedf4e85dd9bcc1d44632676441f

Now, run the command terraform apply and enter "yes" when prompted. This will perform the following:

Create a destination bucket.
Assign roles to the default service account for the source and the destination bucket.
Create a data transfer job with a schedule as defined.

Make sure to run terraform destroy to destroy all the resources.

Conclusion

By following the steps outlined in this blog post, you can easily define a storage transfer job and assign the necessary permissions to the source and destination buckets. This allows you to transfer data between buckets or even between different cloud storage providers.

More information about this can be found in the following blogs:

I hope you found this blog post helpful and informative. I'd love to hear your thoughts on the topic or any issues you've had following along. Did I miss anything important?

Please leave a comment below and let me know what you think. Your feedback is valuable and can help me improve my writing and provide additional insights for other readers. Thank you for taking the time to read this post!

This article is published w/ Scattr ↗️

Setting up a GCP Service account

Aditya Gupta — Thu, 30 Mar 2023 06:33:50 +0000

Introduction

A service account in Google Cloud Platform (GCP) is a special type of account that allows applications running on GCP to interact with other GCP services, such as Google Cloud Storage, Google Cloud SQL, and Google Compute Engine.

Service accounts are typically used by applications that run on GCP, such as virtual machines, containers, and applications running on Google Kubernetes Engine. Using service accounts, applications can securely access GCP resources without requiring users to authenticate individually.

Each service account is associated with a set of credentials, which typically include a private key that can be used to authenticate the service account with other GCP services. Service accounts can also be granted specific permissions to access particular resources, making it easy to control access to GCP resources on a per-service basis.

In this blog post, I will guide you through the step-by-step process of creating a service account on the Google Cloud Platform (GCP). This account will be specifically created to be used for my future blog posts on GCP. By following this tutorial, you will be able to create a service account and obtain the necessary credentials to interact with other GCP services.

The instructions provided in this post will be concise and easy to follow, ensuring that you can complete the process quickly and efficiently. In case you need to create a service account in the future, you can refer back to this post for guidance. Let's get started!

Creating a Service Account

Sign in to your google cloud console at https://console.cloud.google.com.
From the dropdown at the top, select the project you want your service account to be associated with.
From the navigation menu on the left-hand side, select "IAM & Admin" and then select "Service accounts".
Click the "Create service account" button at the top of the page.
Enter a name for your service account and provide an optional description. Click "Create and Continue".
Under the "Grant this service account access to project", select the following roles to be assigned to the service account and click "Continue" (you can modify the roles as per your requirements):
Click "Done".

Creating a Service Account Key

From the navigation menu on the left-hand side, select "IAM & Admin" and then select "Service accounts".
Locate the service account that you created and click on its name.
Click on the "Keys" tab and then click "Add Key".
Click on the "Create new key" button, select "JSON" as the Key Type and click "Create".
This will download the JSON key file to your local machine.

Setting up the environment variable

One last thing is to create an environment variable GOOGLE_APPLICATION_CREDENTIALS and point it to the path of the downloaded key file.

Conclusion

Now, you have a service account that can be used to interact with other GCP services and infrastructure management (through IaaC tools like Terraform etc.) as well.

To learn more about service accounts, you can go over the official documentation here: https://cloud.google.com/iam/docs/service-account-overview

I hope you found this blog post helpful and informative. I'd love to hear your thoughts on the topic or any issues you've had following along. Did I miss anything important? Please leave a comment below and let me know what you think. Your feedback is valuable and can help me improve my writing and provide additional insights for other readers. Thank you for taking the time to read this post!

This article is published w/ Scattr ↗️

Setting up the development environment on Google Virtual Machine

Aditya Gupta — Thu, 30 Mar 2023 06:33:48 +0000

I'm participating in this year's cohort of the Data Engineering Zoomcamp 2023. This is a community-led, free data engineering course of about 8 weeks. In this blog, I'll summarise the steps to configure a Google Virtual Machine to make it ready for the rest of the course.

Configure SSH Keys

Generate a new SSH key with the following commands:

cd ~/.ssh
ssh-keygen -t rsa -f <key-file-name> -C <username> -b 2048

It'll raise a prompt to enter a passphrase. You can leave it and press enter. If it asks for confirmation, press enter again. Here's an example:

This generates 2 files in the .ssh folder, one for the public (gcp-blog.pub) and one for the private key (gcp-blog).

Next, upload the public key to GCP with the following steps:

Open the gcp-blog.pub file and copy its contents. Or you can use the cat command to display the contents in the terminal.

Go to the Google Cloud console > Compute Engine > Settings > Metadata.

Click on SSH Keys > Add SSH Keys

Paste the contents of the public key that you copied previously on the text box and click Save.

Now, you can connect to your Google VMs using the following command:

ssh -i <PATH_TO_PRIVATE_KEY> <USERNAME>@<EXTERNAL_IP>

Create a Virtual Machine

To set up a Virtual Machine:

Go to Compute Engine > VM Instances

Click on Create Instance.

Populate the configurations for the VM with the following details (Name and Region can be as per your preference):

Next, change the boot disk with the following configurations:

Leave the rest of the configurations to default values and click Create.

This will spin up a virtual machine instance for you. In order to ssh into this instance, run the following command:

ssh -i <PATH_TO_PRIVATE_KEY> <USERNAME>@<EXTERNAL_IP>

Here's an example on my system:

You can also configure an ssh alias, which is a convenient way to store the ssh details in a config file. You can follow my blog on this and set up your alias to easily connect with a VM.

https://itsadityagupta.hashnode.dev/ssh-simplified-aliasing-credentials-with-config-files

I have created an alias for the VM by the name dezoomcamp. Here's the new command to ssh:

ssh dezoomcamp

If you want to connect to a VM using any other options, please go through the official documentation on Connecting to VMs.

Configure the Virtual Machine

Now that you have a Virtual Machine running and a way to ssh into it and run Linux commands, let's start with installing the requirements of the course to make it ready for development.

Installing Anaconda

Visit Anaconda's website and copy the link to Linux 64-Bit Installer.

Download the file in the VM:

      wget https://repo.anaconda.com/archive/Anaconda3-2022.10-Linux-x86_64.sh

Run the downloaded file:

      bash Anaconda3-2022.10-Linux-x86_64.sh

Keep pressing Enter to scroll down and enter yes to accept the license terms.
Press Enter to confirm the default location.
Enter yes to run the conda init when asked.
After the installation is complete, run the command source .bashrc to apply the changes to the .bashrc file. Alternatively, you can log out of the session using the logout command and then ssh back in for the changes to take effect.

You'll notice the anaconda environment name in the shell prompt once the changes are applied. Also, from the above image it is confirmed that python is also installed.

Installing Docker

Run the following commands:

sudo apt-get update
sudo apt-get upgrade
sudo apt-get install docker.io

This will install docker but you'll not be able to run it without sudo. It'll throw a permission denied error:

To run docker without sudo, run the following commands:

sudo groupadd docker
sudo gpasswd -a $USER docker
sudo service docker restart

(Refer to this link for an explanation of the above commands.)

Log out of the ssh session and log back in to re-evaluate the group memberships and try running the docker run hello-world command.

This time it works!

Installing docker-compose

Go to the docker-compose GitHub repo and under the latest release, find the "docker-compose-linux-x86_64" asset and copy its link.
Create a new bin folder in VM and download the asset into it:

      mkdir bin
      cd bin
      wget https://github.com/docker/compose/releases/download/v2.16.0/docker-compose-linux-x86_64 -O docker-compose

This will create a new file docker-compose in the bin folder.
Make this file executable:
```
      chmod +x docker-compose
```
Now, add the bin folder to the path:
- Go to the home directory: cd
- Open the .bashrc file: nano .bashrc
- Paste the following to the end of the .bashrc file:
- ```
      export PATH="${HOME}/bin:${PATH}"
```
- Press Ctrl+O > enter > Ctrl+X
- Run source .bashrc for the changes to take effect.

Now, you have docker-compose installed as well.

Installing Pgcli

Pgcli is used to connect to the Postgres database and execute queries. It can be installed using the following command:

pip install pgcli

Once the installation is complete, you can connect to a Postgres database using the command:

pgcli -h <hostname> -u <username> -d <database-name>

It will prompt for the password afterwards.

Installing Terraform

Go to terraform's installation website.
Copy the link for Linux's AMD64 file.
Go to the bin folder created previously and download the file using wget:

    cd ~/bin
    wget https://releases.hashicorp.com/terraform/1.3.9/terraform_1.3.9_linux_amd64.zip

To unzip this file, install the unzip package:
```
    sudo apt-get install unzip
```
Now, unzip the file:

    unzip terraform_1.3.9_linux_amd64.zip

There will be a terraform executable file extracted. You can delete the zip file.
Since this file is in the bin folder and the bin folder is in the path, everything is set up.
Try running the following command to verify terraform's installation:
```
    terraform --version
```

Creating a service account

Go to IAM and Admin > Service Accounts

Click on the "Create Service Account" button at the top and provide the service account name.
Assign the following roles to this service account:

Click Done. The service account is now created.
To generate keys for this service account, click on the 3-dot menu and then on "manage keys"

On the following page, click on Add Key > Create new key> JSON format. Click Create.
This will download a JSON file. This is to be uploaded to the VM using SFTP.
Navigate to the path of the downloaded file on the terminal and open an SFTP connection by running the command: sftp <ssh-alias-name>.
Next, create a folder .gcp and upload the credentials file into it by running the following commands:

    mkdir .gcp
    put <credentials-file-name> .gcp/

This will upload the file to VM. Here's a screenshot from my system:

Authenticate GCP using the service account credentials

To authenticate GCP, we need to set an environment variable $GOOGLE_APPLICATION_CREDENTIALS to point to the service account JSON file.

export GOOGLE_APPLICATION_CREDENTIALS=/home/aditya/.gcp/<credentials-filename>.json

Next, authenticate GCP using the following command:

gcloud auth activate-service-account --key-file $GOOGLE_APPLICATION_CREDENTIALS

Here's my example:

Installing Pyspark

Instructions to install spark on Linux are well documented on the course's GitHub repository.

You can follow the below 2 files in the mentioned order, to install Pyspark:

Tip: You can write all the environment variables mentioned in the above 2 files to .bashrc followed by the source .bashrc command to make the variables permanent. Otherwise they'll need to be setup everytime you restart your VM.

Cloning the course repo

Clone the course repo into the home directory. Your final folder structure should look something like this:

Open a Remote Connection from Visual Studio Code

Install the "Remote-SSH" extension.

Press F1 and select "Remote SSH: Connect to Host". It will show you the hosts configured on your system.

Select the one associated with your Virtual Machine.

Open the course repo folder and you're all set to start your development!

Conclusion

You can also refer to the course video for setting up the development environment here. Make sure to stop your VM once you're done with the setup or your development.

Thanks for reading!

This article is published w/ Scattr ↗️

Linkedin Database Design

Aditya Gupta — Thu, 30 Mar 2023 06:32:12 +0000

Introduction

LinkedIn stands out as a professional networking platform with a user base of over 750 million professionals worldwide. It handles an enormous amount of user data, including profiles, connections, job postings, and much more. In this blog post, I'll design a database for Linkedin and understand the various entities and relationships between them.

To keep the scope of this blog limited, here are the requirements that we want our database to satisfy:

Users can create their profile with information like name, contact information, headline, summary, education, work experience and different skills.
Users can send connection requests to other users, or follow them only. They should also be able to accept or reject the connection request.
Users can create posts, and like and comment on others' posts.

You can view the complete SQL code to create the below design along with the ER diagram on my github repo here.

User Profile

Each user will have some basic information like name, current location, headline and summary. Along with this, each user can have multiple work experiences and multiple education details. Considering these things, we can have the following tables:

UserProfiles:
▸ user_id (Primary Key)
▸ first_name
▸ middle_name
▸ last_name
▸ contact_information_id (Foreign Key to the ContactInformation table)
▸ headline
▸ location
▸ about_section
ContactInformation:
▸ contact_information_id (Primary Key)
▸ email
▸ email_visibility
▸ phone_no
▸ phone_no_visibility
▸ website
▸ website_visibility
Visibility can be one of "PUBLIC", "PRIVATE" or "CONNECTIONS_ONLY".
Skills:
▸ skill_id (Primary Key)
▸ skill_name
EducationDetails:
▸ education_id (Primary Key)
▸ school_id (Foreign Key to the Schools table)
▸ degree
▸ field_of_study
▸ start_date
▸ end_date
▸ grade
▸ description
Schools:
▸ school_id (Primary Key)
▸ school_name
▸ location
▸ website
UserEducationDetails:
▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
▸ education_id (Primary Key, Foreign Key to the EducationDetails table)
This table records the user and their corresponding education details.
EducationSkills:
▸ education_id (Primary Key, Foreign Key to the EducationDetails table)
▸ skill_id (Primary Key, Foreign Key to the Skills table)
Associates skills with education.
UserSkills:
▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
▸ skill_id (Primary Key, Foreign Key to the Skills table)
Associate users with the skills.
Companies:
▸ company_id (Primary Key)
▸ company_name
▸ company_website
▸ about_company
Experiences:
▸ experience_id (Primary Key)
▸ company_id (Foreign Key to the Companies table)
▸ profile_headline
▸ employment_type
▸ start_date
▸ end_date
▸ location_type
▸ employment_location
▸ is_current_role
▸ employment_industry
▸ description
Here, employment_type can be one of "FULL-TIME", "PART-TIME", "SELF-EMPLOYED", "FREELANCE", "INTERNSHIP" or "TRAINEE", and location_type can be one of "ON-SITE", "HYBRID" or "REMOTE".
ExperienceSkills:
▸ experience_id (Primary Key, Foreign Key to the Experiences table)
▸ skill_id (Primary Key, Foreign Key to the Skills table)
Associates experience with skills.
UserExperience:
▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
▸ experience_id (Primary Key, Foreign Key to the Experiences table)
Associates users with experiences.

This completes a user's profile.

Connection Requests

We need to record who sent a connection request to whom, along with its status, and the followers of a user. Here are the tables to record this:

Connections:
▸ connection_id (Primary Key)
▸ request_sent_by (Foreign Key to the UserProfiles table)
▸ request_sent_to (Foreign Key to the UserProfiles table)
▸ request_status
request_status can be one of "CONNECTED" or "PENDING".
Followers:
▸ followed_by (Primary Key, Foreign Key to the UserProfiles table)
▸ following (Primary Key, Foreign Key to the UserProfiles table)

These 2 tables can record information for connections and followers.

Posts and Comments

A user can create a post, and like and comment on others' posts. We can have the following tables for this:

Posts:
▸ post_id (Primary Key)
▸ user_id (Foreign Key to the UserProfiles table)
▸ description
▸ created_at
▸ updated_at
PostReactions:
▸ post_id (Primary Key, Foreign Key to the Posts table)
▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
▸ reaction
reaction can be one of '"LIKE'", "CELEBRATE", "SUPPORT", "FUNNY", "LOVE", or "INSIGHTFUL".
Comments:
▸ comments_id (Primary Key)
▸ post_id (Foreign Key to the Posts table)
▸ user_id (Foreign Key to the UserProfiles table)
▸ description
▸ created_at
▸ updated_at
CommentReactions:
▸ comments_id (Primary Key, Foreign Key to the Comments table)
▸ user_id (Primary Key, Foreign Key to the UserProfiles table)
▸ reaction

Complete Design

Here's a complete database design for the tables mentioned above:

Conclusion

Overall, there are 18 tables in this design. This design represents only the basic functionality. A lot more features can be added to this such as:

LinkedIn Groups
Linkedin Pages
Linkedin Articles
Block a User
Featured Section in a user's profile

and many more.

Also, this design is for the OLTP database. For analytical processing or warehousing, we would need a much more denormalized design. But that's a topic for another blog.

Thanks for reading!

This article is published w/ Scattr ↗️