
#### Table of Contents

1. Introduction
2. Choose Your Dataset
3. Building the Initial Model
4. Automating with Vertex AI Pipelines
5. Deploying for Predictions
6. Cost & Strategy Comparison
7. Conclusion

---

### Introduction

In this post, I explore the end-to-end workflow of GCP Vertex AI using a housing dataset. We'll walk through the entire process—from uploading the data and using AutoML to generate an API, to analyzing the final execution flow. My goal is to establish an entire workflow and to understand the costs involved in this tutorial.

### Choose Data Science Problem

Since the purpose of this is to try Vertex AI AutoML with any dataset, I decided to solve a price prediction problem using data science. This is a classic linear regression task, making it the perfect way to test out Vertex AI without overcomplicating things. I wanted to start with a manageable dataset to see how the platform handles the end-to-end process.

### Choose Your Dataset

Since the goal is to test Vertex AI, I chose the simplest dataset possible. You can find it [here](https://github.com/keke78ui9/datasets/tree/main/house-sample-datasets); I’ve augmented about half of the data to meet the Vertex AI minimum requirement of 1,000 rows.

Below is the sample data from this dataset. The dataset starts with price, followed by other features: area, bedrooms, bathrooms, etc. Price is the target value here. What we'd like to do with this data is use features such as area, bedrooms, and bathrooms to predict the target value **price**.

```csv
price,area,bedrooms,bathrooms,stories,mainroad,guestroom,basement,hotwaterheating,airconditioning,parking,prefarea,furnishingstatus
13300000,7420,4,2,3,yes,no,no,no,yes,2,yes,furnished
12250000,8960,4,4,4,yes,no,no,no,yes,3,no,furnished
12250000,9960,3,2,2,yes,no,yes,no,no,2,yes,semi-furnished
...
```

### GCP AutoML

The rest of the steps involve using this dataset to build, train, and deploy a model on the Vertex AI platform. So what's **AutoML**?

Without AutoML, a typical ML process involves loading the dataset with Python, performing EDA to understand the data and its correlations, then using feature engineering to select the right features. After that, you might do cross-validation to train and evaluate the model and find the most efficient approach for better predictions.

AutoML does all of that for you, so you don't have to worry about the best practices for building a model. It's suitable when you're trying to build your first ML model and want to skip the low-level details.

### Building the Initial Model

For tabular data (rows and columns), **AutoML Tabular** is the most efficient starting point. It handles complex math and feature scaling automatically.

#### 1.1 Data Preparation

- **EDA:** Perform Exploratory Data Analysis to understand distributions.
- **Storage:** Format your data into a CSV and upload it to a **Google Cloud Storage (GCS)** bucket.

#### 1.2 Create Dataset in Vertex AI

Navigate to the Vertex AI console and create a new dataset. Select the **Tabular** type and the **Regression/Classification** option.

![Create Dataset for Vertex AI](/images/vertex-auto-ml/img1.png)

> **What is "Tabular" in Vertex AI?**
> It refers to structured data organized in rows and columns. It is the standard format for predicting numerical values (Regression) or categories (Classification).

#### 1.3 Train the Model

- **Objective:** Select **Regression** (since price is a continuous numerical value).
- **Target Column:** Select the column representing `price`.
- **Budget:** Vertex AI requires a minimum of 1 node hour. For datasets under 100k rows, 1-3 hours is usually sufficient.

![Train the Model for Vertex AI](/images/vertex-auto-ml/img2.png)

As you can see above, the model was successfully trained and now has a version with results. Here is what each metric means for the model we just trained with AutoML.
| Metric | Value | What it's telling us |
| :------------ | :--------------------------- | :---------------------------------------------------------------------------------- |
| **Name** | untitled_2065595810353967640 | The unique identifier for this training run. |
| **Status** | Succeeded | The model training completed without errors. |
| **Created** | Jan 30, 2026 | The timestamp of when the evaluation was finalized. |
| **MAE** | 687,684.8 | The average "raw" error; your predictions are off by this amount on average. |
| **MAPE** | 17.044% | The relative error; predictions are typically off by about 17% of the actual value. |
| **R-squared** | 0.784 | The "goodness of fit"; your model explains ~78% of the data's behavior. |
| **RMSE** | 820,074.06 | Penalizes larger errors more than MAE; suggests some significant outliers exist. |
| **RMSLE** | 0.2 | Measures the ratio of error; lower values mean the model handles scales well. |

---

### Deploying for Predictions

Once trained, you can consume the model in two ways:

#### 3.1 Real-Time Predictions

Best for "instant" price suggestions in an app.

- **Deploy to Endpoint:** Host the model on a Vertex AI Endpoint.
- **Request:** Use the Vertex AI SDK (Python, Node.js, etc.) to send feature data.

```python
# Sample snippet for real-time prediction
from google.cloud import aiplatform

endpoint = aiplatform.Endpoint(endpoint_name="PROJECT_ID/locations/REGION/endpoints/ENDPOINT_ID")
prediction = endpoint.predict(instances=[{"feature_1": "value", "feature_2": 10}])
print(prediction)
```

#### 3.2 Batch Predictions

Best for "offline" processing when you have many predictions to make at once.

- **Input:** Prepare a CSV file with all instances and upload to GCS.
- **Batch Job:** Submit a batch prediction job via the Vertex AI console.
- **Output:** Results are written back to GCS in JSON format.

```python
# Sample snippet for batch prediction
from google.cloud import aiplatform

model = aiplatform.Model(model_name="PROJECT_ID/locations/REGION/models/MODEL_ID")
batch_prediction_job = model.batch_predict(
    job_display_name="housing-price-batch-predict",
    gcs_source_uris=["gs://your-bucket/input.csv"],
    gcs_destination_output_uri_prefix="gs://your-bucket/predictions/"
)
batch_prediction_job.result()
```

---

### Cost & Strategy Comparison

#### Understanding Vertex AI Pricing

1. **Training Cost (AutoML Tabular)**

   - Charged per **node-hour**. A typical small model trains in 1–3 hours on a single node.
   - For free tier: Google offers **$300 credit** over 90 days. A 2-hour training job costs roughly $10–$15.
   - Equation: `Cost = Node-Hours × $5–$8/hour`

2. **Endpoint Hosting Cost**

   - Once deployed, you pay for compute resources even if idle.
   - A single `n1-standard-4` machine costs ~$20/day.
   - **Best practice:** Enable autoscaling or manually scale to 0 when not in use.

3. **Batch Predictions**

   - Cheaper than real-time endpoints for bulk processing.
   - Charged per prediction (roughly $0.015 per 1,000 predictions).
   - **Best for:** Nightly or weekly report generation.

4. **Data Storage (GCS)**
   - Minimal cost for CSV files. First 5 GB is free on free tier.

#### Cost Breakdown for This Example

| Component                 | Hours/Calls      | Cost             |
| ------------------------- | ---------------- | ---------------- |
| Training (AutoML)         | 2                | $10–$15          |
| Model Deployment          | 24 hours (1 day) | ~$20             |
| 10,000 Batch Predictions  | 10k              | ~$0.15           |
| GCS Storage               | CSV only         | Free (under 5GB) |
| **Total (1-day project)** | —                | **~$30–$35**     |

#### Strategy: How to Minimize Costs

1. **Use Free Tier Credits:** Start with the $300 90-day free tier credit. Train and experiment aggressively.
2. **Turn Off Endpoints When Not Testing:** A deployed endpoint costs money even if unused. Delete it after testing.
3. **Prefer Batch Predictions:** For infrequent use cases, batch predictions are 10–100x cheaper than maintaining an endpoint.
4. **Monitor with Cloud Budget Alerts:** Set a budget of $50/month and receive email alerts when you approach the limit.

---

### Conclusion

Vertex AI with AutoML Tabular is an excellent entry point for anyone wanting to explore machine learning on GCP without deep expertise in data science or model architecture. The platform handles the heavy lifting—feature engineering, hyperparameter tuning, and deployment—so you can focus on the business problem.

**Key Takeaways:**

- **Simple data + AutoML = Fast results.** A housing price prediction model takes just 2–3 hours to train and deploy.
- **Costs are transparent and manageable.** With the free tier credit, you can build, train, and deploy multiple models risk-free.
- **Automate retraining with Pipelines.** Once your workflow is validated, use Kubeflow Pipelines to keep your model fresh as new data arrives.
- **Choose the right inference method.** Real-time endpoints are ideal for interactive apps; batch predictions suit reporting workflows.

By starting with the free tier and understanding the cost model upfront, you're well-positioned to confidently scale to production workloads.

---


A practical guide to predicting housing prices using GCP Vertex AI with AutoML Tabular.

How to Build Your First Vertex AI Model Using AutoML


## Table of Contents

1. [Introduction](#introduction)
2. [Switch to the Correct Workspace Environment](#switch-to-the-correct-workspace-environment)
3. [Pull Latest State File](#pull-latest-state-file)
4. [Test by Running Terraform Plan](#test-by-running-terraform-plan)
5. [Run Import to Update State File](#run-import-to-update-state-file)
6. [Conclusion](#conclusion)

## Introduction

When working with Terraform and existing cloud resources, you may need to import those resources into your Terraform state file. This blog shows how to do that.

## Switch to the Correct Workspace Environment

For example, you can run the following command to create a QA workspace environment:

```
terraform workspace new qa
or
terraform workspace select qa
```

## Pull Latest State File

Run this command to get the latest state file for the environment from storage:

```
terraform state pull
```

This is what you get when you run `terraform state pull` — basically nothing is saved to the current workspace state:

```
{
  "version": 4,
  "terraform_version": "1.4.6",
  "serial": 1,
  "lineage": "59eb4435-e702-29bf-71de-1231231",
  "outputs": {},
  "resources": [],
  "check_results": null
}

```

## Test by Running Terraform Plan

If at this point you run `terraform plan`, you'll see some changes if you have active resources in your local files. This is incorrect if the same resource already exists in GCP, and you don't want to run `terraform apply`, which would end up creating duplicate resources or throwing errors.

## Run Import to Update State File

For instance, if you have an existing GCP BigQuery schedule query, you can run the following command to sync the state file:

```
terraform import google_bigquery_data_transfer_config.job projects/1233/locations/us/transferConfigs/12312312
```

The above command should update the state file, and if you run `terraform plan` again, you should not see any changes.

## Conclusion

Importing existing resources into your Terraform state file ensures that Terraform is aware of your current infrastructure. This prevents duplicate resource creation and keeps your state file in sync with your actual cloud resources.


This blog shows how to run Terraform to create a state file from existing resources.

How to Create Terraform State File for Current Resources


## Table of Contents

1. [About Terraform with GCP](#about-terraform-with-gcp)
2. [Preparation](#preparation)
3. [Create Terraform Files](#create-terraform-files)
4. [Start Deployment](#start-deployment)
5. [Conclusion](#conclusion)

## About Terraform with GCP

Terraform provides infrastructure automation to provision resources to many cloud providers. GCP is one of them. This article shows how to use Terraform to deploy a simple BigQuery Schedule Job to GCP.

## Preparation

Before getting started, ensure you have Terraform installed and a GCP account with the necessary permissions.

## Create Terraform Files

### Create `provider.tf` File

Start with the Terraform provider, Google credentials, and the GCP project.

```
terraform {
  required_providers {
    google = {
      source = "hashicorp/google"
      version = "5.15.0"
    }
  }
}

provider "google" {
  credentials = file("{this is option but your can create GCP service account with BigQuery.job.create role}")
  project = "{your gcp project id}"
}
```

### Create Service Account

For the above credentials, below are a few steps to create a service account with the minimal role to perform the GCP operation.

- Go to the GCP Roles page and create a new custom role with `bigquery.job.create` permission.
- Go to the IAM Service Account page, create a new service account, and then assign the custom role you created.
- Once the service account is created, go to the KEYS tab and click `ADD KEY` to create a new JSON key. Save it locally so the above credentials can reference it.

### Create `backend.tf` File

Here, we're using a GCP Cloud Storage bucket to store the Terraform state file. This is similar to sharing the code within the team so that the same infrastructure is deployed no matter who runs it.

```
terraform {
 backend "gcs" {
   bucket  = "{create a GCP cloud bucket}"
   prefix = "test"
 }
}
```

### Create `schedule-job.tf`

Finally, here is the main code. We're writing a simple query and deploying it to a GCP BigQuery Schedule Job that runs every Sunday.

```
resource "google_bigquery_data_transfer_config" "job" {
    display_name = "test-query"
    project = "{your gcp project}"
    location = "us"
    data_source_id = "scheduled_query"
    schedule = "first sunday of quarter 00:00"
    params = {
      query = "select 1 as ID;"
    }
}
```

## Start Deployment

First, go to the folder containing the above Terraform files and run this command to initialize Terraform and download state files from the GCP Cloud Storage bucket:

```
terraform init
```

This command will create a new workspace called `test`. If it does not exist, the state will be saved to the cloud bucket:

```
terraform workspace new test
```

Run the plan command to check what resources will be created in GCP:

```
terraform plan
```

If you have confirmed the resources, run this command to deploy the changes to GCP:

```
terraform apply
```

## Conclusion

Using Terraform is a great way to deploy infrastructure resources to GCP for production environments. It provides reproducible, version-controlled infrastructure management.


This article demonstrates how to use Terraform to deploy a GCP Schedule Job.

How to Create GCP Schedule Job with Terraform


## Table of Contents

1. [Introduction](#introduction)
2. [Sample Terraform Code](#sample-terraform-code)
3. [Run the Sample Terraform](#run-the-sample-terraform)
4. [Use Variables](#use-variables)
5. [Different Values for Each Environment](#different-values-for-each-environment)
6. [Conclusion](#conclusion)

## Introduction

This blog shows how to use Terraform variables to move environment-specific values out of the code, so you can run the same Terraform configuration for different environments and inject the appropriate variables for each one.

## Sample Terraform Code

This blog uses the following Terraform code to get started.

**_provider.tf_**

```
terraform {
  required_providers {
    google = {
      source = "hashicorp/google"
      version = "5.15.0"
    }
  }
}

provider "google" {
  # Configuration options
  credentials = file("your google credential file path")
  project = "{gcp project id}"
}
```

**_backend.tf_**

```
terraform {
 backend "gcs" {
   bucket  = "{gcp bucket for terraform}"
   prefix = "test"
 }
}
```

**_schedule-job.tf_**

```
resource "google_bigquery_data_transfer_config" "job" {
    display_name = "my_test_sj"
    project = "{your project id}"
    location = "us"
    data_source_id = "scheduled_query"
    schedule = "first sunday of quarter 00:00"
    params = {
      query = "call spTest();"
    }
}
```

## Run the Sample Terraform

Running the above sample Terraform should create a scheduled job.

## Use Variables

Now, let's say we want to move the **_display_name_** to a variable, because we want to reuse the same name later.

Let's create a new **_variable.tf_**. If you run `terraform plan`, you can see the display name as **_my_test_sj_**.

### Variable with Default

```
variable "bq_name" {
  type = string
  nullable = false
  default = "my_test_sj"
}
```

### Without Default Variable

Let's say we want to enter the name every time we run Terraform. We can remove the default so that every time we run **_terraform plan_**, it will ask for input for the variable. The example here might not be ideal, but there may be some areas where this approach is useful.

```
variable "bq_name" {
  type = string
  nullable = false
}
```

## Different Values for Each Environment

Assume we have 2 different workspaces: **_dev_** and **_qa_**.

For this case, we define the variables as below.

### variable.tf

```
variable "bq_name" {
  type = string
  nullable = false
}
```

### dev.tfvars

Create a **_dev.tfvars_** file and add the following key-value pairs.

```
name = "dev_job"
```

### qa.tfvars

Create a **_qa.tfvars_** file and add the following key-value pairs.

```
name = "qa_job"
```

At this point, nothing needs to be modified in the schedule job Terraform file. We'll run **_terraform plan_** with the option **_--var-file=dev.tfvars_**. This will run Terraform with the key/value environment variables defined in **_dev.tfvars_**.

```
terraform plan --var-file=dev.tfvars
```

## Conclusion

Using Terraform **_workspaces_** with **_var-file_** allows you to write Terraform configurations for different environments, keeping environment-specific values separate from your infrastructure code.


This blog shows how to use Terraform variables to move environment variables out of the code, so you can run Terraform for different environments and inject the variables for each one.

How to Run Terraform with Input Variables


#### Table of Contents

1. The problem
2. Conclusion

### The problem

Recently, have to resolve below sample data to find ranking per each type, thought of share this, it might have other ways of achieve this but below is what I come out with for how to resolve this problem in SQL.

Below is the sample data to describe this problem.
![result with json array](/images/big-query-group-similar-values/img1.png)

So for instance, for the **_type 3_**, the first should be **_300_** because total is **_50_**, and the rest of the order should be below, also exclude the null.

- 300 -> 50
- 7777 -> 45
- 500 -> 40
- 33333 -> 30
- 200 -> 11
- 100 -> 5

Same as for the **_type 9_**, the order should be below, also ignore the null as well.

- 3000 -> 514
- 77777 -> 458
- 5000 -> 407
- 3000 -> 304
- 333333 -> 304
- 1000 -> 50
- 2000 -> 11

Below is the BQ Script actual solve this problem, ideally just find the duplicate count for either of the column, value_a or value_b, then group data set for value_a and value_b and join the data, finally remove the target duplicate value_a or value_b

```SQL
with sample_data as (
select 1 id, 3 as type, 100 value_a, null value_b, 5 total
union all
select 1 id, 3 as type, 200 value_a, null value_b, 11 total
union all
select 1 id, 3 as type,300 value_a, null value_b, 30 total
union all
select 1 id, 3 as type,300 value_a, 444 value_b, 20 total
union all
select 1 id, 3 as type,null value_a, null value_b, 20 total
union all
select 1 id, 3 as type,null value_a, 33333 value_b, 30 total
union all
select 1 id, 3 as type,500 value_a, null value_b, 40 total
union all
select 1 id, 3 as type,600 value_a, 7777 value_b, 5 total
union all
select 1 id, 3 as type,null value_a, 7777 value_b, 40 total
union all
select 1 id, 9 as type, 1000 value_a, null value_b, 50 total
union all
select 1 id, 9 as type, 2000 value_a, null value_b, 11 total
union all
select 1 id, 9 as type,3000 value_a, null value_b, 307 total
union all
select 1 id, 9 as type,3000 value_a, 4444 value_b, 207 total
union all
select 1 id, 9 as type,null value_a, 333333 value_b, 304 total
union all
select 1 id, 9 as type,5000 value_a, null value_b, 407 total
union all
select 1 id, 9 as type,6000 value_a, 77777 value_b, 54 total
union all
select 1 id, 9 as type,null value_a, 77777 value_b, 404 total
union all
select 2 id, 3 as type,10 value_a, null value_b, 5 total
union all
select 2 id, 3 as type,20 value_a, null value_b, 10 total
union all
select 2 id, 9 as type,null value_a, null value_b, 20 total
)
, agg_value_a_duplicate as (
  select id, type, value_a, count(value_a) count
  from sample_data
  where value_a is not null
  group by id, type, value_a
  having count > 1
)
, agg_value_b_duplicate as (
  select id, type, value_b, count(value_b) count
  from sample_data
  where value_b is not null
  group by id, type, value_b
  having count > 1
)
, remove_value_b as (
  select sample_data.value_b from sample_data
  where sample_data.value_a in (
    select value_a from agg_value_a_duplicate
  )
  and sample_data.value_b is not null
)
, remove_value_a as (
  select sample_data.value_a from sample_data
  where sample_data.value_b in (
    select value_b from agg_value_b_duplicate
  )
  and sample_data.value_a is not null
)
, agg_value_a as (
  select id, type, value_a, sum(total) total
  from sample_data
  where sample_data.value_a is not null
  group by id, type, value_a
)
, agg_value_b as (
  select id, type, value_b, sum(total) total
  from sample_data
  where sample_data.value_b is not null
  group by id, type, value_b
)
, join_value_a_value_b as (
  select id, type, value_a, null as value_b, total from agg_value_a
  union all
  select id, type, null as value_a, value_b, total from agg_value_b
)
select *
from join_value_a_value_b
where (value_b is null or value_b not in (select value_b from remove_value_b))
and (value_a is null or value_a not in (select value_a from remove_value_a))
order by id, type, total desc
```

#### Conclusion

Above script does resolve the issue, but wonder if there's other way?


Use Big Query to aggregate data to find ranking


Here provide simple SQL Query to compare between `page_view`, `session_start` and `scroll` event.
Also, compare the `page_view` with other 2 to see how much difference between each days.

```SQL
with page_view as (
  SELECT event_date, count(1) as page_view_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'page_view'
  group by 1
),
session_start as (
  SELECT event_date, count(1) as session_start_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'session_start'
  group by 1
),
scroll as (
  SELECT event_date, count(1) as scroll_count
  FROM `ga4-table.events_intraday_*`
  where _TABLE_SUFFIX between "20240201" and "20240228"
  and event_name = 'scroll'
  group by 1
)
select
page_view.event_date
, page_view.page_view_count
, session_start.session_start_count
, scroll.scroll_count
, safe_subtract(page_view.page_view_count, session_start.session_start_count) page_minus_session
, safe_subtract(page_view.page_view_count, scroll.scroll_count) page_minus_scroll
, round(safe_divide(page_view.page_view_count - session_start.session_start_count, session_start.session_start_count) * 100, 2) page_vs_session_percent
, round(safe_divide(page_view.page_view_count - scroll.scroll_count, scroll.scroll_count) * 100, 2) page_vs_scroll_percent
from page_view
inner join session_start on page_view.event_date = session_start.event_date
inner join scroll on scroll.event_date = page_view.event_date
order by event_date
```


Compare Similar DataSet in BigQuery


#### Table of Contents

1. Sample JSON
2. Parse the single fields
3. Include the JSON Array into the SQL
4. Display Scalar value from JSON
5. Keep the filter fields at each row
6. Conclusion

You can interact with JSON data with BigQuery, this blog show show to parse the JSON value and load fields with BigQuery.

### Sample JSON

This will be the JSON file using at the blog.

```JSON
{
   "id":"1",
   "first_name":"John",
   "last_name":"Doe",
   "dob":"1968-01-22",
   "addresses":[
      {
         "status":"current",
         "address":"123 First Avenue",
         "city":"Seattle",
         "state":"WA",
         "zip":"11111",
         "numberOfYears":"1"
      },
      {
         "status":"previous",
         "address":"456 Main Street",
         "city":"Portland",
         "state":"OR",
         "zip":"22222",
         "numberOfYears":"5"
      }
   ]
}
```

### Parse the single fields

At very first, assume has the variable contains the JSON string.

```SQL
declare jsonstring default '{"id":"1","first_name":"John","last_name":"Doe","dob":"1968-01-22","addresses":[{"status":"current","address":"123 First Avenue","city":"Seattle","state":"WA","zip":"11111","numberOfYears":"1"},{"status":"previous","address":"456 Main Street","city":"Portland","state":"OR","zip":"22222","numberOfYears":"5"}]}';
```

To load just single fields from the JSON, we can do something as below, with **_$_** sign.

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
```

```
id | first_name | last_name
1 | John | Doe
```

### Include the JSON Array into the SQL

For the above SQL, if we try this with array, you can see the result has the whole JSON file.

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
json_extract_array(jsonstring, '$.addresses') as addresses
```

![result with json array](/images/big-query-access-json-array/img1.png)

### Display Scalar value from JSON

above script is able to show the JSON, but what if we only need the **_city_**?

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
array_agg(y)
from
  unnest(
  array(
      select
        json_extract_scalar(x, '$.city')
      from unnest(json_extract_array(jsonstring, "$.addresses")) x
    )
  ) y
```

![result with json and city field](/images/big-query-access-json-array/img2.png)

### Keep the filter fields at each row

move of the case, above result meet the result, but what if we want to show id, first_name and last_name at each row?

```SQL
select
JSON_VALUE(jsonstring, '$.id') as id,
JSON_VALUE(jsonstring, '$.first_name') as first_name,
JSON_VALUE(jsonstring, '$.last_name') as last_name,
y
from
  unnest(
    array(
    select
    json_extract_scalar(x, '$.city')
    from unnest(json_extract_array(jsonstring, "$.addresses")) x
    )
  ) y
```

![result with json and city field at each row](/images/big-query-access-json-array/img3.png)

#### Conclusion

Above are list of example how to use interact with JSON and Big Query.


How to use BigQuery to Extract JSON Array


This is not the real scenario I was facing, but let's see you want to get all `event_timestamp` from a event called `page_view` for a give day in one column, and the rest of the column in the same row you just keep any `page_view` the fields.

Below is how you can achieve that at GA4 table.

``` SQL
with test as (
  select * 
  from (
    SELECT 
    *
    , row_number() over (partition by event_name) as rn
    FROM `GA4.events_intraday_20230611`
    where event_name = 'page_view'
  )
  where rn = 1
)
, test_2 as (
  SELECT 
  string_agg(safe_cast(event_timestamp as string), '_')
  , event_name
  FROM `GA4.events_intraday_20230611`
  group by event_name
  having event_name = 'page_view'
)
, test_3 as (
  select test_2.*, test.*
  from test_2
  left join test on test_2.event_name = test.event_name
)

```


How to Use Big Query to Get Single Columns Values from All Rows


#### Table of Contents

1. BQ SQL - create procedure without parameter
2. BQ SQL - create procedure with input and output parameter
3. Next, how to make Procedure return Array Type data?

### BQ SQL - create procedure without parameter

Create Procedure has no input or output parameter

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_without_parameter`()

begin
select 'test';
end;
```

Run the no input, output procedure.

```SQL
CALL `Test.sp_without_parameter`();
```

### BQ SQL - create procedure with input and output parameter

Below script will do a simple calculation with **_x_** plus **_y_** and return the **_total_**.

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_with_input_output`
(
 x int64,
 y int64,
 OUT total int64
)

begin
set total = x + y;
end;
```

This's how we can call the procedure to get the result.

```SQL
declare total int64;
CALL `Test.sp_with_input_output`(10, 20, total);
select total;
```

### Next, how to make Procedure return Array Type data?

In order for BQ Sql to return array like type, we can use **_Array_** with **_Struct_**.

First, let's create a procedure return the array custom type, in below example we want to
return list of items contains id, country, device and browser fields.

As you can see the return type is **_OUT items ARRAY<STRUCT<id int64, country STRING, device STRING, browser STRING>>_**

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_with_output_record`(OUT items ARRAY<STRUCT<id int64, country STRING, device STRING, browser STRING>>)
begin
create TEMP TABLE my_temp_table
(
  id int64,
  country string,
  device string,
  browser string
);

insert into my_temp_table
select 1 as id, 'US' as country, 'mobile' as device, 'safari' as browser union all
select 1 as id, 'CA' as country, 'desktop' as device, 'chrome' as browser
;

set items =
array(select struct(id, country, device, browser) from my_temp_table);

drop table my_temp_table;
end;

```

Next, we'll calling the stored procedure to get the list of country, device and browser.

```SQL
DECLARE items ARRAY<struct<id int64, country STRING, device STRING, browser STRING>> DEFAULT NULL;
CALL `Test.sp_with_output_record`(items);
```

Last, we want to join the **_items_** with another table to get the result.

```SQL
select * from unnest(items) as i
inner join `Test.TestLookup` as t
on i.id = t.id
```

The **_TestLookup_** table is a simple lookup table contains below values.

```
id | name
1 | my name
```

Above is how you can call BQ Stored Procedure to get custom Array type, this is useful so you can
move long procedure into small ones for its own responsibility.


How to Use Big Query SQL Procedure


#### Table of Contents

1. Create a Stored Procedure with Input Parameter
2. Execute the Procedure
3. Create Procedure with Multi-statement
4. Run the Procedure has multi-statement

### Create a Stored Procedure with Input Parameter

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_do_something`(the_id STRING)
BEGIN

select * from `Test.MyTestTable` where id = the_id;

END;
```

### Execute the Procedure

```SQL
DECLARE the_id STRING DEFAULT '2023-06-24 06:26:03.927610+00';
CALL `Test.sp_do_something`(the_id);
```

### Create Procedure with Multi-statement

```SQL
CREATE OR REPLACE PROCEDURE `Test.sp_do_something`(the_id STRING)
BEGIN

create TEMP TABLE my_temp_table
(
  country string,
    device string,
    browser string
);

INSERT INTO
my_temp_table (country,
    device,
    browser)
SELECT
  "US" AS country,
  "mobile" AS device,
  "safari" AS browser;

INSERT INTO
  `Test.MyTestTable` (id,items)
SELECT
  the_id,
  ARRAY[ STRUCT('country' AS KEY,
    my_temp_table.country AS value),
  STRUCT('browser' AS KEY,
    my_temp_table.browser AS value),
  STRUCT('device' AS KEY,
    my_temp_table.device AS value) ]
FROM
  my_temp_table;

select * from `Test.MyTestTable` where id = the_id;

END;
```

### Run the Procedure has multi-statement

Now, notice the id has new record as **_test_100_**.

```SQL
DECLARE the_id STRING DEFAULT 'test_100';
CALL `Test.sp_do_something`(the_id);
```

what if you run below SQL Script, it actually complain that table already exist.

```SQL
CALL `Test.sp_do_something`('2000');
CALL `Test.sp_do_something`('400');
```

One of the way to fix is drop the temp table at the end of the Procedure, this should fix the previous issue.

```
drop TABLE my_temp_table;
```


How to Create Stored Procedure with Big Query SQL


## Table of Contents

- [Introduction](#introduction)
- [Create Service Account](#create-service-account)
- [Load Service Account](#load-service-account)
- [Run Node.js Locally](#run-nodejs-locally)
- [Test The Node.js with BigQuery Access](#test-the-nodejs-with-bigquery-access)
- [How to Debug via Visual Studio Code](#how-to-debug-via-visual-studio-code)
- [Conclusion](#conclusion)

## Introduction

This blog shows how to test GCP Cloud Function locally with Node.js. We will
set up a service account, load it in our Node.js code, run the function
locally, and debug it using Visual Studio Code.

## Create Service Account

Create a GCP service account and add the corresponding role. Create a new key
and save the file (JSON or P12) locally.

## Load Service Account

Next, load the file in the Node.js file.

```Javascript
const options = {
    keyFileName: 'location_of_the_file.json',
    projectId: 'your_gcp_project_id'
};

const bigquery = new BigQuery(options);

const dataset = bigquery.dataset('my_dataset');

const table = dataset.table('my_table');
```

## Run Node.js Locally

Add the code below to the `package.json`'s script section. When you run this
with the command `npm run test`, the function will start locally. I have
tested this on macOS with Node version 18; anything below might not work.

```CMD
 "test": "npx functions-framework --target=main [--signature-type=http]",
```

## Test The Node.js with BigQuery Access

Running the command below should start an HTTP server locally. Now you can
verify, debug the code, and make sure everything works before running the
deploy.

```
npm run test
```

## How to Debug via Visual Studio Code

Visual Studio Code is a great tool for software development and has improved a
lot since the beginning. One of the features for debugging Node.js in Visual
Studio Code is that you can switch to the `JavaScript Debug Terminal` and run
`npm run test`. This will use the built-in debugger attached to the process.

So it is pretty simple to debug Node.js with Visual Studio Code.

## Conclusion

Testing GCP Cloud Functions locally before deploying saves time and makes
debugging much easier. By setting up a service account and using the functions
framework, you can run and test your functions on your local machine. Combined
with the Visual Studio Code debugger, you have a smooth local development
workflow.


This blog shows how to test GCP Cloud Function locally with Node.js.

How to Test GCP Function Locally with Node.js


#### Table of Contents

1. Create Sample Data
2. Find list of sequence number with join with **_string_agg_**
3. Find the last number as max
4. Final Script

Assume you have these type of data sets, [1,2,3,4,5], [1,2,3,4,5,6,7], [1,2,3,4,5,6,7,8] and all with key as **_xxx_**, you want to find each array's max value from SQL.

### Create Sample Data

First let's create the sample data set, for key **_xxx_**, we want to get the 5,8,9.

```SQL
with base_data as
(
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '9' as id, 'xxx' as key union all
select '1' as id, 'yyy' as key union all
select '2' as id, 'yyy' as key union all
select '3' as id, 'yyy' as key union all
select '4' as id, 'yyy' as key union all
select '5' as id, 'yyy' as key union all
select '6' as id, 'yyy' as key union all
select '1' as id, 'zzz' as key
)
```

### Find list of sequence number with join with **_string_agg_**

This will return sequence number as string.

```SQL
, data_agg_find_sequence_no as (
select
(split(
string_agg(id, '_')
, '1_'
)) as pre_id
, key
from base_data
group by key
```

### Find the last number as max

Here, substr the last 2 character as max number of that sequence.

```SQL
, data_get_max_no_from_sequence as (
select
replace(
substr(x, -2)
, '_'
, '') as id
, key
from data_agg_find_sequence_no, unnest(pre_id) as x
```

### Final Script

This's the full script, should be able to see aggregated key with max number from the sequence number.

```SQL
with base_data as
(
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '1' as id, 'xxx' as key union all
select '2' as id, 'xxx' as key union all
select '3' as id, 'xxx' as key union all
select '4' as id, 'xxx' as key union all
select '5' as id, 'xxx' as key union all
select '6' as id, 'xxx' as key union all
select '7' as id, 'xxx' as key union all
select '8' as id, 'xxx' as key union all
select '9' as id, 'xxx' as key union all
select '1' as id, 'yyy' as key union all
select '2' as id, 'yyy' as key union all
select '3' as id, 'yyy' as key union all
select '4' as id, 'yyy' as key union all
select '5' as id, 'yyy' as key union all
select '6' as id, 'yyy' as key union all
select '1' as id, 'zzz' as key
)
, data_agg_find_sequence_no as (
select
(split(
string_agg(id, '_')
, '1_'
)) as pre_id
, key
from base_data
group by key
)
, data_get_max_no_from_sequence as (
select
replace(
substr(x, -2)
, '_'
, '') as id
, key
from data_agg_find_sequence_no, unnest(pre_id) as x
)
select * from data_get_max_no_from_sequence
where id != '';
```


This blog show how to use Big Query to find max number from a sequence number for a data set.

How to Use Big Query to Find Max Sequence Number


#### Table of Contents

1. Insert a data into Items Field
2. Query all keys from the dataset
3. Select Insert into the Record Type
4. Find all the unique Key from the table
5. Convert the **_record_** to JSON by using **_to_json_string()_**

Create a Table so we can insert the **_Record_** Repeat type, or query the data.
The blow sample test table, we created a field name as **_items_**, which is record type with key is key and value both are string.

```SQL
CREATE TABLE `Test.MyTestTable`
(
  id STRING,
  items ARRAY<STRUCT<key STRING, value STRING>>
);

```

### Insert a data into Items Field

This SQL script will insert key/value pair data into the items **_Record_** repeated field.

```SQL
insert into `Test.MyTestTable` (id, items)
select safe_cast(current_timestamp() as string),
array[
  struct('country' as key, "UK" as value),
  struct('browser' as key, "chrome" as value),
  struct('device' as key, "desktop" as value)
]
```

### Query all keys from the dataset

This will return all keys from the table

```SQL
SELECT x.key FROM `Test.MyTestTable`, unnest(items) as x;
```

### Select Insert into the Record Type

Below example is join the **\*my_temp_table** table and as select insert into the **_record_** field.

```SQL
  create TEMP TABLE my_temp_table ( country string,
    device string,
    browser string );
INSERT INTO
  my_temp_table (country,
    device,
    browser)
SELECT
  "US" AS country,
  "mobile" AS device,
  "safari" AS browser;
INSERT INTO
  `Test.MyTestTable` (id,
    items)
SELECT
  SAFE_CAST(CURRENT_TIMESTAMP() AS string),
  ARRAY[ STRUCT('country' AS KEY,
    my_temp_table.country AS value),
  STRUCT('browser' AS KEY,
    my_temp_table.browser AS value),
  STRUCT('device' AS KEY,
    my_temp_table.device AS value) ]
FROM
  my_temp_table
```

### Find all the unique Key from the table

This query is to find unique key from the **_record_** type.

```SQL
SELECT distinct(x.key), count(*) FROM `Test.MyTestTable`, unnest(items) as x
group by 1
order by 2 desc
;
```

### Convert the **_record_** to JSON by using **_to_json_string()_**

```SQL
select id, to_json_string(items) from `Test.MyTestTable`
```


This blog demonstrate how to use GCP Big Query record type.

How to Use GCP Big Query Record Repeat Type


## Table of Contents

1. [Introduction](#introduction)
2. [Requirements](#requirements)
3. [Use Correct GCP Project](#use-correct-gcp-project)
4. [Create Alerting Policy for Pub/Sub Topic](#create-alerting-policy-for-pubsub-topic)
5. [Create Alerting Policy for Failed Scheduled Queries](#create-alerting-policy-for-failed-scheduled-queries)
6. [Conclusion](#conclusion)

## Introduction

This blog shows how to create monitoring alerts with GCP and provides a few examples.

## Requirements

- Have a GCP account
- Already downloaded and set up gcloud

## Use Correct GCP Project

Run the `gcloud config list` command to check whether the current account or project is the correct one for your GCP project.

```
gcloud  config list
```

In case the GCP project is not the correct one, run `gcloud init` and follow the steps to set the correct GCP project.

```
gcloud init
```

## Create Alerting Policy for Pub/Sub Topic

The task here is: for some reason we want to know whenever a Pub/Sub topic gets published, and we want to receive an email as well.

We'll create a GCP monitoring policy for a specific Pub/Sub topic by using a PowerShell command.
The parameters `$topic` and `$project` can be replaced with your own project settings. The idea here is to monitor a specific Pub/Sub topic, and if any message has been published, then the `--if=>0` condition kicks in and will send a notification.

```PowerShell
$filter = 'resource.type = \"pubsub_topic\" AND (resource.labels.topic_id = \"{topic}\" AND resource.labels.project_id = \"{project}\") AND metric.type = \"pubsub.googleapis.com/topic/send_request_count\"';
$aggregation = '{"alignmentPeriod": "300s","perSeriesAligner": "ALIGN_MEAN"}';
$filter = $filter -replace "{topic}", $topic
$filter = $filter -replace "{project}", $project
$conditionName = "Condition - " + $topic;
$displayName = "Display Name - " + $topic;
$document = "Failed!" + $topic;
$channel = 123456; // replace with your notification channel id.

gcloud alpha monitoring policies create --if='> 0' --condition-filter=$filter --aggregation=$aggregation --duration='0s' --condition-display-name=$conditionName --notification-channels=$channel --combiner='OR' --enabled --trigger-count=1 --display-name=$displayName --documentation=$document
```

## Create Alerting Policy for Failed Scheduled Queries

Here, we want to send a notification when scheduled queries fail.
First is the filter — here we use the metric to watch the scheduled query and check if the state is FAILED.

```PowerShell
$filter = 'resource.type = \"bigquery_dts_config\" AND metric.type = \"bigquerydatatransfer.googleapis.com/transfer_config/completed_runs\" AND metric.labels.completion_state = \"FAILED\"';
$aggregation = '{"alignmentPeriod": "300s","perSeriesAligner": "ALIGN_MEAN"}';
$conditionName = "Condition Name";
$displayName = "Display Name";
$document = "Failed!";
$channel = 123456; // replace with your notification channel id.

gcloud alpha monitoring policies create --if='> 0' --condition-filter=$filter --aggregation=$aggregation --duration='0s' --condition-display-name=$conditionName --notification-channels=$channel --combiner='OR' --enabled --trigger-count=1 --display-name=$displayName --documentation=$document
```

## Conclusion

GCP Monitoring alerts are a powerful way to stay informed about your cloud infrastructure. By creating alerting policies for Pub/Sub topics and BigQuery scheduled queries, you can quickly detect and respond to issues in your GCP environment.


This blog shows how to create monitoring alerts with GCP and provides a few examples.

How to create GCP Monitoring Alert


#### Table of Contents

1. Create BQ Table for test
2. PubSub Topic
3. Create a Dataflow
4. Manually Publish PubSub Message
5. Send Pub/Sub Message from C#
6. Authenticating as a service account
7. Conclusion

GCP Pub/Sub is managed messaging service provide by GCP, this blog demo how to
create Pub/Sub and use dataflow to subscribe data into BigQuery.

## Create BQ Table for test

First, need to create a BQ table, so we can store our message from Pub/Sub
we send, below is the JSON and BQ Table assume we want to store the
information.

`{&ldquo;id&rdquo;: 100, &ldquo;name&rdquo;:&rdquo;Smith James",
url:&rdquo;https://testsmithjames.com&rdquo;,&rdquo;age&rdquo;:30,&rdquo;price&rdquo;:300,&rdquo;desc&rdquo;:&rdquo;I
like sell my arts.&rdquo;}

## PubSub Topic

Here, we try to create a topic via gcloud command line,

To create a PubSub Topic

gcloud pubsub topics create test_topic_myname

In case, need to delete the topic, below command is how to delete a PubSub
Topic

gcloud pubsub topics delete test_topic_myname

## Create a Dataflow

Now, the following command example will create a dataflow job by using the
template PubSub to BigQuery and will insert the new record to the Big Query
table defined.

This command also expects the cloud bucket already exists.

```
gcloud dataflow jobs run ps-to-bq-test-topic-myname --gcs-location gs://dataflow-templates-us-central1/latest/PubSub_to_BigQuery --region us-central1 --staging-location gs://test-pubsub-name-topic/temp --parameters inputTopic=projects/test-dataflow-sql/topics/test_topic_myname,outputTableSpec=test-dataflow-sql:dataflow_sql_tutorial.test_pubsub_to_bq
```

## Manually Publish PubSub Message

Let's test the PubSub Dataflow to BigQuery, go to the GCP Pub/Sub page, and
paste the below Json, once click the publish button should see the new
record in BigQuery table.

```
{
  "id": 100,
  "name": "smith james",
  "url": "https://testsmithjames.com",
  "age": 30,
  "price": 300,
  "desc": "I like sell my arts"
}
```

## Send Pub/Sub Message from C#

Following is a sample code by using C# to send a message to the projecId and
topicId.

```
TopicName topicName = TopicName.FromProjectTopic(ProjectId, TopicId);

PublisherClient publisher = await PublisherClient.CreateAsync(topicName);

SamplePubSubModel sampleModel = new()
{
                id = 304,
                name = "my name",
                url = "https://test-url",
                age = 40,
                price = 300,
                desc = "some desc"
};

await publisher.PublishAsync(JsonConvert.SerializeObject(sampleModel));
```

Above C# code require the Google.Cloud.PubSub.V1, but also need to
authenticate then the code will successfully run.

## Authenticating as a service account

Now, just run the C# code above, and you'll see the exception which requires
providing the google credentials. There're a few ways to achieve this, what
we're doing here is trying to create a service account that has permission to
pub/sub message.

Once create a service account, make sure has the Role
Pub/Sub Publisher`.

Create a token and save the credential JSON file to the local, if you're using
windows, run the below command. Now, after following the below command, might
still see the credential error but, it might be fixed after restarting the
windows.

`set GOOGLE_APPLICATION_CREDENTIALS=key_path`

## Conclusion

Should be very easy to set up all processes, but might take some time on how
the google application credentials especially if you're using windows.


GCP Pub/Sub is managed messaging service provide by GCP, this blog demo how to create Pub/Sub and use dataflow to subscribe datainto BigQuery.

PubSub to BigQuery via Dataflow with GCP


## Table of Contents

1. [Introduction](#introduction)
2. [Install GCP SDK](#install-gcp-sdk)
3. [Install SDK on macOS](#install-sdk-on-macos)
4. [gcloud init](#gcloud-init)
5. [Confirm Current GCP Setting](#confirm-current-gcp-setting)
6. [Conclusion](#conclusion)

## Introduction

This blog shows a quick memo on how to get started with the GCP Cloud CLI.

## Install GCP SDK

https://cloud.google.com/sdk/docs/install-sdk

Also, reference here for the release notes:
https://cloud.google.com/sdk/docs/release-notes

## Install SDK on macOS

For macOS, double-click the **install.sh** to install it on
macOS.

## gcloud init

Run `gcloud init` to configure the GCP Cloud CLI. This will
start authentication with Google. If you have multiple different GCP
accounts, you can also use `gcloud init` to switch to a
different account.

## Confirm Current GCP Setting

Run `gcloud config list` to check the current GCP account and
current GCP project.

Run `gcloud projects list` to view all the projects in the current
GCP account.

Use `gcloud config set project {project id}` to set the GCP project
ID.

## Conclusion

With the GCP Cloud CLI installed and configured, you can manage your Google Cloud resources directly from the command line. Use `gcloud init` to switch between accounts and `gcloud config set project` to switch between projects.


This blog shows a quick memo on how to get started with the GCP Cloud CLI.

Quick Start to Install Google Cloud CLI


## Table of Contents

1. [Introduction](#introduction)
2. [Go to Cloud Resource Manager](#go-to-cloud-resource-manager)
3. [Create Another GCP Account to Get $300 Credit](#create-another-gcp-account-to-get-300-credit)
4. [Conclusion](#conclusion)

## Introduction

This is a quick note on how to delete unused GCP projects to save money.

## Go to Cloud Resource Manager

Click this link to go to the cloud resource manager page, and choose the
setting to shut down the projects to avoid billing charges.

## Create Another GCP Account to Get $300 Credit

If you just want to test GCP for your project without accidentally paying a
lot, you can use a new email to register a new GCP account to get $300 credit
in 90 days.

## Conclusion

Deleting unused GCP projects is a simple but effective way to avoid
unnecessary billing. Make sure to review your projects regularly and shut down
any that are no longer needed.


This is a quick note on how to delete unused GCP projects to save money.