Q-1). What is Cloud Spanner?

A-1). Google Cloud Spanner is a fully managed, scalable, relational database solution that provides the best of both relational and non-relational worlds. Its ability to scale globally while maintaining strong consistency and ACID transactions makes it a unique and highly effective choice for many enterprise applications.

Q-2). How do you add Cloud Spanner dependencies to a Spring Boot project?

A-2). Using Maven:

There are 2 ways:

  • Using Spring Data Cloud Spanner:
<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
    <version>3.4.0</version> <!-- Check for the latest version -->
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
  • Using Spring Data R2DBC with Cloud Spanner:
<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-spanner-spring-data-r2dbc</artifactId>
    <version>1.0.0</version> <!-- Check for the latest version -->
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-r2dbc</artifactId>
</dependency>

Using Gradle:

  • Using Spring Data Cloud Spanner:
implementation 'com.google.cloud:spring-cloud-gcp-starter-data-spanner:3.4.0' // Check for the latest version
implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
  • Using Spring Data R2DBC with Cloud Spanner:
implementation 'com.google.cloud:google-cloud-spanner-spring-data-r2dbc:1.0.0' // Check for the latest version
implementation 'org.springframework.boot:spring-boot-starter-data-r2dbc'
Q-3). What properties are required to configure Cloud Spanner in a Spring Boot application?

A-3). We can define 2 categories of properties, one is Essential and another is Additional.

Essential Properties

# Google Cloud Project ID
spring.cloud.gcp.project-id=your-project-id

# Cloud Spanner Instance ID
spring.cloud.gcp.spanner.instance-id=your-instance-id

# Cloud Spanner Database ID
spring.cloud.gcp.spanner.database=your-database-id

# Path to the service account key file (optional, if not using Application Default Credentials)
spring.cloud.gcp.credentials.location=file:/path/to/your-service-account-key.json

Additional Properties

spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.connection-timeout=20000

Query Options

spring.cloud.gcp.spanner.query-options.optimize-query-true=false
Q-4). How do you authenticate a Spring Boot application with Google Cloud Spanner?

A-4). There are 3 ways by which one can authenticate a Spring Boot application with Google Cloud Spanner.

  • Using Application Default Credentials (ADC)
    • No additional configuration is required in your code: When running in Google Cloud, the environment will provide the necessary credentials automatically.
    • Ensure no credentials.location property is set: If you’re using ADC, you don’t need to set the spring.cloud.gcp.credentials.location property in your application.properties or application.yml.
    • Set the environment variable for local development: For local development, you can set the GOOGLE_APPLICATION_CREDENTIALS environment variable to point to your service account key file.
export GOOGLE_APPLICATION_CREDENTIALS="/path/to/your-service-account-key.json"
  • Using a Service Account Key File
    • Create a Service Account: Go to the Google Cloud Console and create a service account with the necessary permissions to access Cloud Spanner.
    • Download the Service Account Key File: Download the JSON key file for the service account.
    • Set the path to the service account key file in your application properties: Please find below the properties defined in application.properties file.
spring.cloud.gcp.project-id=your-project-id
spring.cloud.gcp.spanner.instance-id=your-instance-id
spring.cloud.gcp.spanner.database=your-database-id
spring.cloud.gcp.credentials.location=file:/path/to/your-service-account-key.json

Or in application.yml

spring:
  cloud:
    gcp:
      project-id: your-project-id
      spanner:
        instance-id: your-instance-id
        database: your-database-id
      credentials:
        location: file:/path/to/your-service-account-key.json
  • Using Workload Identity Federation
    • Create a Workload Identity Pool: In Google Cloud Console, create a Workload Identity Pool.
    • Configure Identity Provider: Add an identity provider for your non-Google Cloud environment to the Workload Identity Pool.
    • Grant Access to the Workload Identity Pool: Allow the identity provider to impersonate the service account.
    • Configure Application to Use Workload Identity Federation: Update your application configuration to authenticate using Workload Identity Federation. This typically involves setting up environment variables and configuration files for your non-Google Cloud environment.
Q-5). What are the benefits of using Cloud Spanner over traditional relational databases?

A-5). Google Cloud Spanner combines the best aspects of traditional relational databases with the scalability and global reach of NoSQL databases. It is particularly beneficial for applications that require:

  • High availability and reliability
  • Global data distribution with strong consistency
  • Reduced operational complexity through managed services
  • Seamless integration with cloud-native services

These features make Cloud Spanner an attractive choice for modern, distributed, and mission-critical applications.

Q-6). How do you set up a simple connection to Cloud Spanner using Spring Boot?

A-6). There are some steps by which one can easily set up a simple connection to Cloud Spanner using the Spring Boot Application.

  • Add Dependencies

Maven

<dependencies>
    <!-- Spring Boot Starter for Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- Google Cloud Spanner Starter -->
    <dependency>
        <groupId>com.google.cloud</groupId>
        <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
        <version>3.4.0</version> <!-- Use the latest version -->
    </dependency>
    <!-- Spring Boot Starter for Test (optional, for testing purposes) -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

Gradle

dependencies {
// Spring Boot Starter for Data JPA
implementation ‘org.springframework.boot:spring-boot-starter-data-jpa’
// Google Cloud Spanner Starter
implementation ‘com.google.cloud:spring-cloud-gcp-starter-data-spanner:3.4.0’ // Use the latest version
// Spring Boot Starter for Test (optional, for testing purposes)
testImplementation ‘org.springframework.boot:spring-boot-starter-test’
}

  • Configure Application Properties

application.properties

spring.cloud.gcp.project-id=your-project-id
spring.cloud.gcp.spanner.instance-id=your-instance-id
spring.cloud.gcp.spanner.database=your-database-id

# Path to the service account key file (optional, if not using Application Default Credentials)
spring.cloud.gcp.credentials.location=file:/path/to/your-service-account-key.json

application.yml

spring:
  cloud:
    gcp:
      project-id: your-project-id
      spanner:
        instance-id: your-instance-id
        database: your-database-id
      credentials:
        location: file:/path/to/your-service-account-key.json
  • Create an Entity Class
import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
public class User {
    @Id
    private Long userId;
    private String name;
    private String mobileNo;

    // Getters and setters
    public Long getUserId() {
        return userId;
    }

    public void setUserId(Long userId) {
        this.userId = userId;
    }

    public String getName() {
        return firstName;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getMobileNo() {
        return mobileNo;
    }

    public void setMobileNo(String mobileNo) {
        this.mobileNo = mobileNo;
    }
}
  • Create a Repository Interface
import org.springframework.data.repository.CrudRepository;

public interface UserRepository extends CrudRepository<User, Long> {
}
  • Create a Controller Class
public class SpringGCPSpannerController {
	
	private final SpringGCPSpannerService gcpSpannerService;
	
	@Autowired
	public SpringGCPSpannerController(SpringGCPSpannerService gcpSpannerService) {
		this.gcpSpannerService = gcpSpannerService;
	}
	
	
	@GetMapping("/allUsers")
        public List<User> getAllUserData() {
	       LOGGER.info("getAllUserData() -> All Data are fetched");
	       return gcpSpannerService.getAllUsers();
        }
	
	@PostMapping("/saveUser")
        public User saveUserData(@RequestBody User user) {
              LOGGER.info("saveUserData() -> New Record of User saved");
              return gcpSpannerService.saveUser(user);
        }
	
	@GetMapping("/getUser/{id}")
        public User getUserDataById(@PathVariable(value = "id") String id) {
		Long userId = Long.parseLong(id);
    	        User user = gcpSpannerService.getUserById(userId);
    	        LOGGER.info("getUserDataById() -> Fetch the User Detail : {}", user);
                return user;
        }
}
  • Or even create a Spring Boot Application class
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class SpannerDemoApplication implements CommandLineRunner {

    @Autowired
    private userRepository userRepository;

    public static void main(String[] args) {
        SpringApplication.run(SpannerDemoApplication.class, args);
    }

    @Override
    public void run(String... args) throws Exception {
        // Save a new user
        User user = new User();
        user.setUserId(1);
        user.setName("Test User");
        user.setMobileNo("1234567890");
        userRepository.save(user);

        // Retrieve and print the user
        User retrievedUser = userRepository.findById(1).orElse(null);
        if (null != retrievedUser) {
            System.out.println("Retrieved User: " + retrievedUser.getName() + " " + retrievedUser.getMobileNo());
        }
    }
}
Q-7). What is the difference between strong and eventual consistency in Cloud Spanner?

A-7). The differences between strong consistency and eventual consistency are as follows:

Strong ConsistencyEventual Consistency
Ensures immediate visibility of writes to all subsequent reads.Allows for temporary staleness of data.
Guarantees correctness and real-time accuracy.Achieves higher availability and lower latency.
Typically incurs higher latency due to synchronization requirements.Suitable for scenarios where real-time accuracy is less critical.
Q-8). How do you handle database migrations in a Spring Boot application using Cloud Spanner?

A-8). Using Flyway with Spring Boot application along with Cloud Spanner, one can handle the database migration process very easily.

Please find below the steps:

  • Step 1 – Add Dependencies

Maven

<dependencies>
    <!-- Spring Boot Starter for Data JPA -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <!-- Google Cloud Spanner Starter -->
    <dependency>
        <groupId>com.google.cloud</groupId>
        <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
        <version>3.4.0</version> <!-- Use the latest version -->
    </dependency>
    <!-- Flyway Core -->
    <dependency>
        <groupId>org.flywaydb</groupId>
        <artifactId>flyway-core</artifactId>
    </dependency>
    <!-- Optional: Spring Boot Starter for Test (for testing purposes) -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
</dependencies>

Gradle

dependencies {
    // Spring Boot Starter for Data JPA
    implementation 'org.springframework.boot:spring-boot-starter-data-jpa'
    // Google Cloud Spanner Starter
    implementation 'com.google.cloud:spring-cloud-gcp-starter-data-spanner:3.4.0' // Use the latest version
    // Flyway Core
    implementation 'org.flywaydb:flyway-core'
    // Optional: Spring Boot Starter for Test (for testing purposes)
    testImplementation 'org.springframework.boot:spring-boot-starter-test'
}
  • Step 2 – Configure Application Properties

application.properties

spring.cloud.gcp.project-id=your-project-id
spring.cloud.gcp.spanner.instance-id=your-instance-id
spring.cloud.gcp.spanner.database=your-database-id

# Path to the service account key file (if not using Application Default Credentials)
spring.cloud.gcp.credentials.location=file:/path/to/your-service-account-key.json

# Flyway settings
flyway.url=jdbc:cloudspanner:/projects/your-project-id/instances/your-instance-id/databases/your-database-id
flyway.driver=com.google.cloud.spanner.jdbc.JdbcDriver
flyway.user=your-username
flyway.password=your-password

application.yml

spring:
  cloud:
    gcp:
      project-id: your-project-id
      spanner:
        instance-id: your-instance-id
        database: your-database-id
      credentials:
        location: file:/path/to/your-service-account-key.json
flyway:
  url: jdbc:cloudspanner:/projects/your-project-id/instances/your-instance-id/databases/your-database-id
  driver: com.google.cloud.spanner.jdbc.JdbcDriver
  user: your-username
  password: your-password
  • Step 3 – Create Migration Scripts

Migration Script – V1__Initial_setup.sql

CREATE TABLE Person (
    id STRING(36) NOT NULL,
    firstName STRING(255),
    lastName STRING(255),
    PRIMARY KEY (id)
);
  • Step 4 – Run the application

When you start your Spring Boot application, Flyway will automatically detect the migration scripts and apply them to your Cloud Spanner database.

  • Step 5: Verify the Migration

You can verify that the migration was applied successfully by checking the database schema in the Google Cloud Console or using a database client that supports Cloud Spanner.

  • Step 6: Managing Future Migrations

For future schema changes, you can create new migration scripts (e.g., V2__Add_email_to_person.sql) and place them in the src/main/resources/db/migration directory. Flyway will apply these migrations in order based on their version numbers when the application starts.

  • Step 7 – Additional Flyway Configuration (Optional)

application.properties:

# Flyway settings
flyway.baseline-on-migrate=true
flyway.locations=classpath:db/migration

application.yml

flyway:
  baseline-on-migrate: true
  locations: classpath:db/migration
Q-9). How do you optimize database queries for performance in Cloud Spanner?

A-9). The steps to optimize the database queries for fast performance in Cloud Spanner are provided below:

  • Schema Design Optimization
    • Use Appropriate Primary Keys – Choose primary keys that distribute data evenly. Avoid monotonically increasing keys like timestamps or auto-incrementing integers, which can lead to hotspots.
    • Use Secondary Indexes – Create secondary indexes to speed up queries on non-primary key columns. Ensure that the indexes are used efficiently by reviewing query execution plans.
    • Interleaved Tables – Use interleaved tables to physically store related rows together, reducing the number of rows scanned and improving performance for joins and hierarchical data.
  • Query Design and Optimization
    • Analyze Query Execution Plans – Use the EXPLAIN statement to analyze query execution plans and identify performance bottlenecks.
      • EXPLAIN SELECT * FROM YourTable WHERE column = 'value';
    • Optimize Joins –
      • Join Order: Optimize the order of joins to minimize the number of rows processed. Use the most selective joins first.
      • Indexed Joins: Ensure that join conditions use indexed columns.
    • Use Query Parameters – Use query parameters to improve performance and enable query plan caching.
      • String sql = "SELECT * FROM YourTable WHERE column = @value"; Statement statement = Statement.newBuilder(sql).bind("value").to("desiredValue").build();
    • Limit Result Sets – Use pagination to limit the number of rows returned by a query, reducing memory and processing overhead.
      • SELECT * FROM YourTable LIMIT 100 OFFSET 0;
    • Avoid Full Table Scans – Ensure queries are selective and use appropriate filters to avoid full table scans.
  • Efficient Data Types and Encoding
    • Use Appropriate Data Types – Use the most efficient data types for your columns. For example, prefer INT64 for integers and STRING with a specified length for text.
    • Optimize Column Selection – Select only the columns you need in your queries to reduce the amount of data processed and transferred.
      • SELECT column1, column2 FROM YourTable WHERE condition = 'value';
  • Indexing Strategies
    • Create and Maintain Indexes – Create indexes on frequently queried columns. Monitor and maintain these indexes to ensure they remain effective.
    • Covering Indexes – Use covering indexes that include all columns needed by a query to avoid accessing the base table.
  • Optimize Read and Write Operations
    • Batch Reads and Writes – Batch multiple read and write operations to reduce the number of round trips to the database.
      • BatchReadOnlyTransaction batchReadOnlyTransaction = client.batchReadOnlyTransaction(TimestampBound.strong()); batchReadOnlyTransaction.read("YourTable", KeySet.all(), Arrays.asList("column1", "column2"));
    • Use Mutations for Writes – Use mutations to group multiple write operations, reducing the overhead associated with individual transactions.
      • List<Mutation> mutations = new ArrayList<>(); mutations.add(Mutation.newInsertBuilder("YourTable").set("column").to(value).build()); client.write(mutations);
  • Monitor and Analyze Performance
    • Monitoring – Use Google Cloud’s monitoring and logging tools to analyze query performance and identify bottlenecks.
    • Query Statistics – Review query statistics and performance metrics available in the Cloud Spanner console.
    • Timeouts – Set appropriate timeouts for queries to prevent long-running queries from consuming excessive resources.
  • Partitioning and Sharding
    • Data Partitioning – Partition your data to distribute the load evenly across nodes. Use composite primary keys to achieve effective partitioning.
  • Use Best Practices for Transactions
    • Optimize Transactions – Keep transactions short and limit the number of operations within a single transaction to reduce contention and locking issues.
      • client.readWriteTransaction().run(transaction -> { transaction.buffer(Mutation.newInsertBuilder("YourTable").set("column").to(value).build()); return null; });
Q-10). What are the key differences between using JDBC and R2DBC with Cloud Spanner in Spring Boot?

A-10). The differences between JDBC and R2DBC with Cloud Spanner are as follows:

JDBCR2DBC
SynchronousAsynchronous
Blocking API best suited for traditional applicationsNon-blocking API designed for modern applications
Imperative applicationsHigh-concurrency applications
Here ease of use and compatibility are primary concernsIt benefits from a reactive programming model

Code Example

JDBC

@Autowired
private JdbcTemplate jdbcTemplate;

public List<User> getAllUsers() {
    return jdbcTemplate.query("SELECT * FROM User", new BeanPropertyRowMapper<>(User.class));
}

R2DBC

@Autowired
private DatabaseClient databaseClient;

public Flux<User> getAllUsers() {
    return databaseClient.execute("SELECT * FROM User")
                         .as(User.class)
                         .fetch()
                         .all();
}
Q-11). How do you manage schema changes in Cloud Spanner without downtime?

A-11). Managing schema changes in Cloud Spanner without downtime is crucial for maintaining the availability and reliability of your application. There are a few steps as discussed below, one can follow that.

  • Use Online DDL Operations – Cloud Spanner supports online DDL operations, which allow schema changes to be made without locking tables or interrupting ongoing transactions. This feature is essential for maintaining availability during schema modifications.
    • ALTER TABLE MyTable ADD COLUMN new_column STRING(255);
  • Use ALLOW_NULL Columns – When adding new columns, set them as ALLOW_NULL to ensure that existing rows are not immediately affected. Once the column is in place, you can backfill the column with appropriate values if needed.
    • ALTER TABLE MyTable ADD COLUMN new_column STRING(255) ALLOW NULL;
  • Backfill Data in Small Batches – If you need to populate new columns or migrate data, perform these operations in small batches to keep the system responsive.
    • int batchSize = 1000; String selectQuery = "SELECT id FROM MyTable WHERE new_column IS NULL LIMIT " + batchSize; while (true) { List<String> ids = jdbcTemplate.query(selectQuery, (rs, rowNum) -> rs.getString("id")); if (ids.isEmpty()) break; for (String id : ids) { jdbcTemplate.update("UPDATE MyTable SET new_column = ? WHERE id = ?", newValue, id); } }
  • Add Columns and Indices in Multiple Steps
    • Add a new nullable column.
      • ALTER TABLE MyTable ADD COLUMN new_column STRING(255) ALLOW NULL;
    • Backfill data for the new column.
    • Modify the column to be NOT NULL if required.
      • ALTER TABLE MyTable ALTER COLUMN new_column STRING(255) NOT NULL;
    • Add indices if needed.
      • CREATE INDEX MyTableByNewColumn ON MyTable(new_column);
  • Use Rolling Schema Changes – Perform rolling schema changes to ensure compatibility between the old and new schema versions.
    • Add new column: Add a new column that is compatible with both the old and new application versions.
    • Update application: Deploy the new application version that writes to both the old and new columns.
    • Migrate data: Migrate existing data to the new schema format.
    • Remove old column: After confirming the new column is fully populated and in use, remove the old column.
  • Monitor and Validate Changes – Use monitoring tools to track the progress of schema changes and validate that data integrity is maintained throughout the process.
    • Cloud Monitoring: Use Google Cloud Monitoring to track database performance metrics.
    • Validation Scripts: Run scripts to validate data consistency after each batch of changes.
  • Use Change Streams for Synchronization – Cloud Spanner’s change streams can track changes in data and synchronize schema changes without downtime.
    • Enable Change Streams: Set up change streams to monitor data changes.
    • Data Migration: Use change streams to keep data synchronized between the old and new schema versions during migration.
  • Plan and Test Schema Changes
    • Staging Environment: Test schema changes in a staging environment that mirrors production.
    • Rollback Plan: Prepare a rollback plan in case any issues arise during the schema changes.
  • Communicate with Stakeholders – Keep all stakeholders informed about the schema change plan, including potential risks and mitigation strategies.
Q-12). Explain the importance of indexing in Cloud Spanner and how to implement it.

A-12). First one should need to know about the importance of Indexing in Cloud Spanner.

  • Performance Improvement:
    • Faster Query Execution: Indexes allow the database to find and retrieve specific rows much faster than scanning the entire table.
    • Efficient Data Retrieval: By creating indexes on columns frequently used in WHERE clauses, JOIN operations, or sorting, you can significantly reduce query response times.
  • Reduced Resource Consumption:
    • Lower I/O Operations: Indexes reduce the number of read operations needed to fetch the required data, which in turn lowers CPU and memory usage.
    • Efficient Data Access: This results in less disk I/O, leading to better performance and lower costs.
  • Improved Scalability:
    • Handling Large Datasets: Indexes enable Cloud Spanner to handle larger datasets efficiently, ensuring that performance remains consistent even as data volumes grow.
    • Concurrent Access: By optimizing data retrieval, indexes support higher levels of concurrent access, making your application more scalable.
  • Better Query Plans:
    • Optimized Execution Plans: The query optimizer uses indexes to create more efficient execution plans, thus improving overall query performance.

Types of Indexes in Cloud Spanner

  • Primary Index – The primary index is automatically created on the primary key of a table and is used to ensure the uniqueness and order of the primary key columns.
  • Secondary Index – Secondary indexes are created on non-primary key columns to optimize query performance for specific access patterns.
  • Interleaved Index – Interleaved indexes are used for tables that have a parent-child relationship and are physically stored together to improve access patterns.

How to Implement Indexes in Cloud Spanner

  • Creating a Secondary Index – To create a secondary index in Cloud Spanner, use the CREATE INDEX statement.
    • Suppose you have a table Customers and you frequently query by the email column. Creating an index on the email column would optimize these queries.
    • CREATE INDEX CustomersByEmail ON Customers(email);
  • Querying with the Index – After creating the index, you don’t need to modify your queries. Cloud Spanner’s query optimizer will automatically use the index if it improves performance.
  • Indexing Composite Columns – You can create indexes on multiple columns (composite indexes) if your queries filter by multiple columns.
    • CREATE INDEX OrdersByCustomerIdAndOrderDate ON Orders(customer_id, order_date);
  • Indexing with NULL Values – Cloud Spanner indexes support indexing columns that contain NULL values. This is useful for queries that are included NULL in their filtering criteria.
    • CREATE INDEX ProductsByCategoryAndPrice ON Products(category, price);

Best Practices for Indexing in Cloud Spanner

  • Choose Indexes Based on Query Patterns – Analyze your query patterns and create indexes on columns frequently used in search conditions (WHERE clauses), joins, and sorting.
  • Limit the Number of Indexes – While indexes improve read performance, they add overhead to write operations (inserts, updates, deletes). Only create indexes that will be beneficial for your queries.
  • Monitor and Maintain Indexes – Regularly monitor the performance of your indexes and queries. Remove unused or rarely used indexes to optimize write performance.
  • Use Covering Indexes – If a query can be satisfied entirely by the index without needing to access the base table, it’s called a covering index. This can significantly improve query performance.
    • If a query frequently fetches email and name, create an index that includes both columns:
    • CREATE INDEX CustomersByEmailAndName ON Customers(email, name);
  • Consider Interleaved Tables – For hierarchical data, use interleaved tables and indexes to keep related data physically close, reducing the amount of data scanned for queries.

Example – 

CREATE TABLE Orders (
  order_id STRING(36) NOT NULL,
  customer_id STRING(36),
  order_date TIMESTAMP,
  ...
) PRIMARY KEY(order_id),
  INTERLEAVE IN PARENT Customers ON DELETE CASCADE;
Q-13). Discuss the challenges and solutions for handling network latency when using Cloud Spanner with a Spring Boot application.

A-13). Handling network latency is a critical consideration when integrating Cloud Spanner with a Spring Boot application. Network latency can affect the performance and responsiveness of your application.

Challenges

  • Geographical Distance
  • Network Congestion
  • Inefficient Query Design
  • High Request Volume

Solutions

  • Deploy in the Same Region – Use Google Cloud Console or deployment scripts to align the regions of your application and database.
  • Use VPC Peering and Dedicated Interconnects – Set up VPC Peering through Google Cloud Console or use dedicated interconnects for more consistent and lower latency network connections.
  • Optimize Queries –
    • Selective Queries – Fetch only the necessary columns and rows.
    • Indexing – Use indexes to speed up query execution.
    • Batching – Retrieve data in batches to avoid multiple round-trips.
      • SELECT id, name FROM Users WHERE active = true LIMIT 100;
  • Connection Pooling – Configure connection pools in your Spring Boot application using libraries like HikariCP.
    • spring.datasource.hikari.maximum-pool-size=20 spring.datasource.hikari.minimum-idle=5 spring.datasource.hikari.idle-timeout=30000 spring.datasource.hikari.max-lifetime=1800000 spring.datasource.hikari.connection-timeout=30000
  • Caching
    • In-memory Caching – Use libraries like Caffeine or Ehcache for in-memory caching.
    • Distributed Caching – Use Redis or Memcached for distributed caching in clustered environments.

Example with Spring Cache

import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;

@Service
public class UserService {

    @Cacheable("users")
    public User getUserById(String id) {
        // Fetch user from Cloud Spanner
        return userRepository.findById(id).orElse(null);
    }
}
  • Async and Non-blocking Operations
    • Reactive Programming – Use Spring WebFlux with R2DBC for reactive database access.
    • Async Methods – Use @Async in Spring to perform asynchronous operations.

Example with Spring WebFlux

import org.springframework.data.r2dbc.repository.Query;
import org.springframework.data.repository.reactive.ReactiveCrudRepository;
import reactor.core.publisher.Flux;

public interface UserRepository extends ReactiveCrudRepository<User, String> {
    
    @Query("SELECT * FROM Users WHERE active = true")
    Flux<User> findActiveUsers();
}
  • Use Distributed Transactions Sparingly – Use eventual consistency and design your application to tolerate slightly stale data when necessary.
  • Monitor Latency Metrics – Integrate monitoring tools with your application to gather real-time insights into performance.
  • Network Profiling – Regularly review and optimize network configurations based on profiling results.
Q-14). Explain how to implement connection pooling for Cloud Spanner in a Spring Boot application.

A-14). Implementing connection pooling for Cloud Spanner in a Spring Boot application is essential for optimizing performance and resource utilization. The solutions are provided using Maven as the building tool and using application.properties file. The steps are provided below:

  • Add Dependencies – Include the necessary dependencies for Cloud Spanner and connection pooling in your pom.xml file if you are using Maven.
<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-spanner</artifactId>
    <version>latest-version</version>
</dependency>
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>latest-version</version>
</dependency>
  • Configure DataSource – Create a configuration class to set up the DataSource with HikariCP connection pooling.
import com.google.auth.oauth2.GoogleCredentials;
import com.google.cloud.spanner.Spanner;
import com.google.cloud.spanner.SpannerOptions;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.io.IOException;

@Configuration
public class SpannerConfig {

    @Value("${spring.cloud.gcp.spanner.project-id}")
    private String projectId;

    @Value("${spring.cloud.gcp.spanner.instance-id}")
    private String instanceId;

    @Value("${spring.cloud.gcp.spanner.database-id}")
    private String databaseId;

    @Bean
    public DataSource dataSource() throws IOException {
        SpannerOptions options = SpannerOptions.newBuilder()
                .setProjectId(projectId)
                .setCredentials(GoogleCredentials.getApplicationDefault())
                .build();

        Spanner spanner = options.getService();

        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(String.format("jdbc:cloudspanner:/projects/%s/instances/%s/databases/%s",
                projectId, instanceId, databaseId));
        config.setUsername("root");
        config.setPassword(""); // Spanner does not require a password, but HikariCP requires this field.
        config.setMaximumPoolSize(10); // Set your desired pool size
        config.setMinimumIdle(5);
        config.setIdleTimeout(30000);
        config.setMaxLifetime(1800000);
        config.setConnectionTimeout(30000);

        return new HikariDataSource(config);
    }
}
  • Configure Application Properties – Define the Cloud Spanner and HikariCP properties in the application.properties file.
spring.cloud.gcp.spanner.project-id=your-project-id
spring.cloud.gcp.spanner.instance-id=your-instance-id
spring.cloud.gcp.spanner.database-id=your-database-id

# HikariCP settings
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=30000
spring.datasource.hikari.max-lifetime=1800000
spring.datasource.hikari.connection-timeout=30000
  • Use DataSource in Repositories – Ensure that your Spring Data repositories or JDBC templates use the configured DataSource.
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

@Repository
public class UserRepository {

    private final JdbcTemplate jdbcTemplate;

    @Autowired
    public UserRepository(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    // Your repository methods here
}

Example of CRUD Operations

  • Create a User
public void createUser(String userId, String name) {
    String sql = "INSERT INTO Users (user_id, name) VALUES (?, ?)";
    jdbcTemplate.update(sql, userId, name);
}
  • Read a User
public User getUserById(String userId) {
    String sql = "SELECT * FROM Users WHERE user_id = ?";
    return jdbcTemplate.queryForObject(sql, new Object[]{userId}, (rs, rowNum) ->
            new User(rs.getString("user_id"), rs.getString("name")));
}
  • Update a User
public void updateUser(String userId, String newName) {
    String sql = "UPDATE Users SET name = ? WHERE user_id = ?";
    jdbcTemplate.update(sql, newName, userId);
}
  • Delete a User
public void deleteUser(String userId) {
    String sql = "DELETE FROM Users WHERE user_id = ?";
    jdbcTemplate.update(sql, userId);
}
Q-15). How do you handle distributed transactions in Cloud Spanner with Spring Boot?

A-15). Handling distributed transactions in Cloud Spanner with Spring Boot involves coordinating multiple operations across different databases or services in a way that ensures consistency and reliability. The solutions are provided using Maven as the building tool and using application.properties file. The steps are provided below:

  • Add Dependencies – Include the necessary dependencies for Spring Data Cloud Spanner in your pom.xml.
<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-spanner</artifactId>
    <version>latest-version</version>
</dependency>
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
    <version>latest-version</version>
</dependency>
  • Configure Cloud Spanner – Set up your Cloud Spanner configuration in application.properties or application.yml.
spring.cloud.gcp.spanner.project-id=your-project-id
spring.cloud.gcp.spanner.instance-id=your-instance-id
spring.cloud.gcp.spanner.database-id=your-database-id
spring.cloud.gcp.spanner.credentials.location=path-to-your-service-account-key.json
  • Enable Transaction Management – Enable transaction management in your Spring Boot application by adding @EnableTransactionManagement in a configuration class.
import org.springframework.context.annotation.Configuration;
import org.springframework.data.transaction.ChainedTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
public class TransactionConfig {

    // Define other bean configurations if necessary

}
  • Define Transactional Methods – Use the @Transactional annotation to define methods that should be executed within a transaction. Spring will handle the transaction management.
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class UserService {

    private final UserRepository userRepository;

    public UserService(UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @Transactional
    public void createUserAndAccount(User user, Account account) {
        userRepository.save(user);
        accountRepository.save(account);
    }
}
  • Handling Distributed Transactions – For distributed transactions that involve multiple resources, you may need a transaction manager that supports multiple data sources.

Example using ChainedTransactionManager

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.transaction.ChainedTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
public class TransactionConfig {

    @Bean
    public PlatformTransactionManager transactionManager(
            PlatformTransactionManager... transactionManagers) {
        return new ChainedTransactionManager(transactionManagers);
    }
}

Best Practices

  • Idempotency – Ensure that operations are idempotent to handle retries gracefully. This is crucial for distributed systems where network issues can cause retries.
  • Optimistic Concurrency Control – Use optimistic concurrency control provided by Cloud Spanner to handle concurrent updates gracefully.
public void updateAccountBalance(String accountId, BigDecimal amount) {
    Account account = accountRepository.findById(accountId).orElseThrow();
    BigDecimal newBalance = account.getBalance().add(amount);
    account.setBalance(newBalance);
    accountRepository.save(account);
}
  • Compensating Transactions – For long-running transactions, consider implementing compensating transactions to undo operations in case of failures.
  • Handling Rollbacks – Spring Boot and Spring Data provide robust support for rolling back transactions in case of exceptions. By default, any runtime exception will trigger a rollback.
@Transactional
public void transferFunds(String fromAccountId, String toAccountId, BigDecimal amount) {
    Account fromAccount = accountRepository.findById(fromAccountId).orElseThrow();
    Account toAccount = accountRepository.findById(toAccountId).orElseThrow();

    if (fromAccount.getBalance().compareTo(amount) < 0) {
        throw new InsufficientFundsException("Insufficient funds in the account.");
    }

    fromAccount.setBalance(fromAccount.getBalance().subtract(amount));
    toAccount.setBalance(toAccount.getBalance().add(amount));

    accountRepository.save(fromAccount);
    accountRepository.save(toAccount);
}
Q-16). Describe how to use Cloud Spanner’s query execution plans to optimize performance.

A-16). Please find below the detailed guide on how to use Cloud Spanner’s query execution plans to optimize performance:

  • Understanding Query Execution Plans – A query execution plan shows the steps Cloud Spanner takes to execute a SQL query. It includes details about scans, joins, sorts, and other operations.
  • Generating Query Execution Plans – To view a query execution plan, use the EXPLAIN statement followed by your SQL query.
EXPLAIN SELECT * FROM Users WHERE email = '[email protected]';
  • Interpreting the Execution Plan – The execution plan output includes several columns, such as Id, Kind, Detail, Start, End, Rows, and Bytes.
    • Scans – Shows how tables or indexes are scanned.
    • Joins – Indicates how tables are joined.
    • Filters – Displays any filtering operations.
    • Sorts – Indicates if sorting is performed.
    • Aggregations – Shows any aggregate functions used.
  • Identifying Performance Bottlenecks – Look for full table scans, large numbers of rows processed, or expensive join operations. Note any operations that indicate inefficiencies, such as scanning more rows than necessary or performing multiple joins without indexes.
  • Optimizing Queries Based on Execution Plans
    • Using Indexes – Create indexes on columns frequently used in WHERE clauses, joins, and sorts to speed up data retrieval.
      • CREATE INDEX UsersByEmail ON Users(email);
      • Verify the use of indexes in your query execution plan to ensure they are being utilized.
      • EXPLAIN SELECT * FROM Users WHERE email = '[email protected]';
    • Optimizing Joins – Ensure that joins are performed on indexed columns to reduce the cost of join operations.
      • SELECT * FROM Orders JOIN Users ON Orders.user_id = Users.user_id WHERE Users.email = '[email protected]';
    • Using Covering Indexes – Create covering indexes that include all columns required by the query to avoid additional table lookups.
      • CREATE INDEX UsersByEmailAndName ON Users(email, name);
    • Reducing Data Transfer – Select only the necessary columns instead of using SELECT *.
    • Optimizing Aggregations – Use appropriate indexes to optimize aggregate functions and reduce the amount of data processed.
      • CREATE INDEX OrdersByUserId ON Orders(user_id);
    • Query Partitioning – For large datasets, consider partitioning queries to process smaller chunks of data.
      • SELECT * FROM Users WHERE user_id BETWEEN 1 AND 1000;
  • Monitoring Query Performance – Regularly monitor query performance using Cloud Spanner’s monitoring tools and adjust indexes or queries as needed based on changes in data distribution or query patterns.

USE CASE

Scenario – You have a query that fetches user details based on email and performs poorly.

Original Query

SELECT * FROM Users WHERE email = '[email protected]';
  • Analyze Execution Plan
EXPLAIN SELECT * FROM Users WHERE email = '[email protected]';

Output

+----+-------------+----------------------------+------------+-----------+------+--------+
| Id | Kind        | Detail                     | Start      | End       | Rows | Bytes  |
+----+-------------+----------------------------+------------+-----------+------+--------+
|  1 | Distributed |                            |            |           |      |        |
|  2 | Parallel    |                            |            |           |      |        |
|  3 | Serialize   |                            |            |           |      |        |
|  4 | Scan        | Table: Users               |            |           |      |        |
|  5 | Filter      | Condition: email = '[email protected]' | |       | |    |        |
+----+-------------+----------------------------+------------+-----------+------+--------+
  • Optimize with Index
CREATE INDEX UsersByEmail ON Users(email);
  • Verify Optimization
EXPLAIN SELECT * FROM Users WHERE email = '[email protected]';

Optimized Output

+----+-------------+----------------------------+------------+-----------+------+--------+
| Id | Kind        | Detail                     | Start      | End       | Rows | Bytes  |
+----+-------------+----------------------------+------------+-----------+------+--------+
|  1 | Distributed |                            |            |           |      |        |
|  2 | Parallel    |                            |            |           |      |        |
|  3 | Serialize   |                            |            |           |      |        |
|  4 | Scan        | Index: UsersByEmail        |            |           |      |        |
+----+-------------+----------------------------+------------+-----------+------+--------
Q-17). What are some best practices for monitoring and logging when integrating Cloud Spanner with Spring Boot?

A-16). The best practices for monitoring and logging when integrating Cloud Spanner with Spring Boot are as follows:

Monitoring best practices:

  • Use Cloud Monitoring (formerly Stackdriver)
  • Set Up Alerts
  • Monitor Query Performance
  • Use Tracing
  • Analyze Database Statistics
  • Monitor Connection Pooling

Logging best practices

  • Structured Logging in JSON format
  • Use Google Cloud Logging
  • Error and Exception Logging
  • Application Logs
  • Audit Logs
  • Log Levels
  • Log Rotation and Retention

Monitoring Configuration with Spring Boot and Google Cloud Monitoring

import com.google.cloud.spring.autoconfigure.metrics.StackdriverMetricsExportAutoConfiguration;
import io.micrometer.core.instrument.MeterRegistry;
import io.micrometer.core.instrument.Metrics;
import io.micrometer.core.instrument.config.MeterFilter;
import io.micrometer.core.instrument.logging.LoggingMeterRegistry;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MonitoringConfig {

    @Bean
    public MeterRegistry loggingMeterRegistry() {
        LoggingMeterRegistry loggingMeterRegistry = new LoggingMeterRegistry();
        Metrics.addRegistry(loggingMeterRegistry);
        return loggingMeterRegistry;
    }

    @Bean
    public StackdriverMetricsExportAutoConfiguration stackdriverMetricsExportAutoConfiguration() {
        return new StackdriverMetricsExportAutoConfiguration();
    }
}

Logging Configuration with Spring Boot and Google Cloud Logging

application.properties

<!-- Add these dependencies to your pom.xml -->
<dependency>
    <groupId>com.google.cloud</groupId>
    <artifactId>google-cloud-logging</artifactId>
    <version>latest-version</version>
</dependency>
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter-logging</artifactId>
    <version>latest-version</version>
</dependency>

application.yml

# application.yml
logging:
  level:
    root: INFO
    com.yourcompany: DEBUG
  loggers:
    com.google.cloud.logging: ERROR
Q-17). Design a disaster recovery strategy for a Spring Boot application using Cloud Spanner.
A-17). Designing a disaster recovery (DR) strategy for a Spring Boot application using Cloud Spanner involves ensuring data availability, integrity, and application continuity in the event of failures. Here’s a comprehensive approach to creating an effective DR strategy:
1. Disaster Recovery Strategy
  • Data Backup and Restore – Automated Backups: Enable automated backups for Cloud Spanner databases to ensure that data can be restored in case of data loss or corruption.
gcloud spanner databases backups create my-backup \
  --instance=my-instance \
  --database=my-database \
  --retention-period=7d
  • Data Backup and Restore
    • Automated Backups: Enable automated backups for Cloud Spanner databases to ensure that data can be restored in case of data loss or corruption.
      • gcloud spanner databases backups create my-backup \
        --instance=my-instance \
        --database=my-database \
        --retention-period=7d
    • Backup Scheduling – Schedule regular backups (e.g., daily) to minimize data loss. Store backups in a different region for added redundancy.
    • Manual Backups – Perform manual backups before major changes or maintenance activities.
    • Backup Retention – Define a retention policy to keep backups for a specific period (e.g., 30 days) and automate the deletion of old backups.
  • High Availability Configuration
    • Regional and Multi-Regional Instances – Use multi-regional instances to ensure high availability and automatic failover across different geographic locations.
      • gcloud spanner instances create my-instance \
        --config=nam6 \
        --nodes=10 \
        --description="Multi-regional instance"
    • Replication – Leverage Cloud Spanner’s built-in synchronous replication to maintain multiple replicas of your data.
  • Application Redundancy
    • Multiple Regions – Deploy Spring Boot application instances in multiple regions to ensure availability even if one region goes down.
    • Load Balancing – Use a global load balancer (e.g., Google Cloud Load Balancing) to distribute traffic across multiple instances and regions.
    • Auto-Scaling – Configure auto-scaling to handle varying loads and ensure that sufficient resources are available during traffic spikes or failover.
  • Database Migration and Schema Changes
    • Schema Management Tools – Use tools like Flyway or Liquibase to manage database schema changes and migrations in a controlled manner.
    • Version Control –Monitoring and Alerts Version control your schema changes and apply them in a phased approach to avoid disruptions.
  • Monitoring and Alerts
    • Cloud Monitoring – Set up comprehensive monitoring using Google Cloud Monitoring to track metrics such as CPU usage, query latency, and error rates.
    • Custom Metrics – Define and monitor custom application-specific metrics.
    • Alerts – Configure alerts for critical metrics to detect and respond to issues promptly.
  • Disaster Recovery Testing
    • Regular Drills – Conduct regular DR drills to test the effectiveness of your backup and recovery processes. Simulate different failure scenarios to ensure preparedness.
    • Documentation – Maintain detailed DR documentation, including recovery procedures, contact information, and roles/responsibilities.
  • Failover Procedures
    • Automated Failover – Implement automated failover mechanisms to switch traffic to healthy instances or regions without manual intervention.
    • Manual Failover – Define clear manual failover procedures in case automated systems fail. Ensure that the team is trained on these procedures.
  • Data Integrity and Consistency
    • Consistency Checks – Regularly perform consistency checks to ensure that data across replicas is synchronized and accurate.
    • Data Validation – Implement data validation mechanisms in your application to detect and handle data anomalies.
  • Security
    • Access Control – Implement strict access control policies to protect your Cloud Spanner database and application instances.
    • Encryption – Ensure that data is encrypted at rest and in transit to protect against unauthorized access.
    • Audit Logging – Enable audit logging to track access and modifications to the database

Example Configuration for a Multi-Regional Setup

  • Cloud Spanner Configuration
gcloud spanner instances create my-instance \
    --config=nam6 \
    --nodes=10 \
    --description="Multi-regional instance"
  • Spring Boot Configuration (application.properties)
# Cloud Spanner configuration
spring.cloud.gcp.spanner.project-id=my-project-id
spring.cloud.gcp.spanner.instance-id=my-instance
spring.cloud.gcp.spanner.database-id=my-database
spring.cloud.gcp.spanner.credentials.location=classpath:my-service-account-key.json

# Application deployment
server.port=8080

# Health check endpoint
management.endpoints.web.exposure.include=health
management.endpoint.health.show-details=always

# Load Balancing
spring.cloud.loadbalancer.ribbon.enabled=true
Q-18). Explain how to implement multi-region replication in Cloud Spanner for high availability and discuss its impact on the Spring Boot application.

A-18). Implementing multi-region replication in Cloud Spanner ensures high availability and resilience for your data, even in the event of regional failures. Here’s a detailed explanation of how to set up multi-region replication and its impact on a Spring Boot application:

Setting Up Multi-Region Replication in Cloud Spanner

  • Create a Multi-Regional Instance
    • gcloud spanner instances create my-instance \
      --config=nam6 \
      --nodes=10 \
      --description="Multi-regional instance"
    • nam6 is an example configuration for North America. Other configurations are available for different geographic regions.
    • --nodes=10 specifies the number of compute nodes. Adjust this based on your workload requirements.
  • Define a Database – After creating the instance, create your database within this instance.
    • gcloud spanner databases create my-database \
      --instance=my-instance

Impact on the Spring Boot Application

  • Configuration Changes – Update your Spring Boot application to connect to the new multi-regional instance.

application.properties

spring.cloud.gcp.spanner.project-id=my-project-id
spring.cloud.gcp.spanner.instance-id=my-instance
spring.cloud.gcp.spanner.database-id=my-database
spring.cloud.gcp.spanner.credentials.location=classpath:my-service-account-key.json

High Availability – With multi-region replication, Cloud Spanner provides high availability by ensuring that:

  • Automatic Failover – If one region goes down, Cloud Spanner automatically fails over to another region without manual intervention.
  • Read Availability – Reads can be served from any region, ensuring low-latency access for users in different geographic locations.

Impact on Writes

  • Latency – Write operations in a multi-region setup might experience higher latency due to the coordination required for consensus across regions.
  • Transaction Commit Latency – Cloud Spanner uses a distributed consensus protocol (Paxos) to commit transactions, which can slightly increase the commit latency.

Application Design Considerations

  • Resilient Design – Ensure that your application is designed to handle transient latency spikes and possible regional failover.
  • Read-Your-Writes Consistency – Be aware that in a multi-region setup, achieving read-your-writes consistency might introduce additional latency. Design your application logic accordingly.
  • Retry Logic – Implement robust retry logic for database operations to handle transient errors and failovers gracefully.

Example Spring Boot Configuration with Multi-Region Support

  • Dependency Configuration – Ensure you have the necessary dependencies in your pom.xml for Spring Cloud GCP.
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
    <version>2.0.3.RELEASE</version>
</dependency>
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
  • Spring Boot Application Properties – Update your application.properties file with Cloud Spanner configuration details.
# Cloud Spanner configuration
spring.cloud.gcp.spanner.project-id=my-project-id
spring.cloud.gcp.spanner.instance-id=my-instance
spring.cloud.gcp.spanner.database-id=my-database
spring.cloud.gcp.spanner.credentials.location=classpath:my-service-account-key.json

# Connection pool configuration (optional)
spring.datasource.hikari.maximum-pool-size=20
spring.datasource.hikari.minimum-idle=5
spring.datasource.hikari.idle-timeout=60000
spring.datasource.hikari.connection-timeout=30000

Monitoring and Maintenance

  • Monitoring – Use Google Cloud Monitoring to track the health and performance of your Cloud Spanner instance.
    • Monitor metrics such as CPU utilization, read/write latencies, and replication lag.
    • Set up alerts for critical metrics to ensure timely intervention if issues arise.
  • Regular Maintenance
    • Regularly review and adjust the number of nodes in your Cloud Spanner instance based on workload patterns.
    • Keep your application dependencies and Google Cloud SDK up to date to leverage the latest features and improvements.
Q-19). Discuss the trade-offs between consistency and latency in Cloud Spanner and how to balance them in a real-world Spring Boot application.

A-19). Balancing consistency and latency in Cloud Spanner involves understanding the trade-offs between strong and eventual consistency and making design decisions that align with your application’s requirements. Here’s a detailed discussion of these trade-offs and how to balance them in a real-world Spring Boot application:

Consistency Models in Cloud Spanner

  • Strong Consistency
    • Definition – Ensures that once a write is acknowledged, all subsequent reads will reflect that write. This is the default consistency model in Cloud Spanner.
    • Benefits – Guarantees up-to-date data, making it suitable for applications where accuracy and data integrity are critical.
    • Drawbacks – Higher latency due to the need for global consensus among replicas, especially in a multi-region setup.
  • Eventual Consistency
    • Definition – Ensures that, given enough time, all replicas will converge to the same value, but there is no guarantee of immediate consistency after a write.
    • Benefits – Lower latency as it allows reads from nearby replicas without waiting for global consensus.
    • Drawbacks – Reads might return stale data, which could be problematic for applications requiring up-to-date information.
  • Balancing Consistency and Latency – In a real-world Spring Boot application, you can balance consistency and latency by adopting strategies that fit your use case:
    • Choosing the Right Consistency Model
      • Use Strong Consistency
        • For transactions involving critical financial data, inventory systems, or any other domain where immediate consistency is crucial.
        • Example: A banking application where account balances must be accurate after each transaction.
      • Use Eventual Consistency
        • For scenarios where lower latency is more critical than immediate consistency, such as social media feeds, product recommendations, or analytics dashboards.
        • Example: A social media application where user posts can be eventually consistent but need to be served quickly.
    • Optimizing Read Operations
      • Read-Only Transactions – Use read-only transactions with strong consistency when performing critical reads that require up-to-date data.
        • ReadContext readContext = spanner.getDatabaseClient(DatabaseId.of("my-project-id", "my-instance", "my-database")).singleUse();
          ResultSet resultSet = readContext.executeQuery(Statement.of("SELECT * FROM Users WHERE email = '[email protected]'"));
      • Stale Reads – Use bounded staleness for read operations where eventual consistency is acceptable, reducing read latency.
        • ReadContext readContext = spanner.getDatabaseClient(DatabaseId.of("my-project-id", "my-instance", "my-database")).singleUse(TimestampBound.ofExactStaleness(10, TimeUnit.SECONDS));
          ResultSet resultSet = readContext.executeQuery(Statement.of("SELECT * FROM Users WHERE email = '[email protected]'"));
    • Caching Strategy
      • In-Memory Caching – Use in-memory caching (e.g., Redis, Caffeine) to reduce latency for frequently accessed data.
        • Cache data that can tolerate eventual consistency.
        • Example: Caching product catalog information which is frequently read but infrequently updated.
    • Batching and Asynchronous Processing
      • Batch Writes – Batch multiple write operations into a single transaction to reduce the overhead of global consensus.
      • Asynchronous Processing: Use asynchronous processing for non-critical updates to improve application responsiveness.
        • @Async
          public void updateNonCriticalData(User user) {
          userRepository.save(user);
          }
    • Monitoring and Alerts
      • Performance Monitoring – Continuously monitor latency and consistency metrics using Google Cloud Monitoring to identify and address performance bottlenecks.
      • Adaptive Tuning – Adjust consistency settings based on observed application performance and user feedback.

Example Implementation in Spring Boot

  • Application Configuration – Configure the Spanner client for both strong and eventual consistency use cases.
spring.cloud.gcp.spanner.project-id=my-project-id
spring.cloud.gcp.spanner.instance-id=my-instance
spring.cloud.gcp.spanner.database-id=my-database
spring.cloud.gcp.spanner.credentials.location=classpath:my-service-account-key.json
  • Service Layer Implementation – Implement service methods to use strong and eventual consistency as needed.
@Service
public class UserService {

    @Autowired
    private SpannerTemplate spannerTemplate;

    // Method for critical reads with strong consistency
    public User getUserByEmailStrongConsistency(String email) {
        ReadContext readContext = spannerTemplate.getDatabaseClient().singleUse();
        Statement statement = Statement.of("SELECT * FROM Users WHERE email = @email")
            .bind("email").to(email);
        ResultSet resultSet = readContext.executeQuery(statement);
        return mapToUser(resultSet);
    }

    // Method for non-critical reads with eventual consistency
    public User getUserByEmailEventualConsistency(String email) {
        ReadContext readContext = spannerTemplate.getDatabaseClient()
            .singleUse(TimestampBound.ofExactStaleness(10, TimeUnit.SECONDS));
        Statement statement = Statement.of("SELECT * FROM Users WHERE email = @email")
            .bind("email").to(email);
        ResultSet resultSet = readContext.executeQuery(statement);
        return mapToUser(resultSet);
    }

    private User mapToUser(ResultSet resultSet) {
        // Mapping logic here
    }
}
Q-20). How would you handle schema evolution in a live, high-traffic Spring Boot application using Cloud Spanner?

A-20). Handling schema evolution in a live, high-traffic Spring Boot application using Cloud Spanner involves careful planning and execution to avoid downtime and ensure data consistency. Here are the best practices and steps for managing schema changes smoothly:

The best practices one should follow for the schema evolution

  • Backward Compatibility – Ensure that schema changes are backward compatible so that the existing application can continue to function while new changes are being rolled out.
  • Zero Downtime – Implement strategies that allow schema changes to be applied without taking the application offline.
  • Incremental Changes – Apply schema changes incrementally to minimize risk and simplify rollback if necessary.
  • Comprehensive Testing – Test schema changes in a staging environment that mirrors the production setup before applying them to the live system.
  • Automated Migrations – Use database migration tools such as Flyway or Liquibase to automate and version control schema changes.

Steps for Schema Evolution

  • Plan the Schema Changes
    • Identify the schema changes required (e.g., adding a new column, modifying an existing column, adding an index).
    • Ensure that changes are backward compatible. For example, if adding a new column, make sure it has a default value or allows nulls.
  • Test Changes in a Staging Environment
    • Create a staging environment that mirrors the production environment.
    • Apply the schema changes and run comprehensive tests to ensure the application functions correctly with the new schema.
  • Automate the Migration Process
    • Use Flyway or Liquibase for managing schema changes. These tools provide a way to version control and automate database migrations.
    • Create migration scripts for the schema changes.

Example Using Flyway:

  • Add Flyway dependency to your pom.xml:
    • <dependency>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-core</artifactId>
      </dependency>
      <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
      </dependency>
      <dependency>
      <groupId>org.springframework.cloud</groupId>
      <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
      </dependency>
  • Configure Flyway in application.properties:
    • spring.flyway.enabled=true
      spring.flyway.locations=classpath:db/migration
      spring.cloud.gcp.spanner.project-id=my-project-id
      spring.cloud.gcp.spanner.instance-id=my-instance
      spring.cloud.gcp.spanner.database-id=my-database
      spring.cloud.gcp.spanner.credentials.location=classpath:my-service-account-key.json
  • Create migration scripts in src/main/resources/db/migration/:
    • -- V1__Add_new_column.sql
      ALTER TABLE Users ADD COLUMN new_column STRING(256);

      -- V2__Modify_existing_column.sql
      ALTER TABLE Users ALTER COLUMN existing_column STRING(512);
  • Apply Changes with Zero Downtime
    • Deploy the Application in Stages – Use a rolling deployment strategy to gradually deploy the new version of the application that supports the new schema.
    • Apply Schema Changes – Run the migration scripts to apply schema changes to the database.
  • Monitor and Verify
    • Monitor – Use Google Cloud Monitoring to track the performance and health of the application and database during and after the migration.
    • Verify – Ensure that the application is functioning correctly with the new schema. Run automated tests and manual checks if necessary.

Handling Common Scenarios

  • Adding a New Column
    • Backward Compatibility – Add the new column with a default value or allow nulls.
    • Migration Script –
      • ALTER TABLE Users ADD COLUMN new_column STRING(256) DEFAULT 'default_value';
  • Modifying an Existing Column
    • Two-Step Process
      • Add a new column with the desired schema.
      • Migrate data from the old column to the new column.
      • Update the application to use the new column.
      • Drop the old column after ensuring all reads and writes are using the new column.
        • ALTER TABLE Users ADD COLUMN new_column STRING(512);
          UPDATE Users SET new_column = existing_column;
  • Adding an Index
    • Create the Index – Create the new index without affecting the existing application operations.
    • Migration Script –
      • CREATE INDEX UsersByNewColumn ON Users(new_column);
Q-21). Explain the considerations and steps for migrating a large-scale, on-premises database to Cloud Spanner with minimal downtime.

A-21). Migrating a large-scale, on-premises database to Cloud Spanner with minimal downtime is a complex process that requires careful planning, thorough testing, and strategic execution. Here are the key considerations and steps for achieving a successful migration:

Key Considerations

  • Database Compatibility – Ensure that the schema and features of the on-premises database are compatible with Cloud Spanner.
  • Data Volume – Assess the size of the database to determine the best approach for data transfer.
  • Application Impact – Evaluate how the migration will impact the existing applications and plan for necessary changes.
  • Downtime Tolerance – Determine the acceptable level of downtime and plan the migration strategy accordingly.
  • Data Consistency – Ensure that data remains consistent and accurate throughout the migration process.
  • Testing and Validation – Thoroughly test the migration process in a staging environment before executing it in production.

Migration Steps

  • Planning and Assessment
    • Schema Assessment – Analyze the existing database schema and determine necessary changes to align with Cloud Spanner’s schema requirements.
    • Data Volume Assessment – Estimate the total data volume and the rate of data change (writes per second) to choose the appropriate data transfer method.
    • Downtime Requirements – Define acceptable downtime and plan for strategies like near-zero downtime migration if required.
  • Set Up Cloud Spanner Environment
    • Create Cloud Spanner Instance – Set up a Cloud Spanner instance and configure it according to your performance and capacity needs.
      • gcloud spanner instances create my-instance \
        --config=regional-us-central1 \
        --description="Production instance" \
        --nodes=10
    • Create Database and Schema – Create the database and apply the schema in Cloud Spanner.
      • gcloud spanner databases create my-database --instance=my-instance
        gcloud spanner databases ddl update my-database --instance=my-instance --ddl='
        CREATE TABLE Users (
        UserId STRING(36) NOT NULL,
        UserName STRING(256),
        UserEmail STRING(256),
        PRIMARY KEY (UserId)
        )'
  • Data Migration Strategy
    • Initial Data Load – Use a bulk data transfer method to perform the initial data load from the on-premises database to Cloud Spanner.
      • Google Cloud Dataflow – Use Dataflow for large-scale data migration. Dataflow can read from various sources like on-premises databases and write to Cloud Spanner.
      • Custom ETL Scripts – Write custom scripts to extract, transform, and load (ETL) data from the on-premises database to Cloud Spanner.
      • Batch Transfers – For very large datasets, consider transferring data in batches to manage load and performance.
    • Example Dataflow Job – Here’s an example of how you might configure a Dataflow job to migrate data to Cloud Spanner.
      • Pipeline p = Pipeline.create(options);
        PCollection<TableRow> rows = p.apply("ReadFromSource", ...)
        .apply("TransformData", ...);
        rows.apply("WriteToSpanner", SpannerIO.write()
        .withInstanceId("my-instance")
        .withDatabaseId("my-database")
        .withProjectId("my-project")
        .withSchema(schema));
        p.run().waitUntilFinish();
  • Ongoing Data Synchronization
    • Change Data Capture (CDC) – Implement CDC to capture changes in the on-premises database and apply them to Cloud Spanner. Tools like Debezium can help with this.
    • Near Real-Time Sync – Set up a near real-time synchronization process to keep the Cloud Spanner database in sync with the on-premises database until the final cutover.
      • Stream Processing – Use tools like Apache Kafka and Dataflow to stream changes from the on-premises database to Cloud Spanner.
      • Periodic Batches – If real-time sync is not feasible, use periodic batch updates to apply changes incrementally.
  • Testing and Validation
    • Data Integrity Checks – Validate the data in Cloud Spanner against the on-premises database to ensure consistency.
    • Performance Testing – Test the performance of the Cloud Spanner database under expected load conditions.
    • Application Testing – Verify that the application works correctly with Cloud Spanner in a staging environment.
  • Final Cutover
    • Minimal Downtime Cutover – Plan the final cutover to minimize downtime. This typically involves a short window where the application is taken offline to apply the last set of changes.
    • Switch Traffic – Update the application configuration to point to Cloud Spanner and switch traffic to the new database.
    • Monitor and Validate – Monitor the application and database performance closely after the cutover to ensure everything is functioning correctly.
  • Post-Migration Activities
    • Monitoring and Alerts – Set up monitoring and alerting for Cloud Spanner using Google Cloud Monitoring.
    • Backup and Recovery – Implement a backup strategy to ensure data recovery in case of issues.
    • Optimization – Optimize queries and indexes in Cloud Spanner based on the new workload.

Example Spring Boot Configuration

  • Dependencies – Add Cloud Spanner dependencies to your pom.xml.
<dependency>
    <groupId>org.springframework.cloud</groupId>
    <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
    <version>2.0.3.RELEASE</version>
</dependency>
  • Application Properties – Configure Cloud Spanner connection settings in application.properties.
spring.cloud.gcp.spanner.project-id=my-project-id
spring.cloud.gcp.spanner.instance-id=my-instance
spring.cloud.gcp.spanner.database-id=my-database
spring.cloud.gcp.spanner.credentials.location=classpath:my-service-account-key.json
  • Repository Layer – Create a repository interface to interact with Cloud Spanner.
@Repository
public interface UserRepository extends SpannerRepository<User, String> {
}
Q-22). How do you integrate Cloud Spanner with other Google Cloud services (e.g., BigQuery, Dataflow) in a Spring Boot application?

A-22). Integration of Cloud Spanner with BigQuery, the steps are provided below:

Use Case

  • Running complex analytics queries on transactional data stored in Cloud Spanner.
  • Aggregating data from multiple sources for comprehensive analysis.

Steps

  • Set Up Cloud Spanner and BigQuery
    • Create Cloud Spanner Instance and Database
      • gcloud spanner instances create my-instance --config=regional-us-central1 --description="Test Instance" --nodes=1
        gcloud spanner databases create my-database --instance=my-instance
    • Create a BigQuery Dataset
      • bq mk my_dataset
  • Export Data from Cloud Spanner to BigQuery – Use Dataflow to export data from Cloud Spanner to BigQuery.
    • Create a Dataflow Job
      • Pipeline p = Pipeline.create(options);

        SpannerConfig spannerConfig = SpannerConfig.create()
        .withInstanceId("my-instance")
        .withDatabaseId("my-database");

        PCollection<Struct> spannerRecords = p.apply(
        "ReadFromSpanner",
        SpannerIO.read()
        .withSpannerConfig(spannerConfig)
        .withQuery("SELECT * FROM my_table")
        );

        spannerRecords.apply(
        "WriteToBigQuery",
        BigQueryIO.writeTableRows()
        .to("my-project:my_dataset.my_table")
        .withSchema(schema)
        .withWriteDisposition(BigQueryIO.Write.WriteDisposition.WRITE_TRUNCATE)
        );

        p.run().waitUntilFinish();
    • Spring Boot Configuration
      • Add necessary dependencies in pom.xml:
        • <dependency>
          <groupId>org.springframework.cloud</groupId>
          <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
          <version>2.0.3.RELEASE</version>
          </dependency>
          <dependency>
          <groupId>org.springframework.cloud</groupId>
          <artifactId>spring-cloud-gcp-starter-dataflow</artifactId>
          <version>2.0.3.RELEASE</version>
          </dependency>
          <dependency>
          <groupId>com.google.cloud</groupId>
          <artifactId>google-cloud-bigquery</artifactId>
          <version>1.126.2</version>
          </dependency>
    • Configure properties in application.properties
      • spring.cloud.gcp.spanner.project-id=my-project-id
        spring.cloud.gcp.spanner.instance-id=my-instance
        spring.cloud.gcp.spanner.database-id=my-database
        spring.cloud.gcp.spanner.credentials.location=classpath:my-service-account-key.json

        spring.cloud.gcp.bigquery.project-id=my-project-id
        spring.cloud.gcp.bigquery.dataset-name=my_dataset
        spring.cloud.gcp.bigquery.credentials.location=classpath:my-service-account-key.json
  • Integrating Cloud Spanner with Dataflow – Google Cloud Dataflow is a fully managed service for stream and batch processing. It enables you to build data pipelines to extract, transform, and load data from Cloud Spanner to other services or storage.

Use Case

  • Moving data between Cloud Spanner and other storage services.
  • Transforming and cleaning data before loading it into another system.

Steps

  • Create a Dataflow Pipeline
    • Pipeline Code Example
      • PipelineOptions options = PipelineOptionsFactory.create();
        Pipeline p = Pipeline.create(options);

        SpannerConfig spannerConfig = SpannerConfig.create()
        .withInstanceId("my-instance")
        .withDatabaseId("my-database");

        PCollection<Struct> spannerRecords = p.apply(
        "ReadFromSpanner",
        SpannerIO.read()
        .withSpannerConfig(spannerConfig)
        .withQuery("SELECT * FROM my_table")
        );

        spannerRecords.apply("TransformData", ParDo.of(new DoFn<Struct, TableRow>() {
        @ProcessElement
        public void processElement(ProcessContext c) {
        Struct record = c.element();
        TableRow row = new TableRow();
        row.set("column1", record.getString("column1"));
        row.set("column2", record.getString("column2"));
        c.output(row);
        }
        })).apply("WriteToBigQuery", BigQueryIO.writeTableRows()
        .to("my-project:my_dataset.my_table")
        .withSchema(schema)
        .withWriteDisposition(BigQueryIO.Write.WriteDisposition.WRITE_TRUNCATE)
        );

        p.run().waitUntilFinish();
    • Spring Boot Configuration – Add necessary dependencies in pom.xml
      • <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-gcp-starter-data-spanner</artifactId>
        <version>2.0.3.RELEASE</version>
        </dependency>
        <dependency>
        <groupId>org.springframework.cloud</groupId>
        <artifactId>spring-cloud-gcp-starter-dataflow</artifactId>
        <version>2.0.3.RELEASE</version>
        </dependency>
    • Configure properties in application.properties
      • spring.cloud.gcp.spanner.project-id=my-project-id
        spring.cloud.gcp.spanner.instance-id=my-instance
        spring.cloud.gcp.spanner.database-id=my-database
        spring.cloud.gcp.spanner.credentials.location=classpath:my-service-account-key.json

Integrating Cloud Spanner with Other Google Cloud Services

Pub/Sub Integration

  • Use Case – Streaming changes from Cloud Spanner to other systems.
  • Steps
    • Create a Pub/Sub Topic
      • gcloud pubsub topics create my-topic
    • Set Up Dataflow to Read from Pub/Sub and Write to Cloud Spanner
      • PipelineOptions options = PipelineOptionsFactory.create();
        Pipeline p = Pipeline.create(options);

        PCollection<String> messages = p.apply("ReadFromPubSub", PubsubIO.readStrings().fromTopic("projects/my-project/topics/my-topic"));

        messages.apply("WriteToSpanner", ParDo.of(new DoFn<String, Void>() {
        @ProcessElement
        public void processElement(ProcessContext c) {
        String message = c.element();
        // Parse message and write to Spanner
        // Spanner writing logic here
        }
        }));

        p.run().waitUntilFinish();
    • Spring Boot Configuration
      • Add Pub/Sub dependencies in pom.xml
        • <dependency>
          <groupId>org.springframework.cloud</groupId>
          <artifactId>spring-cloud-gcp-starter-pubsub</artifactId>
          <version>2.0.3.RELEASE</version>
          </dependency>
      • Configure properties in application.properties
        • spring.cloud.gcp.pubsub.project-id=my-project-id
          spring.cloud.gcp.pubsub.credentials.location=classpath:my-service-account-key.json