Given a Lambda function’s ephemeral nature, when working with SQLite databases or other files, you would typically be baking it into the functions deployment package, reading the database from S3 using external libraries, or attaching the function to an Amazon EFS file system.
The first approach would mean any changes made during the runtime of the function would be lost, given that the execution of a function is ephemeral. To persist changes to the database would require you to re-deploy the function. However, this does work well for read-only use cases, and if you remain within Lambda’s space constraints. In the next approach, reading from S3 requires using additional dependencies, which you can package into the function or import through layers. There are some good libraries today that allow this, and it is a viable option. Lastly, using a function with an EFS file system mounted allows you to read and write to the SQLite database with low latency. This, however, is the costliest solution compared to the other two alternatives. If it’s a personal project, every cent counts, at least for me.
Read more...
S3
DuckDB has the ability to query S3 Tables, when this was first announced in beta I was unable to get it to work and subsequently gave up. A little over a year later I decided to give this a go again and now that it’s generally available, to my surprise, it just works. There’s still guides on how to set this up with nightly releases and other feature flags but nothing that shows the generally available path, so here’s my steps to get started.
Read more...
I’ve been exploring AWS analytics services again so it’s time for another TIL. This post goes over accessing and querying S3 tables from QuickSight, via Athena, as S3 tables don’t natively work with QuickSight.
Here’s the TL;DR on what you need to do:
Create your S3 Tables bucket, namespace and table. Ensure that your S3 Tables bucket has AWS analytics service integration enabled. Grant your QuickSight user permission to the S3 table via LakeFormation and your QuickSight service role permisisons through IAM. Add Athena as a data source in QuickSight. Add a dataset from the previously created data source and use custom SQL to query your S3 Tables. Verify you can use your table dataset from an analysis. Those are a lot of steps and here are some AWS resources that explain them:
Read more...