Deploying and implementing data solutions

Study guide for Associate Cloud Engineer, Unit 3 · Topic 4. This is the topic's lecture in reading form — every slide's teaching, figures and worked examples, in order — followed by the official Google Cloud pages its claims rest on.

What the exam guide asks, quoted. Deploying and implementing data solutions. Considerations include:

1. Deploying data products (e.g., Cloud SQL, Firestore, BigQuery, Spanner, Pub/Sub, Dataflow, Cloud Storage, AlloyDB)
2. Loading data (e.g., command line upload, load data from Cloud Storage, Storage Transfer Service)

Deploying and implementing data solutions

Bring the product into existence, then get the data into it

Deploying data products — one create command per product, and the handful of settings that cannot be changed once it exists. Loading data — a command-line upload, a load from Cloud Storage, or Storage Transfer Service, chosen by where the data is and how much of it there is.

Unit two chose the data product. This topic builds it and fills it. The guide lists two considerations. The first is deploying data products, with eight examples: Cloud SQL, Firestore, BigQuery, Spanner, Pub/Sub, Dataflow, Cloud Storage and AlloyDB. The second is loading data, with three: a command-line upload, a load from Cloud Storage, and Storage Transfer Service. The exam asks these as tasks — which command creates it, what has to be decided at creation, and which path gets the data in — so every slide here names the command and the decision that cannot be undone.

Deploying data products

One create command per product, and the choices made at creation

• Cloud SQL: gcloud sql instances create — set a root password, and know the default edition
• AlloyDB: create the cluster, then its primary instance; read pools can come later
• Spanner: choose the configuration and the compute capacity at creation
• Firestore, BigQuery, Pub/Sub, Dataflow, Cloud Storage: each has its own create step

Worked example (synthetic). An engineer scripts a new environment. The script creates a Cloud SQL instance, a BigQuery dataset with --location, a Pub/Sub topic and its subscription, and a bucket with a globally unique name — in that order, because the later steps name the earlier resources.

Every product in the first objective is created by its own command, and the exam expects you to know both the command and what it asks you to decide. For Cloud SQL, Google's instruction is plain: use the gcloud sql instances create command to create the instance. Two details matter. Leaving out the edition is itself a decision — if you specify MySQL 8.4 or 9.7, or no version at all, and no edition, the default edition is Enterprise Plus. And the root password: there is an option to set no password, but Google says this isn't recommended for security reasons. AlloyDB for PostgreSQL is created in two steps. First the cluster, with the gcloud alloydb clusters create command; then a primary instance inside it, with the gcloud alloydb instances create command. AlloyDB assigns the password you provide to the postgres database user while creating the cluster, and read pool instances can be added later, whenever needed. Spanner is created with the gcloud spanner instances create command, and it asks two questions up front: the configuration — regional, dual-region or multi-region — and the compute capacity, counted in nodes or processing units, where one node is a thousand processing units. The remaining products each have their own create step, which the next two slides put side by side.

The four databases, and what each asks at creation

The command, and the decision it forces

Worked example (synthetic). A team needs a Spanner instance serving two continents. Choosing multi-region is not enough on its own — that configuration is only available in the Enterprise Plus edition.

Here are the four databases side by side, because the exam likes to swap their commands. Cloud SQL is gcloud sql instances create, and the decisions are the edition and the root password. AlloyDB is the only one of the four created in two steps — gcloud alloydb clusters create, then gcloud alloydb instances create for the primary. Spanner is gcloud spanner instances create, and the configuration is chosen at creation; Google adds one condition worth remembering, that dual-region and multi-region configurations are only available in the Enterprise Plus edition. Firestore is gcloud firestore databases create, and the two things you pass are the location — a Firestore multi-region or region — and the type, either firestore-native for Native mode or datastore-mode for Datastore mode.

BigQuery, Pub/Sub, Dataflow and Cloud Storage

Four more create steps, and two that cannot be undone

Worked example (synthetic). A dataset was created in the United States and the business now needs it in Europe. There is no setting to change — a new dataset must be created in the new location.

The other four products complete the objective. A BigQuery dataset is created with the bq mk command and the location flag, and the location is the decision to get right: after a dataset is created, its location cannot be changed. Pub/Sub needs two resources, not one. Google describes Pub/Sub as systems of publishers and subscribers; publishers send events to the service without regard to how or when they are processed. You create the topic with gcloud pubsub topics create, and a pull subscription with gcloud pubsub subscriptions create — nothing reads from a topic without one. Dataflow is deployed by running a job, and the fastest way is a template: Google provides open source Dataflow templates that you can use instead of writing pipeline code, and the gcloud dataflow jobs run command runs either a custom or a Google-provided template. And a Cloud Storage bucket needs only one thing from you in the console, a globally unique name — but one option, hierarchical namespace, can only be enabled when the bucket is created.

Deploying a streaming pipeline, in order

Each step names a resource the previous step created

Figure: A flow chart of deploying a streaming pipeline: create a Pub/Sub topic, then its subscription; create a BigQuery dataset with a location; run a Dataflow job from a Google-provided template that reads the subscription and writes to the dataset; Dataflow allocates worker virtual machines, and rows land in the dataset.

Worked example (synthetic). A deployment script fails at the Dataflow step because the subscription it names does not exist yet. The order in the figure is the fix: topic, subscription and dataset before the job.

Put the create steps together and a real deployment has an order. The Pub/Sub topic comes first, then the subscription that is attached to it. The BigQuery dataset is created with its location decided. Only then does the Dataflow job run, from a Google-provided template, reading from the subscription and writing to the dataset. When the job runs, Google says the Dataflow service allocates a pool of worker virtual machines to execute the pipeline, and Dataflow's role in general is exactly this shape: read from one or more sources, transform the data, and write it to a destination. The order matters because each step names a resource the previous step created, and a script that runs them out of order fails at the first missing name.

Loading data

Three paths in, chosen by where the data is and how much there is

• Command-line upload: gcloud storage cp, with Storage Object User on the bucket
• From Cloud Storage: bq load into BigQuery, or gcloud sql import sql into Cloud SQL
• Storage Transfer Service: other clouds, on-premises or URLs; built for over 1TiB
• gsutil is legacy — use gcloud storage commands

Worked example (synthetic). A team uploads last night's CSV export with gcloud storage cp, then loads it into a BigQuery table with bq load. The bucket and the dataset were created in the same location, so the load succeeds.

The second objective names three ways to get data in. The first is a command-line upload. Google's instruction is to use the gcloud storage cp command, and to upload you need the Storage Object User role on the bucket. Note the tool: Google says that generally you should use gcloud storage commands instead of gsutil commands, because gsutil is a legacy Cloud Storage command-line interface and minimally maintained. The second path is a load from Cloud Storage into the product that will query it. For BigQuery, use the bq load command, specify the source format, and include a Cloud Storage URI, that is, a uniform resource identifier — a single URI, a comma-separated list, or a URI containing a wildcard. For Cloud SQL, the import reads a dump file from a bucket, which the next slides return to. The third path is Storage Transfer Service, for data that is somewhere else entirely: Google says it can migrate data from other cloud storage providers, on-premises data centers, or HTTP and HTTPS URLs to Cloud Storage, and that it is optimized for transfers involving more than one tebibyte of data.

Four loads, and the condition each one fails on

Know the command, and know what stops it

Worked example (synthetic). A bq load fails although the file exists and the engineer has every role. The bucket is multi-region US and the dataset is in Europe; the rule is location, not permission.

Each load path has one condition the exam likes to test. An upload with gcloud storage cp needs the Storage Object User role on the bucket. A bq load has a location rule: Google says the Cloud Storage bucket must be in the same location as the dataset that contains the table you want to create, append to, or overwrite. And the load can write a new table or partition, or append to or overwrite an existing one. A Cloud SQL import is the one where the permission belongs to someone else. The instance reads the file, so you describe the instance, copy its serviceAccountEmailAddress field, and use gcloud storage buckets add-iam-policy-binding to grant the storage.objectAdmin role to that service account for the bucket. And a Storage Transfer Service job is created with the gcloud transfer jobs create command; it is fully managed, with no infrastructure to manage and no code to write, and it is optimized for large transfers.

Choosing the load path

Where the data is decides the first step

Figure: A decision flow. If the data is on the local machine, upload it to a bucket with gcloud storage cp. If it is in another cloud, on-premises or at a URL, use Storage Transfer Service. Once the data is in a bucket, load it into BigQuery with bq load in the same location as the dataset, or import it into Cloud SQL with gcloud sql import sql after granting the instance's service account access.

Worked example (synthetic). Two hundred tebibytes sit in another provider's object storage and must end up queryable in BigQuery. The path is two steps: Storage Transfer Service to a bucket, then bq load.

The three considerations in the guide are really two decisions in sequence. The first is where the data is now. On your own machine, it goes into a bucket with gcloud storage cp. In another cloud, on-premises, or at a URL, it goes into a bucket with Storage Transfer Service — which Google says is optimized for transfers of more than a tebibyte. Either way it ends up in Cloud Storage, and that is where the second decision starts: where it must be queried. For BigQuery, bq load from the bucket, with the bucket and the dataset in the same location. For Cloud SQL, gcloud sql import sql, after the instance's service account has been granted access to the bucket. Reading the figure top to bottom answers most loading questions on the exam: the first arrow is the transfer, the second is the load.

Four ways data reaches BigQuery

Only one of them is the load the guide names

Figure. Four cards naming the ways data reaches BigQuery: batch load of large volumes, which is bq load from Cloud Storage; streaming load in near real time from messaging systems; change data capture replicating databases in near real time; and federation, which queries external data without loading it.

Worked example (synthetic). An item asks for the way to query files in a bucket without copying them into BigQuery. That is federation — which is not a load at all, and is the distractor-proof answer precisely because of that.

The guide's phrase is load data from Cloud Storage, and in BigQuery's own vocabulary that is one of four methods. A batch load is suitable for loading large volumes of data from a variety of sources — that is bq load. A streaming load loads data in near real time from messaging systems, which is the Pub/Sub and Dataflow pipeline from the first objective. Change data capture replicates data from databases to BigQuery in near real time. And federation to external data sources enables access to external data without loading it into BigQuery. Knowing all four keeps you from choosing a load when the scenario says the data must not be copied, and from choosing a nightly batch when the scenario says near real time. One more flag belongs here: autodetect, which enables schema auto-detection for CSV and JSON data when you load without supplying a schema.

What this topic actually tests

Three discriminations, two objectives

What is fixed at creation? a dataset's location, a Spanner configuration's edition requirement, a bucket's hierarchical namespace. What must exist first? a subscription before a Dataflow job reads it; an AlloyDB cluster before its primary. Whose permission, and which location? you upload; the Cloud SQL instance's service account imports; the bucket and dataset share a location.

Close on the three discriminations this topic turns on. First, what is fixed at creation: a BigQuery dataset's location cannot be changed afterwards, dual-region and multi-region Spanner configurations need the Enterprise Plus edition, hierarchical namespace can only be enabled when a bucket is created, and on Cloud SQL, automatic minor version upgrade cannot be disabled once the instance exists. Second, what must exist first: a Pub/Sub subscription before anything reads a topic, and an AlloyDB cluster before its primary instance. Third, whose permission and which location: you need Storage Object User to upload, the Cloud SQL instance's own service account needs access to the bucket it imports from, and a bq load needs the bucket in the same location as the dataset. And throughout, the tool is gcloud storage, because Google calls gsutil legacy.

Official sources for this topic

• Associate Cloud Engineer exam guide — Section 3
• Create instances — Cloud SQL for MySQL
• Create a cluster and its primary instance — AlloyDB for PostgreSQL
• Create and manage instances — Spanner
• Upload objects from a file system — Cloud Storage
• gsutil tool — Cloud Storage
• Load CSV data from Cloud Storage — BigQuery
• Storage Transfer Service overview
• Create and manage databases — Firestore
• Create datasets — BigQuery
• What is Pub/Sub?
• Create a topic — Pub/Sub
• Create pull subscriptions — Pub/Sub
• Google-provided templates — Dataflow
• Run a template — Dataflow
• Create buckets — Cloud Storage
• Dataflow overview
• Export and import using SQL dump files — Cloud SQL for MySQL
• Create transfers — Storage Transfer Service
• Introduction to loading data — BigQuery