A Django-oriented micro-framework to help structuring BigQuery exports with Data Studio and analytics in mind.
It lets you write minimal extractors that will generate the statistics you need and it will manage everything around them (creating the tables on BigQuery, running the exports, etc).
We'll review here how you can integrate Smolqwery in your project.
First step is (obviously to add it to the dependencies). You just need to
specify that you need the smolqwery
package (use pip
, poetry
or whatever).
You can thank Google for that, the configuration is a bit complex. The first thing to do is to add it in your installed apps:
INSTALLED_APPS = [
# your stuff
"smolqwery",
# more of your stuff
]
Then there is a few variables to configure:
- Things specific to your project
SMOLQWERY_EXTRACTORS
— List of extractors, more on this belowSMOLQWERY_DJANGO_APP
— App in which migrations must be createdSMOLQWERY_FIRST_DATE
— First date of data to include in exports (all days between this date and now will be exported)
- Things that you want to specify about BigQuery
SMOLQWERY_DATASET
— Name of the dataset (database if you compare to PostgreSQL for example). It will be created automatically by the migrations, you don't need to create it yourself.SMOLQWERY_DATASET_LOCATION
— Location of the data. Unless you want to be specific you can just say "EU" or "US"SMOLQWERY_ACL_GROUPS
— List of IAM groups emails that will receive the read/write permissions on the created dataset
- Google credentials (more on this later)
SMOLQWERY_GOOGLE_TYPE
SMOLQWERY_GOOGLE_PROJECT_ID
SMOLQWERY_GOOGLE_PRIVATE_KEY_ID
SMOLQWERY_GOOGLE_PRIVATE_KEY
SMOLQWERY_GOOGLE_CLIENT_EMAIL
SMOLQWERY_GOOGLE_CLIENT_ID
SMOLQWERY_GOOGLE_AUTH_URI
SMOLQWERY_GOOGLE_TOKEN_URI
SMOLQWERY_GOOGLE_AUTH_PROVIDER_X509_CERT_URL
SMOLQWERY_GOOGLE_CLIENT_X509_CERT_URL
So. There is a bunch of credentials to get. Roughly, the steps to get them is:
- Log into the Google Cloud Console
- Create a project (you can re-use this same project between several instances of your code as long as you let each instance use a different dataset name)
- Create a service account within this project
- Download the credentials of this service account (as JSON file) and recopy
the contents of this file into the configuration. For example in the JSON
file there is a
project_id
entry, it matches theSMOLQWERY_GOOGLE_PROJECT_ID
setting
You can now start creating extractors. It's the way your code will convert your data into the statistics you want to store.
The first step is to decide what kind of extractor you're going to do:
- Date-aggregated — Each row is a single date. By example "today there was 345 new users and 23 new contracts"
- Individual rows — Each row is one entry. By example, each row can represent one email that has been sent.
How to choose? If you're dealing with personal data, the only way you can use it for statistics without consent is to aggregate it.
Note — As long as an ID represents a single or a few users (hash, fingerprint, etc) then it's considered to be a personal data. There is no such thing as anonymization. The only way out is aggregation.
The way to handle dates is going to be different depending on the type:
- Date-aggregated extractors don't have to include a date in their data model, since it's the system that decides which date range corresponds to which date "row". For that matter, you need need to reply with the data you deduce from the range you're asked to extract and that's it.
- Individual rows must contain a timestamp field which indicates to which date
this field is related. It can be a timestamp (date/time + timezone) or a
simple date. It will not be added automatically but it is expected to be
named
timestamp
(see below how to define fields). You can override this name in the extractor if you need to.
You declare what you're going to return using a dataclass. It's a bit like Django models, it's used to define what are the fields.
Here is type types mapping (in relation to Big Query's types):
int
→INT64
float
→FLOAT64
bool
→BOOL
str
→STRING
bytes
→BYTES
datetime.date
→DATE
datetime.datetime
→TIMESTAMP
(make sure to use time-zone-aware datetime instances, there is no check of this)
If you need your field to be nullable you can use the Optional
type
annotation.
Another thing is the "differentiated" fields. For example let's say that you have a strictly growing metric ("number of users that ever registered") and you want to easily know the difference from one date to the other ("number of users that registered during the time range") in the data studio. You can mark a field as "differentiated", which will trigger the creation of a view where the metric is differentiated day-by-day.
You would have the user
table with this data:
Date | Registered |
---|---|
2022-01-01 | 10 |
2022-01-02 | 20 |
2022-01-03 | 35 |
And then the user_delta
view with:
Date | Registered |
---|---|
2022-01-01 | 10 |
2022-01-02 | 10 |
2022-01-03 | 15 |
This can be done using the sq_field(diffrentiate=True)
field (which is a
shortcut to dataclasses
's field
created for our purpose).
Here is an example of both a date-aggregated and individual-rows dataclasses:
from dataclasses import dataclass
from smolqwery import sq_field
import datetime
@dataclass
class User:
users: int = sq_field(differentiate=True)
prospects: int = sq_field(differentiate=True)
clients: int = sq_field(differentiate=True)
@dataclass
class Email:
timestamp: datetime.datetime
type: str
The extractors themselves are a simple interface that you need to implement to reach your needs. For example:
class EmailExtractor(BaseExtractor[Email]):
def get_dataclass(self) -> Type[Email]:
return Email
def get_extractor_type(self) -> ExtractorType:
return ExtractorType.individual_rows
def extract(
self, date_start: datetime.datetime, date_end: datetime.datetime
) -> Iterator[Email]:
for email in EmailMessage.objects.filter(
date_sent__gte=date_start, date_sent__lt=date_end
):
yield Email(
timestamp=email.date_sent,
type=email.type,
)
Let's note here that date_start
is inclusive and date_end
is exclusive.
You need to declare your extractors in the settings, for example:
SMOLQWERY_EXTRACTORS = [
"core.smolqwery.UserExtractor",
"core.smolqwery.EmailExtractor",
]
Like Django, and using Django's system in part, Smolqwery will manage its table within BigQuery using migrations. Those schema are automatically created from the dataclasses that you've defined.
However unlike Django you cannot change a data model once it has been created, because it would complicate things a lot for something that you don't really need.
See it that way: you want to add a bunch of statistics to your BigQuery user
table. If your statistics can be computed from observing the data in your
database, you could just create a user2
table and re-compute statistics from
the beginning into this table. This will provide the warranty that all fields
present indeed have a value (as opposed to having to keep all new columns at
NULL).
It's quite flexible this way: you can either create a new table and put just the new fields there, either forget about the old table and re-compute everything from the start in a new table, etc.
So, how do you migrate?
./manage.py smolqwery_make_migrations
./manage.py migrate
Let's note that if you (or the next person) doesn't have the Google credentials configured, then you'll run into an issue.
Now you can run the export.
Either with a test run
./manage.py smolqwery_print_extract -f 2022-01-01 -l 2022-01-05
Either with a real run, that will really insert the data
./manage.py smolqwery_extract
The extract will look up for the date of the last extract table by table. If the
extract was never done then it will fall back to the SMOLQWERY_FIRST_DATE
setting.
All the days between the last extract and the last revolute day (in Django's time zone) will be extracted.
You can safely run this as a cron, several times a day if you want to.
Now all your data is in BigQuery and you can start using it from Data Studio or other sources!