Skip to main content

Connecting

BigQuery offers multiple connection methods to Hubble. This guide details three common methods:

  • BigQuery UI - analysts that need to perform ad hoc analysis using SQL
  • BigQuery SDK - developers that need to integrate data into applications
  • Looker Studio - business people that need to visualize data

Prerequisites​

To access Hubble, you will need a Google Cloud Project with billing and the BigQuery API enabled. For more information, please follow the instructions provided by Google Cloud.

Google does provide a BigQuery Sandbox for free that allows users to explore datasets in a limited capacity.

BigQuery UI​

  1. From a browser, open the crypto-stellar.crypto_stellar dataset.
  2. This will open the public dataset crypto_stellar, where you can browse its contents in the Explorer pane.
  3. Click the star icon in the Explorer pane. This will favorite the dataset for you. More detailed information about starring resources can be found here.
note

Hubble cannot be found from the Explorer pane! You cannot search for the dataset. To view the dataset, you must use the dataset link.

Copy and paste the following example query in the Editor:

select
  account_id,
  balance
from `crypto-stellar.crypto_stellar.accounts_current`
order by balance desc;

This query will return the XLM balances for all Stellar wallet addresses, ordered from largest to smallest amounts.

BigQuery SDK​

There are multiple BigQuery API Client Libraries available.

The following example uses Python to access the Hubble dataset. Use this guide for help setting up a python development environment.

Install the client library locally, and configure your environment to use your Google Cloud Project:

# verify python version
python3 --version
# if you do not have pip, install it
python -m pip install --upgrade pip

# install bigquery client library
pip install --upgrade google-cloud-bigquery
gcloud config set project PROJECT_ID

Use the Python Interpreter to run the example below to list the tables available in Hubble:

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

dataset_id = 'crypto-stellar.crypto_stellar'

# Make an API request
tables = client.list_tables(dataset_id)

# List the tables found in Hubble
print(f'Tables contained in {dataset_id}':)
for table in tables:
    print(f'{table.project}.{table.dataset_id}.{table.table_id}')

Run the example below to run a query and print the results:

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

query = """
SELECT
 account_id,
 balance,
FROM `crypto-stellar.crypto_stellar.accounts_current`
ORDER BY balance DESC
LIMIT 10;
"""

# Make an API request
query_job = client.query(query)

print("The query data:")
for row in query_job:
    # Row values can be accessed by field name or index.
    print(f'account_id={row[0]}, balance={row["balance"]}')

There are various ways to extract and load data using BigQuery. See the BigQuery Client Documentation for more information.

Looker Studio​

Looker Studio is a business intelligence tool that can be used to connect to and visualize data from the Hubble dataset.

To connect Hubble as a data source:

  1. Open Looker Studio
  2. Click on Create > Data Source
  3. Search for the BigQuery connector
  4. (Optional) Change the name of the data source at the top of the webpage
  5. Click Shared Projects > Select your Google Cloud Project
  6. Enter crypto-stellar as the Shared Project name
  7. Click on the Dataset crypto_stellar
  8. Select the desired table to connect
  9. Click CONNECT on the top right of the webpage.

And you're connected!

General information about Looker Studio can be found here.

General information about connecting data sources can be found here.