Big Query

A brief demonstration of using mbpy as a data connector for Big Query

There are convenient scripts available to load Big Query with the tables that have been imported into the SQLite database. They live in the scripts folder and can be viewed with

mbpy scripts list

They are displayed in the group load_bq. Executing them without setup will fail, as there is a process associated with ensuring that script itself has access.

First, it should be noted that the target used in this script is TO-BQ and takes some input parameters:

mbpy stream to-bq --help

In the help file, you will see that the only required parameter is table ID. BigQuery needs the dataset, and . and then the table ID, the part after the dot, in order to load the data. You only need to give it the table ID (the part after the dot) in the script, so where do you supply the dataset?

Environment variables are another way to send paramters, and this is what is used for the TO-BQ command (as well as other targets).

Environment variables are secure ways to send sensitive data in a scripting environment, which allows us to not have to hard code passwords and other sensitive information. In order to execute the script, we need to load these environment variables.

To do that, we create a text file, call it "bq_env_vars.txt", as an example:

export BQ_DATASET_ID=dataset_id
export BQ_CREDENTIALS_FILE=~/path/to/credentials.json
export BQ_PROJECT_ID=project_id    

Each of those variables (in uppercase) have values (after the = sign) that the script will use. How do we use this file?

source /path/to/bq_env_vars.txt

That will load the variables into the current environment, and then executing the script will use them.