This data project demonstrates how Tinybird could be used for real-time analytics retrieving data from BigQuery using the CLI and an Apache Beam connector.
This project is divided into:
- ds-gen: Python script for generating the source dataset used to populate BigQuery
- tb-project: The Tinybird data project
- tableau-connector: The "connectors" to consume Tinybird APIs from Tableau
Start by cloning this repo locally.
cd ds-gen
mkdir output
python3 -mvenv .e
source .e/bin/activate
pip install -r requirements.txt
python3 gen.py
With this script you will be able to generate a synthetic dataset for the invoices data project. It generates these files in the output
directory:
- clients-synthetic-data.json
- recipients-synthetic-data.json
- invoices-synthetic-data.json
- agents-synthetic-data.json
You can just upload manually the generated JSON files in the previous step from the BigQuery console:
Create a dataset called tinybird
and upload the JSON files using these table names:
Check the CLI documentation for other installation options and troubleshooting.
cd tb-project
python3 -mvenv .e
source .e/bin/activate
pip install tinybird-cli
tb auth
tb push datasources/currency.datasource --fixtures
tb push --push-deps
There's a BigQuery connector integrated in the CLI. To start using it run:
export GOOGLE_APPLICATION_CREDENTIALS=absolute_path_to_your_json_service_account
tb auth --connector bigquery
You'll be prompted to introduce the name of your BigQuery project and the name of a Storage bucket to store temporary files.
It supports several ingestion scenarios:
Bulk upload
This is handy to move data from BigQuery to Tinybird for the first time:
export PROJECT_ID="YOUR_PROJECT_ID"
tb datasource append agents \
--connector bigquery \
--sql "select agent_name, id from \`$PROJECT_ID.tinybird.agents\`"
tb datasource append clients \
--connector bigquery \
--sql "select company_country, company_name, id from \`$PROJECT_ID.tinybird.clients\`"
tb datasource append recipients \
--connector bigquery \
--sql "select recipient_country, recipient_code, id from \`$PROJECT_ID.tinybird.recipients\`"
tb datasource append invoices \
--connector bigquery \
--sql "select id, agent_id, recipient_code, client_id, amount, currency, created_at, to_json_string(added_payments) added_payments from \`$PROJECT_ID.tinybird.invoices\`"
Incremental updates
This is the kind of ingestion you want to schedule periodically (e.g. using a cron job), to ingest just the new data incoming to the invoices
table.
tb datasource append invoices \
--connector bigquery \
--sql "select id, agent_id, recipient_code, client_id, amount, currency, created_at, to_json_string(added_payments) added_payments from \`$PROJECT_ID.tinybird.invoices\`" --incremental created_at
Replacements
The dimensions tables (agents, clients, recipients), can be fully replaced when you need to ingest new data:
tb datasource replace agents \
--connector bigquery \
--sql "select agent_name, id from \`$PROJECT_ID.tinybird.agents\`"
Or you can replace parts of data. For instance, to replace the invoices for the year 20201, run this:
tb datasource replace invoices \
--connector bigquery \
--sql "select id, agent_id, recipient_code, client_id, amount, currency, created_at, to_json_string(added_payments) added_payments from \`$PROJECT_ID.tinybird.invoices\` where date(created_at) > date('2021-01-01')" \
--sql-condition="toDate(created_at) > toDate('2021-01-01')"
Now we'll publish some invoices from Google PubSub and deploy a DataFlow pipeline which will ingest those events into the invoices
data source in Tinybird. You can check how your endpoints get updated as data comes in.
Create the Python environment:
git clone https://github.com/tinybirdco/tinybird-beam
cd tinybird-beam/dataflow
python3 -m venv env
source env/bin/activate
pip install -r requirements.txt
# Update the variables in the `sample.env` file and source it:
# TB_TOKEN should be the same token you used to push the data project in the previous steps
source sample.env
Create the PubSub topic:
gcloud pubsub topics create demo-topic
Push the Apache Beam pipeline to DataFlow:
python dataflow.py \
--project=$PROJECT_NAME \
--region=$REGION \
--runner=DataflowRunner \
--temp_location=$TMP_LOCATION \
--input_topic=projects/$PROJECT_NAME/topics/$TOPIC \
--bq_table=tinybird.invoices \
--batch_size=10000 \
--batch_seconds=5 \
--batch_key= \
--tb_host=https://api.tinybird.co \
--tb_token=$TB_TOKEN \
--tb_datasource=invoices \
--tb_columns="id,agent_id,recipient_code,client_id,amount,currency,created_at,added_payments" --worker_machine_type=n1-highmem-32 --enable_streaming_engine --num_workers=1 --max_num_workers=5
This pipeline will batch 10000 elements or a window of 5 seconds to the Tinybird invoices
Data Source.
Once running check the job status:
Start the PubSub publisher:
cd tinybird-beam/pubsub
python pubsub.py
Once you finish running the pipeline remember cleaning resources.