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

Note: This lab requires a partner account. Please follow the lab instructions to create your account before starting the lab.

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 will be made available to you.

This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that 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).

Note: Use an Incognito or private browser window to run this lab. This prevents any conflicts between your personal account and the Student account, which may cause extra charges incurred to your personal account.

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

Note: If you already have your own personal Google Cloud account or project, do not use it for this lab to avoid extra charges to your account.

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 pop-up opens for you to select your payment method. On the left is the Lab Details panel 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 panel.

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 panel.

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 view a menu with a list of Google Cloud products and services, click the Navigation menu at the top-left.

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.

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:

Your Cloud Platform project in this session is set to {{{project_0.project_id | "PROJECT_ID"}}}

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

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

gcloud auth list

3. Click Authorize.

Output:

ACTIVE: * ACCOUNT: {{{user_0.username | "ACCOUNT"}}} To set the active account, run: $ gcloud config set account `ACCOUNT`

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

gcloud config list project

Output:

[core] project = {{{project_0.project_id | "PROJECT_ID"}}} Note: For full documentation of gcloud, in Google Cloud, refer to the gcloud CLI overview guide.

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:

Note: You must use your personal email, not your student account, don't attempt to sign up with google, unless you sign out of your lab account first. If you attempt to sign up with your student id, your trial will be rejected or canceled.

3. Enter some details about yourself

4. Create a simple deployment of 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, you may need it later

6. Click "continue" to go to your elastic deployment

7. Click the hamburger menu (1) and then click "Stack Management".

8. On the right side of the page, select "API keys"

9. Create a new API Key

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

11. Save your new API key for later.

12. From the hamburger menu on the left. Click "Manage this deployment"

13. Click the Gear icon in the "Manage Deployment" column:

14. Copy the Cloud ID to your clipboard and save it for later:

Note: Hold onto the CloudID and API key, you will need them 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

export GOOGLE_CLOUD_PROJECT={{{project_0.project_id|Project ID}}} gsutil mb gs://$GOOGLE_CLOUD_PROJECT gsutil cp -r gs://spls/gsp1153/* gs://$GOOGLE_CLOUD_PROJECT

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.

Create a Cloud Storage Bucket

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:

bq --location=us mk --dataset mainframe_import

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

gsutil cp gs://$GOOGLE_CLOUD_PROJECT/schema_*.json . bq load --source_format=NEWLINE_DELIMITED_JSON mainframe_import.accounts gs://$GOOGLE_CLOUD_PROJECT/accounts.json schema_accounts.json bq load --source_format=NEWLINE_DELIMITED_JSON mainframe_import.transactions gs://$GOOGLE_CLOUD_PROJECT/transactions.json schema_transactions.json

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.

CREATE VIEW `{{{project_0.project_id|Project ID}}}.mainframe_import.account_transactions` AS SELECT t.*, a.* EXCEPT (id) FROM `{{{project_0.project_id|Project ID}}}.mainframe_import.accounts` AS a JOIN `{{{project_0.project_id|Project ID}}}.mainframe_import.transactions` AS t ON a.id = t.account_id;

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

Click Check my progress to verify the objectives.

Create a BigQuery dataset and import data from Cloud Storage

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

SELECT * FROM `mainframe_import.transactions` LIMIT 100

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

SELECT DISTINCT(occupation), COUNT(occupation) FROM `mainframe_import.accounts` GROUP BY occupation

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

SELECT * FROM `mainframe_import.accounts` WHERE salary_range = "110,000+" ORDER BY name

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.

Explore the mainframe data in BigQuery

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):

export CONNECTION_URL=<< your cloud ID from step 1.13 >> export API_KEY=<< your API key from step 1.10 >>

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.

gcloud dataflow flex-template run bqtoelastic-`date +%s` --worker-machine-type=e2-standard-2 --template-file-gcs-location gs://dataflow-templates-{{{project_0.default_region|REGION}}}/latest/flex/BigQuery_to_Elasticsearch --region {{{project_0.default_region|REGION}}} --num-workers 1 --parameters index=transactions,maxNumWorkers=1,query="select * from \`$GOOGLE_CLOUD_PROJECT\`.mainframe_import.account_transactions",connectionUrl=$CONNECTION_URL,apiKey=$API_KEY

This should generate a message similar to the one below

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

Note: If the Dataflow job fails, change the region to us-east1 or us-east4 and rerun the job. E.g. (--region us-east1).

Click Check my progress to verify the objectives.

Create a Dataflow job reading from BQ and pushing to Elastic

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. In order to see 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 in to 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 Donut visualization type. 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 everytime 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: October 26, 2023

Lab Last Tested: October 26, 2023

Copyright 2024 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