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

The package provides several configuration options that can be customized using customize-group to access the ob-bigquery group variables.

Default Header Arguments

The following default parameters are used when invoking the BQ command:

batch

“true” - Run queries in batch mode

format

“csv” - Output format for query results

headers-p

“yes” - Include column headers in output

job-timeout

“0” - Job timeout in milliseconds (0 or empty string = no timeout)

maxrows

“100” - Maximum number of rows to return

Available Header Arguments

The following header arguments can be used in BigQuery source blocks:

project

Any value - GCP project ID to execute the query against

batch

“true” or “false” - Run queries in batch mode

format

“csv” or “pretty” - Output format for query results

headers-p

“yes” or “no” - Include column headers in output

job-timeout

Any positive integer value - Job timeout in milliseconds (empty string or “0” = no timeout)

maxrows

Any positive integer value - Maximum number of rows to return

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 configured in your gcloud settings
• The maxrows argument overrides the default value of maxrows to change the maximum number of rows 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 an element of the query itself. Note the double dollar sign.

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

Lists

Lists 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. Every time that a new version of Emacs is used (or the first time that this command is executed), you need to create a new sandbox using the first command below. You can read the makem.sh for more options.

# Initialize a permanent sandbox directory, DIR (the developer might
# choose to recreate it manually when necessary, leaving it in place
# to save time otherwise).  Then run all linters and tests.
$ make install-deps=t install-linters=t

# Run all rules.
$ make all

# Run all lints.
$ make lint

# Run all tests.
$ make test

# Run ERT tests with verbosity level 1.
$ make v=v test-ert

# Run Buttercup tests with verbosity level 2.
$ make v=vv test-buttercup

Additional Information

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