Writable DDN

Another way to modify and re-structure your data is writing directly through DDN, by employing the standard PostgreSQL DML (INSERT/UPDATE/DELETE) and DDL (CREATE/ALTER/DROP) statements on your repositories.

How it works

By performing a write against the DDN, you essentially create a new latest image containing the desired changes, on top of the existing one directly, without needing to clone/checkout/change/push image locally via sgr.

Besides modifying the table contents (DML), and altering table definitions (DDL), this also lets you create new (private by default) repositories, simply by running CREATE TABLE commands:

CREATE TABLE "splitgraph/my_new_repo".my_new_table
    (LIKE "splitgraph/london_wards".city_merged_2018)

Note that some DML statements, namely the above CREATE TABLE ... LIKE and any ALTER TABLE commands, require full materialisation of the target table inside the DDN in order to be executed correctly. Consequently, they don't reap the performance benefits of the layered querying mode used by default in DDN, and can be slower for really large tables.

There are a couple of minor caveats worth noting:

  • Writes are only applicable to the latest image. You can reference the repo/image using any one out of the three equivalent approaches.
  • Only a single target repo/image per query is supported. In practice this means not chaining multiple write commands delimited by semicolon. You also can't reference more than one table in a DROP TABLE statement.
  • The DDN creates one new image per write statement. You can't batch changes.
  • The target repository must not be an external one (i.e. not a proxy to a remote live data source).

Table of contents