- [Frank] Can you really connect cloud-based BI tools
to a Lakehouse architecture.
(upbeat music)
Hi, I'm Frank from Databricks.
This webcast is teaching you what you need to know
to connect Looker with Delta
and how to create Looker dashboards based on Delta Tables.
In this tutorial you will learn
how to build your first dashboard.
We will work with Looker views and dimensions and measures
and we will use models and explores.
This is a how-to tutorial that assumes you follow along
and you know some basics.
However, there are many introduction resources available
to get you started.
So for those who aren't familiar,
Delta Lake is an open format transactional storage layer
that forms the foundation of a Lakehouse.
Delta Lake delivers reliability, security, and performance
on your data lake for both streaming and batch.
A Lakehouse combines the best elements
of data lakes and data warehouses.
With the lakehouse, you continue to use
the cheap virtually unlimited
and highly available cloud object stores
such as Google cloud storage.
But the Lakehouse is more than a Data Lake.
A Lakehouse supports unstructured semi-structured
and structured data.
The Lakehouse architecture is built on
Apache Parquet
seamlessly integrates
with Apache Spark
and provides reliable semantics
with asset transactions,
schema enforcement
and evolution.
Scalable metadata handling,
SQL support,
versioning,
time travel
as well as deep or shallow clones of your data.
And all this is based on open source delta-io.
There are a number of
(upbeat music) great Google webcasts out there teaching
you Looker such as this introduction to Looker video.
Links to all these external resources mentioned here
can be found in the blog posting
that accompanies this webcast.
These blog posting is referenced
in the description section
of the webcast.
Make sure you got access to Looker.
Looker is not part of the free GCP trial
used GCP console and search for Looker.
And unless you already have one,
request a demo.
Once you've got access,
verify that you can log in.
To access Delta Table Slater we need a database connection.
So go to databases, connections,
click on add connection,
give the new connection a name.
I'll take Frank_looker_databricks.
Choose the right type.
We want to connect to Apache Spark.
Now we need the host.
We need the port number
and we need those additional parameters.
To get those values, change to the data bricks notebook,
go to cluster, go to advanced
and then select JDBC ODBC.
And this is basically where you can copy the host name
and the port number.
Let's copy them
at the port number.
And for those additional parameters what we do is
we go to the official data breaks documentation
for the integration of data breaks and Looker.
If you click on the documentation
and scroll down a little bit,
you will see those parameters.
Now copy the parameters which is actually only
the first part of what we need,
paste them here.
Now the second part is the HTTP path
copy it from the workspace
paste it
database is default.
Username is Token
and for the password actually we need to create a Token.
So go to the top right corner which is the account setting.
Go to settings,
generate a new token.
Give it a name,
copy the token.
Make sure you don't lose it
and paste it here.
Now last but not least
test the connection.
The connection is working.
The test is successful at the connection.
Assuming that you don't have your own data already,
the next module is explaining how to create data
in a Delta Table.
That we can later use to well
use to build dashboards in Looker.
We use a scalar notebook here
and for this demonstration
we create our own small data frame.
We define a path to store the data frame
and then we write it back into delta format.
It's written now.
We show it once more as a table structure,
you can see it here.
And then we read it in the delta format,
and we create a temporary view with the name 'teamscores'.
Once we have this view, we can also use SQL
and do a select star from team scores.
And then we write it back in delta format
and we save it as a table.
The table name is GCP Frank Scores.
Now it's also available on the data on the scores.
This is the scheme after table
and you can also see some sample data.
So based on a scalar data frame,
we created a delta table with the schema and with content,
and exactly that Delta Table
we want to access now from a Looker dashboard.
To create your first Looker Dashboard
change to your Looker account,
go to develop,
select New LookML Project,
give it a name.
Then select a database connection that we just defined.
GCP Frank was the schema click on create project.
It will create a model view of
what is behind your database connection.
So we click on the model.
You see this YAML representation of the data.
And you also see the views.
Now the views represent the tables
and you see the scores view
which is the Delta Table that we just created.
So under explore,
you can get to
the same view.
You can explore the view.
You can use dimensions and combine those dimensions
and define what you want to visualize.
You click on visualize
and you get this bar diagram
and then you can also select a nicer color pallette.
And finally if you like it, you just say save as dashboard.
You create a new dashboard,
you give it a name,
confirm it.
Oops,
confirm it
and save it as a dashboard.
Now if you click on the dashboard,
you can see the dashboard obviously and you can still
explore what is behind the dashboard.
Explore from here,
you can rearrange your data or filter your data.
If you're creating visualizations for Looker
you're dealing with dimensions and with measures.
Now dimensions are columns from a table
and measures are like SQL aggregates,
like some or min or max
To create a visualization i take two dimensions
like the type of crew member and the number.
And if you click run it retrieves the data.
It's showing the data in a table,
you click on visualization and you can see how many
of each group are taking part in the expedition.
If you switch to this pie chart it's not possible to draw it
because it says incompatible data.
And it's actually missing a measure.
Now to create a new measure go to LookML
and let's edit the file
the YAML file directly.
What you do is you add a new measure
which is some in this case,
you save the changes.
Go back to polar crew.
We select a new measure
on visualization click pie chart again.
Run the data
and it's working now.
Let's change to a nicer color palette
and let's save it to the dashboard.
Then let's rearrange the dashboard to
make it look nicer.
And that's it.
You created your second visualization
and let's just give it proper names.
So far everything we've been doing
with Looker was in development mode
but we can configure Git for version control.
So this is a more complete example that I was doing offline.
And what you see here if you go to settings
that Git is already configured.
So if you scroll all the way down
it shows the remote origin URL.
And so this is a versioned dashboard.
This is the GitHub site
and it's showing you the views
that are committed to GitHub
and that can be versioned.
And back to the example if you go to development mode,
now it's turned on,
on the top left corner of the screen.
You see it's running in development mode.
This means whatever I do now
all the changes are applied to a local copy
that I have and I can switch back to production mode.
That will show me again
the production version of my dashboard.
And
that's it.
Congratulations. You built your first dashboard
directly accessing our Data Lake.
(upbeat music)