Afterquery is a pure-client-side javascript tool that downloads jsonp-formatted data from a given URL, applies a configurable series of transformations, and then renders the result as either a data table or a Google Visualizations (gviz) chart.
Although the javascript file happens to be hosted on a server, your data never gets uploaded; your browser handles all the processing steps internally. Also, the permissions to download the jsonp data depends on the cookies in your browser, so you can safely retrieve protected content without granting authorization to an external server.
You can then embed it in an iframe to produce easy dashboards like this:
(That's actually a live graph generated by afterquery just now.)
I wrote afterquery because I found that there are lots of cool tools for extracting data from super huge databases (so-called “big data”) but those tools can take between a few seconds and a few minutes to run. Once you have a smallish dataset produced by running your “big data” query tools, it would be nice to be able to shrink, summarize, and rotate the “not big data” as efficiently as possible. That's this.
First, you need to find a service that produces data in “rows and columns” jsonp format. That is, the structure of the object is [row1,row2,...,rown] where each row is [col1,col2,col3,...,coln].
Then you construct a afterquery URL with the jsonp URL you want to retrieve from, followed by the set of transforms you want to apply. You can just type it into your web browser's URL bar while you experiment. Then, once you have the query the way you want it, paste it into a web page inside an <iframe> tag (like the graph above) or just make a hyperlink to a full-screen chart.
The basic structure is:
https://afterquery.appspot.com?url=http://wherever/my/page/is&<transform1>&<transform2>&<transform3>...
(The default with no transforms is to just show the data in a handy table view without any changes.)
Available transforms:
&limit=n
Discard all data after n rows.
&filter=key>=value1,value2,...
Show only rows where the column named key has a value >= value1 or value2 etc. The operator (>= in this example) can be one of =, <, >, <=, >=, <>, or !=. If you specify more than one value, they are combined in an OR configuration (ie. a row matches if any of the values match). If you provide more than one &filter= clause, they are combined successively in an AND configuration (ie. a row matches only if all the filters are true).
&q=value1,value2,...
Show only rows where any of the columns contain the substring value1 or value2 etc. If more than one value is specified, they are combined in an OR configuration. If you provide more than one &q= clause, they are combined successively in an AND configuration.
&order=[-]key1,[-]key2
Sort the table in order of key1 and then (if key1 is the same) by key2 etc. If a key starts with ‘-’ that means to sort that key in descending order (largest to smallest).
&extract_regexp=key=regexp(match)regexp
Search for the given regular expression in each row in the column named key. If it's found, replace the column with the substring in the (match) part of the regexp. For example, &extract_regexp=ver=version-(.*) would replace a string version-25.4 in column ver with the string 25.4.
&group=key1,key2,...;val1,val2,...
Summarize the table data by summing and counting. This operation works like a simplified version of SQL's “group by” clause. In the resulting output, the order of the columns will be key1,key2,...,val1,val2... and there will only be at most one row with any particular combination of key1,key2,... fields. The val columns will be summed (if they were already numerical) or counted (if they were non-numeric).
A clause like &group=a,b;x,y (if x is a string and y is a number) would be equivalent to this in SQL: select a, b, count(x) x, sum(y) y from MyTable group by a, b
If you apply multiple &group= clauses, it works like using multiple nested subqueries in SQL. (That is, the output of one &group= clause becomes the MyTable of the next one.)
If you leave out the ‘;’ and the val column names, the default is to automatically include all columns other than the key columns.
If you include the ‘;’ but leave out the val column names, that means you don't want any value columns (so only the key fields will be included, and nothing will be summed or counted at that step). So &group=a,b; (with a trailing semicolon) is equivalent to this in SQL: select a, b from MyTable group by a, b.
&treegroup=key1,key2,...;[val1,[val2]]
Like &group=, but produces an output table arranged hierarchically by each key1..keyn, so you can drill down. There can be zero, one, or two val columns; the first value is the size of each box in the tree view (if omitted, they are all the same size), and the second value is the colour of each box (if omitted, the colour varies with the size). &treegroup= isn't really useful unless you also use &chart=tree.
&pivot=rowkeys...;colkeys...;valkeys...
A &pivot= clause works like a &group=
clause, but creates a pivot table. Pivot tables are a bit complicated; the easiest way to learn about them is to play with an example. Here's one to start with.
The simplest way to think of a pivot table is like this: the values originally in the columns named by rowkeys end up down the left of the output table. The values originally in the columns named by colkeys end up as headings across the top of the output table. The values originally in the columns named by valkeys end up as values in the body section of the output table. A pivot table is very handy when you have raw data in SQL-like format and you want to rearrange it to be suitable for charting (where each line in a line chart, say, is usually one column of the table).
If the rowkeys section is empty, the output will have exactly one row (with all the value fields counted or summed into that one row). If the colkeys section is empty, the &pivot= operation is essentially equivalent to a &group=rowkeys...;valkeys... operation. If the valkeys section is empty, there are no values used to calculate the table body, so it is equivalent to an &group=rowkeys...; operation.
&chart=charttype
Instead of showing a table of values, show a chart. The available charttypes are currently: stacked (a stacked area chart), line, spark, column, bar, pie, tree (see treegroup), candle, timeline, dygraph, dygraph+errors.
&title=title
Add a title to the chart.
Here is some raw data (source) produced by an analytics program:
(Tip: in the tables and charts below, click the “Edit” link in the upper-right corner to see how the query works.)
Afterquery can render it as a table like this:
Or pre-sort the table for you like this:
Or filter it by date:
Or summarize the results (like a “group by” in SQL):
Or summarize and display only a subset of columns:
Or do a pivot table (converting rows into columns):
Or filter, then pivot, and then make a chart!
Here's another dataset:
We can use two consecutive grouping operations to first get a list of serial numbers for each version, and then get the count of serial numbers per version (link):
Hmm, those version numbers are ugly because some of them have extra debug information after them. Let's trim it out using a regex:
And make a pivot table to easily show the pattern over time:
Trim out some outliers:
And graph it:
Or graph a subset of the data:
Or maybe show the top 4 versions:
There are lots of them out on the web. If your favourite database query or reporting engine doesn't support jsonp, ask them to add it!
Email apenwarr@google.com. Probably there'll be a mailing list eventually.
The complete source code is contained in render.js.