Loading filters

[geekscrapy]

I've thought a few times now that it would be awesome to have some kind of filtering capability built in at two key points (below). This would allow the user to filter rows from the source and allow some flexibility around sheet reloading.

The places where I think filtering could be implemented:

1. in addRow: on reload, this would allow a user to add a filter (either regex or py expr) when they reload (e.g. reload-expr-filter).
2. in the iterload/loader/file specific function. This would allow loaders like SQL to reload with a custom SQL command, which means unwanted rows would never get to addRow which would be faster.

This could have quite a few uses, firstly the SQL loader could filter based on an SQL statement, calls to APIs could be filtered. This could enable faster loading of files where you know a lot of content is not required.

[frosencrantz]

I would like the ability to automatically filter data after reading or even as part of reading.

Some random questions to better understand why this would be more useful than what is already available.

• Do you reload sheets often when using VisiData?
  • Could you filter the data before reading it with VisiData?
• Could you use macros or playback files to do the filtering you want? Why do you feel these are not sufficient?
• Do you use the dup-selected " command to filter selected rows?
• For SQLite there is the mysterious SqliteQuerySheet, which looks like it would allow you to create a sheet out of a query, instead of a table scan.
• It is possible to add a view to a SQL database, which could be filtered as you wanted.

[geekscrapy]

In response to your Q's above!

1. I don't often reload, but that's because it loads everything 😉 so no need there. In addition, with large datasets (I've used for 26m rows so far, and generally use it with 1/5m rows) I'll take a peek at it with vd, then use grep. But this only scales with certain file types live CSV and tsv, not SQL, json, XML etc. There a consistent way to pre filter (i.e. you have to use different tools for each of those filt types)
2. I'm not sure how macros or playback files would help? As far as I know, they only get applied once the data has been loaded in full, therefore doesn't help the pre load filtering? I could be wrong, don't actually use them that much, and I hardly use batch mode
3. Yes, all the time. But again, you need to load the whole file, and in addition, wait for the selection command to finish before you can start working with the rows of interest. And when you have 10m+ rows, that can take a while to select them all with regex, let alone with expr.
4. Probably, I've not used it (it'd be great to have more info). But it'd be nice to have this for all file types. I've used SQL a couple of times, but with large datasets, it's not the right tool without having some pre filter/search capabilities
5. Yup probably, but this is requi9 making changes outside of vd. One of the great things about vd is that you can chuck data at it and there are no external deps needed to get at the data you want.

Generally, I think this as a good preliminary step to having some complex sheets that interactive with APIs and databases that could hold a lot of data.

Also, vd (inherently) provides us a great method of normalizing data on load, so that's something that could be used with the addRow filter

[saulpw]

I really appreciate this discussion, and getting pinged a couple of times in thoughtful ways is a good way to set something rattling around my brain. So let me share some of my thoughts; please do tell me what you think and what use cases are most important.

As far as I can tell, these are the reasons to add filter-at-load functionality:

1. VisiData could effectively process 10m-100m datasets if it's allowed to filter out most of the rows at load time (since holding everything in memory is a primary constraint).
2. Replaying .vd cmdlogs is unwieldy for basic filtering, in large part because it loads everything before it progresses to the next step of filtering (so you can't reasonably see the first N filtered rows as they come in).
3. Some backends can filter much faster than VisiData can load+filter, and we want to take advantage of that.

(Any others I'm missing?)

Supporting those backends in (3) is why I've been holding out in general.
We can unlock much 'bigger data' if we could just add a WHERE clause in those loaders.
But I'd like to try to provide a somewhat universal filtering interface, so that loaders for backends in (3) can be efficient and still lightweight (not having to do much work to translate), and we can emulate it reasonably for other loaders.

I thought about this a bit today and came up with a half-reasonable API proposal. Of course it's still half-unreasonable :) It's not too complicated and would work to cover 80% of use cases (I think?) but it deliberately snubs the other 20% of use cases in the name of simplicity. And it provides only the shell of a universal filtering interface.

But anyway if you want to start thinking about this, here's my first-pass API proposal:

Sheet.addFilter(col/colname, match_value, inverse=False, exact=True)

Add a filter which will be applied on reload.
A simple CLI option-like syntax (--filter colname:"match_value" maybe?) would call this before the first load.

We need to allow col to be a column name, because in many loaders the columns aren't available until loading has started, and we want to set filters from e.g. the CLI before starting to load.

We could also allow col to be None, indicating "any column", but that may not be a good idea, as it would only be supported via slow emulation (SQL doesn't support it, anyway).

inverse=False filters would combine OR-wise, and inverse=True would combine AND-wise. I believe this maps to how sequential | and \ selections work in VisiData currently (though I haven't thought it through).

Depending on support from the backend, the match_value could be:

• full exact string or value
• substring
• wildcard-based (what wildcard chars, and what do they mean? how are they escaped?)
• regex (including many different flavors)

There is a wide variety of capabilities here, and it would be difficult (or very limiting) to map them from/to a common subset.
So each loader that supports backend filtering would make a reasonable choice about how its match_str functions, as long as a simple alphanumeric string is always a case-insensitive substring matcher.
Then a loader could use whatever non-alphanumeric characters it wants for its wildcards, regex, or any "special:value" implementation-specific thing it wants.
The default for VisiData loaders for backends which don't filter (and thus are filtered via emulation within VisiData), would be case-insensitive regex.

When exact=True it reverts to a plain (non-regex) case-sensitive comparison, with only exact matches.

Sheet.isRowFiltered(row): return True if row should not be included

Any loader that implements iterload() would get the builtin Python regex filtering without any change.
We could add an option or class attribute for loaders that indicates whether they do their own filtering within iterload,
to disable the builtin filtering.

Any loader that re-implements reload() would only have to check each row with isRowFiltered.

Interactions with dup-selected

Bulk selection commands would add filters to an internal collection; dup-selected apply these to the subsheet and set the source to the parent source, so the reload would naturally go to the source instead of getting rows from the parent sheet.
This means you could start loading everything from SQL table with 100m+ rows, press Ctrl+C immediately, and then create filters by selecting rows you want with | (select-col-regex), and then " (dup-selected).

Handling z| (select-col-expr)

Sheet.addFilterExpr(python_expr, inverse=False)

We also need a way to express selections that don't match exact value or by string, like numeric inequalities or other formulaic expressions.
addFilterExpr would take a Python expression, like z|, which a SQL loader would have to parse and translate (blech).

Or maybe there's addFilterPythonExpr which would always be supported the same, but probably only in the slow emulator, and the default addFilterExpr uses it too, but could be overridden by a loader to use an implementation-specific filter expression syntax. Same as addFilter(exact=False); maybe there should be addFilterExact addFilterRegex addFilterSubstring etc? But I'm really trying to avoid that API explosion for this.

• | (select-col-regex) would use addFilter
• z| (select-col-expr) would use addFilterExpr or addFilterPythonExpr
• \ and z\ would do the same but set inverse=True
• , (select-equal-cell) would set exact=True
• s/u on the freq-table would work reasonably

However, with this design, these wouldn't work:

• gt (toggle-selected) couldn't negate the whole filterset because we don't have arbitrary nesting of OR/AND clauses, so it would have to invalidate the whole filter.
• The individual selectors (s/t/u) might be able to get working with a unique key column, but probably using them this way is a mistake and , on the key column seems like a reasonable workaround in that case.

[frosencrantz]

Hi @saulpw and @geekscrapy Has there been any more discussion on this? Having more control of what is loaded into the limited available memory seems important when working with large data sources. Here are some quick takes:

I like the idea of having two types of filters, the Python and the native, which default to Python for loaders that don't have a backend that supports filtering. Native filters would use whatever syntax is native to the backend, like the SQL like with %. I don't like the idea of trying to emulate filtering, that sounds like a maintenance nightmare. For a CSV source, Python loading filters would work and be useful.

For the native filter it is seems like it would be to be able helpful to handle as much of the range of filtering that the backend supports. It seems like it should be able to handle arbtrary queries too. How would you limit input from a Spatialite database or graphql source? Or use the full range of where clauses with subqueries, special functions, or even having/group by that allow filtering.

[saulpw]

Good questions and ideas, @frosencrantz. As mentioned in another discussion (#1043), my new 'dataverter' library (soon to be part of vdplus) will be able to handle Python filters for any data source, and that should be exposed to VisiData on the CLI. Native filters would be trickier to implement consistently, but I agree they would be useful, for both performance and flexibility--some things that are trivial to express in SQL/BPF/whatever, would be a pain to represent in Python.