This lab was developed with our partner, Elastic. Your personal information may be shared with Elastic, the lab sponsor, if you have opted into receive product updates, announcements, and offers in your Account Profile.

GSP1153

Overview

Financial institutions have vast amounts of data about their customers. However, many of them struggle to leverage data to their advantage. Data may be sitting in silos or trapped on costly mainframes. Customers may only have access to a limited quantity of data, or service providers may need to search through multiple systems of record to handle a simple customer inquiry. This creates a hazard for providers and a headache for customers.

Elastic and Google Cloud enable institutions to manage this information. Powerful search tools allow data to be surfaced faster than ever - Whether it's card payments, ACH (Automated Clearing House), wires, bank transfers, real-time payments, or another payment method. This information can be correlated to customer profiles, cash balances, merchant info, purchase history, and other relevant information to enable the customer or business objective.

In this hands-on lab, you'll import synthetic data representing financial records offloaded from a bank's mainframe into BigQuery. You'll then explore it using SQL, then create a Dataflow job to process and ingest a subset of that data into Elastic Search. Finally, you'll create a dashboard in Elastic's Kibana tool to gain a 360 degree view of a customer's financial history.

Objectives

• Importing mainframe data into BigQuery and exploring it using SQL
• Get an Elastic Trial and deploy an Elastic Cluster on Google Cloud
• Creating a Dataflow job from an Elastic template
• Running and monitoring a Dataflow job's progress
• Inspecting datasets in Elastic with Kibana
• Building a dashboard to visualize the mainframe data

Prerequisites

• Familiarity with SQL, Google Cloud and Elastic Search is not required but will be helpful

Setup and requirements

Before you click the Start Lab button

Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials you use to sign in and access Google Cloud for the duration of the lab.

To complete this lab, you need:

• Access to a standard internet browser (Chrome browser recommended).

• Time to complete the lab—remember, once you start, you cannot pause a lab.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane with the following:

   • The Open Google Cloud console button
   • Time remaining
   • The temporary credentials that you must use for this lab
   • Other information, if needed, to step through this lab

2. Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).

   The lab spins up resources, and then opens another tab that shows the Sign in page.

   Tip: Arrange the tabs in separate windows, side-by-side.

   Note: If you see the Choose an account dialog, click Use Another Account.

3. If necessary, copy the Username below and paste it into the Sign in dialog.

   {{{user_0.username | "Username"}}}

   You can also find the Username in the Lab Details pane.

4. Click Next.

5. Copy the Password below and paste it into the Welcome dialog.

   {{{user_0.password | "Password"}}}

   You can also find the Password in the Lab Details pane.

6. Click Next.

   Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges.

7. Click through the subsequent pages:

   • Accept the terms and conditions.
   • Do not add recovery options or two-factor authentication (because this is a temporary account).
   • Do not sign up for free trials.

After a few moments, the Google Cloud console opens in this tab.

Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.

Activate Cloud Shell

Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.

1. Click Activate Cloud Shell at the top of the Google Cloud console.

2. Click through the following windows:

   • Continue through the Cloud Shell information window.
   • Authorize Cloud Shell to use your credentials to make Google Cloud API calls.

When you are connected, you are already authenticated, and the project is set to your Project_ID, . The output contains a line that declares the Project_ID for this session:

gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.

3. (Optional) You can list the active account name with this command:

4. Click Authorize.

Output:

5. (Optional) You can list the project ID with this command:

Output:

Task 1. Elastic - Set up trial account

1. Sign up for a free trial.

• Click Start free trial.

2. Sign up using your personal email and a unique password. Do NOT click the "sign up with Google" button:

3. Enter some details about yourself

4. Select use case as Elastic Search

Note: make sure you use because the rest of the lab will assume you are using that region.

Note: The deployment can take up to 5 minutes.

5. Save your deployment credentials Cloud ID , you will need them later when you load data from BigQuery into Elastic.

6. From the bottom-right corner, click the expand arrow, select "Stack Management" and then click "View All Pages."

7. On the right side of the page under Stack Management, select "API keys".

8. click on Create API Key

9. Name your key "bigquery-import" and select "Create API Key" to generate a new key

10. Save your new API key for later when you load data from BigQuery into Elastic..

Task 2. Create a Cloud Storage Bucket

1. Paste the below code into the Cloud Shell to create a new bucket and copy data from an existing bucket

2. Go to Navigation menu > Cloud Storage > Buckets, click the name of the bucket that you created and confirm that data was copied over. This should look similar to the image below:

Click Check my progress to verify the objectives.

Task 3. Create a BigQuery dataset and import data from Cloud Storage

1. Create a BigQuery dataset by pasting the below commands into the Cloud Shell:

This command creates a BigQuery dataset in the US called "mainframe_import". You should see a result that looks like this:

2. Download the schemas and create two BigQuery tables in the dataset with data from Cloud Storage running the below code sequentially

3. Navigate to BigQuery using the Search bar or Navigation Menu on the left hand side.

4. Click the drop down arrow under your project to view your dataset and table.

There should be two tables in your BigQuery dataset: ‘accounts' and ‘transactions'

table 1: Accounts

table 2: Transactions

Note: this is a small data set of simulated financial data, it doesn't represent real accounts or financial transactions.

5. Go to the BigQuery UI (not the Cloud Shell) and Join the tables into a BigQuery view by running the below code.

In the BigQuery console, you should see two tables and a view:

Click Check my progress to verify the objectives.

Task 4. Explore the mainframe data in BigQuery

1. Go to BigQuery → mainframe_import → transactions

2. Explore the newly imported data.

3. If you go to "Schema" you can see the table's schema

4. "Details" provides metadata on the table, including creation time, data location, size (on disk) and number of rows.

5. "Preview" provides a snapshot of the data

3. Run the below query to select the first 100 rows of data in the table

4. Explore the data more by seeing how many unique occupations are in the accounts dataset. Copy the code below to count the distinct number of occupations

5. Explore the salary range column. Start by querying the highest salary range

1. One hypothesis a Data Analyst might make is that there is a correlation between salary range and age (although other factors such as Occupation will most likely influence this).

• See if you can prove or disprove this. Hint: Only select the columns you need (occupation, salary_range and age_range)

Click Check my progress to verify the objectives.

Task 5. Create a Dataflow job reading from BQ and pushing to Elastic

1. The Dataflow API should already be enabled, if not find it using the search bar

2. Paste the below code into the Cloud Shell to create a Dataflow job, making edits to the parts in << >> for CONNECTION_URL and API_KEY. This job will read data from your BigQuery dataset and push it to Elastic

Enter these values by hand into the cloud shell (one by one):

3. Now we will create a dataflow job to move the account transactions into Elastic Cloud. Cut and paste the following command into the cloud shell.

This should generate a message similar to the one below

4. Go to Dataflow → Jobs to make sure you job is running

Click Check my progress to verify the objectives.

Task 6. Explore the data in Elastic

1. Go back to the Elastic Cloud

2. Go to Discover

3. Click ‘Create a data view'

4. Include the Name, Index pattern and select timestamp from the Timestamp field dropdown. Then click "Save data view to Kibana"

5. To view the sample dataset, set the date range from Jan 3, 2020 to Jan 12, 2020

6. Expand one of the transactions to see the fields you have to work with

Task 7. Create a simple dashboard

1. Select the Analytics "Dashboard" from the left Menu and then select "Create a dashboard"

2. Click "Create visualization" and then drag and drop the field "Records" onto the visualization canvas

3. Click "Save and Return" to save the visualization and return to the dashboard. Grab the lower right corner of the visualization and resize it to look something like this

This bar chart displays the number of transactions that occurred over the time frame. It will also make it easy for the user to zoom into a specific day or hour that they are interested in.

4. Click "Create visualization" to add another visualization to the dashboard, again drag and drop the field "Records" onto the canvas. Change the visualization type to "Legacy Metric". Click "Save and return" to save the visualization and return to the Dashboard.

5. Resize and rearrange to visualizations to look something like this:

6. Click "Create visualization" and drag and drop the file "absolute_amount" onto the canvas. Change the visualization type to "Area" and set the vertical Axis to "Average". Click "Save and return"

7. Click "Create visualization" and select the Pie visualization type then click on paint brush . symbol which will open up the Appearance menu. Under Donut hole, select small and Drag the field "gender.keyword" onto the "Slice by" field, and drag the "running_balance.amount" field onto the metric field. Change the metric function from Median to Average. Click "Save and return"

8. Resize and rearrange the visualizations to look something like this

9. Click "Save" and enter a title for your Dashboard, check "Store time with dashboard" to set the current time range whenever you load the dashboard in the future. Click "Save" to finish saving your dashboard.

Congratulations, you have created your first dashboard.

See if you can create some of these visualizations from the data:

• Create three of the below visualizations in the dashboard, ‘Transaction Count’, ‘Customer Count’ and ‘Top 10 Spenders pie chart’

Congratulations!

In this lab, you have:

• Simulated exporting mainframe financial data (accounts and transactions)
• Imported mainframe data into BigQuery
• Explored and normalized the data using SQL in BigQuery
• Loaded the normalized data into Elastic using DataFlow
• Created a "time series" dashboard to visualize the financial data

Next Steps / Learn More

• Elastic on the Google Cloud Marketplace!
• Elastic Cloud offers a fully managed experience so that you can focus on your data instead of managing the cluster. You can check it out here and play with a free trial.
• Check out the Elastic Integrations page and see how easily you can stream in logs, metrics, traces, content, and more from your apps, endpoints, infrastructure, cloud, network, workplace tools, and every other common source in your ecosystem.
• Leverage the out-of-the-box solutions to give your Elastic project a jump start

Google Cloud training and certification

...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.

Manual Last Updated: December 30, 2024

Lab Last Tested: December 30, 2024

Copyright 2025 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.

#comment