SQLAdapter for the One Big Table

[DiamondJoseph]

Following #778 and the additional tightening up of the SQLAdapter in #1047, we believe we can implement Diamond's desires for a single big table by providing an alternative implementation of the SQLAdapter. We would like an adapter that stores all bluesky run data into a single long but not wide table

From Diamond's perspective, we have been thinking about the Big Table differently, which I am writing here for completeness. From the very beginnings of testing, value is a JSONB field, which (from some simple experimentation) seems to work OK

The Big Table

uid	run_uid	stream	field_name	seq_num	value
42	.	.	.	.	.
43	.	.	.	.	.
44	.	.	.	.	.

CREATE TABLE points(uid SERIAL PRIMARY KEY, run_id CHAR(32), axis Char(32), val JSONB);
INSERT INTO points (run_id, axis, val) VALUES('qwert', 'x', '1.5');
INSERT INTO points (run_id, axis, val) VALUES('qwert', 'robot', '"foo"');
INSERT INTO points (run_id, axis, val) VALUES('qwert', 'shutter', 'true');

SELECT * FROM points WHERE val @@ '$ <3';

Originally posted by @DiamondJoseph in #778

[danielballan]

For completeness I'll share and expand on some thoughts here.

I strongly support exploring this and ensuring that Tiled supports adding an Adapter that can work with this schema. (I believe it already should, as is.)

Compared to our "table-per-schema" approach, this is proposal conceptually more straightforward. I see some potential drawbacks: the coarsening of specific numerical types (e.g. uint8) into JSON and the necessity of self-join to make comparisons across fields in a single point. But in spite of that this may well be an option worth maintaining in core, and recommending for some (even most?) use cases.

[DiamondJoseph]

I've been trying out a very bluesky run structured table structure

create_tables.sql.txt
example-docs.json
example_run.sql.txt

[DiamondJoseph]

From internal discussions, figuring out how we want to expose these on the GraphQL API, we think we want to promote object_keys and add an equivalent config_keys, to reconstruct the Device from its fields.

Also still need some way of storing the stream_datum indices <-> sequence number relation

Here is a first attempt at a GraphQL schema for retrieving per-point data and a reference to where to find h5 data:

"""
Can we define an interface and use GraphQL handling for Unions of `data` types?
"""
enum Dtype {
  "string"
  "number"
  "array"
  "boolean"
  "integer"
}


enum ExitStatus {
  "success"
  "failure"
  "aborted"
}

type StrideSlice {
    start: int!
    stop: int!
    stride: int! = 1
}

type NameFilter {
  name: str!
}

type DtypeFilter {
  dtype: Dtype
}

type Scalar {
  name: str
  dtype: Dtype!
  data: JSON!  # float/int etc.
  precision: int
  units: str
  metadata: JSON
}

type Dataset {
  name: str
  dtype: Dtype!
  data(slice: StrideSlice = None, shape: [int!] = []): [JSON!]!  # float/int etc.
  precision: int
  units: str
  metadata: JSON
}

type DatasetReference {
  name: str
  mimetype: str!
  url: URL!
  parameters: JSON!
  indices: [Slice!]!   # mapping from seq_num to indices?
}

type ScalarReference {
  name: str
  mimetype: str!
  url: URL!
  parameters: JSON!
  index: Slice!   # mapping from seq_num to indices?
}

union FieldFilter = NameFilter | DtypeFilter

type Device {
  name: str!
  fields(filters: [FieldFilter] = []): [Dataset | DatasetReference!]!
  configs(filters: [FieldFilter] = []): [Scalar | ScalarReference!]!
}

type Stream {
  name: str!
  devices(filters: [NameFilter] = []): [Device!]!
  metadata: JSON!  # Hints only, promote Hints?
  seq_nums(slice: StrideSlice = None): [int!]!
}

type Run {
  uuid: UUID!
  data_session: str!
  start_time: float!
  end_time: float
  exit_status: ExitStatus
  metadata: JSON!
  streams(filters: [NameFilter] = []): [Stream!]!
}


type Query {
  run(uuid: UUID!): Run!
  # To be federated with other schemas...
}

Then we can in theory query for data from the specific run like this, which would give all fields on device slits-1 that were read in the primary stream, their first point only.

{ 
run("44ca4429-ff8c-4495-8432-058b87c7f24c'")
  start_time
  streams([NameFilter("primary")]) {
    devices([NameFilter("slits_1")]) {
      name
      fields {
        ... on Dataset {
          dtype
          data(StrideSlice(0, 1))
          precision
          units
        }
      }
    }
  }
}

or query for the metadata of a scan then (from the metadata of the Run?) know which fields/devices to actually query for the data we want:

{
  run("44ca4429-ff8c-4495-8432-058b87c7f24c") {
    start_time
    end_time
    data_session
    exit_status
    metadata
    streams {
      name
      metadata
      seq_nums
      devices {
        name
        fields {
          ... on DatasetReference {
            name
            mimetype
            url
            parameters
          }
          ... on Dataset {
            name
            dtype
            precision
            units
          }
        }
        configs {
          ... on ScalarReference {
            name
            mimetype
            url
            parameters
          }
          ... on Scalar {
            name
            dtype
            precision
            units
            metadata
          }
        }
      }
    }
  }
}

{
  run("44ca4429-ff8c-4495-8432-058b87c7f24c") {
    streams {
      name
      devices([NameFilter("slits-1")]) {
        fields([NameFilter("slits-1-x-gap")]) {
          ... on Dataset {
            data
          }
        }
      }
    }
  }
}