crisp-bigquery | Starter project with full stack BigQuery | GraphQL library

At the end of this section you will have built and deployed a website hydrated by a public BigQuery dataset with GitHub data. All the steps below can be completed in the cloud using a browser. Another option is to use your local development environment and install Google Cloud SDK there. Both options are described below. :bulb: For the browser option you can get the repository cloned and the solution built (with working website you can interact with and query GitHub data) in under 15 min. Admittedly, this timeframe does not include the first step e.g. creating Google cloud account if you don't already have one. The browser based approach uses Google Cloud Shell which is a free Linux VM. If you take this option then there is no need to install anything so skip the Step 2. If you prefer to use the local development environment instead then do not skip this step and execute all commands locally where the Cloud SDK is installed. You won't need to open the Cloud Shell if you take the second option. Another website (developed using Crisp BigQuery as a starter project): COVID-19 Query Tool. Instead of data related to GitHub projects it uses COVID-19 data made available via COVID-19 Open Data repository.
Create Google Cloud Platform (GCP) account and project. Start at cloud.google.com and click on "Get started for free" button. Google will ask for a credit card that will be used for identification and not for payments. The card won't be charged unless you manually upgrade your account to the paid one which you can do later. If you upgrade, do not forget to setup custom cost controls.
Install NodeJS and Google Cloud SDK. Download and run a pre-built Node installer. Then install yarn: npm install yarn -gTo install Cloud SDK follow instructions on this page.
Enable BigQuery API for the project. Go to GCP API Dashboard and ensure the project created at the Step 1 is selected. Then click on "+ENABLE API AND SERVICES" button at the top of the page. Choose BigQuery API on the subsequent "Welcome to the API Library" page and enable it.
Create a table. Create samples.github table optimised for better performance and lower data usage. It will contain GitHub data. In BigQuery Web UI click on the "Activate Cloud Shell" icon and execute the command in the Cloud Shell: bq query --use_legacy_sql=false --destination_table samples.github --time_partitioning_field created_time --clustering_fields repository_name,repository_language --replace '#standardSQL SELECT repository_name, repository_language, repository_size, repository_homepage, actor_attributes_login, repository_owner, TIMESTAMP(created_at) as created_time FROM bigquery-public-data.samples.github_timeline WHERE created_at IS NOT NULL AND repository_name IS NOT NULL AND repository_language IS NOT NULL and repository_owner IS NOT NULL AND repository_size IS NOT NULL AND LENGTH(repository_name) >= 5' The dataset samples with the samples.github table should be created. Queries against this table will incur significantly lower data usage (*) in comparison with the public dataset we used as the data source. The created dataset takes 286 MB counted towards BigQuery free 10 GB storage allowance. (*) That's because the table we created contains a subset of public data, is partitioned e.g. split internally into daily partitions and the frontend allows only queries with the timeframe up to one week long. It means the BigQuery engine doesn't have to scan the whole table as it can select only few daily partitions which brings down the data usage. The usage depends on the amount of data processed by the BigQuery engine while executing the request and not on the size of the returned data. :bulb: For queries covering wider timeframes e.g. years and tables that have small amount of daily data, partitioning into daily partitions could have a detrimental effect on data usage. On the one hand the engine cannot be selective too much in terms of partitions and on the other hand the minimum partition size could be greater than the amount of daily data thus increasing the volume of disk space processed by the engine.
Change and display the table settings. Execute commands: bq update --require_partition_filter samples.github bq show samples.github The first command requires all queries to take advantage of partitioning. The second one shows the table information including the data storage it takes.
Create a service account and give it the permissions to query our dataset. In the following commands: gcloud iam service-accounts create <sa-name> --display-name "<sa-name>" --description "Test SA - delete when not needed anymore" gcloud projects add-iam-policy-binding <project-name> --member=serviceAccount:<sa-name>@<project-name>.iam.gserviceaccount.com --role roles/bigquery.jobUser replace the placeholders: <sa-name> - replace with service account name, <project-name> - replace with the project name. and execute the commands. The role bigquery.jobUser granted by the last command is not enough. Another permission is required and there are two options to add it:
Grant the bigquery.dataViewer role to the service account: gcloud projects add-iam-policy-binding <project-name> --member=serviceAccount:<sa-name>@<project-name>.iam.gserviceaccount.com --role roles/bigquery.dataViewer Then proceed to the next step. Not recommended unless you are using a throw-away project. The drawback of this approach is granting permissions to view all project datasets.
Take more granular approach (recommended) by allowing the service account to query one dataset only. This is the approach described below. Execute the commands: bq show --format=prettyjson samples >/tmp/mydataset.json vi /tmp/mydataset.json Using vi, append the following item to the existing access array and replace the placeholders before saving the file: , { "role": "READER", "userByEmail": "<sa-name>@<project-name>.iam.gserviceaccount.com" } Execute the command to effect the changes for the samples dataset: bq update --source /tmp/mydataset.json samples
Save the service account credentials. Save the credentials (including the private key) into a disk file key.json: gcloud iam service-accounts keys create ~/key.json --iam-account <sa-name>@<project-name>.iam.gserviceaccount.com
Clone the repository and copy the credentials file. To clone the repository to your workstation or Cloud Shell execute: git clone https://github.com/winwiz1/crisp-bigquery.git cd crisp-bigquery The current directory has now been changed to the root of cloned repository. Copy the file key.json created at the previous step to ./key.json. If the repository was cloned to a workstation, you can use SSH to connect to Cloud Shell or simply copy and paste the content of the file.
Build, test and run the solution. Edit the file ./server/.env and add the GCP project ID to it. Then from the repository root execute the command: yarn install && yarn test Assuming the tests finished successfully, execute: yarn start:prod Wait for the message Starting the backend... and point your browser to localhost:3000. If you used Cloud Shell to build the solution, click on the Web Preview icon instead and change the port accordingly. You should see this page: Click on the "New query" button. The data fetched by the backend should be displayed in the table. You can collapse the "Query Options" section by clicking on its header in the top left corner and paginate through the data using the control at the bottom of the page: Then try to submit a more restrictive query, for example with lowercase 'c' as the Repository Name pattern and uppercase 'C' as the Repository Language pattern (do not type quotes). Resting the mouse cursor over the page number shows the tooltip with additional information: