Data & Analytics

Google Cloud Platform provides a comprehensive suite of data and analytics services to handle everything from real-time stream processing to large-scale data warehousing. This section covers relational databases, NoSQL solutions, data processing, and analytics platforms. This guide provides everything from data storage basics to advanced analytics patterns.

Prerequisites

Before working with data and analytics on GCP, ensure you have:

A GCP project with appropriate permissions
Understanding of data modeling concepts
Basic SQL knowledge for database operations
Familiarity with data processing frameworks
Knowledge of your data storage and processing requirements

BigQuery

Fully managed data warehouse for analytics and machine learning. BigQuery is a fully managed, serverless data warehouse designed for large-scale analytics and machine learning. It provides fast SQL queries, automatic scaling, built-in ML integration, and real-time analytics capabilities. Supports petabytes of data with high performance.

Overview

BigQuery is designed for analyzing large datasets using standard SQL queries. It separates storage from compute, allowing independent scaling of each component. The service is optimized for read-heavy analytical workloads.

Key Features

Serverless Architecture: No infrastructure management required
Standard SQL: Familiar SQL syntax with extensions
Automatic Scaling: Handles query complexity automatically
Machine Learning Integration: Built-in ML with BigQuery ML
Real-time Analytics: Streaming data ingestion and querying
Federated Queries: Query external data sources

Data Model

Tables

Columnar storage format
Schema enforcement or schema-on-read
Partitioning and clustering for performance
Time-based partitioning support

Datasets

Logical grouping of tables
Access control at dataset level
Geographic location selection
Data organization and governance

Projects

Top-level container for datasets
Billing and quota management
IAM access control
Resource organization

BigQuery ML

Build ML models using SQL
Support for regression, classification, clustering
Time series forecasting capabilities
Import/export TensorFlow models
AutoML integration

Use Cases

Analyzing large datasets
Building data warehouses
Implementing business intelligence
Running ML on structured data
Real-time analytics and dashboards

Pros

Serverless and automatic scaling
Fast SQL-based queries
Built-in machine learning
Integrates with data ecosystem

Cons

Cost can grow with query complexity
Less control over performance tuning
Data egress costs
Learning curve for optimization

Query Examples

-- Basic query
SELECT 
  product_category,
  SUM(revenue) as total_revenue,
  COUNT(*) as transaction_count
FROM 
  `my-project.sales.transactions`
WHERE 
  transaction_date >= '2024-01-01'
GROUP BY 
  product_category
ORDER BY 
  total_revenue DESC;

-- BigQuery ML prediction
SELECT *
FROM ML.PREDICT(MODEL `my-project.sales.forecasting_model`,
  (
    SELECT 
      product_id,
      region,
      historical_sales
    FROM `my-project.sales.features`
  ));

Data Loading

# Load from Cloud Storage
bq load \
  --source_format=CSV \
  --field_delimiter=',' \
  --skip_leading_rows=1 \
  my-project:sales.transactions \
  gs://my-bucket/sales_data.csv

# Load streaming data
bq insert \
  --table=my-project:sales.transactions \
  'product_id=123,region=us-west1,revenue=100.50'

Cloud SQL and AlloyDB

Managed relational database services for structured data. Cloud SQL and AlloyDB are managed relational database services. Cloud SQL supports MySQL, PostgreSQL, and SQL Server with automated backups, high availability, and vertical scaling. AlloyDB is a PostgreSQL-compatible database with higher performance and advanced features.

Cloud SQL

Database Engines

MySQL: Community and Enterprise editions
PostgreSQL: Latest versions with extensions
SQL Server: Full compatibility with Microsoft SQL Server

Key Features

Automated backups and point-in-time recovery
High availability with regional failover
Vertical scaling (CPU, RAM, storage)
Read replicas for read-heavy workloads
Integration with other GCP services

Use Cases

Traditional web applications
E-commerce platforms
Content management systems
Enterprise applications requiring ACID compliance

AlloyDB

Performance Features

Machine Learning Acceleration: Adaptive query optimization
Columnar Storage: Hybrid row/columnar storage
Vector Indexing: Support for vector similarity search
High Availability: 99.99% availability SLA

Advanced Capabilities

PostgreSQL compatibility (version 14+)
Unmatched performance for complex queries
Intelligent storage management
Advanced security features

Use Cases

High-performance analytical workloads
Vector similarity search for AI/ML
Large-scale PostgreSQL migrations
Applications requiring advanced PostgreSQL features

Use Cases

You need managed relational databases
Want automated maintenance
Require PostgreSQL compatibility with enhanced performance
Building applications requiring ACID transactions
Migrating from on-premises databases

Pros

Fully managed database operations
Automated backups and HA
Scaling capabilities
Reduced administrative overhead

Cons

Higher cost than self-managed
Limited database engine choices
Vendor lock-in considerations
Performance limitations at scale

Cloud SQL Setup

# Create Cloud SQL instance
gcloud sql instances create my-instance \
  --database-version=POSTGRES_14 \
  --tier=db-f1-micro \
  --region=us-central1 \
  --storage-size=10GB

# Create database
gcloud sql databases create my-database \
  --instance=my-instance

# Create user
gcloud sql users create my-user \
  --instance=my-instance \
  --password=secure-password

AlloyDB Setup

# Create AlloyDB cluster
gcloud alloydb clusters create my-cluster \
  --region=us-central1 \
  --network=projects/my-project/global/networks/default

# Create AlloyDB instance
gcloud alloydb instances create my-instance \
  --cluster=my-cluster \
  --region=us-central1 \
  --instance-type=PRIMARY \
  --cpu-count=2 \
  --memory-size=15GB

Cloud Spanner

Globally distributed SQL database with horizontal scaling. Cloud Spanner is a fully managed, globally distributed SQL database that combines the benefits of relational database structure with horizontal scaling. It provides external consistency, high availability (99.999%), and automatic sharding.

Overview

Spanner is designed for globally distributed applications that require strong consistency and high availability. It provides the scalability of NoSQL databases with the relational structure and transactional guarantees of traditional databases.

Key Features

Global Distribution: Data replication across regions
External Consistency: Globally consistent transactions
Horizontal Scaling: Automatic sharding and load balancing
High Availability: 99.999% availability SLA
SQL Compatibility: ANSI SQL with Google extensions
Schema Flexibility: Interleaved tables and hierarchical data

Architecture

Splits and Tablets

Automatic data splitting based on size
Load balancing across servers
Geographic distribution based on configuration
Automatic rebalancing

Replication

Configurable replication patterns
Multi-region and regional configurations
Automatic failover
Consistent reads across regions

Use Cases

You need global data distribution
Strong consistency across regions
Horizontal scaling for large databases
Financial applications requiring ACID guarantees
Global e-commerce platforms

Pros

Global distribution with consistency
Horizontal scaling
High availability (99.999%)
SQL compatibility

Cons

Higher cost than alternatives
Higher latency for cross-region reads
Limited database features
Complex query optimization

Spanner Setup

# Create Spanner instance
gcloud spanner instances create my-instance \
  --config=regional-us-central1 \
  --description="My Spanner instance" \
  --nodes=1

# Create database
gcloud spanner databases create my-database \
  --instance=my-instance

# Create table
gcloud spanner databases ddl update my-database \
  --instance=my-instance \
  --ddl="CREATE TABLE users (
    id STRING(36) NOT NULL,
    name STRING(1024),
    email STRING(1024),
  ) PRIMARY KEY (id)"

Cloud Bigtable

Fully managed NoSQL database for large analytical workloads. Cloud Bigtable is a fully managed NoSQL database designed for large analytical and operational workloads. It provides high throughput and low latency for time-series data, IoT, and big data analytics. Offers seamless scaling, automatic replication, and integration with GCP data and analytics services.

Overview

Bigtable is designed for massive scalability and high performance for read/write workloads. It’s ideal for applications that require high throughput and low latency for large datasets.

Data Model

Tables and Rows

Sparse, multi-dimensional sorted map
Row keys as primary index
Column families for grouping columns
Timestamps for versioning

Column Families

Group related columns together
Store different data types
Configure compression and encoding
Access control at family level

Key Features

High Throughput: Millions of requests per second
Low Latency: Single-digit millisecond latency
Automatic Scaling: Seamless horizontal scaling
High Availability: Replication across clusters
Integration: Native integration with GCP services

Use Cases

Handling time-series data
IoT workloads
Applications requiring high throughput and low latency for large datasets
Real-time analytics
Sensor data storage

Pros

High throughput and low latency
Seamless horizontal scaling
Optimized for time-series data
Integration with analytics tools

Cons

NoSQL query limitations
Limited secondary indexes
Requires data modeling expertise
Not ideal for transactional workloads

Bigtable Setup

# Create Bigtable instance
gcloud bigtable instances create my-instance \
  --cluster-config=region=us-central1 \
  --display-name="My Bigtable instance" \
  --instance-type=PRODUCTION \
  --node-count=3

# Create table
cbt -project my-project -instance my-instance createtable my-table

# Create column family
cbt -project my-project -instance my-instance createfamily my-table cf1

Dataflow

Serverless stream and batch data processing. Dataflow is a fully managed serverless service for stream and batch data processing. It uses Apache Beam for unified programming, providing exactly-once processing semantics, automatic scaling, and integration with GCP data services.

Apache Beam Model

Core Concepts

PCollection: Distributed data collection
PTransform: Data processing operations
Pipeline: Directed graph of transformations
Runner: Execution engine (Dataflow)

Windowing

Fixed windows
Sliding windows
Session windows
Global windows

Triggers

Event time triggers
Processing time triggers
Watermark handling
Late data handling

Key Features

Unified API: Same code for batch and streaming
Exactly-once Semantics: Reliable processing guarantees
Auto-scaling: Automatic resource provisioning
Integration: Native GCP service integration
Monitoring: Built-in monitoring and debugging

Use Cases

Building data pipelines
Processing streaming data
Implementing ETL workflows with automatic scaling
Real-time analytics
Data transformation and enrichment

Pros

Serverless processing
Unified batch and streaming
Exactly-once semantics
Auto-scaling capabilities

Cons

Requires Apache Beam knowledge
Cost for compute resources
Debugging complexity
Vendor lock-in considerations

Dataflow Pipeline

# dataflow_pipeline.py
import apache_beam as beam

def run():
    with beam.Pipeline() as pipeline:
        (pipeline
         | 'Read from Pub/Sub' >> beam.io.ReadFromPubSub(topic='projects/my-project/topics/my-topic')
         | 'Parse JSON' >> beam.Map(parse_json)
         | 'Transform data' >> beam.Map(transform_data)
         | 'Window into fixed intervals' >> beam.WindowInto(beam.window.FixedWindows(60))
         | 'Aggregate results' >> beam.CombinePerKey(sum)
         | 'Write to BigQuery' >> beam.io.WriteToBigQuery(
             'my-project:my-dataset.my-table',
             schema='field1:STRING,field2:INTEGER',
             write_disposition=beam.io.BigQueryDisposition.WRITE_APPEND)
        )

if __name__ == '__main__':
    run()

Dataflow Execution

# Run Dataflow pipeline
python dataflow_pipeline.py \
  --runner=DataflowRunner \
  --project=my-project \
  --region=us-central1 \
  --temp_location=gs://my-bucket/temp \
  --job_name=my-dataflow-job

Dataproc

Managed Apache Spark and Hadoop cluster service. Dataproc is a fully managed service for running Apache Spark and Hadoop clusters. It provides fast cluster creation, auto-scaling, integration with GCP storage and data services, and support for various open-source big data tools.

Supported Components

Processing Frameworks

Apache Spark: Core, SQL, Streaming, MLlib, GraphX
Apache Hadoop: MapReduce, YARN, HDFS
Apache Hive: Data warehouse software
Apache Pig: Scripting platform for data analysis

Additional Tools

Presto: Distributed SQL query engine
Zeppelin: Interactive data analysis notebook
Jupyter: Data science notebooks
Cloud Storage Connector: Direct GCS access

Key Features

Fast Cluster Creation: Minutes to provision clusters
Auto-scaling: Automatic cluster resizing
Integration: Native GCP service integration
Open Source: Standard Apache Hadoop ecosystem
Cost Effective: Per-second billing with preemptible VMs

Use Cases

Running big data processing
Existing Spark/Hadoop workloads
Requiring open-source big data tools
Data science and machine learning
ETL and data transformation

Pros

Managed open-source clusters
Fast cluster creation
Integration with GCP ecosystem
Cost-effective for batch jobs

Cons

Cluster management overhead
Requires big data expertise
Higher cost for long-running clusters
Less automated than serverless options

Dataproc Setup

# Create Dataproc cluster
gcloud dataproc clusters create my-cluster \
  --region=us-central1 \
  --single-node \
  --master-machine-type=n1-standard-4 \
  --master-boot-disk-size=100GB \
  --image-version=2.0-debian10 \
  --project=my-project

# Submit Spark job
gcloud dataproc jobs submit spark \
  --cluster=my-cluster \
  --region=us-central1 \
  --class=org.apache.spark.examples.SparkPi \
  --jars=file:///usr/lib/spark/examples/jars/spark-examples.jar \
  -- 1000

Spark Job Example

# spark_job.py
from pyspark.sql import SparkSession

def main():
    spark = SparkSession.builder.appName("MySparkJob").getOrCreate()
    
    # Read data from Cloud Storage
    df = spark.read.json("gs://my-bucket/data/*.json")
    
    # Process data
    result = df.groupBy("category").count()
    
    # Write results to BigQuery
    result.write.format("bigquery") \
        .option("table", "my-project:my-dataset.results") \
        .mode("overwrite") \
        .save()
    
    spark.stop()

if __name__ == "__main__":
    main()

Data Architecture Patterns

Lakehouse Architecture

Data Lake: Raw data storage in Cloud Storage
Data Warehouse: Structured data in BigQuery
Integration: Seamless data movement between lake and warehouse
Governance: Unified metadata and access control

Real-time Analytics

Streaming Ingestion: Pub/Sub + Dataflow
Stream Processing: Dataflow for real-time transformation
Serving Layer: BigQuery for real-time queries
Visualization: Looker or other BI tools

ML Pipeline

Feature Store: BigQuery for feature storage
Training: Vertex AI for model training
Serving: Vertex AI endpoints for predictions
Monitoring: Observability for model performance

Choosing the Right Data Service

Requirement	Recommended Service
Traditional relational database	Cloud SQL
High-performance PostgreSQL	AlloyDB
Global consistency	Cloud Spanner
Time-series/IoT data	Cloud Bigtable
Analytics/BI	BigQuery
Stream processing	Dataflow
Big data processing	Dataproc

Common Issues and Troubleshooting

BigQuery Query Failures

Verify dataset and table permissions
Check query syntax and schema
Review query size limits
Monitor query costs

Database Connection Issues

Verify connection strings and credentials
Check network connectivity
Review SSL/TLS configurations
Ensure database instances are running

Data Pipeline Failures

Validate data schemas and formats
Check service account permissions
Monitor resource utilization
Review error logs and retry policies

Cleanup Commands

# Delete BigQuery dataset
bq rm -r -f my-project:my-dataset

# Delete Cloud SQL instance
gcloud sql instances delete my-instance

# Delete Spanner instance
gcloud spanner instances delete my-instance

# Delete Bigtable instance
gcloud bigtable instances delete my-instance

# Clean up Dataflow jobs
gcloud dataflow jobs list
gcloud dataflow jobs cancel my-job-id --region=us-central1

Jump to other sections

Explore Messaging & Events for data streaming patterns
Review Observability for data pipeline monitoring

Additional Resources

Next Up → GCP Project Setup