►
From YouTube: OCTO Speaker Series #4 Simon Willison
Description
Personal Data Warehouses: Reclaiming Your Data
Every nerd deserves their own personal data warehouse - a system that gives them the same kind of analytical capability that is usually reserved for giant tech companies. I've figured out how to build one, using SQLite, Datasette and a handful of open source scripts and APIs.
I'll demonstrate my own personal Dogsheep, built using data from GitHub, Twitter, Foursquare, Apple Photos, Google, LinkedIn, 23AndMe and more. I'll show how legislation such as Europe's GDPR have made it easier for us to build our own personal analytics platforms, and discuss the way forward towards a world where we can empower even non-nerds to finally take control of their personal data.
About Simon Willison
Simon is the creator of Datasette, an open source tool for exploring and publishing data.
Datasette is based on Simon's experiences working as a data journalist at the UK's Guardian newspaper.
Simon is also a co-creator of the Django web framework. He recently completed the JSK Fellowship program at Stanford.
Twitter: @SIMONW
GitHub: SIMONW
Web: https://simonwillison.net/
Learn more about the OCTO Speaker series here: https://octo.github.com/
A
A
When
I
was
thinking
about
how
to
introduce
our
speaker
for
today,
I
realized
that
our
mission
is
missing,
something
that
simon
has
been
doing
for
most
of
his
career
writing
software.
That's
impactful
software
that
enables
others
to
use
software,
lowering
the
cost
of
entry
for
everyone
most
often
associated
with
the
creation
of
the
django
web
framework.
Simon
has
been
influencing
journalism
and
journalists
throughout
his
career.
A
B
Oh,
thank
you.
Thank
you,
idan,
hello,
everyone!
So
yeah!
My
name
is
simon
willison,
and
I
am
going
to
be
talking
today
about
personal
data
warehouses.
So
what
they
are,
why
you
want
one
how
to
build
one
and
some
of
the
kind
of
crazy
things
you
can
do
with
them.
Once
you've
got
one
running
and
I'm
just
going
to
share
my
screen.
B
You're
good.
I
am
good.
My
screen
is
visible:
fantastic,
okay,
so,
as
edan
mentioned
I'll,
be
taking
questions
for
this
in
a
google
doc.
So
there's
a
url
at
the
bottom
of
the
screen.
Bitly.Com
octo
hyphen
simon
w.
If
you
go
there,
you'll
be
dropped
into
this
document,
and
you
can
collect
questions
in
this
section
at
the
bottom
and
also,
please
feel
free
to
add
in
notes
and
things
as
we
go
along
as
well.
B
Let's
use
this
as
a
collaborative
space
during
the
talk,
so
I'm
gonna
start
with
a
demo
and
for
the
demo
I'm
going
to
use
cleo.
This
is
cleo
clear
as
my
dog
and
as
you
can
tell,
she
is
a
proud
resident
of
san
francisco.
She
won
first
prize
in
a
costume
competition.
A
couple
of
years
ago,
dressed
here
as
the
golden
gate
bridge,
but
the
question
I
want
to
answer
is
how
much
of
a
san
francisco
hipster
is
clio,
and
I
can
do
that
using
my
data
warehouse.
B
B
Every
time
I
check
in
with
clio,
I
use
the
wolf
emoji
in
the
shout,
which
is
the
check-in
message,
which
means
I
can
run
a
query
where
I
can
say
show
me
all
the
check-ins,
where
the
shout
contains
that
wolf
emoji
and
if
I
do
that
it
will.
This
will
trim
all
of
my
checkings
down
to
basically
a
map
of
cleo's
favorite
places.
This
is
280
places
that
cleo
has
checked
into,
but
I
want
to
know
sort
of
what
kind
of
stuff
is
she
into
so
I'm
going
to
facet
by
venue.
B
Category
faceting
is
essentially
a
sort
of
group
by
count
operation.
That
shows
you
a
count
of
the
number
of
value
of
different
unique
values
in
a
column,
and
so
I
can
see
that
cleo
has
checked
into
a
park
57
times
a
dog
run
32
times,
but
a
coffee
shop
19
times
so
in
our
exploration
of
her
san
francisco
hipsterness.
Let's
take
a
look
at
a
map
of
coffee
shops
that
clejo's
cleo
has
checked
into
she's
got
19
chickens.
They
should
just
here
we
go
there.
A
B
B
How
do
I
do
which?
But
how
about
now
can
be?
Can
you
see
anything
now
success
and
you
can
see
a
map
of
of
clio's
check-ins
right?
Yes,
fantastic!
I
apologize
for
that.
So
very
briefly,
I
started
with
all
of
these
check-ins.
I
filtered
them
down
to
just
the
ones
with
the
wolf
emoji
and
then
I
filtered
those
down
to
just
the
ones
where
the
venue
where
the
venue
category
was
coffee
shop.
B
B
There
was
a
journalist
there
called
simon
rodgers,
who
was
an
absolute
wizard
at
tracking
down
data.
He
could
ask
him
for
any
facts
in
the
world
and
he'd
know
who
to
call
and
who
to
which,
which
government
sources
to
bother
to
get
that
data
back
out
again
and
all
of
that
data
lived
in
excel
spreadsheets
on
his
on
his
desktop
on
his
desktop
computer.
So
we
we
conspired
and
thought
okay.
B
B
The
challenge
we
had
is,
how
do
we
publish
those
numbers
and
we
ended
up
going
for
the
sort
of
cheapest
and
most
easy
to
get
going
solution,
which
was
google
sheets,
so
the
guardian
would
publish
a
google
sheet
with
us
public
debt
by
day
or
whatever.
The
number
was
behind
the
stories
at
the
time
and,
despite
being
a
pretty
low-tech
way
of
doing
things,
this
worked
extremely
well.
B
But
I
was
always
frustrated
by
this
because
I
felt
like
there
really
should
be
something
better
than
google
sheets
for
publishing
data,
something
more
flexible
and
more
essentially
more
open
that
we
could
use
to
get
this
data
out
into
the
world
so
fast
forward.
Seven
years
in
2017
I
was
looking
at
serverless
hosting
and
these
new,
this
new
style
of
hosting
provider
and
thinking
okay.
What
are
the
things
that
I
can
do
with
this
that
I
couldn't
have
done
previously?
It's
a
question.
I
always
ask
myself
with
new
technology
is
okay.
What
does
this?
B
B
But
the
cats
that
you
find
with
a
lot
of
these
things,
like
the
cell,
which
used
to
be
called
zeit
now
or
google
cloud
run
or
or
heroku,
is
that
if
you're
on
a
database,
it
tends
to
cost
extra
because
databases,
things
that
are
storing
data-
that's
changing
over
time-
are
still
a
lot
more
expensive
to
run
and
operate
and
scale
up.
One
of
the
beautiful
things
about
serverless
hosting
is
since
it's
just
code
you
can
they.
B
This
made
me
think:
well,
okay,
so
I'm
not
allowed
to
database
on
these
things,
but
essentially
they
give
I
get
to
give
them
a
docker
container
and
they
run
that
container
in
the
cloud.
What
if
I
put
my
data
in
the
docker
container
like
what,
if
I'm
dealing
with
databases
that
don't
have
updates
they're,
not
changing
but
they're,
still
data
that
I
want
to
publish-
and
I
realized
that
that
was
the
exact
problem
that
we'd
been
wanting
to
solve
with
the
guardian.
B
How
do
you
publish
data
online
in
a
way,
that's
as
flexible
and
inexpensive
as
possible,
and
so
that
was
the
original.
The
initial
impetus
for
dataset
was,
if
I
bundle
the
data
and
the
code
together
in
a
single,
usually
in
a
docker
container
and
deploy
that
to
the
internet.
Can
I
take
advantage
of
serverless
hosting
and
for
the
size
of
projects
I
was
carrying
about,
but
use
it
to
share
these
interesting
data
sets
with
the
world.
So
the
initial,
the
first
couple
of
years
of
on
data
set,
was
spent
solving
exactly
that
problem.
B
B
Data
sources
that
use
github
as
their
primary
way
of
distributing
the
data
and
the
csv
file
is
great,
but
we
can
do
better
than
that,
so
I've
got
a
script
which
runs,
I
think,
a
couple
of
times
a
day
checks
for
updates
to
this,
and
if
there
are
updates
it
publishes
it
using
data
set.
So
this
right
here
is
that
is
the
contents
of
that
csv
file
that
the
is
published
by
this
organization
only
once
I've
got
it
in
data
set,
I
can
start
doing
more
interesting
things
with
it.
B
Dataset
supports
plugins
you've
already
seen
this
plug-in
for
my
my
dog's
map
of
coffee
shops.
This
is
a
plug-in
called
dataset
cluster
map.
What
it
does
is
looks
for
tables
with
latitude
and
longitude
columns
and
if
it
finds
one,
it
draws
them
on
the
map.
So
straight
away.
Looking
this
data,
you
notice,
oh
there's,
a
couple
of
power
plants
down
here
in
antarctica
yeah.
This
is
mcmurdo
station,
which
has
a
6.6
megawatt
oil
generator
and
ross
island
has
a
oh
look
at
this.
B
I
didn't
know
that
there's
a
wind
farm
down
in
antarctica
knocking
out
one
one
megawatts
of
electricity,
but
this
is
also
an
illustration
of
faceting.
So
if
I
want
to
see
a
map
of
just
the
nuclear
power
plants
in
there's,
198
nuclear
power
plants
in
the
world-
and
I
just
want
to
see
the
19
that
are
in
france
here-
it
is-
this-
is
a
slice
and
dice
of
nuclear
power
plants
in
france.
A
couple
of
really
important
features
that
I
should
illustrate.
B
B
I
can
also
get
it
back
as
csv
if
I
want
to
export
it
into
excel
or
other
csv
compatible
software
even
more
fun.
If
I
click
view
and
edit
sql,
I
get
the
actual
sql
query.
That
was
used
to
generate
this
page,
which
I
can
edit.
So
I
can
say
you
know
what
I
just
want:
the
capacity,
the
latitude
longitude
and
the
primary
fuel,
and
if
I
trim
this
down
here
and
then
re-execute
the
query
there
we
go.
This
is
just
giving
me
back.
B
This
is
just
giving
me
back
those
four
columns
which
I
can
then
get
back
as
json,
so
this
in
most
web
applications
would
be
seen
as
a
terrifying
security
hole,
because
this
is
sql
injection,
except
as
a
core
feature
of
the
framework.
The
reason
it's
not
a
problem
here
is:
firstly,
we're
dealing
with
a
read-only
database
updates
and
inserts
and
such
like
just
aren't
allowed.
The
file
is
the
the
database
is,
is
immutable.
B
B
There
are
no
password
hashes
or
any
private
data
at
all,
but
this
also
means
that
we've
got
an
ap,
a
json
api
that
lets
javascript,
execute
sql
queries
like
I
can
construct
this
url
up
here
in
javascript,
add
json
as
the
extension
and
get
back
the
json
results
of
any
sql
query,
which
I
initially
thought
was
just
kind
of
kind
of
amusing,
but
I've
actually
found
as
a
prototyping
tool.
This
is
really
powerful.
B
I
can
build
prototypes
in
javascript,
where
I
literally
embed
sql
statements
into
my
javascript
code,
so
that's
a
sort
of
example
of
the
kind,
the
kind
of
stuff
that
you
can
do
with
dataset.
It's
worth
talking
about
what
the
secret
source
under
the
hood
is,
and
the
secret
source
behind
all
this
is
this
incredible
database
called
sql
lite.
Everyone
on
this
call
uses
sqlite
every
day,
even
if
you
don't
realize
it
because
it
is
the
it
calls
itself
the
most
used
database
engine
in
the
world.
B
It's
designed
to
run
on
on
on
devices,
so
I've
got
a
sqlite
database
in
my
apple
watch
that
tracks
my
steps.
Sql
most
of
the
apps
on
my
iphone
are
using
sqlite,
desktop
apps
use
it
it's
absolutely
everywhere,
and
it's
fast.
It's
self-contained,
it's
very
reliable,
extremely
well
tested,
but
my
favorite
attribute
of
sql
like
is
that
the
database
itself
is
a
single
file
on
disk.
It's
a
db
file.
You
compare
this
to
mysql
or
postgres,
where
you've
got
a
server
with
all
sorts
of
crazy
directory
structures.
B
B
I
stick
it
in
the
container
with
the
application
code,
and
I
put
that
thing
online,
so
I'm
going
to
show
another
demo,
which
I
think
is
particularly
relevant
to
to
to
to
to
thinking
about
how
github
fits
into
all
of
this
last
year.
You
may
remember
that
pg
e,
the
electricity
company
that
covers
most
of
california
had
a
whole
bunch
of
scheduled
outages
to
deal
with
with
risks
of
wildfires.
It
was.
B
They
turned
off
power
to
huge
quantities
of
california,
and
I
got
really
lucky
with
this,
because
six
months
before
they
started
doing
that,
I'd
started
quietly,
scraping
their
outage
map
and
dumping
the
results
into
a
git
repository.
So
this
right
here
is
a
get
github
repo
with
34
000
commits
tracking
the
history
of
outages
that
pg
e
published
on
their
outage
map.
You
can
see
that
two
minutes
ago
they
added
35
new
outages,
they
removed
two
outages
and
they
updated
the
details
on
on
23
of
them.
B
This
is
a
technique
that
I
recently
gave
it
gave
a
name
to
I'm
calling
it
git
scraping.
The
idea
with
git
scraping
is
here
we
go.
Is
that
any
you
can
take
any
data
endpoint
on
the
web,
which
is
a
sort
of
point
in
time.
Snapshot
like
this
is
the
power
outages
right
now
and
if
you
repeatedly
commit
it
to
a
git
repository,
you
get
a
commit
log
that
tells
the
story
of
the
history
of
that
particular
thing.
B
B
So
the
what
this
page
does
is
it
scrapes
the
new
york
times
current
list
of
of
of
votes
that
have
come
in,
but
because
it's
keeping
a
history
of
them
in
in
a
git
repository,
it
can
show
you
how
they
have
changed.
So
it'll
show
you
that
39
minutes
ago
the
there
was
a
change
of
140
votes
in
the
state
of
nevada,
and
this
is
it's
absolutely
worth
taking
a
look
at
the
code
for
this.
B
But
this
technique,
I
think,
is
super
useful
as
a
in
working
in
journalism
for
tracking
things,
but
also
for
all
for
any
time
that
you
want
to
keep
track
of
how
something
changes
over
time
thanks
to
github
scheduled
actions.
Setting
one
of
these
up
is
is
really
really
simple,
so
I've
got
a
demo,
I'm
going
to
do
a
very
a
little
bit
of
live
coding.
Just
to
show
you
how
this
kind
of
stuff
works,
I'm
going
to
use
the
cal
fire
website.
B
B
So
I'm
going
to
hit
inspect
element,
I'm
going
to
open
up
the
network,
tab
refresh
the
page
and
then
a
really
fun
trick
with
these
is
to
filter
by
xhr
requests
and
then
order
by
size,
because
when
you
do
that,
inevitably
the
thing
at
the
top
of
the
list,
the
things
at
the
top
list
are
the
actual
data.
For
that
page.
This
thing
right
here
looks
interesting.
I'm
going
to
open
this
in
a
new
tab.
B
Have
it
looking
at
this?
The
all
year
instance
key
appears
to
be
the
most
interesting.
It's
got
260
things
in
it.
Each
of
these
is
a
fire
that
occurred
this
year.
So
I'm
going
to
curl
this.
That
gives
me
back
the
json.
I'm
I'm
just
interested
in
all
incidents.
So
I'm
going
to
pipe
this
through
the
jq
command
jq
dot.
What
was
that
thing
called
dot
all
year?
Incidents.
B
There
we
go
that's
a
list
of
of
instance
this
year.
Now
I'm
going
to
pipe
this
into
a
tool.
I've
been
building
called
sqlite
utils,
which
is
a
suite
of
utilities
for
manipulating
these
sqlite
databases.
In
this
case,
I'm
going
to
use
the
insert
command,
insert
it
into
a
files.db,
I'm
going
to
call
it
incidents
actually
now
called
ca
fires
and
I'm
going
to
read
from
standard
in.
B
B
So
with
data
set
publish,
I
can
select
the
the
hosting
provider.
I
want
to
use
I'm
going
to
use
verse
cell
because
I
don't
know
it
deploys
really
quickly.
I'm
going
to
say
database
says,
publish
burst
cell
ca
fires.db
and
I'm
going
to
give
it
a
project
called
ca
fires,
and
I'm
also
going
to
tell
it
that
I
want
it
to
install
that
dataset
cluster
map
plugin.
B
B
It's
cued,
so
maybe
maybe
I'm
in
a
queue
behind
a
few
other
things
I'll
come
back
to
this
in
a
few
seconds,
but
the
idea
here
is
to
make
it
to
to
have
as
few
steps
as
possible
between
finding
some
interesting
data,
turning
it
into
a
sqlite
database
such
that
you
can
query
it
and
use
dataset
on
top
of
it
and
then
stick
it
online
with
a
url
so
that
other
people
can
start
using
that
as
well.
Oh
here
we
go.
B
But
what
has
this
got
to
do
with
personal
data
warehouses,
so
I've?
Given
you
a
whistle
stop
tour
of
dataset
for
the
purposes
of
publishing
data
and
sort
of
doing
serious
journalism,
and
then
last
year
I
read
this
essay.
This
is
an
essay
by
stephen
wolfram
and
it's
the
title
is
wonderful.
It's
called
seeking
the
productive
life
some
details
of
my
personal
infrastructure.
The
main
feature
of
this
essay
is
how
long
it
is.
B
If
you
look
at
this
little
scroll
bar
it's
this
incredible
exploration
of
40
years
worth
of
productivity,
hacks
that
stephen
wolfram
has
applied
in
order
to
become
a
ceo
of
a
thousand
person
company
who
works
remotely
he's
based
a
thousand
miles
away
from
the
office,
which
I
guess
we
all
are
now
with
with
kovid,
but
he
was.
He
was
a.
He
was
ahead
of
us
in
that
one,
but
he's
put
an
enormous
amount
of
work
into
optimizing,
every
aspect
of
his
professional
and
personal
life.
B
Now
he
has
a
standing
desk,
but
then
his
heart
rate
monster
showed
him
that
his
heart
rate's
better
when
he
walked
around
outside.
So
he
built
himself
a
little
laptop
tray,
so
he
could
go
walking
in
the
woods
and
he
scanned
every
document
from
when
he
was
11
years.
Old
he's
got
a
green
screen
in
his
basement,
so
he
can
give
talks
from
home.
All
of
this
amazing
stuff-
and
I
was
reading
through
this
thinking-
wow.
This
is
completely
crazy,
but
there
was
one
thing
in
here
that
really
caught
my
eye.
B
He
talked
about
how
he
has
a
thing
that
he
calls
a
meta
searcher,
it's
a
search
box
on
his
personal
homepage
that
can
search
across
all
of
his
emails
and
journals
and
people
he
knows,
and
files
on
his
machines.
Everything
he's
ever
done
all
brought
together
in
one
place,
and
I
thought
to
myself.
You
know
I
really
want
that.
B
I
love
this
idea
of
like
a
personal
portal
onto
my
own
stuff,
and
so
because
it
was
inspired
by
stephen
wolfram,
but
I
was
planning
on
building
a
much
less
sort
of
alpha
version
of
what
stephen
wolf
stephen
wolfram
had
built.
I
decided
to
call
it
dog
sheep
he's
stephen
wolfram.
My
thing
is
called
dog
sheep,
and
so
I've
been
building
this
over
the
past
year,
and
this
is
essentially
this
is
my
personal
data
warehouse.
B
This
is
a
tool
which
pulls
in
as
much
data
as
I
can
find
about
myself
from
as
many
different
sources
and
gives
me
an
interface
that
lets
me
explore
it
and
run
queries
against
it,
and
so
I've
got
data
in
here
from
twitter
from
apple
watch
health
kit,
I've
got
github
data,
swarm,
check-ins
my
photos,
I've
got
a
copy
of
my
genome,
there's
a
whole
bunch
of
different
stuff
that
I'm
running
in
here
and
I'll.
Show
you
a
couple
of
demos.
B
Here's
another
demo
with
clio,
so
clio
has
a
twitter
account
and
every
time
she
goes
to
the
vet,
she
posts
a
selfie
and
she
says
how
much
she
weighs
because
she
gets
weighed
at
the
vet.
And
so
what
I'm
doing
here
is
I've
got
a
sql
query
which
finds
every
tweet
where
she
mentions
her
weight,
uses
a
regular
expression
to
full
pull
out
her
weight
in
pounds,
and
then
I
can
use
a
charting
plug-in
to
draw
a
chart
of
her
weight
over
time.
B
So
this
is
cleo's
clear's
weight
over
time,
as
reported
by
her
personal
selfies
on
twitter.
It's
clearly
another
super
useful
thing
to
be
able
to
do.
I
did
23andme
a
few
years
ago,
so
I've
got
a
copy
of
my
genome
in
in
dog
sheep,
and
I
figured
out
a
sql
query
that
can
tell
me
what
color
my
eyes
are,
and
apparently
my
eyes
are
blue
99
of
the
time,
because
this,
the
gene
that
this
rs
id
is,
is
a
gg
genotype
again
crazy,
useful.
B
I
mentioned
apple
health
kit,
so
I
use
the
apple
watch
the
amount
of
data
the
apple
watch
tracks
about
you
is
absolutely
astonishing
and
something
I
really
like
about
apple's
approach
to
this
is
that
they
don't
stick
this
on
the
cloud
for
you.
They
keep
it
on
your
watch
and
on
your
phone
turns
out.
There's
an
export
button
in
the
health
app
on
the
phone
that
you
can
use
to
export
a
zip
file
full
of
xml
with
all
of
this
stuff.
B
So
I,
when
I
ran
the
san
francisco
half
marathon,
this
was
what
three
years
ago
I
got.
I
now
have
a
map
of
the
exact
route
that
I
took
and
again
it's
available
as
csv
it's
available
as
json.
This
is
a
really
amazing
way
of
exploring
you
of
extracting
exploring
your
own
personal
data.
Every
time
I
go
on
a
walk
now
I
track
it
as
an
outdoor
walk
on
my
watch
just
so
I
can
get
that
gps
data
back
out
again
later
a
couple
of
other
things.
B
This
is
a
super
useful
thing
for
people
who
use
github
a
lot.
I've
pulled
in
all
of
my
github
activity
from
and
that's
commits
I've
made
to
projects
issues
issue
comments
releases
everything
that
I
can
get
out
of
the
github
api
for
my
project
and
it
means
I
can
do
things
like
view
all
of
my
commits
to
everything
every
project
I
have
on
github.
I
can
fast
it
and
say:
okay
show
me
the
61
commits
I
made
a
dataset
cluster
map.
I
can
search
them.
B
So
if
I
search
for
pi
test,
it'll
show
me
all
of
my
commits
that
mentioned
pi
test
in
the
commit
message
and
again
I
can
and
because
I
can
build
custom
sql
queries
here,
I
can
actually
build
essentially
little
applications
that
are
defined
as
sql
queries,
which
I
then
bookmark.
So
if
I
want
so
I've
got
a
bookmark
which
can
show
me
issues
that
I
haven't
replied
to
yet
based
on
a
sort
of
sql
subquery.
I've
got
I
I
use
sql
views
as
well.
B
B
It
does
a
huge
amount
of
work
on
your
own
devices,
including
it
turns
out
running
machine
learning,
analysis
against
your
photographs
directly
on
your
device,
apple
download,
a
machine
learning
model
to
you
to
your
phone,
and
they
run
that
against
photographs
that
you
take
and
it's
all
stored
in
sqlite.
There's
a
sql
like
database
for
the
apple
photos
app
which,
once
you
get
access
to
it
to
it,
you
can
pull
sorts
of
interesting
things
out
of
it.
B
So
here
are
photos
that
I've
taken,
which
is
and
you've
got
location
information
and
that
kind
of
stuff
as
well.
But
if
you
pull
out
the
machine
learning
labels,
you
can
do
things
like
run,
sql
queries
that
show
you
photographs
you've
taken
of
pelicans.
So
this
is
a
sql
query.
That's
finding
everything
where
one
of
the
machine
learning
labels
was
pelican.
B
You
can
see
that
apple
photos
does
a
pretty
great
job
of
identical
and
identifying
pelicans,
but
the
really
fun
thing
I
found
digging
through
their
database
is,
it
turns
out
they
calculate
scores,
so
there
are
columns
in
apple
photos
for
things
like
z,
overall,
aesthetic,
score
z,
highlight
visibility,
score,
harmonious
color,
score,
there's
even
one
for
inch,
pleasant,
camera
tilt
score
and
they
don't
expose
this
in
the
applications,
but
it's
there
in
the
data.
So
this
right
here
isn't
just
a
photos
of
pelicans
I've
taken.
This
is
ordered
by
the
aesthetic
school.
B
So
this
is
apple's
algorithmically
selected,
best
photographs,
I've
taken
the
pelican,
which
is
a
pretty
useful
thing
to
be
able
to
do.
If
you
want
to
know
more
about
this,
there
are
links
in
the
notes.
I
have
a
entering
my
blog,
where
I
talk
about
using
sql
to
find
my
best
photograph
of
a
pelican
and
then
a
few
weeks
ago
I
finally
got
around
to
building
the
search
engine.
B
B
This
is
essentially
12
months
of
pun,
driven
development
at
this
point,
but
this
lets
me
search
across
my
tweets
bookmarks,
my
blog
get
commits
and
blog
entries
my
comments
on
hacking
news
all
of
this
different
stuff,
all
in
one
place,
and
I
actually
use
this
the
other
day.
I
was
trying
to
answer
the
question:
when
did
I
last
last
eat
a
ice
cream
that
was
shaped
like
a
fish?
I
remember
I
did
it
in
cooperty
now.
If
I
search
for
cupertino,
this
shows
me
photo.
It
shows
me
tweets
about
cupertino
oh
hold
on.
B
B
But
this
is
this
hopefully
gives
you
an
idea
for
the
kind
of
the
kind
of
power
you
get
once
you
get
all
of.
If
you
can
get
all
of
your
data
into
one
place,
especially
into
like
a
database
that
you
control
the
things
that
you
can
build
on
top
of,
it
are
essentially
limitless
and
I
feel,
like
lots
of
lots
of
nerdy
people
have
tried
doing
this
stuff
in
the
past.
The
catch
is
always
okay,
so
you've
accumulated
all
of
this
data.
B
How
can
you
build
stuff
on
top
of
it
and
I
think,
being
able
to
having
it
in
these
sqlite
databases
with
a
relatively
simple
web
interface
on
top?
That
feels
to
me,
like
the
missing
piece.
That's
the
thing
that
you
need
in
order
to
be
able
to
slice
and
dice
your
data
without
having
to
write
lots
of
additional
software.
On
top
of
that
that
raw
interface,
oh
there
we
go.
That's
the
the
cell,
the
cell
application
has
is
deployed
here
and
is
running
here,
so
a
couple
of
last
thoughts.
B
So
if
you
dig
around
in
pretty
much
any
large
site,
you
use
facebook
or
linkedin
or
or
twitter
they're
somewhere
buried
in
the
settings,
there'll
be
an
export
button
and
if
you
click
that
export
button,
they'll,
probably
email-
you
a
two
gigabyte,
a
link
to
a
two
gigabyte,
zip
file,
full
of
xml
or
json
or
whatever,
which
isn't
that
useful
on
the
surface.
But
if
you
build
tools
to
turn
that
into
say
a
sqlite
database,
you
can
suddenly
suddenly
start
deriving
all
kinds
of
useful
things
about
it,
the
gdpr.
B
Actually
it
turns
out
you
can
normally
you
can
contact
these
companies
directly
and
make
a.
I
think
it's
called
a
subject:
information
request.
You
can
request
additional
data
which
they
will
send
you
as
well,
but
they're
trying
to
keep
their
customer
support
costs
down
as
much
as
possible
by
giving
you
that
that
that
self-service
export.
B
B
It's
all
open
source
you're,
welcome
to
go
and
run
it
yourself,
but
you
will
have
to
set
up
cron
jobs
run
a
server
somewhere
figure
out
how
to
secure
it
all
of
that
kind
of
stuff,
and
it's
it's
a
lot
of
work,
but
this
it
feels
like
this
is
something
that
regular
non-super
nerd
humans
should
have
access
to.
I've
got
a
few
thoughts
about
that.
I
think
ask
expecting
regular
human
beings
to
run
a
web
service
somewhere
is
pretty
terrifying
right
running
a
web
server
is
not
easy.
B
I
have
been
looking
at
wire
guard
and
tail
scale
as
some
sort
of
personal
vpn
options,
which
do
make
it
a
little
bit
easier
to
secure
access
between
your
devices,
but
this
is
still
like
a
super
power
user
level
type
of
thing.
I've
shown
this
demo
to
a
few
people
wow.
You
could
totally
like
do
this
as
a
hosted
service.
B
I
would
hate
that
right,
taking
responsibility
for
people's
super
private
personal
data
is
a
terrible
risk
and
it's
also
a
terrible
business
model
you're
not
going
to
make
a
great
deal
of
money
out
of
people
doing
that
for
them.
So
I
think
the
best
option
to
this
stuff
are
applications
that
people
run
on
their
own
devices.
B
Imagine
a
desktop
application,
or
even
better
a
mobile
application
that
people
could
run
which
could
ingest
their
data
and
give
them
the
ability
to
explore
it
in
in
this
way,
and
so
I've
been
looking
at
how
dataset
itself
could
run
on
mobile
devices
make
with
an
interface
that
lets
you
into
integrate
with
different
different
data
sources.
I
think
it's
it's
a
lot
again.
It's
a
lot
of
work,
I'm
hoping
I
it's!
B
My
call
to
action:
please
try
this
yourself,
everything
I've
shown
you
today
is
open
source.
If
you
go
to
dataset.io,
that's
details
about
dataset,
I
have
github.com
dog.
Sheep
is
the
organization
that
sits
on
top
of
all
of
the
dog
sheep
tools.
These
are
things
like
github
to
sqlite
and
health
kit
to
sqlite
and
swarm
to
sql
knight,
you
may
notice
a
naming
convention
here.
B
These
are
tools
that
turn
that
data
into
sqlite,
so
that
you
can
use
it
in
that
way
and
if
you
check
out
my
blog
simonwilson.net
or
follow
me
on
twitter,
I'm
this
is
something
I
I
talk
about
a
lot
there
as
well,
so
I'm
going
to
switch
over
to
the
google
doc
and
see
if
we
have
questions.
I
should
answer.
Oh,
what
happened
there
hang
on
bitly.com
octosimw.
B
Okay,
I'll
skip
the
first
two,
because
I
see
it
with
those
just
in
case.
Will
there
be
a
data
set
hosted
service?
I
can
pay
for.
Yes,
there
will,
but
it's
not
for
I'm,
not
I'm
not
creating
it
for
personal
use.
I
mean
you
could
use
it
personally,
but
the
idea
that
I
have
around
the
hosted
services
I
want.
B
I
have
to
install
this
where
and
you
know
the
I
feel
like
I'm
not
able
to
unlock
the
value
of
the
software
I've
built
if
I
have
to
talk
people
through
installing
python
3
and
getting
an
environment
set
up
and
running
a
server
and
all
of
that
kind
of
junk.
So
I
am
building
a
sort
of
aimed
company's
hosted
version
of
data
set
at
the
moment.
B
How
do
you
sync
your
data
from
your
phone
and
watch
to
the
data
warehouse?
It
is
a
manual
process.
You
know
what
I
can
actually
do,
a
very
quick
demo
to
show
you
how
that
works.
I
found
out
recently
you
can
run
quicktime
and
you
can
tell
quicktime
that
you
want
to
do
a
new
recording,
oh
except
aha,
there
we
go
so
you
can
now
see
you
can
you
can
use
quicktime
to
mirror
your
iphone
to
your
desktop,
so
I'm
going
to
open
up
the
apple
health
app
and
then
in
the
health
app.
B
There
is
a
export
all
health
data
button.
You
click
that
and
it
churns
away
for
about
five
minutes,
but
then
it
gives
you
a
literal,
zip
file
that
you
can
airdrop
onto
your
computer.
So
I
do
that
I
airdrop
onto
my
computer
and
then
I
run
my
health
kit
to
sqlites
quick
there.
Most
of
the
sources
and
data
in
dog
sheep,
thankfully,
are
automated.
They
have
apis
that
I
can
hit
from
a
cron
job.
The
health
kit
data
is
the
one
that
is
is
the
best
example
of
data
that
I
can't
do
that
with.
B
I
have
to
about
once
a
month
I'll
run
the
export
I'll
run
the
script,
and
then
I
ssh
up
the
database
when
accessing
github
twitter.
Do
you
want
quiz
against
the
api
yeah
right?
So
one
of
the
core
philosophies
I
have
around
this
is:
I
just
want
all
of
the
data
right,
because
if
you
you
can
give
me
an
api
with
like
search
filters
and
all
kinds
of
different
things
and
I'll
still
think
of
something
that
I
can't
do
with
your
api.
B
What
I'll
do
instead
is,
I
will
try
and
find
an
endpoint
that
I
can
paginate
through
to
get
everything
I'll
pull
down.
All
of
the
data
stick
in
a
local
sqlite
database
and
run
queries
against
it.
That
way,
so
my
biggest
ask
of
anyone
who
has
an
api
is
give
me
a
endpoint
that
gives
me
everything
and
then
give
me
an
end
point
that
gives
me
everything.
That's
changed
in
the
last
say
two
hours,
so
things
like
swarm.
I
run
it.
B
I
actually
you
know,
I
can
show
you
the
swarm
thing,
so
I
have
command
called
swarm
to
sqlite
and
I
can
say
swarm.db
and
I
can
say
since
equals
2w,
and
this
will
then
fetch
all
of
my
check-ins
for
the
past
two
weeks.
Oh
that's
interesting.
I
wonder
why
that
happened
all
right.
The
problems
with
live
demos-
something
has
broken
about
this
or
maybe
it
hasn't.
B
So
I
have
got
that
there
we
go
so
I've
got
my
swarm
database
and
if
I
open
that
up
in
dataset,
I'm
now
looking
at
just
two
weeks
worth
of
check-ins
but
yeah.
So
a
lot
of
the
endpoints
that
the
endpoints
I
like
are
the
ones
where
you
can
say
give
me
everything.
That's
changed
recently,
because
then
you
can
hit
them
from
a
cron
job
as
often
as
you
want
to
have.
You
explored
doing
this
as
a
single
page
app.
So
it's
possible
displays
a
static
site.
B
B
The
other
thing
that's
good
is
I
wonder
if
I've
got
sftrees.com
working,
maybe
it's
sf
python,
and
because
everything
that
dataset
does
is
exposes
a
json
api,
you
can
build
javascript
applications
that
query
that
api-
and
this
is
an
example
I
built,
which
is
a
search
engine
for
trees
in
san
francisco,
and
this
is
a
it's
a
single-page
javascript
app.
If
you
watch
what
it's
actually
doing,
let's
search
for
each
tree
or
palm
trees.
What
it's
doing
is
it's
doing
a
json
request
to
a
data
set
instance
and
running
a
sql
query.
B
So
literally
somewhere
in
here
here
we
go.
There
is
a
sql
query
embedded
in
the
javascript
dataset
supports
course
headers.
So
this
little
this
little
javascript
application
right
here
is
static,
html
and
javascript.
That's
pulling
from
a
dataset
api,
and
I
recently
well
this
year
I
built
a
a
dataset
plugin
that
lets
you
do
a
graphql
queries
as
well.
So
if
you
go
and
look
at
this
demo
here,
there
is
a
graphql
api
to
in
this
case.
B
Do
I
have
any
crown
job
examples
of
the
scrapers
that
I
can
share?
Yes,
what
I
will
do
is
so
quadruple
samsung
scrapers.
I
will
share
those
in
that
document
later
on
I'll
copy
and
paste
them
over
from
my
private
repository
and
then
what
possibilities?
The
data
entry
tools
do
the
writable
can
queries
open
up.
B
So
you
can
create
a
essentially
you
can
configure
dataset
and
say
I
want
a
an
update
query,
an
insert
query
which,
which
sits
on
a
page
and
people
can
then
use
that
to
execute
controlled,
writes
against
the
database
right.
We
don't
want
people
executing
any
sql
that
they
like,
but
if
you
sort
of
allow
list
sql
queries
that
that
you
know
are
safe,
then
you
can
build
them
that
way.
B
I
think
it's
it's
still
very
early
days
with
that
feature,
though,
so
I'm
keen
on
exploring
that
for
things
like
building
crowdsourcing
applications,
accepting
feedback
from
people
like
blog
comments,
there's
lots
of
things
like
that
that
you
could
build,
but
it's
not
really
been
an
emphasis
of
the
projects.
I've
been
working
on,
okay
for
the
local
version.
We
had
one
line
push
to
deploy.
How
do
you
handle
updates?
B
That
is
your,
so
that
was
when
I
ran
data
set,
publish
yep
with
all
of
these
hosting
providers.
You
literally
just
publish
it.
You
deploy
a
new
version
like
the
cell
was
designed
to
allow
people
they
they
actually
had
to
cut
down
the
number
of
deploys
people.
Do
they
now
only
allow?
I
think
160
deploys
a
month
because
people
like
me
would
be
using
it
like
crazy,
but
yeah.
If
you've
got
a
data
source
which
only
changes
once
a
day,
that's
only
24
deploys
a
month.
B
You
know
it's
not
a
big
deal,
so
I
have
because
some
of
my
projects,
like
the
pge
outages
stuff,
because
that's
a
database
that
changes
every
few
minutes.
I
tend
to
run
those
on
traditional
hosting
providers
with
cron
jobs
that
write
to
a
database
on
disk
but
yeah.
If
you've
got
data
that
changes
less
often
than
that,
I
think
there's
no
harm
at
all
in
just
deploying
a
brand
new
version.
Every
time
you
have
an
update
to
the
data.
A
This
is
clearly
at
a
stage
that
demonstrates
the
the
possibility
sort
of
the
power
of
of
the
approach
that
dataset
espouses,
of
pulling
and
data
having
a
static
data
set
and
then
exposing
an
interface
for
working
with
that
data,
and
looking
at
this,
I
can
see
a
future
where
I
reclaim
control
over
the
49
silos
of
my
personal
data.
What
does
that
future?
A
Look
like
you
know
if
I,
if
I
wave
a
magic
wand
and
say
okay,
now
it's
easy
enough
for
the
quote-unquote
non-super
nerds
to
to
do
this
sort
of
thing.
What
do
you
think
that
future
looks
like
when
it
comes
to
sort
of
our
personal
data,
whether
or
not
it's
the
data
set
or
through
other
tools?
Where
are
we
going
with
this.
B
So
I
think
my
my
personal,
like
my
dream
state
is
anyone
can
install
the
dog
sheep
app
on
their
phone.
They
can,
like
click,
some
buttons
in
it
to
authenticate
against
different
providers
start
getting
that
data
coming
in
and
then
they
can
also
like
occasionally
like
do
well.
You
know,
ideally
that
that
would
that
that
would
give
them
and,
to
be
honest,
like
apple
health
kit,
is
sort
of
hinting
in
this
direction.
Apple
healthcare
integrates
with
your
doctor's
like
online
database
for
your
health
data,
but
why
can't
this
exist
for?
B
Why
can't
this
exist
for
all
of
your
other
data
as
well?
The
apis
are
there,
the
exports.
Are
there,
the
legal
framework?
Is
there
thanks
to
things
like
the
gdpr?
What's
missing
is
the
software
that
is
easy
enough
for
regular
people
to
use
that
they
can
start
pulling
that
stuff
in
I'm?
Also,
I'm
I
I'd
love
to
get
to
a
point
with
say
with
dog
sheep,
where
other
people
are
using
it
to
the
point
that
they're
building
extra
extra
sort
of
plugins
and
features
for
it.
B
Like
imagine
if
I
found
the
cool
sql
query
that
figured
out
my
heart
rate
over
time
compared
to
what
I'd
been
reading
on
twitter
and
if
I
could
bundle
that
at
the
sql
query
and
send
it
to
you
and
you
could
run
that
query
against
your
personal
data
as
well.
I
think
I
hinted
that
with
the
the
thing
that
tells
me
what
color
my
eyes
are
by
running
against
my
genome.
I
love
the
idea
of
this
sort
of
home.
B
Spun
here
are
queries
that
you
can
run,
which
will
tell
you
something
interesting,
but
I
don't
need
access
to
your
database,
I'm
just
sharing
the
the
queries
and
the
sort
of
visualization
tools,
and
maybe
little
javascript,
visualization,
plug-ins
and
so
forth.
So
I
think
there's
it
feels
to
me
like
this
could
be
a
massively
exciting,
open
source
ecosystem
of
homebrew
personal
analytics,
like
you
can
find
the
simplest
possible
way
for
people
to
muck
around,
find
something
interesting
and
then
share
it
with
other
people.