End to End Analysis Example
This page will guide you through an end-to-end analysis use case with Hubble and Google Looker Studio
Prerequisites
- Make sure you have connected to Hubble by following the instructions in the Connecting page
- You have access to Google Looker Studio
- You have read and understand the general Best Practices for querying BigQuery data
Create a Dashboard Analyzing Soroban Metrics
Attach Data Sources to Looker Studio
- Select
Create --> Data source
- Find and select the
BigQuery Google Connector
- Find the desired tables that you want to connect. For this example you will want to add a data source for:
crypto-stellar.crypto_stellar.contract_data
Create a New Report (Dashboard)
- Select
Create --> Report
- Add your data sources from above
- Insert a
Pie chart
- Choose
contract_data
as theData source
- Choose
closed_at
as theDate Range Dimension
andcontract_durability
as theDimension
- You should now have a pie chart showing the percentage of Temporary VS Persistent Contract Data Durability
Use Custom SQL to Create a Chart
- In your report, click
Add Data
which will be near the bottom right of your window
- Select
BigQuery
and chooseCUSTOM QUERY
and select your desiredBilling Project
where the query will be charged
- Add the following query and click
Add
-- Find the latest ledger sequence within Hubble.
-- This may be slightly behind the actual Stellar latest ledger
-- because Hubble is scheduled to run and insert data at 5 minute intervals
with latest_ledger_in_hubble as (
select
max(sequence) as latest_ledger_sequence
from `crypto-stellar.crypto_stellar.history_ledgers`
),
-- Find all the ttl that have expired
expired_ttl as (
select
key_hash
, live_until_ledger_seq
-- Saving the date to aggregate on at the final step of the query
, date(closed_at) as ledger_date
from `crypto-stellar.crypto_stellar_dbt.ttl_current`
where true
-- Filter for expired entries only with the use of latest_ledger_sequence
and live_until_ledger_seq < (select latest_ledger_sequence from latest_ledger_in_hubble)
)
-- Aggregate based on the month and contract durability type
select
date_trunc(et.ledger_date, month) as month_agg
, cd.contract_durability
, count(1) as expired_entry_count
from expired_ttl as et
join `crypto-stellar.crypto_stellar_dbt.contract_data_current` as cd
on et.key_hash = cd.ledger_key_hash
where true
-- Optionally filter for a specific date/date range
and et.ledger_date between '2024-02-01' and '2024-06-30'
group by 1,2
order by 1 desc, 2
- Insert
Column chart
- Select
BigQuery Custom SQL
as yourData source
,month_agg
as theDimension
,contract_durability
as theBreakdown Dimension, and
expired_entry_countas the
Metric`
- You should now have a column chart (bar chart) showing the expired Soroban contract entries
note
Note that between 2024-02-01
and 2024-06-30
there aren't that many expired persistent entries
- Click
+Add quick filter
to apply a filter throughout the whole report
- Select
contract_durability
to filter bycontract_durability
values
- Select only
ContractDataDurabilityPersistent
and clickApply
- Your charts should now be filtered and show only
ContractDataDurabilityPersistent
data