🦆 Huey
Huey is a static browser-based app for exploring and analyzing data. Huey supports .csv, .parquet, .json data files as well as .duckdb database files; either as local files, or loading from url. You can also connect to an external Data Lake or Quack server.__Try Huey now__ with some sample reports using the live demo at https://rpbouman.github.io/huey/src/index.html
(Note: this is a live demo that allows you to run Huey without even downloading it. Even though it's available online, it's still a static webapp: any data you load into it is safe, and stays on your local client.)
If you prefer to watch a video instead, here's a few:
Key features
- Supports ``
.parquet`,`.csv`,`.json`,`.xlsx`(MS Excel) both for analysis as well exporting results. Huey can also read DuckDB database files, or connect to a remote catalog. - Comprehensive attributes tab to explore the structure of your dataset
- Intuitive query builder that supports projection, aggregation, filtering, and (sub)totals
- A pivot table to present analysis results
- Many different aggregate functions for reporting and data exploration
- Automatic breakdown of date/time columns to temporal hierarchy with derived attributes for year, month, quarter etc
- Extensive support for arrays and `
STRUCT`data types to allow immediate ad-hoc analysis of complex, nested data (typical for JSON data) - Export of result data and SQL queries to file or clipboard.
- Blazing fast, even for large files - courtesy of DuckDB
- Truly light-weight. Huey depends on DuckDb-WASM, and a Tabler Icons font, but nothing more. (Dependencies may be added in the future, but only when strictly necessary.)
- Accessible. Huey uses semantic HTML and aria-roles. Please let us know if you find Huey has accessibility issues!
- Run it your way!
file://` - no server required). But if you like, you can serve from any webserver like you would with any web page.
- The latest stable release is available online as Live demo site. One click and you're up and running! Even in this setup, any data you process with Huey remains local and private. There is no active server-side process.
- Huey is also a progressive web app (PWA). This lets you install Huey on your device just as if it's a native app. Running as PWA is described in more detail later in this readme.
- It's free! Huey is released under the MIT license, just like DuckDB.
Limitations
- Huey is based on DuckDB-WASM (a DuckDB instance compiled to WebAssembly). DuckDB is awesome, and WASM is too!
However, the WASM runtime imposes some limits which result in a poorer performance as compared to native DuckDB. (Note: this is not about DuckDB or DuckDB-WASM, the following limitations are mainly related to the WASM runtime)
Most notably:
- WASM in general is limited to a theoretical 4Gb memoery limit. In practice this may be closer to 3Gb.
However, it's complicated: DuckDB (and thus, DuckDB-WASM) uses advance streaming analytics, which often can process and aggregate datasets with volumes that exceed this limit.
- WASM is confined to a single thread, eliminating some of DuckDB's optimizations.
- Some of the most interesting DuckDB capabilities, such as access to external remote databases such as MySQL, PostgreSQL, and the Unity Catalog rely on extensions that are not readily available to DuckDB-WASM
That said, DuckDB-WASM is still incredibly fast when compared to any in-browser alternative.
If you find that your use-case is stretching WASM's limits or downright blocked, then you might try to use Huey as a client and connect to a DuckDB Server.
- Huey is subject to typical restrictions of a web application.
In particular, URL datasources may not be directly accessible due to same-origin policy and/or missing CORS headers.
If these issues are hampering your use case, the obvious step would be to server Huey over HTTP(S), and configure your webserver to pass the right headers.
This is a common scenario for any web application; it's just Huey is built in a way that also gets a lot done in completely static, offline scenarios.
But that alone doesn't mean you can't or shouldn't host Huey over HTTP(S)!
- Huey is best enjoyed on the desktop. Huey ought to work on a modern tablet. If you find it doesn't, please submit an issue so this can be worked on.
Huey does not work on your phone. If it does, then you just got lucky, but the experence will likely be frustrating because of the small screen size.
There are currently no plans to make Huey work on a phone.
Getting started
For a super quick start, the following sections present sample reports using the live demo.
All these examples use a URL to a publicly available dataset as datasource.
(For some of them you may need to wait a little. You'll find that most of the time is spent waiting for the download while the analysis itself is pretty quick.)
CSV Examples
- Los Angeles International Airport: Number of Flight operations, by flight type and reporting period.
This is from an open data set "Los Angeles International Airport - Flight Operations By Month" provided by the city of Los Angeles.
The raw CSV data provides the number of flight operations, as well as a timestamp and various attributes that describe the flight, such as whether it is an arrival or departure; a charter or a scheduled flight, and whether it is a domestic or an international flight.
The report demonstrates the following Huey features:
- Reporting Period timestamp is correctly detected from the CSV data as TIMESTAMP type by the DuckDB CSV reader
- Huey year and month derived attributes are applied on the row axis to produce a clear temporal breakdown
- Flight type and Arrival/Departure are placed on the columns axis
- Domestic/International is placed on the filters axis, showing all filter values in an expanded state. This lets you slice the data by checking/unchecking the checkbox for a specific filter value
- The sum of the flight operations appears in the cells
- (Sub)totals are included at the year, month and flight type level
- City of Chicago Energy Benchmarking: Use of electricity, gas and steam by district over years 2018 - 2022.
This is from the Chicago Energy Benchmarking dataset provided by the Chicago Data Portal.
The raw CSV data contains metrics for different kinds of energy and water consumption, along with the year and many columns identifying the location.
The Huey report places the Community area on the rows axis (along with the first letter of the community area for quick alphabetic browsing), the year on the columns axis, and the sum of various energy consumption metrics in the cells.
In addition, the report has an IN BETWEEN filter, allowing the data to be sliced in 3-year periods.
- Montgomery County of Maryland: Warehouse and Retail Sales.
JSON Examples
- Github Events: Actors against repo and time and event type.
This sample uses "github's public events endpoint".
The raw JSON data is an array of objects representing github events.
The event object itself has scalar properties like id, type, and created_at timestamp, as well object-typed properties repo, actor and payload.
The report demonstrates the following Huey features:
- The DuckDB JSON reader correctly extracts the created_at string to a TIMESTAMP type.
- Derived attributes year, month, and day as well as iso-time are applied on the column axis to produce a clean temporal breakdown
- Event type also appears on the columns axis, showing CreateEvent, DeleteEvent, PushEvent and so on for each timestamp
- The repo's id and name properties are extracted from the repo-object nested inside the event object and placed on the rows Axis
- In the cells, thelogin property extracted from the nested actor-object is aggregated using the list aggregator.
Parquet examples
- Train Services count across stations and service types
The raw dataset contains trips from the Dutch train network.
The huey report presents the stations on the rows and the train types on the columns, with the number of trips as cell values.
It demonstrates the following Huey features:
- on the columns, the "First letter" derived attribute is used to produce an alphabetic index for the train stations
- on the columns the "No Case" derived attribute is used for a case-normalized presentation the train types. In the original dataset, the `type` column contains both the values `Stoptrein` and `stoptrein`; by using the "No Case" derived attribute, both are presented in a single column.
Running Locally
Want to run Huey locally? No problem!
From the live demo, you can install the Huey progressive web app on your local device with just one click.
Alternatively, you can download or check out the Huey source files and resources and run it as a local static webpage in your browser.
Running Huey on your Device as Progressive Web App (PWA)
1) Visit the live demo
2) Use your browser's capabilities to install Huey on your local device. Typically this manifests itself as an installation icon in your browser's URL bar:
After installing Huey as PWA, it appears just as if it is a local native app, and you should be able to find it using your operating system's launch bar or start button:
PWA Offline
The Huey PWA caches itself automatically using your browser's caching API.
This requires a modest amount of storage, but the benefit is that this allows Huey to be used even when you're completely disconnected from the internet.
PWA File Handler
The Huey PWA explicitly advertises the ability to open certain data files.
Typically, the operating system picks this up and offer an "Open With" feature in the context menu of the file browser:
This way, you don't even need to locate the app anymore - you just right click on a file you want to analyze, and choose "Open with Huey".
Uninstalling the PWA
If you decide the PWA is not for you, that's fine - you can always uninstall it using your operating system's uninstall feature.
Running Huey from a folder on your device
1) Use git to check out the Huey github repository to a local folder, or download the repository as a .zip file and extract it to a folder.
2) Open index.html in your web browser.
Note that although Huey runs locally, it depends on DuckDB-WASM and Tabler Icons, which are served by the jsdelivr.com CDN, so make sure you're connected to the internet.
Once these resources are downloaded, they are typically cached by your browser, often allowing you to run Huey even without an internet connection.
With either approach, the Huey files and resources are available in a folder of your choosing.
Of course, if you checked out the repository you can use
git pull to update too.
Registering and Analyzing Files with Huey
Huey uses DuckDb-WASM to read and analyze data files.
General browser security policies prevent web applications from autonomously accessing files on the local file system.
Web application users need to explicitly select the files they want to analyze.
Huey then registers them in DuckDB-WASM's virtual file system so they become available for analysis.
Registering Files
To register one or more files, you can:
- Click the 'Upload...' button !upload button icon.
The upload button is always available as the leftmost button on the toolbar at the top of the page. The upload action will pop up a file browser dialog that lets you browse and choose one or more files from your local filesystem.
In the file browser dialog, navigate to the file or files that you want to explore, select them and then confirm the dialog by clicking the 'Ok' button.
Note that by default, the File Browser dialog only lists files with a extension recognized by Huey.
If your file doesn't happen to have one of those known extensions, just choose "All files .":
- If you installed Huey as PWA, and your files have an extension recognized by Huey, then you can typically also open it by right clicking the file and then choosing Open With from the context menu.
- Drag 'n Drop one or multiple files unto the "Datasources" tab in the sidebar.
Upload Dialog
Either action will open the Upload dialog.
The upload dialog will show a progress bar for each file that is being registered. Additional progress items may appear in case a duckdb extension needs to be installed and/or loaded.
After completion of the upload process, the upload dialog is updated to indicate the status of the uploads (or the extension installation, if applicable).
Items that encountered an error are indicated by red progressbars. In case of errors, the item is expanded to reveal any information that might help to remedy the issue.
Successful actions are indicated by green progressbars. Successfully loaded files are available in the Datasources tab, from where you can start exploring their contents by clicking the explore button !explore button. As a convenience, the explore button is also present in the upload dialog.
Datasource Tab
Successfully upload files are added to the Datasource tab.
Each Datasource has a couple of buttons to work with the datasource:
- The Download Button
lets you download the data. You will be prompted to choose the output format. You can read more about data export in the section about the Export dialog.
- The Datasource Settings button
opens the Datasource Settings dialog.
- The Remove button
removes the datasource from the tab.
- The Analyze Datasource button
loads the datasource attributes into the Attributes tab so you can analyze its data using the Quey builder.
Huey will attempt to group files having similar column signature. The group appears as a separate top-level node in the Datasources tab, with its individual files indented below it.
A file group has its own explore button, so that you can not only explore the individual files, but also the UNION of all Files in the group:
Files that cannot be grouped appear in a separate Miscellaneous Files group.
Opening DuckDb files
Apart from directly reading data files, Huey can also open existing DuckDB database files and access its tables and views.
The process for accessing duckdb files is exactly the same as for accessing data files.
Huey assumes data files with a
`.duckdb` extension are DuckDB database files.
If your DuckDB database file happens to have another extension - that's totally fine!
They just won't appear automatically in the browser's File Browser dialog, because by default, that only lists files with extensions that Huey knows about.
You can always override that and select "All files (.)".
Successfully loaded DuckDB database files appear in the DuckDb Folder, which appears at the top of the DataSources tab.
The schemas in the duckdb database file are presented as folders below the duckdb file entry, and any tables or views in the schema are presented below the schema folder.
Each table or view has an explore button which you can click to explore the data.
Note: We ran into a limitation - when the duckdb file itself refers to external files, then it's likely that Huey (or rather, DuckDB-WASM) won't be able to find them.
But native duckdb tables, as well as views based on duckdb base tables work marvelously and are quite a bit faster than querying bare data files.
Using Remote Datasets
Huey is not just for local files! You can also access remote data by registering a URL or
.
You will be prompted to enter the URL:
After confirming, the upload dialog appears just like when uploading local files.
Note that loading data from URL is subject to certain restrictions due to browser security policies.
Typically the URL needs to be either in the same domain as from where Huey is served, or the remote server needs to pass CORS headers to overcome the same-origin policy.
In addition, some URLs might require authentication.
If that is the case, you can use the [Secrets Manager](#secrets-manager" target="_blank" rel="noopener">connecting to a remote Catalog to create and maintain the secret.
Remote Catalogs
In Huey, you can use the Catalogs Manager to attach to remote databases.
In this README, the Catalogs Manager is described in detail in its own section.
Exploring Datasources
The Datasources have an explore button !explore button.
After clicking it, the left sidebar switches to the Attributes tab, which is then is populated with a list of the Attributes of the selected Datasource.
Attributes, Derived Attributes, and Aggregates
You can think of Attributes as a list of values (a column) that can be extracted from the Datasource and presented along the axes of the pivot table.
The pivot table has two axes for placing attribute values:
1) Attributes appearing on the horizontal axis are used to generate column headers. For this reason the horizontal axis is also known as the 'columns'-axis.
2) attributes appearing on the vertical axis are used to generate row headers. For this reason the vertical axis is also known as the 'rows'-axis.
The selection of attributes and their placement on the axis is represented by the Query Builder.
The following screenshot may help to explain:
In the screenshot, the Attribute Sidebar is at the left side. The workarea is to the right of the Attribute Sidebar.
The Query Builder is at the top right of the workarea. The pivot table is at the bottom right.
Searching Attributes
When there are a lot of attributes, it can be useful to use search to find them.
To do that, use the Attribute Search feature: Simply type a part of the attribute name in the search input.
After a brief timeout, the list of attributes will automatically show only those attributes that (partially) match the search string:
The search string is treated as a regular expression, and is matched in a case-insensitive manner, making it both easy and powerful.
For extra convenience, the attribute searchstring also supports
% as wildcard for any sequence of characters (just like the SQL LIKE operator).
Query Builder
The screenshot shows a simple query, with one attribute "hvfhs_license_num" placed on the columns axis of the Query Builder.
Placing the attribute on the Columns axis causes its values to be shown as column headings of the pivot table.
Likewise, the attribute "dispatching_base_num" is placed on the Rows axis, and this causes its values to show as row headings in the pivot table.
Finally, the generic "count" aggregator is placed on the cells axis. This causes the value of the aggregate to be computed for each combination of values of the rows- and columns-headings.
The aggregated value are placed in the cells at the intersection of the corresponding row and column.
By default, the cell headers appear on the Columns axis, below the last Column Axis item (if any).
The cell headers can also by placed on the Rows axis, in which case they appear right to the values of the last row axis item:
(Note that for this particular example, which has only one aggregator on the cells-axis, its placement on either cells or rows doesn't make much difference.)
Placing Attributes
Attributes can be placed by clicking one of the desired axis-placement buttons, which appear to the left of the attribute name.
Once the items are placed in the rows and column axes, you can move and flip the axes by clicking on the axis icon that appears right before the "Rows" and "Columns" axis header text.
Clicking on the axis icon of the Cells axis will affect the placement of the cell headers on either of the Rows- and Columns- axes.
Items that are placed inside the Query builder have buttons to manipulate them: At the left and right side of the query items, there are buttons to move the item a single position to the left or right within the axis.
Items on the rows and columns axes also have a button to move the item from one axis to the other.
Items also have a button to remove it from the query.
Drag and Drop
Instead of using the buttons in the Attribute sidebar, you can also drag Attributes from the sidebar and drop them at the desired position in the Query Builder.
Drag and Drop also works for items that are already placed inside the Query Builder.
Query Execution
After changing the Query, it must be executed so the pivot table may be updated.
If the "Autorun query" checkbox on the toolbar is checked, this will happen automatically.
If the "Autorun query" checkbox is not checked, then you can execute the query by clicking the "play" button that appears just in front of the checkbox label:
Derived Attributes
Right before the attribute item, there is a widget to expand the Attribute so its derived Attributes and Aggregates are revealed.
You can think of a derived attribute as an expression (formula) that calculates some aspect from a single value from the attribute upon which it is based.
For example, from an attribute that represents timestamp values, we can extract only the date part, or only the time part, or even the individual parts like year, month, and so on.
The values that are thus derived from the original attribute values can be thought of as a 'virtual' column.
Derived attributes may be placed on either the rows or the columns pivot table axis.
Derived attributes may be used as filter too, and they can also be aggregated.
The attribute's data type primarily determines which derived attributes are supported.
Derived attributes tend to be grouped in folders around a similar use case or topic.
Here's a (non-exhaustive) list of derived attribute folders:
- date fields: Applies to attributes with temporal data types, like
`DATE` and `TIMESTAMP`. Derived attributes in this category are mainly to present temporal data in a hierarchically organized breakdown, as well as to offer various formats and labels.
Typical use cases are to compare data aggregated in the cells across years, month or day of the week.
- time fields: Similar to date fields, but for those data types that also carry a time part.
- string operations: Applies to attributes with a text type.
This offers different collation variants of the text data to support case-insensitive filtering and/or sorting as well as actual case conversion.
- hashes various hash functions, which may be useful to compare large text fields
- array statistics: for calculating aggregates on the elements of array-typed attributes.
Array statistics are described in more detail along with other types of aggregates.
Aggregates
Aggregates are expressions that calculate a single result on a collection of values.
In statistics, this concept is also known as data reduction, as it takes many values as input to produce a single output value, which somehow represents the entire group of values.
Typical examples of aggregate operations are counts, summation and averaging, but there are many more and more sophisticated aggregate operations.
To understand aggregates in Huey, it is useful to distinguish a few different ways to categorize aggregates:
- aggregation operation:
This categorization refers to the method that is used to produce the output value based on the collection of input values.
For example, a count simply returns the number of input values, while summation works by adding all the input values together and returning the total.
The attributes tab of the sidebar has a generic count aggregator at the very top.
All attributes also have a count and distinct count aggregator, which appears together with the derived attributes when you expand the attribute.
Most attributes have a statistics folder which contains basic statistical descriptive operations like min (minimum), max (maximum), median and mode, as well as the special purpose entropy aggregator.
In particular, numerical attributes have additional aggregators like sum (summation), avg (average), stdev (standard deviation), as well as more sophisticated ones like skewness and kurtosis.
Most attributes also have a list aggregators folder.
The aggregators in the list aggregators folder return a structure (like histogram) or a list as output value.
List aggregators are not for general purpose, but can be useful for data exploration or text analysis.
- source or scope of the input values for the aggregation.
In Huey, we can distinguish the following scopes: cell aggregates, axis aggregates, and array aggregates.
Each is discussed in more detail below.
Cell-aggregates
Cell aggregates are items used to populate the cells of the query result.
Cells refer to the intersections of the items appearing on the rows- and columns axes.
Cell aggregates take their inputs from all the rows in the underlying dataset that correspond with the values on the rows- and columns dataset.
If we consider the entire pivot table as a SQL query, the cell aggregates would be plain aggregate functions in the
`SELECT`-list, while the items on the row- and cells- axes would appear in the `GROUP BY`` clause.
In Pivot tables, cell contents are always aggregate values. In the Query editor, cell-aggregates are created by simply placing an aggregate on the cells-axis.
Note that the cells axis only accepts aggregate items. This is intentional: the cells-axis is to define the content for the cells, and almost by definition, any given cell corresponds to a collection of rows, and thus requires an aggregator to produce a single value to populate the cell with.
In the context of OLAP and pivot tables, Cell-aggregates are the most common use case. But Huey also defines axis aggregates and array aggregates (discussed below).
Axis-aggregates
Axis aggregates are aggregate items that appear on any other axis than the cells axis.
Just like cell aggregates, axis aggregates also take values from the underlying rows as input values. But while cell aggregates are calculated on only the rows corresponding to the intersection of the rows- and columns- axes, axis aggregates are computed with respect to a particular partition of the items from the axis on which it is placed.
Functionally, axis aggregates can be used to calculate (sub)totals. In this regard they are somewhat similar to the totals feature, but without generating super aggregate rows.
If we consider the entire pivot table as a SQL query, axis aggregates would be window functions over a partition of items from the axis on which it is placed.
Axis aggregates are created by placing an aggregate item on any axis that is not the cells-axis. This can be done either by clicking the rows- or columns- button on an aggregate item in the Attributes tab. Alternatively, an aggregate item can be dragged from the Attributes tab to the axis. Finally, items on the cells axis can also be dragged and then dropped on a non-cells axis. Once the item is placed, any non-aggregate items that appear right before the new axis aggregate item are used to define its partition.
Once the axis aggregate is created it can be freely positioned anywhere on its axis - this will not change the partition definition. If an item that is used in the partition definition of any axis-aggregates is moved or removed, then that item is removed from the partition definition of those axis-aggregates.
All aggregate operations that are available for cell-aggregates are also available as axis aggregates.
Array aggregates
While cell-aggregates and axis-aggregates take input values from the underlying rows, array aggregates apply to the elements of a single array-typed value. In the Attributes sidebar, array attributes can be found in the array statistics folder.
Array aggregates are technically derived attributes for array-typed attributes that happen to apply an aggregate function to the array elements. The repertoire of aggregate operations for aggregate elements is largely the same as for cells- and axis- aggregates.
Structured types, Arrays, and Maps
Attributes can have any kind of datatype, including composite or "nested" data types - that is, types whose values are not scalar, but which consist of multiple elements. Especially when exploring JSON files one is likely to encounter attributes having these types.
Structured Types
Values with a Structured type (STRUCT) are in the end just values, and can be projected on the query axis as such. Attributes of this type also have a "structure" folder that gives access to its members.
Members are also just attributes, and will have their own derived attributes and aggregates, in accordance with the member type. Of course, members that are themselves of a structured type have their own structure folder that gives access to its members.
Arrays
Arrays are also just values and can be treated as such.
Attributes of an array type have a set of "array operations" derived attributes:
- elements: unnests the array and projects the element value on a separate tuple. Just like with members of structured types, array elements are just like attributes and may have derived attributes and aggregates in accordance with their type.
- element indices: unnests the array, and projects the element index. If both elements and element indices appear together on the same axis, then they are unrolled at the same level, so that the indices and the element values refer to the same element.
- length: returns the length of the array.
- sort values: array value after sorting the elements
- unique values: (sorted) array value after removing the duplicate elements
- unique values length: length of the deduplicated array.
When the elements or element indices derived attributes are applied to multiple, independent attributes, then they are unrolled independently, in order of appearance on the axis.
Arrays also support a collection of "array statistics":
Array statistics are special derived attributes that calculate an aggregate value over the array's elements.
Maps
Maps are structured types that are somewhat similar to Arrays. While arrays have an ordered collection of elements with an associated integer index, Maps are an unordered collections of entries. Map entries are values (which can be of any type) which are uniquely identified by a key, which also may have any type.
Maps have a folder with map operations:
- entries gives access to the key- and value- derived attributes.
- entry count: the number of entries in the map
- keyset: the (sorted) list of keys.
Filtering
The query editor supports a special Filters axis. Items placed on the filters axis represent conditions that are applied on the underlying dataset. Items can appear independently on the filter axis: they are not automatically visible in the query result, but items that appear on the filter axis may also (additionally) be placed on the rows or columns axis.
Immediately after placing a new item on the Filters axis, the Filter Dialog pops up right below the Filter axis item. The Filter dialog lets you choose values and operators to filter the query results.
Filter Types
The Filter type dropdown appears in the top of the Filter Dialog. Here you choose the operator that should be used to filter the data. The options are:
- __Include__: the values in the data must match any of the filter values exactly
- __Exclude__: rows from the data appear only when the value from the respective item does not match any of the filter values. (Negated include)
- __Like__: the values in the data must match the pattern of one or more filter values. The pattern is a simple
You finished the article!
Want to go deeper or explore more stories?