Hacker News Reader

Comment by throw_m239339 | Hacker News Reader

> Column removal

Wow, finally. I Love SQlite. It saved my a* in so much projects where I had to implement an ETL, I just spawned some in memory SQLite database instead of writing some painful custom code in Language X or Z over and over again. Also used it to generate static search results in a flat file blogging platform since it does have some full text search capabilities. And let's not even talk about custom functions, including custom aggregation functions, in any client language, directly in your application code...

View on HN

SQLite is really great at crunching data! I definitely prefer it over pandas in most cases, as SQL is naturally fit to joins, aggregates etc. Also SQLite works natively with JSON, which is a huge time saver.

I prefer it over pandas for joins etc too. My workflow is (1) do the simple stuff in Python using pandas (2) for some of the complex stuff, I just start creating sqlite tables. If you have datasette installed, you can also view the tables (choosing to write intermediate ones for greater debuggability) pretty easily in your browser.

datasette is a wonderful piece of software! Simon Willison has created a great tooling around SQLite. Can't imagine how much time and energy he has invested in these projects.

Honest question because I haven't messed with Datasette much beyond skimming the home page - how does it improve on a general SQL client like DataGrip or Squirrel or DBeaver?

Obviously I'm biased, so I'd love to hear answers to this from other people (plus I've not really used any of those alternatives much).

Datasette is very "webby". Queries you execute end up in your URL bar as ?sql= parameters, which means you can navigate to them in your history, bookmark them, share links with other people (if your Datasette is shared) and open them in new tabs.

It also does web-style tricks like turning foreign key references into hyperlinks through to the associated records.

Datasette's table browsing feature has faceting, which is enormously powerful. I don't know if those alternatives have this feature or not, but I use this constantly. Demo here (the owner, country_long and primary_fuel columns): https://global-power-plants.datasettes.com/global-power-plan...

Datasette's plugin system is pretty unique too. You can install plugins like https://datasette.io/plugins/datasette-cluster-map and https://datasette.io/plugins/datasette-vega to add visualizations, which again are bookmarkable and hence easy to share with other people.

All of that said, I don't really see Datasette as competing with existing SQL clients. It's intended more as a tool for exploratory data analysis - I've put very little work into running UPDATE/INSERT statements for example, it's much more about turning a set of relational data into something people can interactively explore.

I work in a research organization where I am responsible for crunching data and produce reports highlighting the most "notable" results. Not that the other data is uninteresting, but the volume is such that Excel cannot handle it and even distributing it can be challenging for non-computer-technical folks without dedicated solutions.

Instead, I can dump all of the processed results into a table, create some views highlighting analysis X vs Y, and share links that give others the ability to ask questions I had not even considered. Now the user is empowered to ask anything and they do not need to engage me for "simple questions". Everybody wins. I believe there is also an extension that allows you to generate and save new queries through the web interface.

It is not a tool for a professional analyst, but a means to collaborate with others. There are heavier/more feature rich alternatives, but Datasette is my favorite tool for getting results out the door without hassle (can run it off of a laptop after a pip install).

It's so great to hear people using it like this!

https://datasette.io/plugins/datasette-saved-queries is the plugin for storing queries - it's pretty basic, there's lots of scope for improving the story around that.

I have had such enthusiastic feedback from granting people access to the ~full dataset. They have been conditioned to expect whatever subset can fit inside an email or a powerpoint slide. I feel a little embarrassed when people fawn over the utility because it is so easy to get running.

Have not yet had a chance to try the idea, but I am toying with using render-images to bake in pre-built plots + markdown for reporting the output. Queryable report in a file. Dynamic Vega plotting (RShiny-ish) is also in the back of my mind, but that feels too close to magic.

It is an incredibly useful tool, and I appreciate the workflows you have enabled.

I use datasette, datagrip and excel to process sales data.

- datasette to surface data via rest to excel (power query)

- datagrip to get the data how I want it, the json1 extension is so so much easier to work with than power query and for my use cases extremely fast.

This gives you (arguably) the best data grid in the world (excel) but without the horrible experience of building a pipeline in power query that will eventually become too slow and/or randomly crash and hang.

I would really, really like an in the box Regex extension so I can create SQLite views without the crazy lengths I have to go with SQL to, for example split a comma delimited list in a field.

I get the feeling we’ve been trained to expect less, with poor, incredibly slow legacy products. But really for many use cases all the opensource relational databases give instant results.

> I had to implement an ETL

Buddy, google DuckDB, it's like OLAP SQLite. You'll thank me later.

Does someone here use DuckDB in production? Is it as stable as SQLite?

The hosting company I have to work with has a very old version of SQLite installed on the server and they don't want to update it. So I was looking at whether I could replace it with DuckDB since it seems to be easy to install with pip.

Answering the question you didn't ask:

pysqlite3-binary is a Linux only package in PyPI that includes a recent version of SQLite

apsw is a cross-platform package, it brings in a modern version as well, and additionally exposes everything SQLite can do (useful for me, you can write VFS drivers in python for it). The version of apsw in pypi is hopelessly out of date, the homepage has more details on how to install the latest version.

Thanks for the recommendations, I will have a look at pysqlite3-binary and apsw.

DuckDB is designed for query processing, not updating data - I guess you would not want to use it for the transaction processing workloads just because it's easier to install. If you are doing mostly complex queries, then DuckDB is great, but I think there is going to be little usage directly in production, I'd expect it's more used for data processing

And how great is SQLite for transactional workloads? Every transaction locks up the entire db. There is no page or even table level locking. DuckDB can't be worse than that.

SQLite is not at the top of the charts, but there's a looong ways to drop below it among the wide variety of desirable properties that it offers.

SQLite in WAL mode allows single concurrent writer and unlimited readers. Which is completely fine for a wide range of production use cases.

In practice, I've found that most SQLite write operations take low-milliseconds to complete - so even under a very heavy write load (assuming WAL mode) you're unlikely to see any contention between writes.

For my own stuff I maintain an in-memory Python queue of write operations and apply them in order against a single write connection - while running numerous other read-only connections to serve SELECTs.

Yes I'm doing very few update or insert, but I run a lot of queries, so I guess it should be more than okay for DuckDB.

I very, very much like the idea of DuckDB, but have had many headaches with what I would consider "low-medium" amounts of log data (400 million rows, 180GB+, after stripping out any un-needed text) which works without issue using SQLite, btw, other than the expected slowness of row-oriented storage on the column-aggregate queries that I needed.

I have tried many different ways of importing the data with no luck. Usually it turned into a memory issue. This was irrespective of how I tried to import, whether it was in batches through a script, or using the built-in import functionality, or breaking the data into chunks prior to import, or anything else I tried. I occasionally keep trying to find a way, because I like the idea and features of DuckDB so much.

Of note, MonetDB slurped it right in without issue, but I would much rather use something not so "heavy" like DuckDB.

Also, regarding SQLite, I have DBs with billions of rows that work flawlessly, though not as fast as postgres.

You could try Clickhouse. It's a bit heavier than DuckDB and the default mode is server-client. But you can also use the client (a single binary) without a server to directly query data from csv or parquet files.

edit: added a better link, the stand-alone mode is called clickhouse-local

https://clickhouse.tech/docs/en/interfaces/cli

https://altinity.com/blog/2019/6/11/clickhouse-local-the-pow...

Interesting! I did not know about the standalone cli feature. Thanks! Will check it out.

One of the DuckDB authors here. Can you open an issue wrt the import problem? Should not happen. Try setting a database file and a memory limit.

> Probably the second most popular source of user suffering.

It was #1 for me! Finally indeed.

I would have guessed desire to be the #1 source of user suffering.

I'm curious about this as I always prefer files and collections of structures or classes than having to deal with databases and ORMs. What else can you do aside from joining and aggregation?

You don't need to use an ORM, just use a query builder.

Possibly the first step towards the ability to rename a column?

Rename is already here! Came in 3.25.0 (https://www.sqlite.org/changes.html)

Reply on news.ycombinator.com