Five minute demo

Introduction

This demo will show you how to use Splitgraph to:

  • query a large remote public data image (dataset) with any of your favorite tools, without having to download it completely
  • write a Splitfile that can build a derivative image from it
  • publish your derivative image on Splitgraph Cloud.

Prerequisites

You need to register on Splitgraph Cloud to pull and publish data. If you followed the installation instructions, you already did this. You can also use sgr cloud register or sgr cloud login to register or log in from the sgr client.

If you do not wish to register on Splitgraph Cloud, you can still use Splitgraph in a decentralized fashion, sharing data with other Splitgraph instances. See the decentralized demo for an introduction that showcases importing data from CSV files and tracking changes to images, using Splitgraph's version control functionality.

First, make sure that your Splitgraph engine is initialized and test your connection to Splitgraph Cloud. This demo is written from the point of view of the splitgraph-demo user, so replace splitgraph-demo with your own username in command invocations.

$ sgr init
Initializing engine PostgresEngine LOCAL (sgr@localhost:5432/splitgraph)...
Waiting for connection......
Database splitgraph already exists, skipping
Ensuring the metadata schema at splitgraph_meta exists...
Running splitgraph_meta--0.0.1.sql
Running splitgraph_meta--0.0.1--0.0.2.sql
Running splitgraph_meta--0.0.2--0.0.3.sql
Installing Splitgraph API functions...
Installing CStore management functions...
Installing the audit trigger...
Engine PostgresEngine LOCAL (sgr@localhost:5432/splitgraph) initialized.


$ sgr sql -r data.splitgraph.com "SELECT splitgraph_api.get_current_username()"
splitgraph-demo

Pulling a data image

The 2016 US Presidential Election precinct-level returns dataset (source) has 2 million rows, takes up 500MB as a CSV file and only 25MB in Splitgraph. It's a perfect example to showcase Splitgraph's features.

Let's inspect this image remotely without cloning it, using sgr show. The sgr client uses the same protocol to communicate with the registry as with the local Splitgraph engine, so you can use the same commands for both local and remote operations.

$ sgr show -r data.splitgraph.com splitgraph/2016_election:latest
Image splitgraph/2016_election:3835145ada3f07cad99087d1b1071122d58c48783cbfe4694c101d35651fba90

Created at 2019-10-10T15:51:41.122370
Size: 26.81 MiB
No parent (root image)

Tables:
  precinct_results

Let's also inspect the precinct_results table in this image. Splitgraph stores tables as a composition of multiple content-addressable, immutable objects.

$ sgr table -r data.splitgraph.com splitgraph/2016_election:latest precinct_results
Table splitgraph/2016_election:3835145ada3f07cad99087d1b1071122d58c48783cbfe4694c101d35651fba90/precinct_results

Size: 26.81 MiB
Rows: 1989234
Columns:
  year (integer)
  stage (character varying)
  special (boolean)
  state (character varying)
  state_postal (character varying)
  state_fips (bigint)
  state_icpsr (bigint)
  county_name (character varying)
  county_fips (bigint)
  county_ansi (bigint)
  county_lat (numeric)
  county_long (numeric)
  jurisdiction (character varying)
  precinct (character varying)
  candidate (character varying)
  candidate_normalized (character varying)
  office (character varying)
  district (character varying)
  writein (boolean)
  party (character varying)
  mode (character varying)
  votes (bigint)
  candidate_opensecrets (character varying)
  candidate_wikidata (character varying)
  candidate_party (character varying)
  candidate_last (character varying)
  candidate_first (character varying)
  candidate_middle (character varying)
  candidate_full (character varying)
  candidate_suffix (character varying)
  candidate_nickname (character varying)
  candidate_fec (character varying)
  candidate_fec_name (character varying)
  candidate_google (character varying)
  candidate_govtrack (character varying)
  candidate_icpsr (character varying)
  candidate_maplight (character varying)
  id (integer, PK)

Objects:
  o960395135f32c2da7b4b9371da06b0a9d00925472df2ec1ef687acf6f24894
  o07f95f9944d673d8cc05a5b5174253adc513d377ccd811928ae0fe6923e451
  o40e4e46594064e402ccdb914ffb8c9f59267a67b33420cf704073729a6671b
  o1ccf32547f73be8047c3de06760b32c1077c3bd0b0d8309575ebe9ab8eb86c
  oaf3cca3210e1903f06872d0041097dd284423958352d5cd7b5209eceec6cbf
  odc1546c25cac950eca4b551642305d548172b554643f046a16cd9da236c895
  oe60d81ef239980ceb5869fd787d159f227b59b66ab6ceef18aed45cd62ae8f
  o9e77e782f8bda76be6a3f4d29b82661e32d614f7d63230619e8635b9b2b512
  of06f6cfb7bd95047bb49bcd3ab6b98da63c2605aa9659c064cee3bd708b0e4
  o0671aabeb9d4ce362515624616a5a202503db99d7a83007bfdf63fd71b91af
  ...

Now let's actually clone the image. By default, this will only download the image's metadata. Splitgraph will not transfer any actual data until it's required, unless you explicitly ask for it.

$ sgr clone splitgraph/2016_election
Gathering remote metadata...
Fetched metadata for 1 image, 1 table, 20 objects and 1 tag.

Querying the image

To interact with this image, you could check it out, which turns the image into a schema with PostgreSQL tables.

However, imagine if this dataset were a few hundred gigabytes large. Should you really need to download all of it to query a few rows?

Splitgraph objects contain metadata that can help it to discard objects that are irrelevant to a query. The US election dataset is stored ordered by county FIPS code and so queries that filter on this column only need to download a couple of objects instead of the whole dataset.

Let's check out the image into a so called "layered querying" schema. This sets up a Postgres foreign data wrapper for this image that acts as a shim and presents itself as normal Postgres table to clients.

$ sgr checkout --layered splitgraph/2016_election:latest
Checked out splitgraph/2016_election:3835145ada3f.

We're interested in precinct-level election results for District of Columbia (FIPS code 11001).

$ sgr sql -s splitgraph/2016_election "EXPLAIN SELECT \
  candidate_normalized, SUM(votes) FROM precinct_results \
  WHERE county_fips=11001 GROUP BY candidate_normalized"
GroupAggregate  (cost=5698135.18..5699554.45 rows=1 width=64)
Group Key: candidate_normalized
->  Sort  (cost=5698135.18..5698608.27 rows=189234 width=30)
Sort Key: candidate_normalized
->  Foreign Scan on precinct_results  (cost=20.00..5677020.00 rows=189234 width=30)
Filter: (county_fips = 11001)
Multicorn: Objects removed by filter: 18
Multicorn: Scan through 2 object(s) (2.56 MiB)
JIT:
Functions: 7
...

Here, we can see that this query will only download two objects. Let's actually run it: behind the scenes, this will lazily download required data.

$ sgr sql -s splitgraph/2016_election "SELECT \
  candidate_normalized, SUM(votes) FROM precinct_results \
  WHERE county_fips=11001 GROUP BY candidate_normalized"
clinton  282830
in         6551
johnson    4906
stein      4258
trump     12723

sgr sql is a shorthand to run an SQL query against the current engine. However, any PostgreSQL client will work with Splitgraph, allowing you to work with Splitgraph using your favorite tools, including DataGrip, pgAdmin or other clients like pgcli or DBeaver.

You can use sgr config -n to print out a libpq connection string to the current engine.

$ sgr config -n
postgresql://sgr:supersecure@localhost:5432/splitgraph

Using Splitfiles to build derivative images

Splitfiles are similar to Dockerfiles and are the easiest way to build Splitgraph images. They offer Dockerfile-like caching, provenance tracking, fast rebuilds, joins between datasets and full SQL support.

Let's create a Splitfile that summarizes the vote counts for every candidate in every state.

$ cat <<EOF > votes_by_state.splitfile
FROM splitgraph/2016_election IMPORT {
  SELECT candidate_normalized, state_postal,
    SUM(votes) AS total_votes
  FROM precinct_results
  GROUP BY candidate_normalized,
    state_postal
} AS votes_by_state
EOF

Build this image. Even though we have it pulled locally, this isn't necessary to build it: Splitgraph downloads required image fragments automatically.

$ sgr build votes_by_state.splitfile
Executing Splitfile votes_by_state.splitfile with arguments {}

Step 1/1 : FROM splitgraph/2016_election IMPORT {  SELECT candidate...
Resolving repository splitgraph/2016_election
Gathering remote metadata...
No image/object metadata to pull.
Importing 1 table from splitgraph/2016_election:3835145ada3f into votes_by_state
Processing table sg_tmp_32eba453c2f1dd1a13ca58ae3b901a47
 ---> ee8dc4bb7c47
Successfully built votes_by_state:ee8dc4bb7c47.

Take a look at the new image and run a query against it.

$ sgr table votes_by_state:latest votes_by_state
Table votes_by_state:ee8dc4bb7c4766cc30593b77e0b6b3dbb2863c06a4ed2274127d558901abeb2f/votes_by_state

Size: 6.38 KiB
Rows: 793
Columns:
  candidate_normalized (character varying)
  state_postal (character varying)
  total_votes (numeric)

Objects:
  o91e4939e520a19c02ca876c7c5248dcf96f9c3fa55552c6ed8359caea842e9


$ sgr sql -s votes_by_state "SELECT * FROM votes_by_state LIMIT 10"
FL  72993
         IN    603
         MD  22131
         ND   6397
         PA  39004
         RI   9439
         WY   6904
above    NY      1
abraham  NY      0
adams    MD     44

Splitgraph caches the outputs of Splitfiles, in the same way that Docker caches Dockerfile layers. It does not rebuild the image if the source data has not changed.

$ sgr build votes_by_state.splitfile
Executing Splitfile votes_by_state.splitfile with arguments {}

Step 1/1 : FROM splitgraph/2016_election IMPORT {  SELECT candidate...
Resolving repository splitgraph/2016_election
Gathering remote metadata...
No image/object metadata to pull.
 ---> Using cache
 ---> ee8dc4bb7c47
Successfully built votes_by_state:ee8dc4bb7c47.

Splitfiles also support joins between Splitgraph images. For an example, or to learn more about Splitfiles, check out the Splitfile reference or some sample Splitfiles.

Publishing a dataset on Splitgraph Cloud

Let's push your image to the Splitgraph registry, making it available publicly. By default, sgr pushes the image to a repository with the same name in your own namespace. In the case of this demo user, that means sgr pushes the freshly built dataset to splitgraph-demo/votes_by_state.

$ sgr push votes_by_state
Pushing votes_by_state to splitgraph-demo/votes_by_state on remote data.splitgraph.com
Gathering remote metadata...
No objects to upload.
Uploaded metadata for 2 images, 1 table, 0 objects and 0 tags.
Setting upstream for votes_by_state to splitgraph-demo/votes_by_state.


$ sgr show -r data.splitgraph.com splitgraph-demo/votes_by_state:latest
Image splitgraph-demo/votes_by_state:ee8dc4bb7c4766cc30593b77e0b6b3dbb2863c06a4ed2274127d558901abeb2f
Importing 1 table from splitgraph/2016_election
Created at 2020-05-22T18:07:44.828748
Size: 6.38 KiB
Parent: 0000000000000000000000000000000000000000000000000000000000000000

Tables:
  votes_by_state

Splitgraph lets you upload a README and a description for your dataset directly from the CLI. Create a README and a metadata file.

$ cat <<EOF > dataset-readme.md
## votes_by_state

This dataset summarizes votes by state in the 2016 US Presidential Election.

This is also my first dataset (woohoo!)
EOF


$ cat <<EOF > splitgraph.yml
readme: dataset-readme.md
description: US 2016 Election votes summary by state.
EOF

Upload the metadata to Splitgraph Cloud (replace splitgraph-demo with your username).

$ sgr cloud metadata splitgraph-demo/votes_by_state
README updated for repository splitgraph-demo/votes_by_state.
Description updated for repository splitgraph-demo/votes_by_state.

The dataset is now available on https://www.splitgraph.com/YOUR_USERNAME/votes_by_state.

Splitgraph also uploads the image's provenance to Splitgraph Cloud, allowing anyone to see how the image was built and sourced. In addition, the 2016 Election dataset dependents page will now list the new image as a dependent.

You also have an OpenAPI-compatible REST API generated for the dataset, provided by PostgREST. More perks like this coming soon.

Further reading