Show HN: SQLite-S3-query – Python function to query a SQLite database on S3 | Hacker News Reader

Show HN: SQLite-S3-query – Python function to query a SQLite database on S3

github.com

38 pointsmichalc15 comments

I just implemented something similar in Go[0]. Just yesterday I added the ability to compile it as a loadable sqlite3 extension, so you can use the normal sqlite3 cli tool to query databases hosted on an http(s) connection.

[0]: https://github.com/psanford/sqlite3vfshttp

Is there a reason you're implementing the AWS auth yourself and not just using the `Range` argument in the boto3 SDK [1]? Seems like it would significantly reduce the amount of code here.

1. https://boto3.amazonaws.com/v1/documentation/api/latest/refe...

I could do this, and it certainly crossed my mind. But depending on the project, it could significantly increase the amount of code overall. It feels a feels a bit... heavy to me.

I can understand the AWS auth code... while there seem to be so many layers in boto3/botocore. There's a lot of code to add a few HTTP headers to each request. (It was a few years ago, but I posted about not using boto3 https://charemza.name/blog/posts/aws/python/you-might-not-ne...)

I might be suffering a little bit from not-invented-here syndrome admittedly... I don't know. But one of the things I really like about S3 is how much of its API is quite simple: it's just HTTP (I briefly wrote about _that_ as well https://charemza.name/blog/posts/http/s3/the-core-s3-api-is-...). My instinct is that in many cases, it's better to use a simple API directly rather than (what I see as) a more complex code base on top of it.

While I can see the logic behind that, what you are giving up is proper credential support (profile, env, instance, pod, etc) and a decent built in retry/exception policy. Using boto3 the entire library becomes something like:

    s3_client = boto3.client("s3")
    
    class S3VFS:
        def get_range(self, from_, to_):
            return s3_client.get_object(..., Range=f'bytes={from_}-{to_}')['Body'].read()

     with apsw.Connection(..., vfs=S3VFS())

That's drastically simpler, and safer to include from a library consumer point of view.

Looks pretty similar to https://github.com/phiresky/sql.js-httpvfs My first instinct is to wonder why not giving them credit, but maybe you’d never heard of it before…

You are right. I hold my hands up: I did not give credit to what made me think of doing this. I think I saw this (or at least, their blog post https://phiresky.github.io/blog/2021/hosting-sqlite-database...) on HN a few weeks back.

I have now given them credit.

Why not just use the SQL abilities already in S3? CSV, JSON, parquet, Athena, glue, query a specific object

I'm hearing this for the first time. can you link to some documentation? sounds really interesting

https://docs.aws.amazon.com/AmazonS3/latest/userguide/select...

There is certainly overlap with what's available in AWS/S3, so in many situations, if you're architecting a system, some of the things you suggest may be completely reasonable. In fact, in another project I'm involved in, we use S3's S3 Select to query CSVs extensively.

But I think there is a, or could be, a place for this:

- I don't think (but very happy to be corrected) that the things you mention can query SQLite files directly. So, there would have to be both a conversion of data, and then the query language used would not be SQLite's flavour of SQL. So, if you're using, or pro, using SQLite and wanting to keep the moving parts and languages down, then I _think_ there is a good argument for sqlite-s3-query to be used (with the caveat that it's a _very_ early version!)

- If you want, "light" dependency on AWS. There are a number of S3-compatible services that this could work on. Or even, the code could be tweaked to just use HTTP range headers, remove the dependency on versionId, which I think is only feature that makes this quite S3-specific. (Although, this would allow the case of errors/corruption if the database is replaced half way through a query...)

Athena is great for if you have _a lot_ of data. It isn't great for real time queries.

I use a SQLite database stored in S3 + SQLite's FTS5 for a really cheap full text search implementation.

I'm really interested to read articles or how to around this solution. Do you have anything public that could help me?

For accessing the database I use sqlite3vfshttp[0] with an s3 presigned url for authentication.

[0]: https://github.com/psanford/sqlite3vfshttp

Yeah, Athena is great for doing queries across multiple S3 objects. I have also used sqlite recently too, usually either with pandas reading from S3 then dumping it to a sqlite database table. I don't really like pandas but it's useful for creating the table structure for me.

What's the performance of this?

Reply on news.ycombinator.com