4 ms·

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

38 points by michalc 5y ago

orf 5y ago
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/reference/services/s3.html#S3.Client.get_object https://boto3.amazonaws.com/v1/documentation/api/latest/refe...
- michalc 5y ago
  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-need-boto-3/ 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-beautiful/ 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.
  - orf 5y ago
    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.
hk1337 5y ago
Why not just use the SQL abilities already in S3? CSV, JSON, parquet, Athena, glue, query a specific object
- krat0sprakhar 5y ago
  I'm hearing this for the first time. can you link to some documentation? sounds really interesting
  - indrax 5y ago
    https://docs.aws.amazon.com/AmazonS3/latest/userguide/selecting-content-from-objects.html https://docs.aws.amazon.com/AmazonS3/latest/userguide/select...
- michalc 5y ago
  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...)
- psanford 5y ago
  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.
molyss 5y ago
Looks pretty similar to https://github.com/phiresky/sql.js-httpvfs 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…
- michalc 5y ago
  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-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database...) on HN a few weeks back. I have now given them credit.
psanford 5y ago
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 https://github.com/psanford/sqlite3vfshttp
rmbyrro 5y ago
What's the performance of this?

38 points by michalc 5y ago

orf 5y ago

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/reference/services/s3.html#S3.Client.get_object https://boto3.amazonaws.com/v1/documentation/api/latest/refe...

michalc 5y ago
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-need-boto-3/ 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-beautiful/ 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.
- orf 5y ago
  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.

hk1337 5y ago

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

krat0sprakhar 5y ago
I'm hearing this for the first time. can you link to some documentation? sounds really interesting
- indrax 5y ago
  https://docs.aws.amazon.com/AmazonS3/latest/userguide/selecting-content-from-objects.html https://docs.aws.amazon.com/AmazonS3/latest/userguide/select...
michalc 5y ago
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...)
psanford 5y ago
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.

molyss 5y ago

Looks pretty similar to https://github.com/phiresky/sql.js-httpvfs 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…

michalc 5y ago
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-databases-on-github-pages/ https://phiresky.github.io/blog/2021/hosting-sqlite-database...) on HN a few weeks back. I have now given them credit.

psanford 5y ago

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 https://github.com/psanford/sqlite3vfshttp

rmbyrro 5y ago

What's the performance of this?