0

I am trying to form a OQL in GemFire which would query a particular attribute within the list.

I have huge number of Employee objects in GemFire with employeeId as the key.

On querying by ID we receive the following object. We convert the object into XML for some use.

<Employee>
         <employeeId>592266</employeeId>
         <employeeExperienceList>
                 <experience>
                             <org>XYZ</org>
                             <toDate>10/1/2010</toDate>
                             <fromDate>2/3/2014</fromDate>
                 </experience>
                  <experience>   
                             <org>ABC</org>
                             <toDate>2/15/2014</toDate>
                             <fromDate>3/17/2018</fromDate>
                 </experience>
         </employeeExperienceList>
</Employee>

Now I want to develop an OQL which retrieves all the Employees who are from the Organization ABC.

How can I add this to the where clause?

John Blum
  • 7,381
  • 1
  • 20
  • 30
Arijit Dasgupta
  • 325
  • 3
  • 14

1 Answers1

1

Perhaps this is best explained with an example; see here. Specifically, this is the OQL query you are looking for.

You can find more on Pivotal GemFire's Query capabilities here.

Hope this helps.

Cheers! John

John Blum
  • 7,381
  • 1
  • 20
  • 30
  • Thank you for the detailed answer. It works as exected. – Arijit Dasgupta Mar 23 '18 at 14:25
  • I do have one more question. I have huge data and it takes a lot of time to get data. What index should be added to the spring context. I have added index on simple attributes. How can we add index for list.attribute as in this case – Arijit Dasgupta Mar 23 '18 at 14:28
  • I just learned that OQL would be created with `queryService.createIndex("indexName", "emp.experiences", "/Employees emp");` where `experiences` would be `class Employee { List experiences; }` However, you should be mindful that indexing on a field that contains a collection will significantly increase the size of the index because a copy of the whole object is added to the index for each element in the collection. – John Blum Mar 23 '18 at 19:22
  • It would probably be better to index a specific field/property on the Experience type, e.g. `org`, so then the OQL Index creation would become `queryService.createIndex("indexName", "exp.org", "/Employees emp, emp.experiences exp");` – John Blum Mar 23 '18 at 19:37
  • You should consult the samples (http://gemfire.docs.pivotal.io/geode/developing/query_index/index_samples.html) and Pivotal GemFire User Guide on "Working with Indexes" (http://gemfire.docs.pivotal.io/geode/developing/query_index/query_index.html) for more detailed information. – John Blum Mar 23 '18 at 19:38
  • Hi John,I have tried to index exp.org the same way you have mentioned, but it does not help.It takes the same time as it used to take without indexing. Points that might help to find, if I am doing something wrong :: 1. I use a gemfire function where I use the query Service. I have add the code to create the index in the function. 2.When I do a list indexes with stats in GFSH,it shows the the org.exp index is available with uses count as 9 and other stats. 3. In the Gemfire Logs I see the info,"Query Executed in 123.3 ms;rows count=62285 index used(1) ExpOrg Index(Result : 62285)"//Same Query" – Arijit Dasgupta Mar 26 '18 at 18:47
  • Hi Arijit - Yes, it is possible an OQL Index won't make a difference or even hurt perf, actually. It also is advisable to create Indexes before executing queries that would require the Index, and not in a Function since that will (possibly) be done each time you invoke the Function (depending on your logic). – John Blum Mar 26 '18 at 19:05
  • Also, if you are using Functions and your data can be partitioned (sharded) in a `PARTITION` Region (http://gemfire.docs.pivotal.io/geode/developing/partitioned_regions/chapter_overview.html), then it would be advisable to query the "local" data set as opposed to the entire contents of the Region (e.g. `/Employees`) in your Function since GemFire's distributed compute using Functions works like the Scatter-Gather pattern (Map-Reduce style). See here (http://gemfire.docs.pivotal.io/geode/developing/querying_basics/querying_partitioned_regions.html) for more details. – John Blum Mar 26 '18 at 19:09
  • You can use the `o.a.g.cache.partition.PartitionRegionHelper` class to get the "local" data set on the node of execution inside your Function. You can even do some pre-filtering on the client-side of the Function invocation to target the Function even further based on data of interests (http://gemfire.docs.pivotal.io/geode/developing/query_additional/partitioned_region_key_or_field_value.html). Remember to keep these things in mind (http://gemfire.docs.pivotal.io/geode/developing/query_additional/partitioned_region_query_restrictions.html). – John Blum Mar 26 '18 at 19:12