5 ms·

> 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

> 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...

dmarlow 6y ago
> Probably the second most popular source of user suffering. It was #1 for me! Finally indeed.
- asveikau 6y ago
  I would have guessed desire to be the #1 source of user suffering.
iagovar 6y ago
> I had to implement an ETL Buddy, google DuckDB, it's like OLAP SQLite. You'll thank me later.
- bondant 6y ago
  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.
  - banana_giraffe 6y ago
    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.
    - bondant 6y ago
      Thanks for the recommendations, I will have a look at pysqlite3-binary and apsw.
  - exyi 6y ago
    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
    - snicker7 6y ago
      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.
      - infogulch 6y ago
        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.
      - nalgeon 6y ago
        SQLite in WAL mode allows single concurrent writer and unlimited readers. Which is completely fine for a wide range of production use cases.
      - simonw 6y ago
        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.
    - bondant 6y ago
      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.
  - mhuffman 6y ago
    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.
    - em500 6y ago
      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://clickhouse.tech/docs/en/interfaces/cli https://altinity.com/blog/2019/6/11/clickhouse-local-the-power-of-clickhouse-sql-in-a-single-command https://altinity.com/blog/2019/6/11/clickhouse-local-the-pow...
      - mhuffman 6y ago
        Interesting! I did not know about the standalone cli feature. Thanks! Will check it out.
    - hfmuehleisen 6y ago
      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.
nalgeon 6y ago
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.
- abhgh 6y ago
  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.
  - nalgeon 6y ago
    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.
    - mmcdermott 6y ago
      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?
      - simonw 6y ago
        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-plants/global-power-plants 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 https://datasette.io/plugins/datasette-cluster-map and https://datasette.io/plugins/datasette-vega 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.
rgacote 6y ago
Possibly the first step towards the ability to rename a column?
- benasher44 6y ago
  Rename is already here! Came in 3.25.0 (https://www.sqlite.org/changes.html https://www.sqlite.org/changes.html)
- deleted 6y ago
  [deleted]
u678u 6y ago
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?
- oblio 6y ago
  You don't need to use an ORM, just use a query builder.

dmarlow 6y ago

> Probably the second most popular source of user suffering. It was #1 for me! Finally indeed.

asveikau 6y ago
I would have guessed desire to be the #1 source of user suffering.

iagovar 6y ago

> I had to implement an ETL Buddy, google DuckDB, it's like OLAP SQLite. You'll thank me later.

bondant 6y ago
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.
- banana_giraffe 6y ago
  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.
  - bondant 6y ago
    Thanks for the recommendations, I will have a look at pysqlite3-binary and apsw.
- exyi 6y ago
  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
  - snicker7 6y ago
    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.
    - infogulch 6y ago
      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.
    - nalgeon 6y ago
      SQLite in WAL mode allows single concurrent writer and unlimited readers. Which is completely fine for a wide range of production use cases.
    - simonw 6y ago
      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.
  - bondant 6y ago
    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.
- mhuffman 6y ago
  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.
  - em500 6y ago
    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://clickhouse.tech/docs/en/interfaces/cli https://altinity.com/blog/2019/6/11/clickhouse-local-the-power-of-clickhouse-sql-in-a-single-command https://altinity.com/blog/2019/6/11/clickhouse-local-the-pow...
    - mhuffman 6y ago
      Interesting! I did not know about the standalone cli feature. Thanks! Will check it out.
  - hfmuehleisen 6y ago
    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.

nalgeon 6y ago

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.

abhgh 6y ago
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.
- nalgeon 6y ago
  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.
  - mmcdermott 6y ago
    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?
    - simonw 6y ago
      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-plants/global-power-plants 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 https://datasette.io/plugins/datasette-cluster-map and https://datasette.io/plugins/datasette-vega 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.

rgacote 6y ago

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

benasher44 6y ago
Rename is already here! Came in 3.25.0 (https://www.sqlite.org/changes.html https://www.sqlite.org/changes.html)
deleted 6y ago
[deleted]

u678u 6y ago

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?

oblio 6y ago
You don't need to use an ORM, just use a query builder.