6 ms·
Personal Data Warehouses: Reclaiming Your Data
- simonw 6y agoThis is a video and annotated summary of a talk I gave for the GitHub OCTO Speaker Series yesterday, describing how I've been building my own personal data warehouse on top of my Datasette and Dogsheep open source projects. I feel like every nerd eventually tries to build something like this. My main contribution here is the observation that importing data from personal sources into SQLite massively reduces the amount of work needed to run further analysis, especially with a web interface that lets you run and bookmark queries.
- heimatau 6y agoI'd like to encourage you to consider doing something for people too. Even if it's in the 'cloud' but maybe where something like telegram/signal has to 'pair' each device with your account and ultimately, the user has the keys. I don't have the time to watch your presentation but I skimmed your slides. Wolfram is legit. I didn't know that about him and I plan on reading that article about his productivity. Also, I didn't realize that others were actually interested in this. I've been trying to do this type of stuff for....about 4 years now. I'm not super tech savvy and I also keep getting busier. I really want this for myself. I have a ton of webpages that I want to put into a system (dated on when I enter it, tags, etc). I currently salvaged my entire email system, even after a lot of headache (years....worth). I have Todoist for tasks. I have Pocket for bookmarks. I want a place for all my notes, tidbits, all my digital content to be put into one place. Even recently, I was thinking, I could get an ANN/ML that would see what I'm 'saving' and doing and it could suggest higher value ideas/content for me to consider. (Like a Youtube recommendation engine but...instead of trying to get you to waste your time on something, instead the ANN is focused on trying to give you a ROI for what you've coming across; becoming a better father, as ME, as an individual..., or taking my first step to learning something that I didn't realize that I wanted to know [accounting is one strange topic that has arise in my life], etc, etc). I would like to eventually get a team and hire a few personal assistants but...ultimately my game plan is what I wrote above. A decent algorithm that has a solid feedback loop based on my experience through life, to help me become more productive on what my goals and thought processes are. I say this all to you, hoping that maybe you or someone in HN could build this. I'd probably be willing to go up to $1k a year or something. Until I generate a lot more money for myself, through my own brute force, I won't have the cash flow to get someone to help me build this myself. I wouldn't want to sell it. I'd want it for myself because there is a 'good enough' threshold that I think would be incredibly valuable to me. (Sure, you could keep iterating...but I suspect that after the threshold is reached, it would be a certain limit of diminishing returns until some major algo breakthroughs happen) P.S. I do understand than you want to target businesses, etc. That's where the money is, sure. But...I wish people could finally get a solid product for once. What's that email service that reduces everyone's clutter that people are paying significantly for? Yeah. Be that, but for the personal data warehouse. I'd pay the premium.
- dnautics 6y agoHey thanks! I'm interested in building this over the next year or so, and I was thinking of using SQLite for exactly these reasons, thanks for confirming my suspicions
- BenGosub 6y agoIf I store data from different sources in the same sqlite db file, is it possible to do joins? I think that there are a lot of use cases if I can join data from different sources of my knowledge base
- simonw 6y agoYes, absolutely. SQLite can even join data together from multiple different database files, though the Datasette interface doesn't support cross database joins just yet.
- BenGosub 6y agoCan I use the same database to pull from multiple sources? And Does datasette provide tools to make it easier to do joins on data from different sources?
- simonw 6y agoYes, you can load data from different sources into the same database - each of the Dogsheep tools lets you pick the database file that you are importing data into. You can create bookmark SQL queries that perform joins in Datasette, or set up views. Here's an example: https://covid-19.datasettes.com/covid?sql=select%0D%0A++++++ny_times_us_counties.date%2C%0D%0A++++++ny_times_us_counties.county%2C%0D%0A++++++ny_times_us_counties.state%2C%0D%0A++++++ny_times_us_counties.fips%2C%0D%0A++++++ny_times_us_counties.cases%2C%0D%0A++++++ny_times_us_counties.deaths%2C%0D%0A++++++us_census_county_populations_2019.population%2C%0D%0A++++++1000000+*+ny_times_us_counties.deaths+%2F+us_census_county_populations_2019.population+as+deaths_per_million%2C%0D%0A++++++1000000+*+ny_times_us_counties.cases+%2F+us_census_county_populations_2019.population+as+cases_per_million%0D%0A++++from%0D%0A++++++ny_times_us_counties%0D%0A++++++join+us_census_county_populations_2019+on+ny_times_us_counties.fips+%3D+us_census_county_populations_2019.fips%0D%0A++++where%0D%0A++++++%22date%22+%3D+%28%0D%0A++++++++select%0D%0A++++++++++max%28date%29%0D%0A++++++++from%0D%0A++++++++++ny_times_us_counties%0D%0A++++++%29%0D%0A++++order+by%0D%0A++++++deaths_per_million+desc%3B https://covid-19.datasettes.com/covid?sql=select%0D%0A++++++...
- pcr910303 6y agoIf you’re interested in this ‘Personal Data Warehouse’ concept, you might also be interested in @karlicoss’s articles[0][1][2] about his infrastructure on saving personal data. There are various technical differences, for example @simonw preferring lightweight SQLite databases compared to @karlicoss preferring dumping the data and parsing-when-needed[3], but the purposes are similar enough that I think they should be mentioned. There were also some very constructive HN discussion[4] on these articles, where @simonw have also introduced Dogsheep before :-) [0]: Building data liberation infrastructure — https://beepb00p.xyz/exports.html https://beepb00p.xyz/exports.html [1]: Human Programming Interface — https://beepb00p.xyz/hpi.html https://beepb00p.xyz/hpi.html [2]: The sad state of personal data and infrastructure — https://beepb00p.xyz/sad-infra.html https://beepb00p.xyz/sad-infra.html [3]: Against unnecessary databases — https://beepb00p.xyz/unnecessary-db.html https://beepb00p.xyz/unnecessary-db.html [4]: https://news.ycombinator.com/item?id=21844105 https://news.ycombinator.com/item?id=21844105
- karlicoss 6y agoThanks for sharing, me and @simonw seem to be bumping into each other regularly :) I've been meaning to give Datasette a try and plug it into my system! Even though I prefer to rely on code as the main interface, in most cases I already have sqlite for free, because it's used as a cache [0]. If a function is marked with @cachew decorator, its results would be cached on the disk, invalidated on arguments changes, etc. [0] https://github.com/karlicoss/cachew#what-is-cachew https://github.com/karlicoss/cachew#what-is-cachew
- karlicoss 6y agoP.S. just gave it a go! pipx install datasette && datasette install datasette-cluster-map ## warm up HPI (to invalidate and dump a newer sqlite cache file) python3 -c 'import my.photos as P; P.print_all()' ## run Datasette datasette /path/to/cache/photos.sqlite tada! https://imgur.com/a/N25Zirs https://imgur.com/a/N25Zirs very nice :) Only had to adjust the query in the last step to conform to the naming (i.e. `select _cachew_union_repr_Photo_geo_lat as latitude, _cachew_union_repr_Photo_geo_lon as longitude`)
- 29athrowaway 6y agoHaving a copy of your data "reclaims" your data only in terms of access but it does not reclaim the control over it. Once you upload data into many of those services, in most cases you them give a permanent license to use it in whatever way they want. Then, if you have your own website, nothing prevents Clearview AI [or some equivalent company] from crawling your website, index their photo into their facial recognition db. I don't think those companies care at all about robots.txt.
- simonw 6y agoThat's absolutely true. The GDPR gives you a right to request deletion, but it's not a great defense against companies like Clearview harvesting your data for other purposes. Hopefully the legal framework will continue to get stronger around that. In the meantime, we can still have a LOT of fun by pulling our data back into systems that let us run our own queries.
- NateEag 6y agoIt's not super-difficult to add HTTPS and HTTP basic auth (or similar) to websites these days. That's enough to keep automated scrapers from getting into the private parts of your website.
- 29athrowaway 6y agoHTTPS is not a deterrent for bot. Authentication is. Unless the bot identifies itself as such by setting a user-agent header honestly you cannot distinguish a browser from a bot.
- NateEag 6y agoRight. That's why I said HTTPS and auth. Auth to keep the bots out, and HTTPS because it's free and easy to add, and keeps network admins from sniffing your username and password.
- neolog 6y agoVery cool. You mentioned wanting to do it on mobile, I wonder if you could plug it into a little Beeware [1] app. [1] Beeware - https://beeware.org/ https://beeware.org/ - Write your apps in Python and release them on iOS, Android, Windows, MacOS, Linux, Web, and tvOS using rich, native user interfaces.
- simonw 6y agoI think Beeware could be a really good fit for this. I should spend some time exploring that option!
- aboodman 6y agoThis is like a super lofi version of Camlistore and I love it. Will play soon.
- caseyf7 6y agoSimon is a true gem of our community. Always love reading about the things he is working on and interested in.
- m0zg 6y agoI'm a big fan of the idea, especially in the light of Google Photos announcement. I'd ideally want my phone to back up my pictures, location, health data, documents, and other data to something self-hosted, with at least decent mining/visualization/search built in, without ever sharing it with any third party.
- ohthehugemanate 6y agoI have a home nextcloud server for this (easy to run with docker!). - The nextcloud phone clients upload our photos/videos automatically. - My homedir videos, photos, and documents directories are all symlinked from my nextcloud directory so they sync automatically. - My journal/notes are stored in Markdown, inside my documents directory. Synced per the above. - nextcloud includes a calendar server, to which I sync my google and outlook calendars. - I have face recognition run on my photos by a nextcloud plugin. - nextcloud also includes online collaborative docs, and other capavilities that I don't use yet. Probably there are some for Geo data. The whole thing is searchable through the web, desktop, or phone clients. I can pick a day and call up all the photos, notes, calendar appointments, and files for it. Or search by person, or search names and contents of files... It's also got a WebDAV interface and a standardized API, share links with optional passwords, and it's multi-user friendly. Oh, and of course it's the cheapest way to get terabytes of storage. Highly recommended!
- m0zg 6y agoI'm a little wary of OwnCloud, and generally of any software written in PHP. PHP invites problems. That said, I find this page reassuring: https://owncloud.com/security/ https://owncloud.com/security/. It does seem like they took security pretty seriously a few years ago, and only a few issues have been discovered since then.
- touristtam 6y ago> PHP invites problems What's the issue with PHP? I don't get the same vibe as you from that specific programming language.
- magnusmagnusson 6y agoIf you cannot take your data with you, it's not yours. 'Personal Data Warehouse' does not make it yours unless you can remove 'your' data from services (Facebook, Github, etc) you gave it to. And that'd probably require that you can control their code, which is also not happening.
- deleted 6y ago[deleted]
- bytesandhumans 6y agoIt's a fantastic idea. I find it quite hard or techie for average users, but I think it's a very good point to start from
- robertlagrant 6y ago> But the really fun part is that it turns out any time you track an outdoor workout on your Apple Watch it records your exact location every few seconds Eek
- simonw 6y agoThe good news is that Apple keep that data on your device. You can export it directly from the Health app on your phone - it's not a cloud thing.
- jl6 6y agoI believe all data warehouses are limited by the quality of their data model. Most start with good relational intentions over a small domain, but eventually get bogged down arguing how semantic angels might dance on ontological pins. The parts that work become ossified and impossible to change. The system starts to fragment into multiple federated datastores or unstructured file dumps (“big data!”) where you have to build your own integration every time you want to use the data. Someone comes along and proposes a unifying model (“everything is an event!”) and rebuilds the whole thing but with an extra layer of complexity. Someone suggests buying an industry data model instead - surely the data experts will have solved all these problems for us? A skunkworks project spins up and starts implementing the bought model with good relational intentions over a small domain... I don’t think personal data warehouses are immune to any of these forces.
- simonw 6y agoMy implementation deliberately avoids any hint of a unified data model: each source of data (Twitter, Swarm, GitHub etc) gets a database schema that matches as closely as possible to the original JSON. Then you figure out and bookmark basic SQL queries for each one. If their data model changes, stuff will break and you will need to fix it. I'm fine with that.
- iblaine 6y ago>I believe all data warehouses are limited by the quality of their data model. This is common and most organizations overcome this problem with a higher effort downstream by Analysts & BI. I personally prefer 3NF tables, event streams or Kimball, in that order, as solutions to create a better DW. The reality is ugly behavior in data modeling can be overcome by Analysts & BI tools. And that seems to be the norm these days...use a DW to structure data to make data accessible, then let Analysts figure out the metrics. With the recent emphasis on tools like Looker or Tableau, this pattern seems to be working.
- lyxsus 6y ago> I believe all data warehouses are limited by the quality of their data model. wrt that, what do you think about Solid from Tim Berners-Lee? (solidproject.org)
- sradman 6y agoDogsheep Beta is a pun on Wolfram Alpha [1] (dog/sheep/beta are humble alternatives to wolf/ram/alpha). Both are queryable knowledge bases. Dogsheep Beta leverages SQLite FTS (Full Text Search) adding a search interface to Datasette’s SQL, accessed via a Python powered WebUI. All self-hosted. The “personal” part leverages Datasette plug-ins for services that export user data (e.g. Twitter, Hacker News, GitHub, Pocket, Evernote, and Apple HealthKit). More a poor-man’s ElasticSearch for SQLite/Datasette than a Data Warehouse, in my opinion. Simon Wilson is on a roll delivering simple yet powerful tools. [1] https://en.wikipedia.org/wiki/WolframAlpha https://en.wikipedia.org/wiki/WolframAlpha
- deleted 6y ago[deleted]
- bywaterstreet 6y agoGreat work, Simon, and a very interesting talk too. Off to read the linked Wolfram article: https://writings.stephenwolfram.com/2019/02/seeking-the-productive-life-some-details-of-my-personal-infrastructure https://writings.stephenwolfram.com/2019/02/seeking-the-prod...
- foxhop 6y agoMy data warehouse has always been a computer under my physical control with my data. TrueNAS 12 is awesome and hardware is a steal (ebay r720xd 128G with a bunch of disks) https://russell.ballestrini.net/dreaming-of-unlimited-home-computer-storage-capacity/ https://russell.ballestrini.net/dreaming-of-unlimited-home-c...
- BlueTemplar 6y agoThis is an interesting mirror to Tim Berners-Lee data pod project! https://news.ycombinator.com/item?id=18100895 https://news.ycombinator.com/item?id=18100895
- adkadskhj 6y agoLove these topics. I've been obsessed with this subject over the last ~4 years since i heard about Camlistore (Perkeep). Oddly, in the time span my love for content addressable stores has only grown - but my desire for "retaining" any online data has shrunk to near nothing. With the exception of Spotify, i avoid not only giving data but leaving a footprint on the cloud. I know it's happening, but i rotate accounts frequently, delete old posts, etc - and try to scrub with limited effort. This hasn't affected my motivation for a data warehouse; but it has limited my desire to spend any effort on keeping things in sync. Which is good for me, because watching the effort Camlistore went through to constantly scrape and maintain syncing behavior from Facebook, Twitter, etc.. i'm glad to avoid it. These days i mostly focus on writing a Camlistore with some of my own preferred interfaces. Notably "normal SQL database". I didn't enjoy Camlistore's app interface, i just want the feeling of using SQL. But i also love content addressable stores so that's where i sit. My efforts currently work towards merging concepts between Camlistore and Noms DB, with a SQL and Git like interfaces. Large problem for side projects, but plenty to keep me interested.
- sebasydoc 6y agoThis video was so interesting !
- plaidfuji 6y agoThis was a super information-rich video and I’ll definitely have to check out the Datasette library. But when I think of a “personal data warehouse”, I see a lot more utility in centralizing financial data (cash, assets, debts, receipts), health records, government documents, and other more... non-trivial information. The examples of “plot your dogs weight over time by filtering tweets” and “rank pictures you’ve taken of Pelicans by ML-graded aesthetic quality” are delightful but absurdly frivolous (and I honestly can’t tell whether they were meant as serious examples or not..). What I want this to be is a kind of mint.com, but with the ability to ingest from a wider swath of sources, and without having to grant a third party app access to my bank account. I’m sure many companies have tried to make all-encompassing life-tracker tools that I’m not aware of, but I’m equally certain that they have serious security and privacy problems. And it’s entirely possible that this is all just wishful thinking... that centralizing sensitive data in this way will always create a single point of failure that is inherently risky.
- simonw 6y agoThe examples I use are meant to be frivolous and amusing, but more importantly they're examples that it's safe to share in a demo. I've done some work on importing financial details into SQLite and Datasette (so far just using CSV exports from online banking) but they're not something I'm comfortable sharing in a video! I should probably add a note about those to my presentations - explicitly call out that you can absolutely import financially sensitive data into a personal data warehouse like this, but it won't be something you'll want to show other people.
- plaidfuji 6y agoFirst of all, I appreciate the response! The examples were good - they demonstrated the flexibility and depth of the tool. I suppose I’m just lamenting the fact that I’d like to use this tool to create a data warehouse for sensitive financial info, but I don’t trust myself to ensure I wouldn’t be creating a massive risk of identity theft, given my level of security expertise.. again, loved the video.
- brnt 6y ago
- jefurii 6y agoLots of copies keeps stuff safe.