ob-bigquery: Emacs Org-Babel support for BigQuery

Installation

This package is not in any repository, therefore, you need to install it manually or by using any of the extensions that can consume packages from GitHub.

Manually

If installed manually in the load-path, the package can be configured using `use-package` as following.

(use-package ob-bigquery :ensure t)

Quelpa

To install the package using quelpa, install quelpa ~ and add the following to your Emacs configuration file:

(quelpa '(ob-bigquery :repo "lhernanz/ob-bigquery" :fetcher github))

External dependencies

This package requires the bq command line tool that is part of the gcloud CLI. See installation instructions here. Authentication is delegated into the gcloud command. Therefore, you need to get the right credentials by using gcloud auth login before using this mode.

The default configuration for bq uses the legacy SQL syntax. To be able to use Standard SQL, include the following lines in the ~/.bigqueryrc file:

[query]
--use_legacy_sql=false

Configuration

Use customize-group to get into the ob-bigquery group variables.

Usage

To create and execute a sample BigQuery query within an Org-mode source block, use the following example:

#+BEGIN_SRC bigquery :project "project-id" :maxrows 10 :var age=30
   SELECT name, age FROM `dataset.my_table` WHERE age > $age
#+END_SRC

Some comments about the above example:

• The project argument enables you to use different GCP projects to execute different queries. If not specified, bq will select the default project
• The maxrows argument overrides the default value of maxrows in bq to change the maximum values that the query will return
• You can use the standard var argument to create variables that will be replaced in the body. See next section about the different types of variables that are supported

Using Different Types of Variables

The package supports different types of variables, including literal values, string values, integers, and lists. Here are examples of how to use each type:

String Values

This is the most common case. The mode takes care of quoting so that you can use the same query regardless of the type of parameter.

SELECT * FROM `dataset.my_table` WHERE greeting = $greeting

Integer

SELECT * FROM `dataset.my_table` WHERE amount = $amount

Literal Values

These are useful when the variable defines and element of the query itself. Note the double dollar sign.

SELECT * FROM `dataset.my_table` WHERE $$field = $value

Lists

List are supported to cover for the multi-value use case. The list is translated into a comma separated list of values in the query. Single row tables are also supported to be able to use external tables as arguments.

SELECT * FROM `dataset.my_table` WHERE greeting IN ($greetings)

Development

This package uses emacs-buttercup to implement unit tests and makem.sh to execute the tests. Executing make will create a sandbox and execute all the tests. make lint will execute all the linters. See more options in the makem.sh github page. To create the sandbox for the first time, add the following variables to the make command install-deps=t install-linters=t.

Additional Information

• bq command-line tool reference
• Explore the bq command-line tool
• Quickstart: Install the Google Cloud CLI
• BigQuery enterprise data warehouse