个人数据仓库：回收您的数据

昨天，我为GitHub的Octo扬声器系列做了一个关于个人数据仓库的演讲，重点是我的Datasette和DogSheet项目。演讲的视频现在已经可以观看了，我在这里展示它，并附上演讲的注释摘要，包括到演示和更多信息的链接。

在演讲的前几分钟，屏幕分享出现了一个短暂的技术故障--我已经在笔记中添加了屏幕截图，这些截图显示了如果我的屏幕被正确分享，你会看到什么。

我将谈论个人数据仓库，它们是什么，你为什么想要一个，如何建立它们，以及一旦你建立了一个你可以做的一些有趣的事情。

这是我的狗，Cleo--当她在这里的狗狗服装比赛中获得第一名时，她打扮成金门大桥！

所以我想回答的问题是：克利奥在多大程度上是旧金山的潮人？

我在Foursquare Sarm上有一个数据库，里面有我十年的签到记录--这是用我的Sum-to-Sqlite工具生成的。每次我和Cleo在某个地方签到时，我都会在签到消息中使用狼的表情符号。

如果我按场地类别分类，我可以看到她已经入住了57个公园，32个跑狗场，19家咖啡店和12家有机食品杂货店。

然后我可以根据场地类别进行分类，然后过滤到她在咖啡店的19个签到。

能够建立一张你的狗狗喜欢的咖啡店地图，这显然是建立你自己的个人数据仓库的一个非常有价值的原因。

这个演示的关键是我正在运行的名为Datasette的Web应用程序。我已经在这个项目上工作了三年，目标是让探索各种形状和大小的数据变得尽可能容易和便宜。

10年前，我在伦敦为《卫报》(Guardian)工作。当我加入这个组织时，我意识到的一件事是，报纸收集了大量的数据。任何时候，当他们在报纸上发布图表或地图时，总得有人收集背后的信息。

那里有一位名叫西蒙·罗杰斯(Simon Rogers)的记者，他是一位收集任何你能想到的需要的数据的巫师。他清楚地知道从哪里获取它，并在他的台式电脑上收集了大量精美的电子表格。

我们决定要公布故事背后的数据。我们创办了一家名为数据博客的网站，目的是为我们的故事配上背后的原始数据。

我们最终使用谷歌工作表发布了这些数据。它奏效了，但我一直觉得应该有一种更好的方式来发布这种结构化数据，以一种对我们的受众尽可能有用和灵活的方式。

快进到2017年，当时我正在研究一种名为“无服务器”托管的新事物--特别是一种名为Zeit Now的新事物，它后来更名为Vercel。

我最喜欢的Serverless的一个特点是“归零”--当你的项目收到流量时，你只需为托管付费。

如果你和我一样，喜欢做一些副业，但你不喜欢在以后的生活中每月花5美元去做这些项目，那么这就是完美的选择。

问题是，无服务器提供商往往会向您收取额外的数据库费用，或者要求您从其他提供商那里购买托管数据库。

但是，如果您的数据库没有更改，该怎么办呢？你能把你的数据库和你的代码捆绑在同一个容器里吗？

这是另一个演示。世界资源研究所(World Resources Institute)保存着世界上每一家发电厂的CSV文件。

我有一个脚本，可以获取他们的最新数据并使用Datasette发布。

Datasette支持插件。你已经在我的Cleo咖啡店演示中看到过这个插件--它被称为Datasette-cluster-map，它的工作方式是查找包含经度和纬度列的表格，并在地图上绘制数据。

一看到这些数据，你就会注意到南极洲下面有几座发电厂。这是麦克默多电站，它有一台6.6兆瓦的石油发电机。

哦，你看，罗斯岛下面也有一个风力发电场，发电能力为1兆瓦。

但这也是多面性的展示。我可以只看到法国的核电站，并在地图上看到它们。

而我在界面上看到的任何东西，我都可以作为JSON输出。这是一份JSON文件，显示了法国所有的核电站。

这是一个CSV导出，我可以使用它将数据拉入Excel或其他与CSV兼容的软件中。

如果我点击“查看和编辑SQL”来返回用于生成页面的SQL查询，我就可以编辑并重新执行该查询。

在大多数Web应用程序中，这会被视为一个可怕的安全漏洞--这是一个SQL注入攻击，是一个有文档记录的功能！

首先，它被设置为只读数据库：不允许使用会修改它的INSERT和UPDATE语句。查询也有一秒的时间限制。

其次，这个数据库中的所有内容都是为发布而设计的。这里没有可能暴露的密码散列或私有用户数据。

这也意味着我们有一个JSON API，它允许JavaScript对后端执行SQL查询！事实证明，这对快速成型非常有用。

这一切都构建在SQLite之上。每个观看这场演讲的人每天都在使用SQLite，即使你不知道它。

大多数iPhone应用程序使用SQLite，许多桌面应用程序也使用SQLite，它甚至可以在我的Apple Watch中运行。

我最喜欢的特性之一是SQLite数据库是磁盘上的单个文件。这使得复制和发送变得很容易，也意味着我可以将数据捆绑在单个文件中，将其包含在Docker文件中，然后将其部署到无服务器主机上，以便在互联网上提供服务。

这是另一个演示，帮助展示GitHub是如何融入这一切的。

去年，覆盖加州大部分地区的电力公司PG&Amp；E切断了该州大片地区的电力供应。

我很幸运：六个月前，我开始搜集他们的停机地图，并将历史记录到GitHub存储库。

Simonw/PGE-Outages是一个GIT存储库，有34,000条提交记录，跟踪PG&；E在其停运地图上发布的停运历史。

我正在使用这些数据发布一个Datasette实例，其中包含其历史停机的详细信息。这是一个页面，显示了受停机影响最多的客户订购的当前停机情况。

有关此项目的更多详细信息，请参阅通过抓取git repo来跟踪PG&；E故障。

我最近决定给这项技术起个名字。我称之为Git抓取--这个想法是把网络上代表某个时间点的任何数据源提交到一个Git库中，这个库讲述了这个特定事物的历史故事。

下面是我的文章，更详细地描述了这个模式：Git抓取：通过抓取到Git存储库来跟踪随时间的变化。

这是由亚历克斯·盖纳(Alex Gaynor)和一支日益壮大的发行人团队创建的《纽约时报》(New York Times)选举宣传网站。它收集《纽约时报》的选举结果，并利用一段时间内的数据来显示结果的趋势。

它使用了一个按计划运行的GitHub操作脚本，再加上一个非常聪明的Python脚本，可以把它变成一个有用的网页。

我将进行一些实时编码，向您展示这些东西是如何工作的。

每当我看到这样的地图，我的第一反应就是打开浏览器开发工具，试着弄清楚它是如何工作的。

如果我打开网络选项卡，刷新页面，然后只过滤XHR请求。

一个巧妙的窍门是按大小排序--因为不可避免的是，列表顶部的内容是页面上最有趣的数据。

这似乎是一个JSON文件，告诉我加利福尼亚州目前发生的所有火灾！

现在，我将进一步将其转换为Datasette实例。

我将使用cURL来获取该数据，然后通过JQ管道将其传递给只过滤AllYearIntents数组的人。

接下来，我将把它输送到我一直在构建的名为sqlite-utils的工具中--这是一套用于操作SQLite数据库的工具。

我将使用“INSERT”命令并将数据插入到事件表的ca-fires.db中。

你可以直接看到其中一排的位置不好，因此它出现在南极洲。

我还可以从各个县的角度来分析，看看2020年哪个县的火灾最多--河滨有21个。

我将更进一步，使用一个名为Datasette Publish的命令将其放到互联网上。

Datasette Publish支持多种不同的宿主提供程序。我要用维塞尔。

我将告诉它将该数据库发布到一个名为“ca-fires”的项目中，并告诉它安装Datasette-cluster-map插件。

然后，这将获取该数据库文件，将其与Datasette应用程序捆绑在一起，并将其部署到Vercel。

这里的目标是尽可能少地查找一些有趣的数据，将其转换为可与Datasette一起使用的SQLite数据库，然后将其在线发布。

这就是我刚刚创建的数据库--互联网上的任何人都可以访问和建立数据库。

为了发布数据，我带您参观了一下Datasette，希望能做一些严肃的数据新闻工作。

去年，我读到了斯蒂芬·沃尔弗拉姆(Stephen Wolfram)的这篇文章：寻求生产性生活：我个人基础设施的一些细节。斯蒂芬·沃尔夫勒姆申请成为一家1000人远程工作公司的首席执行官，这是对长达40年的生产率黑客的一次令人难以置信的探索。他优化了职业和个人生活的方方面面。

但其中有一部分真的吸引了我的眼球。他谈到了一种他称之为“元搜索器”的东西--他个人主页上的一个搜索引擎，可以搜索他曾经做过的每一封电子邮件、日记、文件，所有这些都在一个地方。

我对自己说，我真的想要这样。我喜欢这个我自己的东西的个人门户的想法。

因为它的灵感来自斯蒂芬·沃尔夫拉姆，但我计划打造一个不那么令人印象深刻的版本，所以我决定把它命名为DogSheet。

所以从本质上说，这就是我的个人数据仓库。它从我能找到的尽可能多的来源提取我的个人数据，并为我提供一个浏览这些数据并对其运行查询的界面。

我从Twitter，Apple HealthKit，GitHub，Sarm，Hacker News，Photos，一份我的基因组中获得了数据……。各种各样的事情。

这是另一个关于克利奥的故事。Cleo有一个推特账号，每次她去看兽医时，她都会上传一张自拍，并说她有多重。

下面是一个SQL查询，它查找每条提到她体重的推文，使用正则表达式提取她的体重(以磅为单位)，然后使用Datasette-Vega图表插件显示一段时间内她的自我报告体重图表。

选择Created_at，regexp_Match('；.*？(\d+(\.\d+))lb.*'；，Full_text，1)作为lbs，Full_Text，Case When(media_url_https不为空)，然后选择json_Object('；img_src&39；，media_url_https，'；width'；)，然后选择json_object('；img_src&39；，media_url_https，'；width'；，300)结束为左侧推文中的照片加入tweets.id=media_tweets.twets_id左侧加入media.id=media_tweets.media_id，其中Full_Text Like%lb%'；User=3166449535且lbs不是NULL GROUP BY Tweets.id ORDER BY CREATED_AT Desc Limit 101

几年前我做了23andMe，所以我有一份狗羊的基因组。这个SQL查询告诉我我的眼睛是什么颜色。

选择rsid、基因型、病例基因型，当rsid；aa；然后；aa；然后；rsid；rs12913832和#39；rsid=#39；rsid=#39；rs12913832&39；的基因组解释结束时，80%的情况下选择rsid、基因型、病例基因型；当rsid=；rs12913832&39；时，选择rsid、基因型、病例基因型的几率为99%；当rsid=；rsid=；rs12913832&39；时，选择rsid、基因型、病例基因型的几率为99%；当rsid=；rs12913832&39；

我真的很喜欢苹果在这方面的做法，那就是他们不只是把你所有的数据上传到云端。

这些数据存储在您的手表和手机上，手机上的Health应用程序中有一个选项可以将其导出--作为一个充满XML的压缩文件。

我编写了一个名为HealthKit-to-sqlite的脚本，将该压缩文件转换为SQLite数据库，现在我有了一些表格，比如我的基础能量消耗、我的体脂百分比、我爬过的楼梯。

但真正有趣的是，每当你在苹果手表上追踪户外锻炼时，它每隔几秒钟就会记录下你的确切位置，然后你就可以重新获得这些数据了！

这是我三年前参加旧金山半程马拉松的确切路线地图。

现在，我开始跟踪每次散步时的“户外行走”，这样以后我就可以再次获取GPS数据。

我从GitHub获得了大量关于我的项目的数据--我所有的提交、问题、问题评论和发布--所有我可以使用GitHub-to-Sqlite工具从GitHub API中获得的信息。

所以我可以做一些事情，比如查看我在所有项目中的所有承诺，搜索它们并对它们进行切面。

我有我所有的版本，当我写我的周记，并想弄清楚我一直在做什么的时候，这是很有用的。

事实证明，Apple Photos应用程序使用的是SQLite数据库，如果你知道自己在做什么，就可以从其中提取照片元数据。

他们实际上在你自己的设备上运行机器学习模型，以找出你的照片是什么！

你可以使用机器学习标签来查看你拍摄的鹈鹕的所有照片。以下是我拍摄的所有照片，Apple Photos已经确认它们是鹈鹕。

原来他们还有ZOVERALLAESTHETICSCORE、ZHARMONIOUSCOLORSCORE、ZPLEASANTCAMERATILTSCORE等栏目。

几周前，我终于开始着手打造我一直想要的东西：搜索引擎。

我叫它狗羊Beta，因为Stephen Wolfram有一个叫Wolfram Alpha的搜索引擎。

这是双关语驱动的开发：我不久前想出了这个双关语，我非常喜欢它，所以我致力于构建软件。

我想知道我最后一次吃华夫饼是什么时候。我知道它在库比蒂诺，所以我在狗羊Beta上搜索库比蒂诺，找到了这张照片。

我希望这能说明，如果你把所有的个人数据放在一个地方，你能做多少！

几年前在欧洲通过的GDPR法律对解决这一问题真的很有帮助。

公司必须向您提供访问它们存储的有关您的数据的权限。

许多大型互联网公司对此做出了回应，提供了自助式出口功能，通常隐藏在设置中的某个地方。

您也可以直接从公司请求数据，但自助服务选项可帮助他们降低客户支持成本。

我今天向你展示的一切都是开源的：你可以免费安装这个软件并自己使用。

但需要进行大量的组装。您需要找出身份验证令牌，找到托管它的地方，设置cron作业和身份验证。

指望普通人在某个地方运行安全的网络服务器是相当可怕的。我一直在研究WireGuard和TailScale，以帮助简化设备之间的安全访问，但这在很大程度上仍然只针对超级用户。

将其作为托管服务运行并不吸引人：对人们的个人数据负责是可怕的，而且这可能不是一项伟大的业务。

我认为最好的选择是在人们自己的个人设备上运行-他们的手机和笔记本电脑。我认为让Datasette在这些环境中运行是可行的，我真的很喜欢用户能够将他们的个人数据导入到他们控制的设备上并在那里进行分析的想法。

我在构建我的个人DogSheet仓库时使用过的大部分工具都在DogSheet GitHub组织中--其中很多都使用了Sqlite的命名约定。

问：是否有/是否有我可以支付$的Datasette托管服务？我想支付每月5美元，以获得最新版本的狗羊与所有最新的插件！

我不想为个人隐私数据建立一个托管网站，因为我认为人们应该自己控制这些数据，而且我也不认为有一个特别好的商业模式。

相反，我正在为Datasette(称为Datasette Cloud)构建一个面向公司和组织的托管服务。我希望能够为新闻编辑室和其他小组提供一个私人的、安全的、托管的环境，在那里他们可以彼此共享数据并运行分析。

问：如何将手机/手表中的数据同步到数据仓库？这是人工操作吗？

健康数据是手动的：iOS Health应用程序有一个导出按钮，可以生成一个XML压缩文件，然后您可以将其AirDrop到笔记本电脑上。然后，我针对它运行HealthKit-to-Sqlite脚本，以生成DB文件和SCP，并将其发送到我的DogSheet服务器。

我的许多其他DogSheet工具都使用API，可以在cron上运行，从Sarm、Twitter和GitHub等获取最新数据。

问：在访问Github/Twitter等网站时，您是针对它们的API运行查询，还是定期将数据先同步(我猜主要是检索)到仓库，然后在本地查询？

我总是尝试获取所有数据，以便在本地进行查询。让您运行查询的API的问题是，不可避免地有一些我想做的事情是API无法完成的-所以我更愿意将所有内容都吸收到我自己的数据库中，这样我就可以编写我自己的SQL查询。

下面是我的群集到SQLite脚本的一个示例，它只获取过去两周的签入(使用来自环境变量的身份验证凭据)。

问：你有没有尝试过把它作为一个单页面应用程序来做，这样就有可能把它作为一个静态站点来部署？那里的制约因素是什么？

实际上，使用SQL.js(SQLite编译为WebAssembly)完全可以在客户端JavaScript中查询SQLite数据库。

这个可观察到的笔记本是一个使用它对从URL加载的SQLite数据库文件运行SQL查询的示例。

Datasette的JSON和GraphQL API意味着它可以很容易地充当SPA的API后端。

我建这个网站是为了给旧金山的树木提供一个搜索引擎。查看源代码，查看它如何在后台命中Datasette API：https://sf-trees.com/?q=palm。

您可以使用网络窗格查看它是否正在针对Datasette后端运行查询。

可写的预录查询是一个相对较新的Datasette特性，它允许管理员配置一个更新/插入/删除查询，填写表单的用户可以调用该查询，或者通过JSON API访问该查询。

这个想法是为了使构建后端变得容易，除了提供只读查询之外，这些后端还可以处理简单的数据输入。这是一个很有潜力的功能，但到目前为止，我还没有把它用于任何有意义的事情。

目前它可以生成一个非常基本的表单(带有单行输入值，类似于这个搜索示例)，但我希望在未来扩展它，通过插件支持自定义表单小部件，比如日期、地图位置或针对其他表格的自动完成。

问：在本地版本中，您需要通过1行操作来部署新的数据板：您是如何处理更新的？是否有类似的单行更新来更新现有的已部署数据集？

每次数据更改时，我都会部署全新的安装！这对于一天只更改几次的数据非常有效。如果我有一个每小时更改多次的项目，我会将其作为常规VPS运行，而不是使用无服务器主机提供商。

2020年11月14日凌晨3：53发布·带标签的GitHub、演示文稿、演讲、演讲、数据集、狗羊、Weeknote、sqliteutils·在Twitter上关注@simonw