bigquery-erd
Entity Relationship Diagram (ERD) Generator for Google BigQuery, based upon eralchemy.
Examples
ERD for a NewsMeme database schema (taken from the original project).
Installation
pip install bigquery-erd
eralchemy requires GraphViz to generate the graphs and Python. Both are available for Windows, Mac and Linux.
Usage
Usage from Python
Find an example notebook here.
But Wait, BigQuey is not a Relation Database?
That's right. You cannot enforce primary or foreign key constraints in BigQuery. However, that doesn't mean that you should not be able to have logical dependencies between tables.
Defining Relations through Column Descriptions
We use the column description field in BigQuery to define relations between columns in a format that we can later parse programmatically.
Let's assume we have a table a with a column id and another table a with a column a_id that serves as a foreign key relation to a.id.
We then add the following description to b.a_id:
-> b.id
Defining Relations to Datasets Explicitly
Per default, we assume that the related tables are located inside the same dataset. However, you can also define the datasets explicitly. This is especially useful if the two related tables are not located within the same dataset.
Let's assume that table a is located in dataset d1 and table b is located in d2.
The description in b.a_id would then be:
-> d1.a.id
Defining Cardinality Explicitly
Cardinality defines the relationship between two tables. This package understands four different cardinalities:
*, meaning "0..N"?, meaning "{0,1}"+, meaning "1..N"1, meaning "1"
Per default, we assume a cardinality of *:1. You can also define the relation's cardinality explicitly.
Let's assume that every record in a has at least 1 related record in b.
the description in b.a_id would be:
-> +:1 a.id
Example
You can find a example Google BigQuery project for the NewsMeme schema with annotated descriptions here.
Defining Custom Description RegEx
The default RegEx for relations in column descriptions is ->\s([?*+1 ]:[?*+1 ]\s)?(.*\.)?(.*)\.(.*)$. You can define a custom RegEx by setting the GBQ_RELATION_PATTERN environment variable.
The RegEx should match four capture groups, where:
- The first group is the cardinality (which is optional)
- The second group is the dataset id (which is optional)
- The third group is the table id
- The fourth group is the column
Metadata
Release files for bigquery-erd 0.1.1
For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.
Source distribution (sdist)
| File | Size | Uploaded | Provenance | Actions |
|---|---|---|---|---|
| bigquery-erd-0.1.1.tar.gz | 7.9 kB | Without provenance | Details for bigquery-erd-0.1.1.tar.gz |
Built distribution (wheel)
| File | Interpreter | ABI | Platform | Actions Reset |
|---|---|---|---|---|
| bigquery_erd-0.1.1-py3-none-any.whl | Python 3 | none | any | Details for bigquery_erd-0.1.1-py3-none-any.whl |
Total release size: 16.3 kB
Release files / bigquery-erd-0.1.1.tar.gz
| Download URL | bigquery-erd-0.1.1.tar.gz |
|---|---|
| Size | 7.9 kB |
| Tags | Source |
|
SHA-256 checksum How to use checksums |
1723bc447c2922439cbf00b4601b1ccaf4b59bdc13ccf1a288bd0002b28fbad8
|
|
BLAKE2b-256 checksum How to use checksums |
753eb1e6ccfec0ce60656f3b896f76c3cd5724c7090e0dc9072a749c077c628d
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
poetry/1.1.4 CPython/3.8.0 Linux/4.15.0-1077-gcp
|
Release files / bigquery_erd-0.1.1-py3-none-any.whl
| Download URL | bigquery_erd-0.1.1-py3-none-any.whl |
|---|---|
| Size | 8.4 kB |
| Tags | Python 3 |
|
SHA-256 checksum How to use checksums |
30c66c505e45247cdea5c3a2446c369c8b83757bf53cc1148dc6b318baddbd8b
|
|
BLAKE2b-256 checksum How to use checksums |
7566fc13dc6db80792c9a0066e51b1e94d439d9199b51560f07a4fdd1d0f225c
|
| Upload date | |
|
Uploaded using Trusted Publishing? What is trusted publishing? |
No |
| Uploaded via |
poetry/1.1.4 CPython/3.8.0 Linux/4.15.0-1077-gcp
|
Security for bigquery-erd 0.1.1
Provenance
Provenance describes where a file came from. On PyPI, provenance is shared via attestations, which provide a verifiable record of the build or publishing details. View details, limitations and caveats.
No provenance found
None of the files in this release were uploaded with provenance metadata.
A note on package security
PyPI is a repository for distributing packages, not a service for auditing them. While third-party security researchers and our staff do their best to detect and remove malicious packages, PyPI cannot guarantee the quality, security or suitability of any given package.
To learn more about protecting yourself from malicious actors, visit our package security documentation.