Composite index required does not exist, yet defined in index.yaml

2
votes

I have some IoT devices that are sending some data into a Google Cloud Datastore.

The Datastore is setup as Cloud Firestore in Datastore mode.

Each row has the following fields:

  • Name/ID
  • current_temperature
  • data
  • device_id
  • event
  • gc_pub_sub_id
  • published_at
  • target_temperature

And these are all under the ParticleEvent kind.

I wish to run the following query; select current_temperature, target_temperature from ParticleEvent where device_id = ‘abc123’ order by published_at desc.

I get the below error when I try to run that query:

GQL query error: Your Datastore does not have the composite index (developer-supplied) required for this query.

So I setup an index.yaml file with the following contents:

indexes:

- kind: ParticleEvent
  properties:
  - name: data
  - name: device_id
  - name: published_at
    direction: desc

- kind: ParticleEvent
  properties:
  - name: current_temperature
  - name: target_temperature
  - name: device_id
  - name: published_at
    direction: desc

I used the gcloud tool to send this successfully up to the datastore and I can see both indexes in the indexes tab.

However I still get the above error when I try to run the query.

What do I need to add/change to my indexes to get this query to work?

1
google-cloud-platformgoogle-cloud-datastore
the error message does not suggest the correct index you should build? - oziomajnr
@Ogbe Nope. It just includes a link to a help page on creating indexes, which is what I used to create the above. - James
Datastore is weird when you want to choose specific columns, try simply select * from ParticleEvent order by published_at desc, and make an index only for published_at with desc. - MT-FreeHK
@MatrixTai I can run that kind of query without a composite index at all. I was able to run that query as soon as my datastore had rows in it. I had missed a where condition in my query which I have added now. I want to be able to select specific columns with a where condition and order by. - James
Still the same idea, never select specific columns. Just index published_at and device_id this case. I understand that's sound non-sense, but that's way how to solve it. - MT-FreeHK

1 Answers

2
votes

Though in the comment I simply suggest select * (that's the best way, I do think)

There is a way make your query work.

- kind: ParticleEvent
  properties:
  - name: device_id
  - name: published_at
    direction: desc
  - name: current_temperature
  - name: target_temperature

The reason why is select is done at the end and thus you need the index of current_temperature and target_temperature in a lower level.

Why I don't suggest this way is because, when your data grows and you need more combination of indexing just because of select specific columns. Your index size will grow exponentially.

But let's say if you sure you will just use this once and always query the data like this, then feel free to indexing it.

Or, if the connection bandwidth between your computer and google cloud is very small such that downloading more data causes you lag.