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

Example 96 with SqlFieldsQuery

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

the class IgniteSqlSplitterSelfTest method testDistributedJoinsUnion.

/**
 * @throws Exception If failed.
 */
@Test
public void testDistributedJoinsUnion() throws Exception {
    CacheConfiguration ccfg1 = cacheConfig("pers", true, Integer.class, Person2.class);
    CacheConfiguration ccfg2 = cacheConfig("org", true, Integer.class, Organization.class);
    IgniteCache<Integer, Object> c1 = ignite(0).getOrCreateCache(ccfg1);
    IgniteCache<Integer, Object> c2 = ignite(0).getOrCreateCache(ccfg2);
    try {
        c2.put(1, new Organization("o1"));
        c2.put(2, new Organization("o2"));
        c1.put(3, new Person2(1, "p1"));
        c1.put(4, new Person2(2, "p2"));
        c1.put(5, new Person2(3, "p3"));
        String select = "select o.name n1, p.name n2 from Person2 p, \"org\".Organization o" + " where p.orgId = o._key and o._key=1" + " union select o.name n1, p.name n2 from Person2 p, \"org\".Organization o" + " where p.orgId = o._key and o._key=2";
        String plan = c1.query(new SqlFieldsQuery("explain " + select).setDistributedJoins(true).setEnforceJoinOrder(true)).getAll().toString();
        X.println("Plan : " + plan);
        assertEquals(2, StringUtils.countOccurrencesOf(plan, "batched"));
        assertEquals(2, StringUtils.countOccurrencesOf(plan, "batched:unicast"));
        assertEquals(2, c1.query(new SqlFieldsQuery(select).setDistributedJoins(true).setEnforceJoinOrder(false)).getAll().size());
        select = "select * from (" + select + ")";
        plan = c1.query(new SqlFieldsQuery("explain " + select).setDistributedJoins(true).setEnforceJoinOrder(true)).getAll().toString();
        X.println("Plan : " + plan);
        assertEquals(2, StringUtils.countOccurrencesOf(plan, "batched"));
        assertEquals(2, StringUtils.countOccurrencesOf(plan, "batched:unicast"));
        assertEquals(2, c1.query(new SqlFieldsQuery(select).setDistributedJoins(true).setEnforceJoinOrder(false)).getAll().size());
    } finally {
        c1.destroy();
        c2.destroy();
    }
}

Example 97 with SqlFieldsQuery

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

the class IgniteSqlSplitterSelfTest method testIndexSegmentationPartitionedReplicated.

/**
 * @throws Exception If failed.
 */
@Test
public void testIndexSegmentationPartitionedReplicated() throws Exception {
    CacheConfiguration ccfg1 = cacheConfig("pers", true, Integer.class, Person2.class).setQueryParallelism(4);
    CacheConfiguration ccfg2 = cacheConfig("org", false, Integer.class, Organization.class);
    final IgniteCache<Object, Object> c1 = ignite(0).getOrCreateCache(ccfg1);
    final IgniteCache<Object, Object> c2 = ignite(0).getOrCreateCache(ccfg2);
    try {
        c2.put(1, new Organization("o1"));
        c2.put(2, new Organization("o2"));
        c1.put(3, new Person2(1, "p1"));
        c1.put(4, new Person2(2, "p2"));
        c1.put(5, new Person2(3, "p3"));
        String select0 = "select o.name n1, p.name n2 from \"pers\".Person2 p, \"org\".Organization o where p.orgId = o._key";
        SqlFieldsQuery qry = new SqlFieldsQuery(select0);
        qry.setDistributedJoins(true);
        List<List<?>> results = c1.query(qry).getAll();
        assertEquals(2, results.size());
        select0 += " order by n2 desc";
        qry = new SqlFieldsQuery(select0);
        qry.setDistributedJoins(true);
        results = c1.query(qry).getAll();
        assertEquals(2, results.size());
        assertEquals("p2", results.get(0).get(1));
        assertEquals("p1", results.get(1).get(1));
        // Test for replicated subquery with aggregate.
        select0 = "select p.name " + "from \"pers\".Person2 p, " + "(select max(_key) orgId from \"org\".Organization) o " + "where p.orgId = o.orgId";
        X.println("Plan: \n" + c1.query(new SqlFieldsQuery("explain " + select0).setDistributedJoins(true)).getAll());
        qry = new SqlFieldsQuery(select0);
        qry.setDistributedJoins(true);
        results = c1.query(qry).getAll();
        assertEquals(1, results.size());
        assertEquals("p2", results.get(0).get(0));
    } finally {
        c1.destroy();
        c2.destroy();
    }
}

Example 98 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 99 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 100 with SqlFieldsQuery

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

the class IgniteSqlSplitterSelfTest method testIndexWithDifferentSegmentationLevelsFailure.

/**
 * @throws Exception If failed.
 */
@Test
public void testIndexWithDifferentSegmentationLevelsFailure() throws Exception {
    CacheConfiguration ccfg1 = cacheConfig("pers", true, Integer.class, Person2.class).setQueryParallelism(4);
    CacheConfiguration ccfg2 = cacheConfig("org", true, Integer.class, Organization.class).setQueryParallelism(3);
    final IgniteCache<Object, Object> c1 = ignite(0).getOrCreateCache(ccfg1);
    final IgniteCache<Object, Object> c2 = ignite(0).getOrCreateCache(ccfg2);
    try {
        c2.put(1, new Organization("o1"));
        c2.put(2, new Organization("o2"));
        c1.put(3, new Person2(1, "p1"));
        c1.put(4, new Person2(2, "p2"));
        c1.put(5, new Person2(3, "p3"));
        String select0 = "select o.name n1, p.name n2 from \"pers\".Person2 p, \"org\".Organization o where p.orgId = o._key and o._key=1";
        final SqlFieldsQuery qry = new SqlFieldsQuery(select0);
        qry.setDistributedJoins(true);
        GridTestUtils.assertThrows(log, new Callable<Void>() {

            @Override
            public Void call() throws Exception {
                c1.query(qry);
                return null;
            }
        }, CacheException.class, "Using indexes with different parallelism levels in same query is forbidden.");
    } finally {
        c1.destroy();
        c2.destroy();
    }
}