App Engine

What Are Projection Queries?

A common situation... you have some large entities, but sometimes you only need to retrieve a few of their fields.

With projection queries, you can query the Datastore for specific properties of an entity

Get only the properties you need

Projection queries are similar to SQL queries of the form:
SELECT name, email, phone, FROM CUSTOMER
instead of:
SELECT * FROM CUSTOMER

Why would you want to use Projection Queries?

Efficient, lower latency
Cheaper to read: 1 small operation per each entity returned.
However: they can require additional indexes (which could mean higher write costs)

Examples of Projection Queries (Python)

Using GQL:

from google.appengine.ext import db
proj = db.GqlQuery("SELECT content, author FROM Greeting")

Using Query:

from google.appengine.ext import db
proj = db.Query(Greeting, projection=('content', 'author'))

You handle the results of these queries like a standard query.

Examples of Projection Queries (Python)

Using NDB:

from google.appengine.ext import ndb
greetings = Greeting.query().order(-Greeting.date).fetch(
         20, projection=[Greeting.content, Greeting.author])

Examples of Projection Queries (Java)

import com.google.appengine.api.datastore.PropertyProjection;
import com.google.appengine.api.datastore.RawValue;

Query proj = new Query("Greeting",guestbookKey);
    proj.addProjection(new PropertyProjection("user", User.class));
     proj.addProjection(new PropertyProjection("date", Date.class));

Some Limitations On What Properties Can Be Projected

You can only project indexed properties.
You cannot project a property that also is referenced in an equality filter. For example:

SELECT A FROM kind WHERE A = 1
is not supported, but

SELECT A FROM kind WHERE B = 1 or
SELECT A FROM kind WHERE A > 1
is supported.
You cannot project the same property more than once.
The entities returned by a projection query are only partially populated, so they cannot be saved back to the Datastore.

Projections and Indexes

An index is required for all properties specified in the projection.
So, projection queries can sometimes require new composite indexes
For example,
SELECT A, B, C FROM Kind
requires
Index(Kind, A, B, C)
(So, even though reads are cheaper, the additional indexes might increase your write costs)

Will projections require new indexes?

You can look at index.yaml or the Index Viewer panel in the admin console: what indexes are your existing queries using?
You will need additional indexes if the properties in the projection are not already required for a composite index.
SELECT * FROM Kind WHERE A > 1 ORDER BY A, B
requires the index:
Index(Kind, A, B)
Since A and B are already in the existing query, projecting them does not change the indexes needed:
SELECT A, B FROM Kind WHERE A > 1 ORDER BY A, B
(Any required new indexes will be autogenerated to index.yaml when you run using the dev app server.)

Generating and deploying the new indexes

Run locally to automatically generate new index.yaml entries
Redeploy, then give the new indexes time to build after deployment.

Reducing the number of indexes you need

Be consistent about which properties are projected, even when not all the properties are needed. This will minimize the number of indexes.
For example, these queries require two different indexes:

SELECT A, B FROM Kind → Index(Kind, A, B)
SELECT A, B, C FROM Kind → Index(Kind, A, B, C)
If you always project A, B and C (even when C is not required), only one index will be needed

An example (using NDB)

Suppose we have a model like this, and a standard non-projection query...

from google.appengine.ext import ndb

class Greeting(ndb.Model):
  """Models an individual Guestbook entry."""
  content = ndb.StringProperty()
  date = ndb.DateTimeProperty(auto_now_add=True)
  author = ndb.UserProperty()
  uprop = ndb.StringProperty()
  uprop2 = ndb.StringProperty()
  ...
  uprop9 = ndb.StringProperty()

  @classmethod
  def query_book(cls, ancestor_key):
    return cls.query(ancestor=ancestor_key).order(-cls.date)
...
greetings = Greeting.query_book(ancestor_key).fetch(50)

An example (using NDB), cont.

The autogenerated index in app.yaml will look like this:

- kind: Greeting
  ancestor: yes
  properties:
  - name: date
    direction: desc

An example (using NDB), cont.

But, we don't want to fetch all these properties! A projection query version:

class Greeting(ndb.Model):
  """Models an individual Guestbook entry."""
  content = ndb.StringProperty()
  date = ndb.DateTimeProperty(auto_now_add=True)
  author = ndb.UserProperty()
  uprop = ndb.StringProperty()
  uprop2 = ndb.StringProperty()
  ...
  uprop9 = ndb.StringProperty()

  @classmethod
  def query_book(cls, ancestor_key):
    return cls.query(ancestor=ancestor_key).order(-cls.date)

...
greetings = Greeting.query_book(ancestor_key).fetch(
    50, projection=[Greeting.content, Greeting.author])

An example (using NDB), cont.

The autogenerated index in app.yaml for the projection query:

- kind: Greeting
  ancestor: yes
  properties:
  - name: date
    direction: desc
  - name: author
  - name: content

An example (using NDB), cont.

What if we project on date, too?

...        
greetings = Greeting.query_book(ancestor_key).fetch(
    50, projection=[Greeting.content, Greeting.author, Greeting.date])

No change to the index:

- kind: Greeting
  ancestor: yes
  properties:
  - name: date
    direction: desc
  - name: author
  - name: content

Projection Queries and Multivalued Properties

Projecting a multi-valued property will not result in all values for that property being populated.
A separate entity will be returned for each unique combination of projected values that match the query.

<Thank You!>

JDO transaction retry example

PersistenceManager pm = PMF.get().getPersistenceManager();
  // ...
  try {
    for (int i =0; i < NUM_RETRIES; i++) {
      pm.currentTransaction().begin();
      // ...do the transaction work ...
      try {
        pm.currentTransaction().commit();
        break;
      }
      catch (JDOCanRetryException e1) {
        if (i == (NUM_RETRIES - 1)) {
          throw e1;
        }
      }
    }
  }
  finally {
    if (pm.currentTransaction().isActive()) {
      pm.currentTransaction().rollback();
    }
    pm.close();
  }