Queries currently fail on large datasets

[feczo]

Queries currently fail on large datasets (>6000 entities). [1] We
have many cases where we have well over 30K entities of a given Model that
we'd like the ability to iterate over.

[1] Code snippet & traceback:

>>> query = dataset.query().kind('Account')
>>> print datetime.datetime.utcnow(); all_accts = query.fetch(6000); print
datetime.datetime.utcnow()

2014-10-02 09:27:55.235381
2014-10-02 09:28:54.029017

>>> print datetime.datetime.utcnow(); all_accts = query.fetch(7000); print
datetime.datetime.utcnow()

2014-10-02 09:29:01.298517
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File
"/Users/ehiggins/src/datastore/.env/lib/python2.7/site-packages/gcloud-0.02.2-py2.7.egg/gcloud/datastore/query.py",
line 307, in fetch
query_pb=clone.to_protobuf(), dataset_id=self.dataset().id())
File
"/Users/ehiggins/src/datastore/.env/lib/python2.7/site-packages/gcloud-0.02.2-py2.7.egg/gcloud/datastore/connection.py",
line 207, in run_query
datastore_pb.RunQueryResponse)
File
"/Users/ehiggins/src/datastore/.env/lib/python2.7/site-packages/gcloud-0.02.2-py2.7.egg/gcloud/datastore/connection.py",
line 64, in _rpc
data=request_pb.SerializeToString())
File
"/Users/ehiggins/src/datastore/.env/lib/python2.7/site-packages/gcloud-0.02.2-py2.7.egg/gcloud/datastore/connection.py",
line 58, in _request
raise Exception('Request failed. Error was: %s' % content)
Exception: Request failed. Error was: Internal Error

[erichiggins]

Tagging myself since I submitted this through support.

[dhermes]

@erichiggins Are you referring to cloud support?

@feczo Can you try to make this clear when filing future issues?

Thanks

[erichiggins]

@dhermes Yep, Cloud/Enterprise support. @feczo opened this ticket on my behalf.

[dhermes]

@erichiggins Why do you need to fetch this many at once? Why can't you save the cursor and start a new query?

Also, a raw query with no filters is asking for the whole firehose of the dataset. Are you doing some kind of large-scale transformation?

[tseaver]

FWIW, #204 added the ability to use the cursor on a query.

[erichiggins]

@dhermes I can certainly use cursors in cases where large datasets are required, but there doesn't appear to be any documentation around cursor support for this library, or any documented limitations regarding how many entities is "too many".

Regarding the raw query, there are cases where we need to fetch all entities of a given type when syncing with other systems for a variety of purposes. This is quite intentional and we look forward to this functionality through the Datastore API since it's fairly painful to do with the Remote API or having GAE send the data outward.

[dhermes]

Some thoughts:

• I agree it'd be nice to have best practices on query size and maybe even enforce it in the library. This is API dependent (i.e. lives outside the library). @proppy may have an idea of reasonable limits or know who would.
• Our datastore.query docs don't mention the cursor (as of 10:24pm Pacific on 10/14/2014). I'm not sure how/when they get updated but @silvolu can probably comment. You can make them locally via tox -e docs (from the repository root). Then the latest and greatest will be at $GIT_ROOT/docs/_build/html/datastore-api.html.
• Based on the fact that you are using all_accts = query.fetch(...) it seems you are using an out of date version. Note that now Query.fetch() returns a list of fetched results as well as a cursor, a boolean indicating whether there are more, and so on.
• It seems that a full sync with another system would be more suited for an object store (i.e. Cloud Storage) than for a key-value store like the datastore. Maybe making your case more clear might help us "engineer" around the problem, i.e. accomplish the underlying goals is a different way.
• You could store a last_updated time stamp on objects and query for updates. This way your sync can patch data rather than having to reload data which hasn't changed since your last sync. I'd imagine if you are syncing a lot, the number of reads of full-size entities you'd save would make up for the number of extra writes/storage you'd deal with for a single property.

[proppy]

Cursor should be documented (and be recommend as the idiomatic way to iterate over large result set) and developer should have some control over the batch_size for each RPC.

/cc @pcostell who can comment on the implementation in NDB.

[dhermes]

I agree. If a certain cursor size results in 500s (even if they are flaky), is it worth disallowing it in user land? Or are you suggesting that the ndb approach is to accept any number the user likes but enforce smaller batches and paging with cursors under the covers?

UPDATE: @proppy which docs are you referring to? The official Cloud Datastore API query docs don't mention batching or max fetch size or anything it seems. I suppose any query to any webserver that begins to exceed 60s will cause problems. Is there some part of the documentation regarding max payload size or request time-outs?

[erichiggins]

@dhermes Thanks for the follow-up. Just a few quick responses to some of the points you made:

• "...it seems you are using an out of date version..." -- I raised this as another concern about this library through Cloud Support, actually. This repo/library is not using any sort of versioning that developers typically expect from Python packages, which makes it hard for me to know what version I'm actually using. I'd recommend Semantic Versioning, the uses of Releases and Tags in Git/GitHub, and keeping the version listed in your setup.py up to date. I'm not sure how things have changed at the Googs, but if you're required to update things in Github based on what you had to check into Perforce then this may be more difficult to manage.
• "It seems that a full sync with another system would be more suited for an object store..." -- I can elaborate on our use-case and why this isn't an option for us, but some background might also help. Our production web application (optimizely.com) has been running for over 5 years on GAE, which predates Cloud Storage and is the primary source of truth for our data. The Google-managed backup service (Datastore -> Storage) is still in trusted-tester mode, so this is a recently-available option. Previous iterations that were developer-managed ate up all of our quotas. Our use-case involves at least three separate systems, which either need to make small, ad-hoc queries (known entity key) or full entity-type queries to do larger aggregations or transforms (machine learning, statistical analysis, in-house availability outside of the production application) and they typically require the data to be fresh.
• "You could store a last_updated time stamp on objects and query for updates. ": Agreed. In most cases we have such a property, but this solution really only improves subsequent queries. The first query still needs to fetch all entities.

Thanks again for the quick support on this. Looking forward to the improvements!

[silvolu]

@erichiggins first of all, thanks for using the library and providing us with extremely useful feedback!
Most of the problems that emerge from this issue are caused by the fact that this library is still under construction and we restarted working on it only recently.
Our main concern so far has been code hygiene (style, missing docs, address TODOs...), and we're almost done there, so you'll see us start fixing more of your concerns in the upcoming days.
Let me start by prioritizing work on making sure that the docs are always up to date: I'll work on the build process right now.

[erichiggins]

@silvolu Awesome. Thank you!

[pcostell]

Right now Cloud Datastore does have the limitation that RPCs will fail if they exceed 60 seconds. For now we recommend issuing queries for smaller batches and using cursors to get all the result. Eventually if an RPC is taking too long Cloud Datastore should respond with whatever progress is made with a cursor to continue the work.

[dhermes]

Ok great.

@pcostell

• The message content from @erichiggins logs indicates a 500 of some sort (i.e. Internal Error), however, the exception doesn't list the status code, so maybe not. Would a 408 Timed Out not be a more appropriate response? Or is this just an artifact of the RPC/HTTP imperfect mapping?

@erichiggins

• It seems we have a conclusive theory of why fetching 7000 fails and 6000 doesn't, because the backend query exceeds 60 seconds.

• To add on what @silvolu said, we are testing comprehensively and you will be plenty safe checking out from head:

  pip install git+git://github.com/GoogleCloudPlatform/gcloud-python.git@master

  I know this doesn't quite work in a production system, but as far as new feature discovery it can be quite useful.

• What sort of versioning do you expect / are you referring to? I apologize for being unclear, we certainly don't have the version in PyPI up to date with HEAD in development.

• Thanks for using the library and kicking our tires! :)

[erichiggins]

@dhermes Thanks for the updates!

Regarding versioning, there are a couple of pieces that coincide. In both a development and production environment we rely on a specific package version for tools like pip and virtualenv. It's possible to use a commit hash to lock to down to a point in time in order to ensure consistency, but then there becomes a problem of knowing when/if to "upgrade". That's why I also asked for a Changelog in this repo (#243) which, in addition to versioning, makes it clear to developers what has changed.

If you started using Releases on GitHub, then you can effectively get a Changelog without adding a specific file that also needs to be maintained. I usually cite the Python Requests library as a good example to follow.

[silvolu]

@erichiggins for versioning the plan is effectively to use releases on GitHub. Stay tuned :)

[dhermes]

Can we close this out? We are inches from our first tag / release and it would be more appropriate to open an issue about documenting limits of the API and potentially putting a cap on query fetch size under the hood and stringing queries together if the number of entities requested is too large.

@pcostell do you think 1000 entities is a reasonable cap? Do you think it even makes sense for a library to have such a cap?

[pcostell]

The servlet has actually been updated to handle timing issues. So if you do hit a time-based or size-based limit it should return partial results. In this case, the server should return query results with more_results set.

In general I think it makes sense for client libraries not to impose caps. The server should have proper handling for these cases. That way when the server changes what it can handle, things will continue to work for users.

[dhermes]

@pcostell I was under the impression from googleapis/google-cloud-node#5 that more_results was always set to MORE_RESULTS_AFTER_LIMIT.

Is this no longer the case?

Provided it is implemented, this is super exciting!

[erichiggins]

Whatever the limit and type (time-based or number of entities), please ensure that it's well-documented so developers don't end up debugging. Looking forward to the release!

[dhermes]

@erichiggins We hope to cut a release soon. LMK if you are still having issues and I'm happy to re-open.