AAtsushi's Blog
Certifications

GCP PDE Mock Exam Notes

→ 日本語版を読む
  • Overview
    • AutoML Vision
    • Apache Hadoop
    • BigTable
      • Storing CPU and memory usage of millions of computers as time series
      • When to increase BigTable cluster size
      • When there is a performance bottleneck
      • Changing storage after creating a Bigtable instance
      • Row key best practices
      • Isolating batch analytics workloads from other applications
    • BigQuery
      • Backup
      • Performance
      • BigQuery Legacy SQL
        • TABLE_DATE_RANGE function
      • Denormalization
      • Region/Multi-region
      • Notes on CSV import
      • Updating large-scale records without exceeding quotas
      • Metrics
      • Prioritization with BigQuery Reservations
    • Cloud Pub/Sub
    • Cloud Spanner
      • Secondary indexes
    • Cloud Storage
    • Cloud SQL
      • HA configuration
    • Compute Engine instances
    • DataProc
    • Dialogflow
    • Datastore
    • Data Loss Prevention API (DLP)
    • Dataflow
      • Apache Beam
      • Monitoring
      • Windowing
      • Window types
      • Updating pipelines
      • Deduplication with GUID
      • Deduplication with InsertID
      • Exception handling
      • Code examples
      • Handling malformed or corrupt rows
    • Kafka
    • Logging
    • Monitoring
    • Natural Language API
    • Video Intelligence API
    • Machine Learning
    • Other DBs
    • Other Tools
      • Pig / Pig Latin
    • General DB
      • Self-joins
    • DB Services
    • PDE Workshop
    • CloudSpanner
    • Cloud SQL
    • The term "Storage" in GCP
    • Cloud Spanner Secondary Indexes
    • Row key design in Bigtable
    • BigQuery wildcard tables

Overview

Notes on important points to remember from the Udemy GCP PDE mock exam, organized by service.

AutoML Vision

  • AutoML Vision enables "supervised learning" that recognizes patterns from labeled data.
  • For example, packages handled by a shipping company differ from human faces and other objects, so general pre-trained models cannot be used — supervised learning is required.
  • AutoML Vision training requires at least 100 images per category/label.

Apache Hadoop

MapReduce job best practices

  • MapReduce jobs should be created one per purpose and should not be monolithic (for maintainability and reduced testing effort).

BigTable

Storing CPU and memory usage of millions of computers as time series

  • Create a narrow table in BigTable and set a row key combining the Compute Engine computer identifier and the sample timestamp per second.

When to increase BigTable cluster size

  • Write operation latency is persistently increasing.
  • Storage utilization has exceeded xx% of maximum capacity.

When there is a performance bottleneck

  • The Key Visualizer tool for Bigtable scans each table in a cluster daily and displays usage patterns.
    • You can identify hotspot rows and excessive CPU usage.
  • Best performance is achieved when reads and writes are evenly distributed across the entire table.
    • If reads and writes are concentrated on specific rows, redesign the schema.

Changing storage after creating a Bigtable instance

  • After creating an instance, SSD/HDD storage type cannot be changed.
  • The only option is to export data from the existing instance and import it into a new instance with the desired storage type.

Row key best practices

  • Using "timestamp only" is not recommended.
  • A row key of sensorId + timestamp is recommended.

Isolating batch analytics workloads from other applications

  • Running batch analytics jobs that perform large numbers of bulk reads on the same cluster as applications that perform reads and writes can slow down application processing.
  • Use app profiles to route requests to different clusters.

BigQuery

Backup

  • If corruption is detected within 7 days, query the table at a past point in time using a snapshot decorator to restore the table to its state before corruption.
  • Export data from BigQuery and create a new table containing the exported data (excluding corrupt data).
  • Store data in separate tables for specific time periods. This way, only a portion of the data needs to be restored to a new table rather than the entire dataset.
  • For example, organize data into separate tables by month, export and compress the data, and store it in Cloud Storage. This avoids the need to restore the entire dataset.
  • Create copies of the dataset at specific intervals. These copies can be used when a data corruption event exceeds the period capturable by point-in-time queries (e.g., more than 7 days ago). Dataset copies can also be made from one region to another to ensure data availability in the event of a regional failure.
  • Store the original data in Cloud Storage. This allows you to create a new table and reload uncorrupted data, then update your application to point to the new table.

Performance

  • Query execution after streaming inserts should wait longer than the average latency for data availability after streaming inserts — always wait at least twice the estimated average latency before running a query.
  • Partitioning and clustering can improve query performance when using WHERE clauses.
  • When updating large amounts of data daily, APPEND performs better than BigQuery UPDATE.

BigQuery Legacy SQL

TABLE_DATE_RANGE function

Queries daily tables that overlap with the time range between timestamp1 and timestamp2. Table names must have a date suffix in YYYYMMDD format.

  TABLE_DATE_RANGE([myproject-1234:mydata.table_prefix_],
                    TIMESTAMP('2014-03-25'),
                    TIMESTAMP('2014-03-27'))
  • Standard SQL queries cannot reference views defined in legacy SQL.

Denormalization

Denormalization improves query speed and simplifies queries.

Region/Multi-region

BigQuery uses two types of locations:

  • A region is a specific geographic location, such as London.
  • A multi-region is a large geographic area containing two or more geographic locations, such as the United States.

Notes on CSV import

When loading CSV files into BigQuery, note the following:

  • CSV files do not support nested or repeated data.

  • Remove byte order mark (BOM) characters. They may cause unexpected issues.

  • When using gzip compression, BigQuery cannot read data in parallel. Loading compressed CSV data into BigQuery takes longer than loading uncompressed data.

  • You cannot include both compressed and uncompressed files in the same load job.

  • The maximum size for a gzip file is 4 GB.

  • When loading JSON or CSV data, the date portion of timestamp values in TIMESTAMP columns must use dashes (-) as separators and be in YYYY-MM-DD (year-month-day) format. The time portion hh:mm:ss (hour-minute-second) must use colons (:) as separators.

Updating large-scale records without exceeding quotas

https://cloud.google.com/blog/products/bigquery/performing-large-scale-mutations-in-bigquery

  • Quotas limit the amount of specific shared Google Cloud resources a cloud project can use, including hardware, software, and network components.
  • In this case, UPDATE statements are causing this issue, so data must be imported using alternative methods to avoid hitting this limit.
  • Google Cloud's recommended approach for importing into BigQuery is to use the BigQuery MERGE statement to perform batch updates to an existing table based on the contents of another table (containing new data):
MERGE dataset.Inventory T
USING dataset.NewArrivals S
ON T.ProductID = S.ProductID
WHEN MATCHED THEN
  UPDATE SET quantity = T.quantity + S.quantity -- If the product already exists, add to inventory
WHEN NOT MATCHED THEN
  INSERT (ProductID, quantity) VALUES (ProductID, quantity) -- If the product doesn't exist, insert as a new product

Metrics

  • slots/allocated_for_project
    • Shows the number of BigQuery slots currently allocated to query jobs within the project.

Prioritization with BigQuery Reservations

  • Flat-rate billing (BigQuery Reservations) is particularly suitable for large enterprises with multiple business units having workloads with different priorities and budgets.
  • A fixed number of slots is allocated to your projects, allowing a hierarchical priority model to be established across projects.

→ This likely refers to "reservations."

Cloud Pub/Sub

  • For Cloud Pub/Sub push subscriptions, if a message is not acknowledged within the acknowledgement deadline, Pub/Sub resends the message, resulting in duplicate messages being sent.
    • Expired acknowledgement messages are identified by the expired value in the response_code label of the acknowledgement message operation metric.

Cloud Spanner

Secondary Indexes

Cloud Spanner automatically creates an index for each table's primary key. For example, SingerId, the primary key of the Singers table, is automatically indexed — no action is needed.

Cloud Spanner also allows other columns to be designated as secondary indexes.

In addition to the benefits of lookups, secondary indexes allow Cloud Spanner to perform index scans more efficiently rather than full table scans.

Adding a secondary index to a column enables more efficient searches for data in that column.

For example, if you need to quickly look up a set of SingerIds for a specific range of LastName values, create a secondary index on LastName so Cloud Spanner doesn't need to scan the entire table.

CREATE INDEX SingersByLastName ON Singers(LastName)

https://cloud.google.com/spanner/docs/secondary-indexes

  • Avoiding hotspots
    • An important consideration when choosing a primary key is avoiding hotspots.
    • Monotonically increasing values or epoch timestamps are inappropriate as they can cause hotspots.
    • Best practice is to use random values such as hashed key values or UUIDs.

Cloud Storage

Uploading when there are many small files:

  • Bundle multiple files into a TAR file.
  • Or use gsutil -m to transfer multiple files to GCS in parallel. Note: even if some file downloads fail, gsutil does not track which files were successfully downloaded.

Cloud SQL

HA Configuration

Create a Cloud SQL instance in one zone and create a failover replica in a different zone within the same region. https://cloud.google.com/sql/docs/mysql/high-availability#normal

Compute Engine Instances

https://cloud.google.com/tpu/docs/tpus#when_to_use_tpus

CPU

  • Rapid prototyping requiring maximum flexibility
  • Simple models that don't take long to train
  • Small-scale models with small actual batch sizes
  • Models with many custom TensorFlow operations written in C++
  • Models constrained by the host system's available I/O or network bandwidth

GPU

  • Models without source code or where modifying the source is too cumbersome
  • Models with many custom TensorFlow operations that must run at least partially on CPU
  • Models using TensorFlow operations not available on Cloud TPU (see the list of available TensorFlow operations)
  • Medium to large-scale models with large actual batch sizes

TPU

  • Models dominated by matrix computations
  • Models with no custom TensorFlow operations in the main training loop
  • Models that take weeks or months to train
  • Very large-scale models with very large actual batch sizes

DataProc

  • Saving a MySQL dump file to Cloud Storage allows it to be mounted in Cloud SQL and imported into Dataproc (presumably meaning mounting into Cloud SQL within Dataproc).
  • Storing data in GCS instead of HDFS preserves data even after the cluster is deleted.
  • Using GCS instead of HDFS as persistent disk storage is the most cost-optimal approach.
  • For persistent storage, you can specify either solid-state drives (SSD) or hard disk drives (HDD). SSD storage is the most efficient and cost-effective choice for most use cases. HDD storage may be appropriate for very large datasets (over 10 TB) where latency is not critical or access frequency is low.
  • Initialization actions run at Dataproc startup to add dependencies. If internet access is unavailable due to security policies, copy dependencies to a GitHub repository or Cloud Storage in advance.
  • When using Parquet files in Spark jobs, the recommended file size is 1 GB. Parquet files of 200–400 MB are too small. Aim for a minimum of 1 GB.

Dialogflow

  • Dialogflow enables conversational apps to respond to voice commands and voice conversations.
  • It combines speech recognition and natural language understanding, accessible via a single API call.

Datastore

  • Can execute operations where the result is either "complete success" or "nothing happens."
  • Datastore is best for applications that require highly available access to large amounts of structured data. Datastore can store and query all types of data including:
    • Product catalogs providing real-time inventory and product details for retail stores
    • User profiles providing customized experiences based on user's past behavior and preferences
    • Transactions based on ACID properties, such as transferring funds from one bank account to another.

https://cloud.google.com/datastore/docs/concepts/overview#what_its_good_for

Data Loss Prevention API (DLP)

Use case: Build a Cloud Function that reads a Pub/Sub topic and passes data to the Cloud Data Loss Prevention API. Use DLP tagging and confidence levels to have the Cloud Function determine whether to pass data to a bucket or quarantine it.

Dataflow

Apache Beam

Monitoring

Use case: When aggregating from Cloud Pub/Sub to Cloud Storage via Dataflow, monitor the following metrics:

  • Increase in source Subscription/num_undelivered_messages
  • Decrease in the growth rate of destination used storage

Windowing

  • Dividing a PCollection according to timestamps is called windowing. Transforms that aggregate multiple elements, such as GroupByKey, are implicitly performed on a window basis.
  • Windowing is useful for datasets like streaming data where new elements are continuously added — i.e., Unbounded PCollections.
  • By default in Apache Beam, only one window is assigned to all elements of a PCollection. This is called the global window. Even for streaming data, the global window ignores data that arrives late. For Unbounded PCollections like streaming data, a non-global window function must be configured. If GroupByKey is applied to an Unbounded PCollection without a non-global window function, it throws an IllegalStateException error.
  • For example, applying 5-minute windows (or 4-minute sliding windows with 30-second steps) to all elements of a PCollection — these are non-global windows.

https://beam.apache.org/documentation/programming-guide/#windowing

Window Types

  • Tumbling window (called Fixed window in Apache Beam)
  • Hopping window (called Sliding window in Apache Beam)
  • Session window
    • Session windows are for tracking not just by time but also by KEY. Useful for data with irregular delivery intervals. Session windows allow setting a minimum gap duration; windows arriving after the minimum gap are processed as separate windows.
    • While processing was done per "time" unit, processing can now be done per "KEY" unit. Use cases include aggregating actions based on user behavior.
    • Example 1: Aggregating web page access counts per user.
    • Example 2: Sending a push notification to a user when there has been no interaction on the website for 1 hour.

https://www.case-k.jp/entry/2019/11/08/173450#セッションウィンドウ

Updating Pipelines

Dataflow jobs can be stopped in two ways. To prevent data loss when stopping a streaming pipeline, draining the job is the best approach.

  • Cancel Applicable to both streaming and batch pipelines. Canceling a job causes the Dataflow service to stop processing data including buffered data.

  • Drain Applicable to streaming pipelines only. Draining a job allows the Dataflow service to finish processing data in the buffer while stopping ingestion of new data. https://cloud.google.com/dataflow/docs/guides/stopping-a-pipeline#drain

Deduplication with GUID

  • On the sender side, assign a GUID to each data entry.
  • On the receiver side, discard received entries whose GUID is already known.
  • This enables deduplication even when the same record is sent multiple times.

Deduplication with InsertID

Reference

When an insertId is specified for an inserted row, BigQuery uses this ID to support best-effort deduplication for up to 1 minute. This means if you try to stream the same row with the same insertId to the same table multiple times within that period, BigQuery may deduplicate those occurrences and retain only one.

This system assumes that rows with the same insertId are identical. If two rows have the same insertId, which row BigQuery retains is non-deterministic.

To ensure no duplicate rows remain after streaming, use the following manual process:

  1. Add insertId as a column in the table schema and include insertId values in each row's data.
  2. After streaming stops, run a query to check for duplicates:
#standardSQL
SELECT * EXCEPT(row_number)
FROM (
  SELECT
  *,
  ROW_NUMBER() OVER (PARTITION BY ID_COLUMN) row_number
  FROM `TABLE_NAME`
)
WHERE row_number = 1

SQL Explanation

  • ROW_NUMBER() OVER (PARTITION BY ID_COLUMN) row_number Assigns a sequential number to each row within a group (partition) of records sharing the same InsertId. Reference
  • Extract rows with row number 1 within each partition sharing the same ID_COLUMN value. This removes duplicates.
  • SELECT * EXCEPT(row_number) — Finally, exclude the row_number column from the destination table.

Exception Handling

  • In Dataflow, exceptions can be caught by adding a try...catch block to the DoFn in the pipeline code.

  • When an exception is caught, detailed error analysis is needed, so it is effective to use sideOutput to create a PCollection. After saving to a PCollection, it can also be saved to Pub/Sub, enabling reprocessing of failed data.

  • Failed elements can also be tracked:

    • Log failed elements and check the output using Cloud Logging.
    • Follow the log viewing steps to check for warnings and errors in Dataflow worker logs and worker startup logs.
    • Write failed elements to additional outputs in ParDo for later investigation.

Code Examples

BigQueryIO.read.from() reads an entire table directly from BigQuery. BigQueryIO.read.fromQuery() executes a query and reads the results after execution.

Handling Malformed or Corrupt Rows

Google Cloud's recommended approach is to use a pattern called the dead-letter queue (or dead-letter file).

Catch exceptions in the DoFn.ProcessElement method and log the error as normal.

However, instead of dropping the failed element, use branching output to write the failed element to a separate PCollection object.

These elements are then written to a data sink and can be inspected or processed later using a separate transform.

Kafka

When deploying a Kafka cluster on Google Cloud, using VM instances is the optimal approach.

Logging

  • Log filters can be used to filter only logs related to a specific BigQuery table, and a log routing sink can be configured to Cloud Pub/Sub. This allows notifications to be sent when data is added to a specific table via INSERT. About log routing
  • Enabling audit logs allows security, audit, and compliance entities to monitor Google Cloud data and systems to check for vulnerabilities and potential external data misuse (policy violations).
  • Protecting data access logs:
    • Protect data access logs from personnel holding the Cloud IAM Owner role for the project.
    • Create a Cloud Storage bucket in a newly created project and configure an export sink to it. Restrict access to that project.

Monitoring

  • When deploying a MariaDB SQL database on a VM instance:
    • Cloud Monitoring cannot collect custom metrics such as network connections, disk IDs, and replication status by default.
    • In that case, collecting custom metrics using OpenCensus is recommended.
      • A free open-source project.
      • Provides vendor-neutral support for collecting metrics and trace data in various languages.
      • Collected data can be exported to various backend applications including Cloud Monitoring.

Natural Language API

  • Use entity analysis to attach topic labels to documents.
  • Use sentiment analysis to attach sentiment labels to documents.

Video Intelligence API

  • The LABEL_DETECTION feature can identify entities appearing in video footage and annotate them with labels (tags).
  • This feature can identify objects, locations, activities, animal species, products, and more.
  • Unlike object tracking, label detection annotates the entire frame (without bounding boxes).
  • For example, a video of a train passing a railroad crossing might return labels such as "train," "transportation," and "railroad crossing."
  • Each label has a time segment indicating when the entity was detected, expressed as a time offset (timestamp) from the beginning of the video.

Machine Learning

  • Fashion

    • Building a model to recommend clothing to users on an e-commerce site. Since it is known that users' fashion preferences change over time, build a data pipeline to stream new data into the model as it becomes available.
    • Periodic retraining is required. In such cases, it is generally effective not only to add new data but also to use data that was previously used for training.

  • Subsampling Subsampling is a data processing technique primarily used for imbalanced data that generates a new subset from data of a specific class. This reduces the dataset size for that class and improves learning performance.

Other DBs

For NoSQL databases at the hundreds-of-TB scale:

  • HBase
  • MongoDB
  • Cassandra

HBase

  • A NoSQL database similar to Google's Bigtable.
  • Designed to provide fast random access to massive amounts of structured data.

MongoDB Atlas

  • Provides a fully managed service on Google's globally scalable and reliable infrastructure.
  • With Atlas, databases can be easily managed with just a few UI clicks or API calls.
  • Easy migration, with advanced features like global clusters, enabling low-latency read and write access from anywhere in the world.

Apache Cassandra

  • An open-source distributed NoSQL database that achieves scalability and high availability without compromising performance.
  • Delivers linear scalability and proven fault tolerance on commodity hardware or cloud infrastructure, making it an ideal platform for mission-critical data.

Other Tools

Pig / Pig Latin

Pig is a platform for analyzing large datasets.

Built on Hadoop, it provides ease of programming, optimization opportunities, and extensibility.

Pig Latin is a relational dataflow language and one of Pig's core components.

When building data pipelines, Pig Latin is a better choice than SQL for several reasons:

  • Pig Latin allows pipeline developers to decide where to checkpoint data in the pipeline.

  • Pig Latin allows direct selection of a specific operator implementation without relying on an optimizer.

  • Pig Latin supports pipeline splitting.

  • Pig Latin allows developers to insert their own code almost anywhere in a data pipeline.

General DB

Self-joins

A self-join is a join of a table with itself. While joins are performed on two or more tables, those tables need not be different. A self-join is executed as a join of two identical tables.

DB Services

  • Time series data that is constantly being generated and written → Cloud Bigtable

PDE Workshop

Best practice for Dataproc analytics and data processing is to create separate Dataproc clusters. Create ephemeral clusters so they are deleted when the job ends. Use the GCS connector to connect to GCS.

Bigtable: wide-column NoSQL (wide-column store: a key-value structure where the value portion is itself a set of key-value pairs — column names and values — of arbitrary count). HBase-compatible. Find outliers using the Bigtable cbt tool. Excels at IoT time series data.

Dataproc and BigQuery integration: read/write access is possible using the BigQuery connector.

CloudSpanner: Traffic spikes → CPU resources are depleted rather than storage usage, so monitor CPU utilization. Manual scale: configure in the console. Auto scale: requires coding.

Cloud SQL does not scale horizontally.

In GCP, databases are sometimes referred to as "storage."

In Cloud Spanner, searching by columns other than the primary key defaults to a full scan, which is slow. Creating secondary indexes on columns you want to search enables faster queries. Best practice for Dataproc analytics and data processing is to create separate Dataproc clusters. Create ephemeral clusters so they are deleted when the job ends. Use the GCS connector to connect to GCS.

Bigtable: wide-column NoSQL (wide-column store: a key-value structure where the value portion is itself a set of key-value pairs — column names and values — of arbitrary count). HBase-compatible. Find outliers using the Bigtable cbt tool. Excels at IoT time series data.

Dataproc and BigQuery integration: read/write access is possible using the BigQuery connector.

CloudSpanner

Can scale out rather than scale up. Traffic spikes → CPU resources are depleted rather than storage usage, so monitor CPU utilization.

  • Manual scale-out: configure in the console.
  • Auto scale-out: requires coding.

Cloud SQL

Cloud SQL does not scale horizontally — only vertical scaling (scale-up) is supported. The service stops during scale-up.

The term "Storage" in GCP

In GCP, databases are sometimes referred to as "storage."

Cloud Spanner Secondary Indexes

In Cloud Spanner, searching by columns other than the primary key defaults to a full table scan, which is slow. Creating secondary indexes on columns you want to search enables faster queries.

Row key design in Bigtable

BigQuery Wildcard Tables

Using wildcards in table names (table name prefix + asterisk) allows querying across multiple tables.

<project_name>.<dataset_name>.<table_name_prefix>*

← Back to articles