Currently we always immediately execute when the query changes.
But not everybody likes that.
Should be possible to toggle whether the query executes automatically or explicitly on request.

I believe there are several aspects to this

• items on the rows and/or columns axis in the query editor should give access to a simple filter that allows include/exclude of values. Typically these would be used for multiple values making it impactical to display them
• there is nothing in principle against having filters on the cells axis but we have to think about what it means. Does it mean the corresponding column values are filtered before doing a pivot query? Or should they apply to the aggregate results. Also, for typical, classic metrics it is unlikey simple inclusion / exclusion is enough. It typically doesn't make sense to consider metrics on individual value level, it would be more about value ranges and/or buckets of values.
• There is a need for a 'slicer' filter functionality. By that we mean a set of restrictions that are not visible or rendered in the pivot table, but which are visible in the query axis. For these types of filters it would make sense to see the selected values in the slicer query item and it should also be possible to see multiple values. In practice the slice would just be another axis in the query editor, but a special one (just like the cells is a special one); one of the features of the slicer axis could be that the items could be expandable / collapsible, so as to reveal or hide its selected values.
• sometimes, for some datasources, it may make sense to apply transformations are early as possible. So it could make sense to have filter support at the Attribute UI level

When rendering the columns with multiple aggregates on the columns axis, the width of the column header is repeated consistenly, even if the column for which the value is not rendered (because of multiple cells) could be much more narrow

We currently do not have a way for the user to control the way files are read.

column width for "Reconciliation: FS Item : SCOA" column is too big

Having a setting for a max default column width would be nice: 450px

Sample Data.csv

Consider a dataset like this: LAPD calls for service https://data.lacity.org/api/views/iy4q-t9vr/rows.csv?accessType=DOWNLOAD

It would be really convenient to be able to sort on the count and get a ranking for which codes are issued most often.

Would be nice to show the value of a cell by hovering over as a title:

With a title:

Would be nice to be able to open a SQL file and then execute the contents.

This would be primarily useful for power users to do whatever is not supported through the ui.

When analyzing a list of files at once, it would be convenient if we could hit a checkbox to include/exclude a particular file. We could do this from the header:

Derived date fileds like month num, day of week, etc. should be formatted according to the localized date format (or whatever the user has configured through settings)

The way this will work is we will create a DateFormatter (using Intl) and then convert the values receieved from duckdb

The picklist filter goes a long way on its own, but sometimes it would be useful if the results would not be cluttered by the values that are already eliminated by any other filters that happen to be active.

This would be a simple on/of switch (checkbox) in the global scope. Users would be able to control it from within the filter dialog itself, and possibly also from the settings dialog. The state of the setting would be treated as a user preference (although the user might decide to change the setting interactively, depending on what data set they are currently examining)

Need a proper query progress indicator to indicate the app is busy processing.

Right now it's not super useful as huey does not change the in memory database.
but at some point we could imagine users chosing to have files copied to the database, perhaps even remodel it a bit, create a datamart, etc.
And then it would be useful to download the database so it could be uploaded a next time and have everything in place.
According to @carlop this would work:

Copy database statement? That has some weight, but works for sure. Basically attach x, copy to x, detach x, .download x

So we're storing this idea here for now.

Ideally, huey should have all the features to create the result for the one billion row challenge (1brc).

https://github.com/gunnarmorling/1brc

When only one query axis (rows or columns) is populated, we could run a query of the form:

SELECT <tupleitems>, <cellsetitems>
FROM datasource
GROUP BY <tupleitems>,
ORDER BY <tupleitems> 

and use the results to populate both the cellset and the tupleset, whereas now we always do one query for the tuples and one for the cells. In other words, we could maybe cut the wait time in half of we'd do just one query.

File: https://static.data.gouv.fr/resources/populations-legales-communales-2017-2021/20231228-135143/poplegales2017-2021.parquet

Query: Rows(ANNEE_RP, CODDEP)
Cells: count(*), SUM(PMUN)

Results:

Equivalent duckdb query:

select ANNEE_RP, CODDEP, count(*), sum(PMUN)
from "C:\roland\projects\QuaQuery\files\poplegales2017-2021.parquet"
group by ANNEE_RP, CODDEP
order by ANNEE_RP, CODDEP

Results:

┌──────────┬─────────┬──────────────┬───────────┐
│ ANNEE_RP │ CODDEP  │ count_star() │ sum(PMUN) │
│ varchar  │ varchar │    int64     │  int128   │
├──────────┼─────────┼──────────────┼───────────┤
│ 2017     │ 01      │          393 │    643350 │
│ 2017     │ 02      │          800 │    534490 │
│ 2017     │ 03      │          317 │    337988 │
│ 2017     │ 04      │          198 │    163915 │
│ 2017     │ 05      │          162 │    141284 │
│ 2017     │ 06      │          163 │   1083310 │
│ 2017     │ 07      │          335 │    325712 │
│ 2017     │ 08      │          449 │    273579 │

Right now, the upload experience is not as intuitive as it should be.
Users manage to upload files, and they succeed, but then are surprised that nothing happens after upload.

It would be good to have a popup after upload to nudge the users in the right direction.
It should probably be configurable whether such a popup should always appear.
Perhaps, multiple actions are possible after upload, and it should be configurable what action to take.

We have the following type systems to deal with:

• javascript native typesystem
• duckdb column type system
• arrow/duckdb wasm resultset type system
• dom typesystem
• type and value presentation to the user

the relationships between these systems:

• when we onboard a new datasource, we get column metadata which we obtain from duckdb using a DESCRIBE statement
• the column metadata controls the options in the AttributeUi. Each column is always available as itself, but its data type determines which aggregators and derivations are available.
• derivations typically have their own, fixed column type.
• some aggregators also have a fixed column type. Example: STDEV
• some aggregators will (should) always have a column type identical to the column type of the attribute. Example: MIN/MAX/
• some aggregators are always in a particular family of types, example: COUNT will always be some positive integer type
• some aggregators have a column type similar to that of the attribute, but with a different precision. Example: SUM

When we execute a query to populate the axes/tuples, we obtain a resultset as an "Arrow table". This is where it becomes interesting:

• the resultset has a schema property with resultset metadata, which is a list of fields that have an Arrow type object as well as a typeId. See: https://github.com/apache/arrow/blob/740889f413af9b1ae1d81eb1e5a4a9fb4ce9cf97/js/src/enum.ts#L158
• the mapping from duckdb column type to arrow data type may sometimes be surprising. For example, a duckdb HUGEINT is mapped to an Arrow Decimal, presumably because the largest integer type in Arrow is not precise enough to represent duckdb HUGEINTs. See duckdb/duckdb-wasm#1579
• the mapping from Arrow types to javascript types may also be surprising. In the aforementioned example, the ArrowDecimal is represented in javascript as a Uint32[4]. We have also seen an Arrow Timestamp (coming from a duckdb TIMESTAMP, which is reasonable) whose javascript representation is a Number primitive (duckdb/duckdb-wasm#1563 (comment)). The reasoning in this case is that the Number represents the number of milliseconds from epoch and can thus be used to instantiate a javascript Date object, which would approximate the duckdb TIMESTAMP value. The reason for not returning a javascript Date instead is that the duckdb TIMESTAMP has microseconds precision (in fact it is defined as the number of microseconds after epoch), whereas the javascript Date is only in milliseconds after epoch. By returning a Number that includes 3 decimals, no precision is lost while the conversion to javascript Date is still conveniently easy.
• Arrow has a concept called "storage type" and a given logical arrow type may be represented by several different storage types, depending on parameters;

Once we got the value from the resultset, we often need to format it so it can be presented in the UI. For formatting of numbers and dates we use the Intl.NumberFormat or Intl.DateTimeFormat instances and those accept javascript values

The real trouble starts when we need to use the values returned by a resultset as values for parameters in a prepared statement. There is absolutely no guarantee that this will work. Even a relatively simple case, where we receive a Javascript BigInt instance from the resultset fails when we try to bind it (see: duckdb/duckdb-wasm#1563)

Idea is to have a new tab in the sidebar, next to the attributes that keeps track of all queries we executred on the current datasource, along with some statistics like execution time and last execution

Queries in the history list are clickable; click one will reload that query and execute it.

Excel pivot tables seem to automatically "hierarchize" the items on the rows axis:

I think it would be cool to provide something like that.

I think it would be good to build this before building "proper" hierarchies (see: https://github.com/rpbouman/huey/issues/new) as this functionality would already bring in a lot of the capabilities needed for proper hierarchies, while it would be much easier to implement as it presumes much less from the user and thus the UI (in particular, in case of proper hierarchies users need a ui to define how the hierarchy is implemented, whereas this proposed feature simply treats all items on one axis as the levels of one hierarchy)

When using a duckdb file in which one of the columns is numeric, the following error appears. I've done several tests with other banks and they all show the same error.
Note: With csv files it gave the same error.

The min and max icons are a constant source of distraction when doing demos and it's now way beyond level chuckle.

Firefox does not support the Popover API (yet). This means many (most) huey dialog interactions don't work or are broken.
Apparently firefox has support for the api but its not on by default. It is expected to land "soon"

The query ui items should have tooltips to explain what they are doing (esp. when it's about a derivation)
Following screenshot is a mock up showing the tooltip (title attribute):

We should change the item instantiation code to include a title that describes the item.

We currently use a generic database icon for duckdb files. Would be nice if it could be more specific

This is an experimental browser feature but well worth following. It somewhat allows us to open a file chooser in a specific directory

https://developer.mozilla.org/en-US/docs/Web/API/Window/showOpenFilePicker

Before:

After click

We have a csv upload with data that looks like this:

LOGDATE,LOGTIME,SERVER,TASK_ID,TYPE,Type of Measurement,MEASUREMENTS,LOAD,ITERATION,ITERATION_CONTINUES,PROOF,RESPONSE_TIME,RESPONSE_TIME_S,ITERATION_DATE,ITERATION_TIME,ServerType,CPU,Server Capacity
11/27/2023,10:10:15 AM,host1,MIO0000098,Forced Logical Read,HL Logical Read,All,20% CPU 10%,1,1,"50,000",6,6,11/27/2023,10:10:09 AM,DB,48 No Boost,DB 48 No Boost
11/27/2023,10:10:58 AM, host1,MIO0000098,Forced Logical Read,HL Logical Read,All,20% CPU 10%,2,2,"50,000",6,6,11/27/2023,10:10:52 AM,DB,48 No Boost,DB 48 No Boost
11/27/2023,10:11:42 AM, host1,MIO0000098,Forced Logical Read,HL Logical Read,All,20% CPU 10%,3,3,"50,000",6,6,11/27/2023,10:11:36 AM,DB,48 No Boost,DB 48 No Boost
11/27/2023,10:12:25 AM, host1,MIO0000098,Forced Logical Read,HL Logical Read,All,20% CPU 10%,4,4,"50,000",6,6,11/27/2023,10:12:19 AM,DB,48 No Boost,DB 48 No Boost
11/27/2023,10:13:08 AM, host1,MIO0000098,Forced Logical Read,HL Logical Read,All,20% CPU 10%,5,5,"50,000",5,5,11/27/2023,10:13:03 AM,DB,48 No Boost,DB 48 No Boost

DuckDB correctly infers a DATE datatype for LOGDATE column. In the resultset, these are returned as JavaScript Date objects. Then, when we try to fetch the cells, the condition on the aggregate query fails to retrieve the tuple values.

Scrolling feels slower than necessary. For a large part this is due to how we handle it now:

• we buffer scroll events and wait for a timeout (couple of hundred milliseconds)
• when the timeout expires, we switch to a busy state, which pops up a progress indicator and blurs the output
• we do a query when necessary, and then render the results.

The consequence of this approach is that every scroll movement feels as if it requires backend action. Even when we have a suitable amount of cached tuples and cells, it still feel slow.

what we should do is:

• immediately respond to scroll event, or at the very least shorten the timeout
• whatever amount of data we can serve from the cache should immediately be rendered without popping up the spinner
• after all scroll events died out, check if are missing tuples/cells, and if necessary, pop up a spinner and do the necessary backend queries.

Currently there is no way to filter on NULL values

Detection of file type signature is now based on:

• datasource type (must be identical )
• file extension (must be identical if file)
• column names (must be identical)
• column types (can be fuzzy, based on loose typing)

this works pretty well but there are cases where the loose typing is still too strict.

In some cases it may be more appropriate to lump all files together that have identical column names, regardless of types.

Alternatively if we would have a proper wizard for ingestion we could let the user control exactly which files should be lumped together.

Looks like none of the controls in the SettingsDialog have tooltips (titles)

my_table.csv

The date and time derivations were a temporary crutch to allow friendly formatting of timestamp values.
Now that we have formatting support also on the axes, we should reconsider, as the formatting should now be done by formatters.

As I see it now there are two possibilities :

1. the current implementation of these derivations was always wrong, as they merely format the timestamp value into a iso date and time respectively. The proper implementation should have delivered a duckdb DATE and TIME value respectively, and these can then be formatted into user friendly values

2. The current date and time representations were chose so precisely because they represent ISO date and time, which we think is a good and desirable thing as they are very portable and "universal". Derivations that deliver a DATE and TIME datatype are still useful because they can be formatted into local formats, but that does not change that we still would also like to be able to easily get a plain iso date or time, and since the current implementation does precisely that, it seems wasteful to first have duckdb create DATE and TIME values, and then letting the result javascript Date values be formatted down again to strings.

This is a long term goal.

Idea is to allow datasets that have a recursive foreign key to be presented as a tree view, including expand/collapse functionality, and having the non-leaf nodes show the aggregated result over its descendants.

In theory:

• there could be multiple (independent) hierarchies in the same dataset
• hierarchies could be placed on either row or columns axis
• the relationship could be based on compound keys

In practice, it would already be great if we could support just one hierarchy on the rows axis based on a single column foreign key.
This particular constrained example would be immediately useful for balance sheet like analysis.

When setting option "Max. fraction digits" to zero, the summations of values are not working anymore:

Error is thrown in SQLHelper.js (function createNumberFormatter):

When using a "Max. fraction digits" higher than zero, the summation is done correct.

The init code for the dialog needs to be amended to fetch the current setting of the applyAllFilters option and use it to set the state of the checkbox, right before the piece where we attach the event handelr to the checkbox

Upper Capital -> Upper Case

This would be implemented as a on/off property at the items of the rows and column axis query items.
Based on the state of these properties, we would generate a GROUP BY GROUPING SETS query, and this would more or less automatically deliver the required results.

Text in select not readable

The filter dialog has a picklist which can be populated based on a search criterion entered by the user. The user then picks items from the list to formulate a condition.

Currently the picklist criterion supports like wildcards but nothing else.
It would be nice to add options search in a case-insensitive way, as well as options to include implied wildcards at either end of the entered criterion (effectively searching with "contains" semantics)

A view that makes use of CTEs in a duckdb file cannot be used as a source. When trying to select it, the console shows an error indicating that the table could not be found.

Steps to reproduce:
Create a duckdb file with a table:
create table Cities as select * from (values ('Amsterdam', 1), ('London', 2)) Cities(Name, Id);

On top create a view that uses a CTE:
create or replace view v_cities as select * from Cities;

Use the created duckdb file as a source and select the view. Huey will show an indicator that it is loading the metadata, but when opening the console, it turns out that there is an error:
Uncaught (in promise) Error: Catalog Error: Table with name Cities does not exist! Did you mean "xxx.Cities"?

The functional requirement is to have a visual on the values/conditions, esp. for those items that are not on the rows/columns axes.

Did a little experiment and I like how it looks and feels:

code for the filter item is something like this:

<!-- This is the normal li we use for axis items -->
<li
  style="overflow: hidden; resize: both"
>
  <details>
    <summary>Caption</summary>
    <ol>
      <li>Value1</li>
      <li>Value2</li>
    </ol>
  </details>
</li>

We may need to play a little bit with whether the li or the details element needs to be resizable and the resize property should probably be available only when the details element is expanded.

We use the resize feature to support horizontal resize of the columns.
We also apply some styling to alter the appearance of the resize widget.

In Mozilla Firefox, neither work.
It looks like this feature is simply not supported on elements that are part of a table layout.

It's nice that huey provides so many derivations and aggregations. It's also a bit much.

We should at least distinguish between those things that are "very common" and those that are "special interest", such that we end up for a rather small set of items directly below the attribute, and tuck away the more exotic or special things in a folder structure below the attribute.

For example, SUM and AVERAGE are probably "very common";
kurtosis and skewedness on the other hand are probably "special interest".

There may be shades of grey, depending on the type of user so ideally it would be somewhat configurable