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}"

Leave a Reply

Using Input Forms with SQL Queries – Apache Zeppelin