Adding Shared Queries

It is common that multiple visualizations on the same page use the same database query. For example, you may want to include both a chart and a table for the same data. Or you may want a summary version and detailed version of the same information. White Label Data makes this possible through a feature called Shared Queries. A shared query is shared between all visualizations on a page. It runs once per page and then allows multiple visualizations to use that data directly or to perform SQL transforms on that data to taylor it to specific visualizations.

In a Layer file, you add a query to the pipeline in the standard way but mark it as shared:

Query:

<query name="myquery">
SELECT * FROM MYTABLE
</query>

Pipeline:

{
    "steps" : [
        {
            "action" : "bigquery_query",
            "query_name": "myquery",
            "shared": true

        }
    ]
}

Now, any subsequent pipeline steps in this Layer or any layer that depends on this layer have access to this DataFrame. They can map it into their figures and perform transforms.

Performing SQL Transforms on Shared Queries

Queries that are marked as shared are available in memory and can be queried in SQL. You use a SQLite syntax for these queries. The above example had a shared query called myquery and creates an in-memory table with the same name. In other words, there is an in-memory table called myquery that you can query using SQL syntax with myquery as the tablename.

Query:

<query name="mytransform">
SELECT count(*) FROM myquery
</query>

Pipeline:

{
    "steps" : [
        {
            "action" : "sql_transform",
            "query_name" : "mytransform"
        }
    ]
}

The results are placed into a DataFrame and can then be used for mapping into a figure.

Option Value(s) Description
action sql_transform Specifies that it is a SQL transform on a shared query.
query_name A string The name of the query specified in a <query> tag within the combined Layer.
output_dataframe A string The name to use when creating a new DataFrame. This allows you to have multiple DataFrames in the pipeline context and to map columns from multiple queries to a single visualization. If no name is specified, the DataFrame will be named after the query name.