statement blocks
statement
s are sql queries that hit the database and return results to your Jinja context. Here’s an example of a statement
which gets all of the states from a users table.
{%- call statement('states', fetch_result=True) -%}
select distinct state from {{ ref('users') }}
{%- endcall -%}
The signature of the statement
block looks like this:
statement(name=None, fetch_result=False, auto_begin=True)
Args:
name
(string): The name for the result set returned by this statementfetch_result
(bool): If True, load the results of the statement into the Jinja contextauto_begin
(bool): If True, open a transaction if one does not exist. If false, do not open a transaction.
Once the statement block has executed, the result set is accessible via the load_result
function. The result object includes three keys:
response
: Structured object containing metadata returned from the database, which varies by adapter. E.g. successcode
, number ofrows_affected
, totalbytes_processed
, etc. Comparable toadapter_response
in the Result object.data
: Pythonic representation of data returned by query (arrays, tuples, dictionaries).table
: Agate table representation of data returned by query.
For the above statement, that could look like:
{%- set states = load_result('states') -%}
{%- set states_data = states['data'] -%}
{%- set states_status = states['response'] -%}
The contents of the returned data
field is a matrix. It contains a list rows, with each row being a list of values returned by the database. For the above example, this data structure might look like:
>>> log(states_data)
[
['PA'],
['NY'],
['CA'],
...
]
While the statement
and load_result
setup works for now, we intend to improve this interface in the future. If you have questions or suggestions, please let us know in GitHub or on Slack.