3 ms·

I often use SQLite for data analysis. Put all my raw data in, query out parts, and graph them, etc. It's quite annoying to have to dig up another tool just to g

I often use SQLite for data analysis. Put all my raw data in, query out parts, and graph them, etc. It's quite annoying to have to dig up another tool just to get some common functions.

Nihilartikel 4y ago
I love SQLite, but recently I've found that a Jupyter notebook with duckdb and pandas is a pretty powerful solution. They're well integrated and duckdb sql can refer to dataframes in scope as if they were tables.
bluejellybean 4y ago
Honest question, if I have PostgreSQL containerized in docker such that it's trivially easy to spin up a new database, what then is the use-case for SQLite? I frequently hear people talk about it with such rosy tone, but I just don't see the reason to actually set it up when the alternatives today are so easy to use, what am I missing?
- jbverschoor 4y ago
  Single file Easy to embed No ports, no sockets Easy to back up
- chomp 4y ago
  I mean, why reach for the cordless screwdriver when all you need is a couple turns of a Phillips head? It’s cool that it’s easy to spin up databases but that’s a lot of complexity that may not be necessary in all cases.
- duped 4y ago
  Because it's even easier to embed sqlite, that's why. Docker is a big and heavy tool that has nonzero baggage. SQLite is a single .c/.h file.
- jamal-kumar 4y ago
  You'd be surprised how many programs running on things that aren't web application servers which end up using relational queries. I remember my first encounter with it under the hood. It was a huge list of all my location data stored on my early generation iphone, you had to pull some tricks to extract the file off of your phone and then boom I got a map of where I had been around 2009 better than even I remember it. I think there was a congressional hearing over this. Now they just store that stuff off of the device.
- orthecreedence 4y ago
  If you were to ship a desktop app that stored its local data in relational format, would you bundle docker and postgres along with it?
asdff 4y ago
How large are your data? I'm thinking about implementing something similar but I'm not sure if its worth the time versus reading in flat files.
- sicp-enjoyer 4y ago
  Not big (a few GBs?). But being able to query is one easy way to get join and statistics.
  - asdff 4y ago
    I'm assuming you are plotting in python or R, is there an advantage here using sql queries instead of subsetting a flat file with some function and plotting these subsets?
    - sicp-enjoyer 4y ago
      gnuplot. Yes, like I mentioned JOIN between two data sets is terrifically useful and quite a pain to write iteratively. Do you have another approach for doing joins?

I often use SQLite for data analysis. Put all my raw data in, query out parts, and graph them, etc. It's quite annoying to have to dig up another tool just to get some common functions.

Nihilartikel 4y ago

I love SQLite, but recently I've found that a Jupyter notebook with duckdb and pandas is a pretty powerful solution. They're well integrated and duckdb sql can refer to dataframes in scope as if they were tables.

bluejellybean 4y ago

Honest question, if I have PostgreSQL containerized in docker such that it's trivially easy to spin up a new database, what then is the use-case for SQLite? I frequently hear people talk about it with such rosy tone, but I just don't see the reason to actually set it up when the alternatives today are so easy to use, what am I missing?

jbverschoor 4y ago
Single file Easy to embed No ports, no sockets Easy to back up
chomp 4y ago
I mean, why reach for the cordless screwdriver when all you need is a couple turns of a Phillips head? It’s cool that it’s easy to spin up databases but that’s a lot of complexity that may not be necessary in all cases.
duped 4y ago
Because it's even easier to embed sqlite, that's why. Docker is a big and heavy tool that has nonzero baggage. SQLite is a single .c/.h file.
jamal-kumar 4y ago
You'd be surprised how many programs running on things that aren't web application servers which end up using relational queries. I remember my first encounter with it under the hood. It was a huge list of all my location data stored on my early generation iphone, you had to pull some tricks to extract the file off of your phone and then boom I got a map of where I had been around 2009 better than even I remember it. I think there was a congressional hearing over this. Now they just store that stuff off of the device.
orthecreedence 4y ago
If you were to ship a desktop app that stored its local data in relational format, would you bundle docker and postgres along with it?

asdff 4y ago

How large are your data? I'm thinking about implementing something similar but I'm not sure if its worth the time versus reading in flat files.

sicp-enjoyer 4y ago
Not big (a few GBs?). But being able to query is one easy way to get join and statistics.
- asdff 4y ago
  I'm assuming you are plotting in python or R, is there an advantage here using sql queries instead of subsetting a flat file with some function and plotting these subsets?
  - sicp-enjoyer 4y ago
    gnuplot. Yes, like I mentioned JOIN between two data sets is terrifically useful and quite a pain to write iteratively. Do you have another approach for doing joins?