Simulate a table with multiple keys

Question

My project has a need for storage such that Data is formed like tuples of <A,B,C>. <A,B> and <A,C> are both valid primary keys. Users can tell my project <A,B,_> and get C, or can tell my project <A,_,C> and get B. I'm struggling to make this work in Cloud Spanner. Right now I have a table like:

CREATE TABLE Example {
  A uint64 NOT NULL,
  B uint64 NOT NULL,
  C string(MAX) NOT NULL,
} PRIMARY KEY (A, B)

which makes it very easy to retrieve C given A and B (without loss of generality), but I can't find a way with the Read API to retrieve B given A and C. Moreover, I expect there to be a large number of rows, but very few values for A, so simply retrieving all rows prefixed by A and filtering on the client will be prohibitively expensive. Another approach I can think of is to have two redundant tables of <A,B,C>, one keyed on <A,B> and one keyed on <A,C>, and only update them together in transactions, but this doubles my writes and sounds like it will cause concurrency troubles down the road. Is there some other way to get the behavior I want?

Answer 1

You want a Secondary Index:

In your case, you could create an index like: CREATE INDEX ExampleByAC ON Example(A, C)