Side note this is a useful pattern I use in general. Use python to get some data into SQLite then I either use fast api, datasette, or Streamlit depending on what I want to do with the data. SQLite is great and a lot of the power of SQLite I feel hasn’t even been fully tapped yet by the community, especially virtual tables.
I use pandas or pyspark when I am transforming the data. When I am ready to distribute the data or serve the data behind an api, script or web app I write it to SQLite.
The benefit is every language can read sqlite, theres a rich ecosystem of sqlite tools like datasette and peewee to build with and sqlite can act as a serverless database keeping infrastructure costs low for serving the data.
I also like that sqlite has built in full text search and fuzzy matching capabilities making it quick to cook up a nice webapp for static data.
Also depending on your use case its nice to not have a dependency on pandas or take up a lot of room in your lambda package size for instance.
built some hand tools for analyzing the covid data git repos in this way earlier in the pandemic.
it was interesting in that it gave visibility into back corrections for old days. (and provided data for predicting a final count which took 3-5 days to stabilize, before those 3-5 days elapsed)
That's why I'm using it to build a SQLite database. I've run the script against ~20,000 commits and it took an hour to run... but then on subsequent runs when there are only a few new commits it finishes in a few seconds.
Git can be very performant if you use a good interface to it. Unfortunately, the CLI interfaces are slow, and e.g. libgit2 is a bit awkward to use. However, there is the object reader and writer interface, which allows reading and writing raw Git objects to its database - with it, you can build some nice high-performance tools.
It's unlikely to be the most performant since git is first and foremost designed to version control plain text source code for developing software. This is version controlling JSON data.
Git stores every version of a file as distinct compressed copies (objects). Periodically, it packs these objects into a pack file. To save space, delta encoding is used in this process. As far as I recall, this is done on a line by line level. In this case, simply dumping this JSON data is going to hamper the delta encoding and the repository will grow substantially in size each time there is a change in data.
Then there's the issue of retrieving the data. The basic implementation of reading objects using git checkout and rewriting the objects to disc but there are other ways to read the raw file contents into memory and do your stuff there.
It's not going to be the most performant or best way of version controlling data but in many cases it will do a decent job.
I also do this, but be weary of situations where you’re using ephemeral containers and you have to download, scrape, then reupload a bare repo.. Sure, you can use GitHub or Gitlab, but I opted for stashing and archiving into GCS / S3 directly and had to write some pretty intricate code to essentially perform a mitosis each month so the repo only got to about 100 mb. Fortunately, got it working before we hit 2gb flying around every 15m.. Also running garbage collection is tricky and didn’t appear to be possible with libgit2, so a lot of nuance here too don’t let the simplicity of the idea fool you. I learned a lot of things about Git from this project.. so yeah, just be careful is all in certain architectural contexts. However, I agree, it’s an extremely useful technique to approach scraping this way.
Edit: PS we use Apache arrow and save as parquet files, which has lots of benefits over SQLite because you can democratize the dataset by using Athena / BigQuery.. and I diplomatically agreed to use Go to consume the data and serve the API.. so that’s been time consuming.. but fun!
Could you expand, or point me in the direction of more information about what you mean by "benefits over SQLite because you can democratize the dataset by using Athena / BigQuery" as it sounds very interesting.
So we have a semi-large organization where our product people are very savvy, but not to a point where they are going to jump with joy having to download a huge tool chain with docker just to grab a SQLite database and fiddle with some analytical data. However, they absolute will learn commands and SQL or some python if they just login to a portal and start chomping away.. kind of like the whole SAP / data warehousing industry. Just don’t make people download a bunch of crap (figuratively speaking). Also, this particular data isn’t sensitive, it’s technically derived from public sources so it’s safe to “democratize” in that sense. I’m not sure how the approach would change with user data.
So how can I as an engineer do the most good for the widest possible audience? Well, I was in a meeting and I was taking about using SQLite and my peer was like “I dunno it sounds a little complicated” and I took that to heart and responded with, well ya know from where I sit it seems like all the big players are just using flat files in cloud storage as data lakes.. didn’t sleep well that night and then just perusing other options I came across Apache Drill and then it’s spiritual successor, Arrow and it hit me.. well if Athena / BigQuery understands parquet files and Apache arrow writes parquet files without any sort of infra like spark.. what if our data pipeline didn’t save it in SQLite but Apache Parquet and by doing so (with still plenty of complexity ironically), we all of a sudden have a dataset that can technically be read by API developers and the cloud data lake products. All one really needs at that point is access to a cloud bucket..
It’s still been a lot of work, and I haven’t realized all my promises as we’re still trying to ship the product, but I think in the long run, cloud storage + Apache arrow will be the de facto approach to time series data. And in some ways, it also can give event stores in the context of eventsourcing a facelift too.. but that’s not as developed of a thought for now.
19 comments
[ 3.2 ms ] story [ 57.1 ms ] threadThe benefit is every language can read sqlite, theres a rich ecosystem of sqlite tools like datasette and peewee to build with and sqlite can act as a serverless database keeping infrastructure costs low for serving the data.
I also like that sqlite has built in full text search and fuzzy matching capabilities making it quick to cook up a nice webapp for static data.
Also depending on your use case its nice to not have a dependency on pandas or take up a lot of room in your lambda package size for instance.
it was interesting in that it gave visibility into back corrections for old days. (and provided data for predicting a final count which took 3-5 days to stabilize, before those 3-5 days elapsed)
For example, here is a program which, given a file's SHA1 and path, finds the last commit to have that file version at that path: https://github.com/cybershadow/misc/blob/master/git-find-fil...
Git stores every version of a file as distinct compressed copies (objects). Periodically, it packs these objects into a pack file. To save space, delta encoding is used in this process. As far as I recall, this is done on a line by line level. In this case, simply dumping this JSON data is going to hamper the delta encoding and the repository will grow substantially in size each time there is a change in data.
Then there's the issue of retrieving the data. The basic implementation of reading objects using git checkout and rewriting the objects to disc but there are other ways to read the raw file contents into memory and do your stuff there.
It's not going to be the most performant or best way of version controlling data but in many cases it will do a decent job.
Edit: PS we use Apache arrow and save as parquet files, which has lots of benefits over SQLite because you can democratize the dataset by using Athena / BigQuery.. and I diplomatically agreed to use Go to consume the data and serve the API.. so that’s been time consuming.. but fun!
So how can I as an engineer do the most good for the widest possible audience? Well, I was in a meeting and I was taking about using SQLite and my peer was like “I dunno it sounds a little complicated” and I took that to heart and responded with, well ya know from where I sit it seems like all the big players are just using flat files in cloud storage as data lakes.. didn’t sleep well that night and then just perusing other options I came across Apache Drill and then it’s spiritual successor, Arrow and it hit me.. well if Athena / BigQuery understands parquet files and Apache arrow writes parquet files without any sort of infra like spark.. what if our data pipeline didn’t save it in SQLite but Apache Parquet and by doing so (with still plenty of complexity ironically), we all of a sudden have a dataset that can technically be read by API developers and the cloud data lake products. All one really needs at that point is access to a cloud bucket..
It’s still been a lot of work, and I haven’t realized all my promises as we’re still trying to ship the product, but I think in the long run, cloud storage + Apache arrow will be the de facto approach to time series data. And in some ways, it also can give event stores in the context of eventsourcing a facelift too.. but that’s not as developed of a thought for now.
"Dolt marries two familiar concepts, Git and MySQL. The first and only SQL database that supports clone, branch, and merge."
https://www.dolthub.com/