Cloud Function - Load data into Big Query tables against GCS events

In this article, you will learn "How to Fire Cloud Functions on GCS object events to pull the CSV file into BigQuery Data Table" in Google Cloud Platform. Cloud Functions are a serverless computing service offered by Google Cloud Platform (GCP) which are an easy way to run your code in the cloud.

It supports Java, Python, Ruby, Node.js, Go, and .Net. Currently, Google Cloud Functions support events from the following providers- HTTP, Cloud Storage, Cloud Firestore, Pub/Sub, Firebase, and Stackdriver. Gen1 is more lightweight, one concurrency per instance, simple features and less knob to tweak, cheaper, it's pretty much deploy and forget, it is actually an AppEngine standard, while gen2 is on Cloud Run (on GKE), you have more control, up to 1k concurrency per instance, larger resources, longer timeouts, etc, If you don't need it, just use gen1. To complete the tasks outlined above, you must have a GCP account and appropriate access.

To accomplish this task, you can use Google Cloud Functions to trigger on Google Cloud Storage (GCS) object events and then pull the CSV file into a BigQuery data table. Here's a general outline of how you can do this:

• Set up Google Cloud Functions: Create a Cloud Function that triggers on GCS object events. You can specify the event types (e.g., google.storage.object.finalize) to trigger the function when a new file is uploaded to a specific bucket.
• Configure permissions: Ensure that your Cloud Function has the necessary permissions to access both GCS and BigQuery. You'll likely need to grant the Cloud Function service account permissions to read from GCS and write to BigQuery.

• Write the Cloud Function code: Write the Cloud Function code to handle the GCS object event trigger. When a new CSV file is uploaded to GCS, the function should read the file, parse its content, and then insert the data into a BigQuery table.

• Create a BigQuery table: Before inserting data into BigQuery, make sure you have a table created with the appropriate schema to match the CSV file structure.

• Insert data into BigQuery: Use the BigQuery client library within your Cloud Function code to insert the data parsed from the CSV file into the BigQuery table.

For the actual demo, please visit us at our YouTube channel at -

To learn more, please follow us - 🔊 http://www.sql-datatools.com To Learn more, please visit our YouTube channel at — 🔊 http://www.youtube.com/c/Sql-datatools To Learn more, please visit our Instagram account at - 🔊 https://www.instagram.com/asp.mukesh/ To Learn more, please visit our twitter account at - 🔊 https://twitter.com/macxima

PySpark — Retrieve matching rows from two Dataframes

Data integrity refers to the quality, consistency, and reliability of data throughout its life cycle. Data engineering pipelines are methods and structures that collect, transform, store, and analyse data from many sources.

If you are working as a PySpark developer, data engineer, data analyst, or data scientist for any organisation requires you to be familiar with dataframes because data manipulation is the act of transforming, cleansing, and organising raw data into a format that can be used for analysis and decision making.

For example, you have some user’s data in dataframe-1, and you have to new users’ data in a dataframe-2, then you must find out all the matched records from dataframe-2 and dataframe-1. In PySpark, you can retrieve matching rows from two Dataframes using the join operation. The join operation combines rows from two Dataframes based on a common column.

# importing sparksession from  
from pyspark.sql import SparkSession 

# pyspark.sql module 
from pyspark.sql.functions import col

# Create a Spark session and giving an app name 
spark = SparkSession.builder.appName("UpdateMutliColumns").getOrCreate() 

Dataset 1: In this dataset, we have three columns such as Name, Age and Occupation and have a pre-defined schema for our PySpark dataframe as given below — 

# Sample data for DataFrame1
dataset1 = [("Ryan Arjun", 25, "Engineer"),
         ("Kimmy Wang", 30, "Data Scientist"),
         ("Saurabh Yadav", 22, "Analyst")]



# Define the schema for DataFrame1
ds_schema1 = ["Name", "Age", "Occupation"]

PySpark Dataframe 1 from dataset 1 — In PySpark, we are going to call already existing pre-defined createDataFrame function which takes two parameters such as data and schema and passing the above dataset1 and ds_schema1 as given below-  

# Create DataFrames
df1 = spark.createDataFrame(dataset1, schema=ds_schema1)


### show the schema of the dataframe
df1.printSchema()

# Show the original DataFrames
print("DataFrame 1:")
df1.show() 

Dataset 2: In this dataset, we have three columns such as Name, Sex and Country and have a pre-defined schema for our PySpark dataframe as given below —

# Sample data for DataFrame2
dataset2 = [("Ryan Arjun", "Male", "Indian"),
         ("Kimmy Wang", "Female", "Japan"),
         ("Lovish Singh", "Male", "China")]



# Define the schema for DataFrame2
ds_schema2 = ["Name", "Gender", "Country"]

 

PySpark Dataframe 2 from dataset 2 — In PySpark, we are going to call already existing pre-defined createDataFrame function which takes two parameters such as data and schema and passing the above dataset1 and ds_schema2 as given below-

# Create DataFrames for second dataset
df2 = spark.createDataFrame(dataset2, schema=ds_schema2) 


### show the schema of the dataframe
df2.printSchema()

# Show the original DataFrames
print("DataFrame 2:")
df2.show()

Get matching records from both dataframes — In this example, df1.join(df2, “Name”, “inner”) performs an inner join based on the “Name” column. The resulting DataFrame, joined_df, contains only the rows where the “Name” column is common in both Dataframes as given below —

# Join DataFrames based on the "Name" column
joined_df = df1.join(df2, "Name", "inner")

### show the schema of the dataframe
joined_df.printSchema()

# Show the original DataFrames
print("DataFrame with Matching rows:")
joined_df.show()

Note: You can adjust the join type (inner, left, right, full) based on your specific requirements. Additionally, if the column names are different in the two Dataframes, you can specify the join condition explicitly using the on parameter. You can adjust the join condition based on your specific use case and column names.

 

Now, you can see that it is just piece of cake to get the matching records from both dataframe based on your matching keys.

⌛Lets learn more on the data validation side which is the most important part of the data engineering.

Data validation — Data validation is the process of checking the data against predefined rules and standards, such as data types, formats, ranges, and constraints.

1. 💫Schema Validation: Verify data adherence to predefined schemas, checking types, formats, and structures.
2. 💫Integrity Constraints: Enforce rules and constraints to maintain data integrity, preventing inconsistencies.
3. 💫Cross-Field Validation: Validate relationships and dependencies between different fields to ensure logical coherence.
4. 💫Data Quality Metrics: Define and track quality metrics, such as completeness, accuracy, and consistency.
5. 💫Automated Validation Scripts: Develop and run automated scripts to check data against predefined rules and criteria.

 

To learn more, please follow us -
🔊 http://www.sql-datatools.com

To Learn more, please visit our YouTube channel at —
🔊 http://www.youtube.com/c/Sql-datatools

To Learn more, please visit our Instagram account at -
🔊 https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -
🔊 https://twitter.com/macxima

Data Engineering — Best ETL Solution

Data engineering is fighting over standards and governance, and it is not easy to align a large organization to a set of governing standards. You must choose the technology stack and tools that are appropriate for you, the company, and your requirements. If you are searching for ETL solutions for the enterprise, the following are some extra considerations- 

1. Market availability of skill set
2. No code or low code
3. Monitoring your pipelines has never been easier
4. Model of licensing. It depends on the number of automobiles, memory, and so on.

 

Note: In your tooling, make a split in data ingestion, data transformation and data storage and look for those 3 parts separately.

For example, you can work on a full open-source data platform with-

1.  Airbyte or Airflow for data ingestion,

2. dbt or DataForm for transformation and

3. you can use a combination of Postgress, minio and clickhouse for storage.

 

To learn more, please follow us -
http://www.sql-datatools.com

To Learn more, please visit our YouTube channel at —
http://www.youtube.com/c/Sql-datatools

To Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/

To Learn more, please visit our twitter account at -
https://twitter.com/macxima

GCP— Cloud Run a fully managed compute platform

Cloud Run is undoubtedly the next generation of Google Cloud’s “serverless” remedies, and it is one step down in cloud abstractions, enabling you to tweak a bit more without having to worry about scaling (too much). Google Cloud Run is a fully managed compute platform that enables you to deploy containerized applications in a serverless environment. It is part of Google Cloud Platform (GCP) and is designed to simplify the deployment and scaling of containerized applications without the need for managing infrastructure.

 

Cloud Run is Google’s next generation of serverless, with AppEngine remaining to help those who have previously committed to it. 

Cloud Run has several advantages –

— Cloud run enables you to deploy a service to any area inside a single project, making your API truly global.

— Cloud Run also lets you configure a static IP address, while AppEngine does not. This is useful when you need to relay mail or connect to a service that restricts access based on IP address.

— Cloud Run’s docker image support is also more configurable than AppEngine standard, and Cloud Run offers more robust choices (additional ram, etc.).

— Cloud Run is utilised for scaling out a single container several times, which is typically used for microservices, APIs, and frontend UIs that are wrapped within containers.

 

Here are key features and concepts of Google Cloud Run:

Containerized Applications: Google Cloud Run supports containerized applications, allowing you to use Docker containers to package and deploy your applications.

 

Serverless Platform: Cloud Run follows a serverless computing model, where developers focus on writing code and deploying containers without dealing with the underlying infrastructure. It automatically scales based on the incoming request traffic.

 

Scaling: Cloud Run can scale from zero to handle any number of requests. It automatically provisions and scales container instances based on demand.

HTTP(S) Request Handling: Cloud Run is primarily designed for handling HTTP(S) requests, making it suitable for web services, APIs, and microservices.

 

Event-Driven Architecture: In addition to handling HTTP requests, Cloud Run can be triggered by events from various sources, such as Cloud Storage changes, Pub/Sub messages, and more.

 

Integration with GCP Services: Cloud Run seamlessly integrates with other Google Cloud services, enabling you to build end-to-end solutions. It can be used in conjunction with Cloud Storage, Cloud Pub/Sub, Cloud SQL, and other GCP services.

 

Build and Deploy from Container Registry: You can build your container image and deploy it directly from Container Registry, Google Cloud’s container image registry.

 

Environment Variables and Secrets: Cloud Run allows you to configure environment variables and manage secrets securely. This is useful for configuring your application and handling sensitive information.

 

Managed TLS Certificates: Cloud Run provides managed TLS certificates, enabling secure communication over HTTPS without the need for manual certificate management.

 

Multi-Region Deployment: You can deploy Cloud Run services to multiple regions, allowing you to serve content closer to your users.

 

Cost Model: Google Cloud Run follows a pay-as-you-go model based on the number of vCPU-seconds and GB-seconds consumed during request processing.

 

Google Cloud Run provides a flexible and cost-effective solution for deploying and managing containerized applications. It allows developers to focus on building features and applications while abstracting away the complexities of infrastructure management.

 

Cloud Run is significantly more user-friendly, especially when it comes to deployment and development iterations. You may specify a minimum number of instances to aid with cold boot and vertically scale the instances you do have. Running your own instance is undoubtedly the lowest choice in terms of CPU time expenditures, but I would advise you to consider man hours as well when deciding between serverless and self-managed.

 

Ease of Deployment and Development Iterations: Google Cloud Run is praised for its simplicity and ease of use. The deployment process is streamlined, and developers can iterate on their applications quickly. This ease of deployment and iteration is a significant advantage for projects with dynamic development requirements.

 

Minimum Number of Instances and Cold Boot: Cloud Run allows you to set a minimum number of instances, which can be beneficial for addressing cold start latency. This ensures that there are pre-warmed instances ready to handle incoming requests, reducing the impact of cold starts on application responsiveness.

Vertical Scaling: Cloud Run’s ability to vertically scale instances based on demand is valuable. It ensures that resources are allocated efficiently, and the platform can handle varying workloads effectively.

Cost Considerations: While running your own instances may be the cheapest option in terms of raw CPU time costs, it’s crucial to factor in other costs, including development time and operational overhead. Serverless solutions often abstract away infrastructure management complexities, saving time and effort.

Man Hours and Development Efficiency: Considering man hours is a wise approach. The ease of use and reduced operational burden with serverless platforms can contribute to higher development efficiency and faster time-to-market. This becomes particularly relevant when balancing the costs associated with managing your own infrastructure.

 

Recommendation- We often recommend Serverless (Cloud Run, App Engine) over computational Engine, especially if you are unfamiliar with and are unwilling to figure out how much computational capacity you require.

 

Cloud Run is newer and offers some additional capabilities, such as concurrency (a single instance may handle several simultaneous requests), but both should function properly. Cloud Run goes the distance and provides an exceptional service. It may take you an hour to construct a decent and optimized Dockerfile, but everything else will be a breeze after that.

 

To learn more, please follow us -
http://www.sql-datatools.com
To Learn more, please visit our YouTube channel at —
http://www.youtube.com/c/Sql-datatools
To Learn more, please visit our Instagram account at -
https://www.instagram.com/asp.mukesh/
To Learn more, please visit our twitter account at -
https://twitter.com/macxima