SQL Modules and Variable Expansion

The %sql cell magic serves two purposes:

• executing a SQL query statement immediately (if the -m/--module argument is omitted);
• defining a SQL module for later execution (if the -m/--module argument is used).

Variable substitution is done immediately in the first case using the IPython notebook execution environment.

In the second case a Python module is created and immediately imported, using the name supplied in the -m/--module argument. This module will have several variables defined in its namespace:

• _sql_module_arg_parser is a Python argparse argument parser that is created from any Python variable definitions at the start of the cell;
• _sql_module_main is the main query statement (datalab.data.SqlStatement object) to be executed for the cell; i.e. the one with no DEFINE prefix;
• _sql_module_last is the last query statement defined in the cell; this is frequently the same as _sql_module_main
• for each SQL query that starts with a DEFINE <name> prefix, `<name>' will be bound to that query statement.

The argparser is built by finding the start of the first query statement, and treating all the code preceding it as Python code. This code is executed in new environment that has a few helper functions predefined, and then the resulting environment is introspected to create an argument parser. For example:

x = 3

will result in a -x argument being defined with a default value of 3.

The special predefined helper functions datestring and source provide a way of generating arguments that map to table names or tables that can be bound to variables in the query. datestring takes a format argument and an offset argument (both strings); it gets a datetime, applies the offset, and uses that to generate a string based on the format, assigning the result to the variable. When binding to such a variable you would actually supply the initial datetime formatted with "%Y%m%d", or use one of the special strings "today", "yesterday", or "now".

For example:

when = datestring("%Y-%m-%d", "-1d")

would create a --when argument in the parser that could be applied using:

--when 20160228

to get:

"2016-02-27"

Similarly:

--when=yesterday

would result in a "%Y-%m-%d" formatted date for the day before yesterday.

Offsets can be specified with + or -, followed by a number, and a unit, like 'd' for days, 'h' for hours, etc. The offset can also use a comma-separated list of offsets that will be applied in order.

The logic for handling this is in datalab.data._sql.py in the function _date.

The source function is similar except that after resolving to a string it will then further try to resolve to a BigQuery table with that name. This makes it useful for writing query statements that act on log files, for example (e.g. we might use something like:

yesterdays_logs = source("myproject:mydataset.logfile-%Y%m%d", "-1d")