Querying the engine

You can check out Splitgraph images into schemata with the same name as the repository that the image belongs to. For example:

$ sgr checkout example/repo_1:latest

will create a schema on the engine with name example/repo_1. This means that any application that can access the engine via a normal database connection can interact with the checked-out repository.

sgr provides a shorthand, sgr sql, to run arbitrary SQL queries against the engine:

$ sgr sql --schema example/repo_1 "SELECT * FROM demo"

However, any other SQL client will do. For example, you can use pgcli to query and write data to the engine. In the default sgr engine add configuration, the connection string is:

pgcli postgresql://sgr:password@localhost:5432/splitgraph

Data Delivery Network

If you're registered on Splitgraph Cloud, you can also run SQL queries against the Splitgraph Data Delivery Network directly without having to operate your own Splitgraph engine. For example:

sgr cloud sql 'SELECT COUNT(1) FROM "splitgraph/socrata:latest".datasets'

See the documentation for sgr cloud sql and the Data Delivery Network for more details, including how to configure other SQL clients to access the DDN.

Example

In these subsections, we'll create a couple of example repositories and some images and then use the Splitfile language to define a reproducible transformation on these datasets.

sgr comes with a few routines to set up repositories with some example data:

$ sgr example generate example/repo_1

Generated example/repo_1:demo with 10 rows, image hash 103cb2da2da0

This creates a single repository, example/repo_1, with a single table in it called demo. The table has two columns, key (an integer) and value (a hexadecimal string).

You can also inspect the currently checked out image in-depth:

$ sgr show example/repo_1:latest

Image example/repo_1:103cb2da2da000f3dce9512e3cc67434d7a3c977c0df259411c73a2687244706

Created at 2020-04-06T10:14:54.184552
Size: 963.00 B
Parent: 0000000000000000000000000000000000000000000000000000000000000000

Tables:
  demo

This image has a parent with hash 00000..., denoting an empty image, and one table, demo.

Let's take a closer look at the new table.

$ sgr table example/repo_1:latest demo

Table example/repo_1:103cb2da2da000f3dce9512e3cc67434d7a3c977c0df259411c73a2687244706/demo

Size: 963.00 B
Rows: 10
Columns:
  key (integer, PK)
  value (character varying)

Objects:
  o26c6d8345cba276f807d7bcf906531568f309c2609a3420d98c01a6c99b166

This table is currently mapped to a single object.