5 ms·

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

by hk1337 5y ago

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.
- hk1337 5y ago
  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.
- gsempe 5y ago
  I'm really interested to read articles or how to around this solution. Do you have anything public that could help me?
  - psanford 5y ago
    For accessing the database I use sqlite3vfshttp[0] with an s3 presigned url for authentication. [0]: https://github.com/psanford/sqlite3vfshttp https://github.com/psanford/sqlite3vfshttp

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

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

hk1337 5y ago
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.
gsempe 5y ago
I'm really interested to read articles or how to around this solution. Do you have anything public that could help me?
- psanford 5y ago
  For accessing the database I use sqlite3vfshttp[0] with an s3 presigned url for authentication. [0]: https://github.com/psanford/sqlite3vfshttp https://github.com/psanford/sqlite3vfshttp