Examples with SqlFieldsQuery org.apache.ignite.cache.query.SqlFieldsQuery used on opensource projects

Example 41 with SqlFieldsQuery

use of org.apache.ignite.cache.query.SqlFieldsQuery in project ignite by apache.

the class IgniteSqlSplitterSelfTest method testEmptyCacheAggregates.

/**
 * Check results of aggregate functions if no rows are selected.
 *
 * @throws Exception If failed,
 */
public void testEmptyCacheAggregates() throws Exception {
    final String cacheName = "ints";
    IgniteCache<Integer, Value> cache = ignite(0).getOrCreateCache(cacheConfig(cacheName, true, Integer.class, Value.class));
    try (QueryCursor<List<?>> qry = cache.query(new SqlFieldsQuery("SELECT count(fst), sum(snd), avg(snd), min(snd), max(snd) FROM Value"))) {
        List<List<?>> result = qry.getAll();
        assertEquals(1, result.size());
        List<?> row = result.get(0);
        assertEquals("count", 0L, ((Number) row.get(0)).longValue());
        assertEquals("sum", null, row.get(1));
        assertEquals("avg", null, row.get(2));
        assertEquals("min", null, row.get(3));
        assertEquals("max", null, row.get(4));
    } finally {
        cache.destroy();
    }
}

Example 42 with SqlFieldsQuery

use of org.apache.ignite.cache.query.SqlFieldsQuery in project ignite by apache.

the class IgniteSqlSplitterSelfTest method testSubQueryWithAggregate.

/**
 */
public void testSubQueryWithAggregate() {
    CacheConfiguration ccfg1 = cacheConfig("pers", true, AffinityKey.class, Person2.class);
    IgniteCache<AffinityKey<Integer>, Person2> c1 = ignite(0).getOrCreateCache(ccfg1);
    try {
        int orgId = 100500;
        c1.put(new AffinityKey<>(1, orgId), new Person2(orgId, "Vasya"));
        c1.put(new AffinityKey<>(2, orgId), new Person2(orgId, "Another Vasya"));
        List<List<?>> rs = c1.query(new SqlFieldsQuery("select name, " + "select count(1) from Person2 q where q.orgId = p.orgId " + "from Person2 p order by name desc")).getAll();
        assertEquals(2, rs.size());
        assertEquals("Vasya", rs.get(0).get(0));
        assertEquals(2L, rs.get(0).get(1));
        assertEquals("Another Vasya", rs.get(1).get(0));
        assertEquals(2L, rs.get(1).get(1));
    } finally {
        c1.destroy();
    }
}

Example 43 with SqlFieldsQuery

use of org.apache.ignite.cache.query.SqlFieldsQuery in project ignite by apache.

the class IgniteSqlSplitterSelfTest method doTestDistributedJoins.

/**
 * @param c1 Persons cache.
 * @param c2 Organizations cache.
 * @param orgs Number of organizations.
 * @param persons Number of persons.
 * @param pageSize Page size.
 * @param enforceJoinOrder Enforce join order.
 */
private void doTestDistributedJoins(IgniteCache<?, ?> qryCache, IgniteCache<Integer, Person2> c1, IgniteCache<Integer, Organization> c2, int orgs, int persons, int pageSize, boolean enforceJoinOrder) {
    assertEquals(0, c1.size(CachePeekMode.ALL));
    assertEquals(0, c2.size(CachePeekMode.ALL));
    int key = 0;
    for (int i = 0; i < orgs; i++) {
        Organization o = new Organization();
        o.name = "Org" + i;
        c2.put(key++, o);
    }
    Random rnd = new GridRandom();
    for (int i = 0; i < persons; i++) {
        Person2 p = new Person2();
        p.name = "Person" + i;
        p.orgId = rnd.nextInt(orgs);
        c1.put(key++, p);
    }
    String select = "select count(*) from \"org\".Organization o, \"pers\".Person2 p where p.orgId = o._key";
    String plan = (String) qryCache.query(new SqlFieldsQuery("explain " + select).setDistributedJoins(true).setEnforceJoinOrder(enforceJoinOrder).setPageSize(pageSize)).getAll().get(0).get(0);
    X.println("Plan : " + plan);
    if (enforceJoinOrder)
        assertTrue(plan, plan.contains("batched:broadcast"));
    else
        assertTrue(plan, plan.contains("batched:unicast"));
    assertEquals((long) persons, qryCache.query(new SqlFieldsQuery(select).setDistributedJoins(true).setEnforceJoinOrder(enforceJoinOrder).setPageSize(pageSize)).getAll().get(0).get(0));
    c1.clear();
    c2.clear();
    assertEquals(0, c1.size(CachePeekMode.ALL));
    assertEquals(0L, c1.query(new SqlFieldsQuery(select).setDistributedJoins(true).setEnforceJoinOrder(enforceJoinOrder).setPageSize(pageSize)).getAll().get(0).get(0));
}

Example 44 with SqlFieldsQuery

use of org.apache.ignite.cache.query.SqlFieldsQuery in project ignite by apache.

the class IgniteSqlSplitterSelfTest method checkSimpleQueryWithAggr.

/**
 * Simple query with aggregates
 */
private void checkSimpleQueryWithAggr(IgniteCache<Integer, Value> cache) {
    try (QueryCursor<List<?>> qry = cache.query(new SqlFieldsQuery("SELECT count(fst), sum(snd), avg(snd), min(snd), max(snd) FROM Value"))) {
        List<List<?>> result = qry.getAll();
        assertEquals(1, result.size());
        List<?> row = result.get(0);
        assertEquals("count", 15L, ((Number) row.get(0)).longValue());
        assertEquals("sum", 30L, ((Number) row.get(1)).longValue());
        assertEquals("avg", 2, ((Integer) row.get(2)).intValue());
        assertEquals("min", 1, ((Integer) row.get(3)).intValue());
        assertEquals("max", 3, ((Integer) row.get(4)).intValue());
    }
}

Example 45 with SqlFieldsQuery

use of org.apache.ignite.cache.query.SqlFieldsQuery in project ignite by apache.

the class IgniteSqlSplitterSelfTest method checkQueryFails.

/**
 * @param cache Cache.
 * @param sql SQL.
 * @param enforceJoinOrder Enforce join order flag.
 */
private void checkQueryFails(final IgniteCache<Object, Object> cache, String sql, boolean enforceJoinOrder) {
    final SqlFieldsQuery qry = new SqlFieldsQuery(sql);
    qry.setDistributedJoins(true);
    qry.setEnforceJoinOrder(enforceJoinOrder);
    GridTestUtils.assertThrows(log, new Callable<Void>() {

        @Override
        public Void call() throws Exception {
            cache.query(qry);
            return null;
        }
    }, CacheException.class, null);
}