Superset Tests Harness using Jinja¶

In [2]:

%load_ext jupyterlab_sql_editor.ipython_magic.trino
%config Trino.host='localhost'
%config Trino.port=8080
%config Trino.httpScheme='http'
%config Trino.auth=None
%config Trino.user='the-user'

%config Trino.cacheTTL=3600
%config Trino.outputFile="/tmp/trinodb.schema.json"

# comma seperated list of schema to cache in the schema file
%config Trino.catalogs="system,tpch"

%load_ext jupyterlab_sql_editor.ipython_magic.trino %config Trino.host='localhost' %config Trino.port=8080 %config Trino.httpScheme='http' %config Trino.auth=None %config Trino.user='the-user' %config Trino.cacheTTL=3600 %config Trino.outputFile="/tmp/trinodb.schema.json" # comma seperated list of schema to cache in the schema file %config Trino.catalogs="system,tpch"

In [3]:

%%trino --limit 3 --output grid
SELECT
    *
FROM
      tpch.tiny.orders

%%trino --limit 3 --output grid SELECT * FROM tpch.tiny.orders

DataGrid(auto_fit_params={'area': 'all', 'padding': 30, 'numCols': None}, corner_renderer=None, default_render…

Only showing top 3 row(s)

In [4]:

# Superset function to retrieve filters
def filter_values(column: str, default=None, remove_filter: bool = False):
    return VALUE_LIST

VALUE_LIST = ['Clerk#00000036', 'Clerk#000000779']

# Superset function to retrieve filters def filter_values(column: str, default=None, remove_filter: bool = False): return VALUE_LIST VALUE_LIST = ['Clerk#00000036', 'Clerk#000000779']

In [5]:

%%trino --limit 1 --jinja --output sql
SELECT
    *
FROM
  tpch.tiny.orders
WHERE
    orderkey in ({{ "'" + "','".join(filter_values('clerk')) + "'" }})

%%trino --limit 1 --jinja --output sql SELECT * FROM tpch.tiny.orders WHERE orderkey in ({{ "'" + "','".join(filter_values('clerk')) + "'" }})

Out[5]:

SELECT 
    *
FROM
  tpch.tiny.orders
WHERE
    orderkey in ('Clerk#00000036','Clerk#000000779')

Elaborate a function to build list of values¶

This function can then be registered in Superset and re-used in virtual datasets.

In [10]:

VALUE_LIST = ['Clerk#00000036', 'Clerk#000000779']

# Superset function to retrieve filters
def filter_values(column: str, default=None, remove_filter: bool = False):
    return VALUE_LIST

def quote_value(v):
    if isinstance(v, str):
        # escape quotes found in value
        v = v.replace("'", "''")
        # quote string values
        v = f"'{v}'"
    return str(v)

def sql_filter_value_list(column: str, default=None):
    """
        Build the SQL string representation of a list of values
        taking into consideration the value type. String will get quoted
        but numbers will not. Also quotes within these strings get escaped
    """
    values = filter_values(str, default, True)
    if len(values) > 0:
        quoted_values = [quote_value(v) for v in values]
        return ",".join(quoted_values)
    return None

VALUE_LIST = ['Clerk#00000036', 'Clerk#000000779'] # Superset function to retrieve filters def filter_values(column: str, default=None, remove_filter: bool = False): return VALUE_LIST def quote_value(v): if isinstance(v, str): # escape quotes found in value v = v.replace("'", "''") # quote string values v = f"'{v}'" return str(v) def sql_filter_value_list(column: str, default=None): """ Build the SQL string representation of a list of values taking into consideration the value type. String will get quoted but numbers will not. Also quotes within these strings get escaped """ values = filter_values(str, default, True) if len(values) > 0: quoted_values = [quote_value(v) for v in values] return ",".join(quoted_values) return None

In [11]:

%%trino --limit 1 --jinja --output sql
SELECT
    *
FROM
  tpch.tiny.orders
WHERE
    orderkey in ({{sql_filter_value_list('clerk')}})

%%trino --limit 1 --jinja --output sql SELECT * FROM tpch.tiny.orders WHERE orderkey in ({{sql_filter_value_list('clerk')}})

Out[11]:

SELECT 
    *
FROM
  tpch.tiny.orders
WHERE
    orderkey in ('Clerk#00000036','Clerk#000000779')

In [ ]:

In [ ]: