Skip to main content

Add Query Result Transform

Transform Query Results

The query results in Querybook can be transformed to provide meaningful statistics or visualization. This can be tailored to the needs of the organization through the use of web plugins. Common use cases include:

  • Add an external URL to the result cell
  • Transform the cell into images or videos
  • Format a number cell into a human-readable format
  • Get the sum/avg of cells in a column

To implement the features above, we would need to add detectors, analyzers, and transformers. In the next section, we will cover each in detail as well as how to add them to Querybook.

Detectors

Detectors are used to figure out the "type" of a column. The detector gets provided with the column name and all values under that column to evaluate the type of the column.

There are three things to note here. First of all, since the query results of Querybook are fetched from the source via CSV and the query samples are fetched directly from the query engine, we must ensure the detector can detect both string and native types. For example, a number type detector must check if the value is a javascript number or a number string. Another thing to watch out for is that the type detected does not need to be associated with the actual type of the query engine. For example, a URL type's underlying database type is most likely to be varchar. Last but not least, there is no need to check every single value under a column for efficiency purposes. The default Querybook type detectors take a random sample of the values and only check based on that.

Analyzers

Analyzers provide meaningful statistics such as average, min, max, and median. They form many to many relationships with the type detected by detectors. The stat generated by an analyzer is calculated based on all values of a column. Since Querybook's frontend can only show a limited amount of rows, the stats generated may not be representative of the actual query result and should only be used as a reference. You can view the stats of a column by hovering over the zap icon in the column header.

Transformers

Transformers can be used to create alternative views of a column cell. Similar to analyzers, one transformer can be applied to multiple types provided by detectors. Each transformer can be thought of as a React render function where it turns a primitive javascript value into a React component. By hovering over the zap icon in the column header, you can see and toggle all possible transformers for a given column. Additionally, clicking on the zap icon would toggle either the first transformer or the last active transformer. For simplicity, only one transformer of a column can be active at a time.

Implementation

Depending on the use case, transformers/analyzers/detectors can be added to Querybook either directly in the source code or as a plugin. The default transformers/analyzers/detectors are located under querybook/webapp/lib/query-result/ and they serve as examples of how to add these features.

For org specific use cases, you can add transformers/analyzers/detectors by setting the following variables in the window object in the web plugin (See this Plugin Guide to learn how to set up plugins for Querybook). They are:

window.CUSTOM_COLUMN_STATS_ANALYZERS;
window.CUSTOM_COLUMN_DETECTORS;
window.CUSTOM_COLUMN_TRANSFORMERS;

The typing for these variables are defined in App.tsx for reference.