Examples with QueryPlan org.apache.phoenix.compile.QueryPlan used on opensource projects

Example 16 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class AggregateIT method testGroupByOrderByDescBug3451.

@Test
public void testGroupByOrderByDescBug3451() throws Exception {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    try (Connection conn = DriverManager.getConnection(getUrl(), props)) {
        String tableName = generateUniqueName();
        String sql = "CREATE TABLE " + tableName + " (\n" + "            ORGANIZATION_ID CHAR(15) NOT NULL,\n" + "            CONTAINER_ID CHAR(15) NOT NULL,\n" + "            ENTITY_ID CHAR(15) NOT NULL,\n" + "            SCORE DOUBLE,\n" + "            CONSTRAINT TEST_PK PRIMARY KEY (\n" + "               ORGANIZATION_ID,\n" + "               CONTAINER_ID,\n" + "               ENTITY_ID\n" + "             )\n" + "         )";
        conn.createStatement().execute(sql);
        String indexName = generateUniqueName();
        conn.createStatement().execute("CREATE INDEX " + indexName + " ON " + tableName + "(ORGANIZATION_ID,CONTAINER_ID, SCORE DESC, ENTITY_ID DESC)");
        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container2','entityId6',1.1)");
        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container1','entityId5',1.2)");
        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container2','entityId4',1.3)");
        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container1','entityId5',1.2)");
        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container1','entityId3',1.4)");
        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container3','entityId7',1.35)");
        conn.createStatement().execute("UPSERT INTO " + tableName + " VALUES ('org2','container3','entityId8',1.45)");
        conn.commit();
        String query = "SELECT DISTINCT entity_id, score\n" + "    FROM " + tableName + "\n" + "    WHERE organization_id = 'org2'\n" + "    AND container_id IN ( 'container1','container2','container3' )\n" + "    ORDER BY score DESC\n" + "    LIMIT 2";
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
        assertEquals(indexName, plan.getContext().getCurrentTable().getTable().getName().getString());
        assertFalse(plan.getOrderBy().getOrderByExpressions().isEmpty());
        assertTrue(rs.next());
        assertEquals("entityId8", rs.getString(1));
        assertEquals(1.45, rs.getDouble(2), 0.001);
        assertTrue(rs.next());
        assertEquals("entityId3", rs.getString(1));
        assertEquals(1.4, rs.getDouble(2), 0.001);
        assertFalse(rs.next());
    }
}

Example 17 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class PhoenixRuntimeTest method testGetPkColsDataTypes.

@Test
public void testGetPkColsDataTypes() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl(), new Properties());
    int i = 0;
    PDataType[] pTypes = PDataType.values();
    int size = pTypes.length;
    StringBuilder sb = null;
    try {
        for (i = 0; i < size; i++) {
            PDataType pType = pTypes[i];
            String sqlTypeName = pType.getSqlTypeName();
            if (sqlTypeName.equalsIgnoreCase("VARBINARY ARRAY")) {
                // JIRA - https://issues.apache.org/jira/browse/PHOENIX-1329
                continue;
            }
            if (pType.isArrayType() && PDataType.arrayBaseType(pType).isFixedWidth() && PDataType.arrayBaseType(pType).getByteSize() == null) {
                // Need to treat array type whose base type is of fixed width whose byte size is not known as a special case. 
                // Cannot just use the sql type name returned by PDataType.getSqlTypeName().
                String baseTypeName = PDataType.arrayBaseType(pType).getSqlTypeName();
                sqlTypeName = baseTypeName + "(15)" + " " + PDataType.ARRAY_TYPE_SUFFIX;
            } else if (pType.isFixedWidth() && pType.getByteSize() == null) {
                sqlTypeName = sqlTypeName + "(15)";
            }
            String columnName = "col" + i;
            String tableName = "t" + i;
            sb = new StringBuilder(100);
            // create a table by using the type name as returned by PDataType
            sb.append("CREATE TABLE " + tableName + " (");
            sb.append(columnName + " " + sqlTypeName + " NOT NULL PRIMARY KEY, V1 VARCHAR)");
            conn.createStatement().execute(sb.toString());
            // generate the optimized query plan by going through the pk of the table.
            PreparedStatement stmt = conn.prepareStatement("SELECT * FROM " + tableName + " WHERE " + columnName + " = ?");
            Integer maxLength = pType.isFixedWidth() && pType.getByteSize() == null ? 15 : null;
            stmt.setObject(1, pType.getSampleValue(maxLength));
            QueryPlan plan = PhoenixRuntime.getOptimizedQueryPlan(stmt);
            // now go through the utility method, get column name and type name and
            // try creating another table with the returned info. Use the query plan generated above.
            // If table can be created with the returned sql type name, then great!
            // It would mean "Roundtrip" of column data type name works.
            List<Pair<String, String>> pkCols = new ArrayList<Pair<String, String>>();
            List<String> dataTypes = new ArrayList<String>();
            PhoenixRuntime.getPkColsDataTypesForSql(pkCols, dataTypes, plan, conn, true);
            tableName = "newt" + i;
            columnName = "newCol" + i;
            String roundTripSqlTypeName = dataTypes.get(0);
            // create a table by using the type name as returned by the utility method
            sb = new StringBuilder(100);
            sb.append("CREATE TABLE " + tableName + " (");
            sb.append(columnName + " " + roundTripSqlTypeName + " NOT NULL PRIMARY KEY)");
            conn.createStatement().execute(sb.toString());
        }
    } catch (Exception e) {
        fail("Failed sql: " + sb.toString() + ExceptionUtils.getStackTrace(e));
    }
}

Example 18 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class PhoenixPreparedStatement method getMetaData.

@Override
public ResultSetMetaData getMetaData() throws SQLException {
    if (statement.getOperation().isMutation()) {
        return null;
    }
    int paramCount = statement.getBindCount();
    List<Object> params = this.getParameters();
    BitSet unsetParams = new BitSet(statement.getBindCount());
    for (int i = 0; i < paramCount; i++) {
        if (params.get(i) == BindManager.UNBOUND_PARAMETER) {
            unsetParams.set(i);
            params.set(i, null);
        }
    }
    try {
        // Just compile top level query without optimizing to get ResultSetMetaData
        QueryPlan plan = statement.compilePlan(this, Sequence.ValueOp.VALIDATE_SEQUENCE);
        return new PhoenixResultSetMetaData(this.getConnection(), plan.getProjector());
    } finally {
        int lastSetBit = 0;
        while ((lastSetBit = unsetParams.nextSetBit(lastSetBit)) != -1) {
            params.set(lastSetBit, BindManager.UNBOUND_PARAMETER);
            lastSetBit++;
        }
    }
}

Example 19 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class ViewIndexIT method testMultiTenantViewLocalIndex.

@Test
public void testMultiTenantViewLocalIndex() throws Exception {
    String schemaName = generateUniqueName();
    String tableName = generateUniqueName();
    String indexName = "IND_" + generateUniqueName();
    String viewName = "VIEW_" + generateUniqueName();
    String fullTableName = SchemaUtil.getTableName(schemaName, tableName);
    createBaseTable(schemaName, tableName, true, null, null);
    Connection conn = DriverManager.getConnection(getUrl());
    PreparedStatement stmt = conn.prepareStatement("UPSERT INTO " + fullTableName + " VALUES(?,?,?,?,?)");
    stmt.setString(1, "10");
    stmt.setString(2, "a");
    stmt.setInt(3, 1);
    stmt.setString(4, "x1");
    stmt.setInt(5, 100);
    stmt.execute();
    stmt.setString(1, "10");
    stmt.setString(2, "b");
    stmt.setInt(3, 2);
    stmt.setString(4, "x2");
    stmt.setInt(5, 200);
    stmt.execute();
    stmt.setString(1, "10");
    stmt.setString(2, "c");
    stmt.setInt(3, 3);
    stmt.setString(4, "x3");
    stmt.setInt(5, 300);
    stmt.execute();
    stmt.setString(1, "20");
    stmt.setString(2, "d");
    stmt.setInt(3, 4);
    stmt.setString(4, "x4");
    stmt.setInt(5, 400);
    stmt.execute();
    conn.commit();
    Properties props = PropertiesUtil.deepCopy(TestUtil.TEST_PROPERTIES);
    props.setProperty("TenantId", "10");
    Connection conn1 = DriverManager.getConnection(getUrl(), props);
    conn1.createStatement().execute("CREATE VIEW " + viewName + " AS select * from " + fullTableName);
    conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + viewName + "(v2)");
    conn1.commit();
    String sql = "SELECT * FROM " + viewName + " WHERE v2 = 100";
    ResultSet rs = conn1.prepareStatement("EXPLAIN " + sql).executeQuery();
    assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + SchemaUtil.getPhysicalHBaseTableName(fullTableName, isNamespaceMapped, PTableType.TABLE) + " [1,'10',100]\n" + "    SERVER FILTER BY FIRST KEY ONLY\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
    rs = conn1.prepareStatement(sql).executeQuery();
    assertTrue(rs.next());
    assertFalse(rs.next());
    TestUtil.analyzeTable(conn, fullTableName);
    List<KeyRange> guideposts = TestUtil.getAllSplits(conn, fullTableName);
    assertEquals(1, guideposts.size());
    assertEquals(KeyRange.EVERYTHING_RANGE, guideposts.get(0));
    conn.createStatement().execute("ALTER TABLE " + fullTableName + " SET " + PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=20");
    TestUtil.analyzeTable(conn, fullTableName);
    guideposts = TestUtil.getAllSplits(conn, fullTableName);
    assertEquals(5, guideposts.size());
    // Confirm that when view index used, the GUIDE_POSTS_WIDTH from the data physical table
    // was used
    sql = "SELECT * FROM " + viewName + " WHERE v2 >= 100";
    stmt = conn1.prepareStatement(sql);
    stmt.executeQuery();
    QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
    assertEquals(4, plan.getSplits().size());
}

Example 20 with QueryPlan

use of org.apache.phoenix.compile.QueryPlan in project phoenix by apache.

the class ViewIndexIT method testCreatingIndexOnGlobalView.

@Test
public void testCreatingIndexOnGlobalView() throws Exception {
    String baseTable = generateUniqueName();
    String globalView = generateUniqueName();
    String globalViewIdx = generateUniqueName();
    try (Connection conn = DriverManager.getConnection(getUrl())) {
        conn.createStatement().execute("CREATE TABLE " + baseTable + " (TENANT_ID CHAR(15) NOT NULL, PK2 DATE NOT NULL, PK3 INTEGER NOT NULL, KV1 VARCHAR, KV2 VARCHAR, KV3 CHAR(15) CONSTRAINT PK PRIMARY KEY(TENANT_ID, PK2 ROW_TIMESTAMP, PK3)) MULTI_TENANT=true");
        conn.createStatement().execute("CREATE VIEW " + globalView + " AS SELECT * FROM " + baseTable);
        conn.createStatement().execute("CREATE INDEX " + globalViewIdx + " ON " + globalView + " (PK3 DESC, KV3) INCLUDE (KV1)");
        PreparedStatement stmt = conn.prepareStatement("UPSERT INTO  " + globalView + " (TENANT_ID, PK2, PK3, KV1, KV3) VALUES (?, ?, ?, ?, ?)");
        stmt.setString(1, "tenantId");
        stmt.setDate(2, new Date(100));
        stmt.setInt(3, 1);
        stmt.setString(4, "KV1");
        stmt.setString(5, "KV3");
        stmt.executeUpdate();
        stmt.setString(1, "tenantId");
        stmt.setDate(2, new Date(100));
        stmt.setInt(3, 2);
        stmt.setString(4, "KV4");
        stmt.setString(5, "KV5");
        stmt.executeUpdate();
        stmt.setString(1, "tenantId");
        stmt.setDate(2, new Date(100));
        stmt.setInt(3, 3);
        stmt.setString(4, "KV6");
        stmt.setString(5, "KV7");
        stmt.executeUpdate();
        stmt.setString(1, "tenantId");
        stmt.setDate(2, new Date(100));
        stmt.setInt(3, 4);
        stmt.setString(4, "KV8");
        stmt.setString(5, "KV9");
        stmt.executeUpdate();
        stmt.setString(1, "tenantId");
        stmt.setDate(2, new Date(100));
        stmt.setInt(3, 5);
        stmt.setString(4, "KV10");
        stmt.setString(5, "KV11");
        stmt.executeUpdate();
        conn.commit();
        // Verify that query against the global view index works
        stmt = conn.prepareStatement("SELECT KV1 FROM  " + globalView + " WHERE PK3 = ? AND KV3 = ?");
        stmt.setInt(1, 1);
        stmt.setString(2, "KV3");
        ResultSet rs = stmt.executeQuery();
        QueryPlan plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
        assertTrue(plan.getTableRef().getTable().getName().getString().equals(globalViewIdx));
        assertTrue(rs.next());
        assertEquals("KV1", rs.getString(1));
        assertFalse(rs.next());
        TestUtil.analyzeTable(conn, baseTable);
        List<KeyRange> guideposts = TestUtil.getAllSplits(conn, baseTable);
        assertEquals(1, guideposts.size());
        assertEquals(KeyRange.EVERYTHING_RANGE, guideposts.get(0));
        conn.createStatement().execute("ALTER TABLE " + baseTable + " SET " + PhoenixDatabaseMetaData.GUIDE_POSTS_WIDTH + "=20");
        TestUtil.analyzeTable(conn, baseTable);
        guideposts = TestUtil.getAllSplits(conn, baseTable);
        assertEquals(6, guideposts.size());
        // Confirm that when view index used, the GUIDE_POSTS_WIDTH from the data physical table
        // was used
        stmt = conn.prepareStatement("SELECT KV1 FROM  " + globalView + " WHERE PK3 = ? AND KV3 >= ?");
        stmt.setInt(1, 1);
        stmt.setString(2, "KV3");
        rs = stmt.executeQuery();
        plan = stmt.unwrap(PhoenixStatement.class).getQueryPlan();
        assertTrue(plan.getTableRef().getTable().getName().getString().equals(globalViewIdx));
        assertEquals(6, plan.getSplits().size());
    }
}