Apache Zeppelin is a fantastic open source web-based software that allows users to build and share great looking data visualizations using various languages, including SQL.
Apache Zeppelin allows dynamic creation of text input forms in your notebooks. Input forms can be created and referenced using a simple template language. For example, using ${variable_name}
in a SQL query will reference the value in the associated input form (in this case the “variable_name” input form). If this text input form does not already exist, Zeppelin will add it to your notebook. To set a default value for a text input form, use the ${variable_name=value}
syntax.
Note that traditionally in SQL queries text values in WHERE
clauses are enclosed with single quotes (e.g. WHERE column = 'value'
). However, surrounding a variable/template with single quotes can cause issues with the Zeppelin interpreter. To avoid this surround your variable/template with double quotes (e.g. WHERE first_name = "${first_name=John}"
.
Using a Text Input Form in SQL Queries
Here is an example running a SQL query that references the value in the “First Name Filter
” input form. Notice that the default value for this form is “John” but we have since changed this value to “James”.
%mysql
SELECT
first_name "first name",
last_name "last name"
FROM
data.users
WHERE
first_name = "${First Name Filter=John}"
Using Multiple Text Input Forms in SQL Queries
Zeppelin allows the dynamic creation of many input forms in each notebook. Here is an example of running a SQL query that references the values of multiple input forms:
%mysql
SELECT DISTINCT
first_name,
last_name,
username
FROM
data.users
WHERE
first_name = "${First Name=John}"
AND last_name = "${Last Name=Mcgee}"