Examples with PhoenixStatement org.apache.phoenix.jdbc.PhoenixStatement used on opensource projects

Example 16 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class LocalIndexIT method testLocalIndexScanJoinColumnsFromDataTable.

@Test
public void testLocalIndexScanJoinColumnsFromDataTable() throws Exception {
    String tableName = schemaName + "." + generateUniqueName();
    String indexName = "IDX_" + generateUniqueName();
    String indexTableName = schemaName + "." + indexName;
    TableName physicalTableName = SchemaUtil.getPhysicalTableName(tableName.getBytes(), isNamespaceMapped);
    String indexPhysicalTableName = physicalTableName.getNameAsString();
    createBaseTable(tableName, null, "('e','i','o')");
    Connection conn1 = getConnection();
    try {
        conn1.createStatement().execute("UPSERT INTO " + tableName + " values('b',1,2,4,'z')");
        conn1.createStatement().execute("UPSERT INTO " + tableName + " values('f',1,2,3,'a')");
        conn1.createStatement().execute("UPSERT INTO " + tableName + " values('j',2,4,2,'a')");
        conn1.createStatement().execute("UPSERT INTO " + tableName + " values('q',3,1,1,'c')");
        conn1.commit();
        conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)");
        ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + indexTableName);
        assertTrue(rs.next());
        HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(), TestUtil.TEST_PROPERTIES).getAdmin();
        int numRegions = admin.getTableRegions(physicalTableName).size();
        String query = "SELECT t_id, k1, k2, k3, V1 FROM " + tableName + " where v1='a'";
        rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
        assertEquals("CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER " + indexPhysicalTableName + " [1,'a']\n" + "    SERVER FILTER BY FIRST KEY ONLY\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
        rs = conn1.createStatement().executeQuery(query);
        assertTrue(rs.next());
        assertEquals("f", rs.getString("t_id"));
        assertEquals(1, rs.getInt("k1"));
        assertEquals(2, rs.getInt("k2"));
        assertEquals(3, rs.getInt("k3"));
        assertTrue(rs.next());
        assertEquals("j", rs.getString("t_id"));
        assertEquals(2, rs.getInt("k1"));
        assertEquals(4, rs.getInt("k2"));
        assertEquals(2, rs.getInt("k3"));
        assertFalse(rs.next());
        query = "SELECT t_id, k1, k2, k3, V1 from " + tableName + "  where v1<='z' order by V1,t_id";
        rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
        assertEquals("CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER " + indexPhysicalTableName + " [1,*] - [1,'z']\n" + "    SERVER FILTER BY FIRST KEY ONLY\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
        rs = conn1.createStatement().executeQuery(query);
        assertTrue(rs.next());
        assertEquals("f", rs.getString("t_id"));
        assertEquals(1, rs.getInt("k1"));
        assertEquals(2, rs.getInt("k2"));
        assertEquals(3, rs.getInt("k3"));
        assertEquals("a", rs.getString("V1"));
        assertTrue(rs.next());
        assertEquals("j", rs.getString("t_id"));
        assertEquals(2, rs.getInt("k1"));
        assertEquals(4, rs.getInt("k2"));
        assertEquals(2, rs.getInt("k3"));
        assertEquals("a", rs.getString("V1"));
        assertTrue(rs.next());
        assertEquals("q", rs.getString("t_id"));
        assertEquals(3, rs.getInt("k1"));
        assertEquals(1, rs.getInt("k2"));
        assertEquals(1, rs.getInt("k3"));
        assertEquals("c", rs.getString("V1"));
        assertTrue(rs.next());
        assertEquals("b", rs.getString("t_id"));
        assertEquals(1, rs.getInt("k1"));
        assertEquals(2, rs.getInt("k2"));
        assertEquals(4, rs.getInt("k3"));
        assertEquals("z", rs.getString("V1"));
        query = "SELECT t_id, V1, k3 from " + tableName + "  where v1 <='z' group by v1,t_id, k3";
        rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
        assertEquals("CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER " + indexPhysicalTableName + " [1,*] - [1,'z']\n" + "    SERVER FILTER BY FIRST KEY ONLY\n" + "    SERVER AGGREGATE INTO DISTINCT ROWS BY [\"V1\", \"T_ID\", \"K3\"]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
        rs = conn1.createStatement().executeQuery(query);
        assertTrue(rs.next());
        assertEquals("f", rs.getString("t_id"));
        assertEquals(3, rs.getInt("k3"));
        assertEquals("a", rs.getString("V1"));
        assertTrue(rs.next());
        assertEquals("j", rs.getString("t_id"));
        assertEquals(2, rs.getInt("k3"));
        assertEquals("a", rs.getString("V1"));
        assertTrue(rs.next());
        assertEquals("q", rs.getString("t_id"));
        assertEquals(1, rs.getInt("k3"));
        assertEquals("c", rs.getString("V1"));
        assertTrue(rs.next());
        assertEquals("b", rs.getString("t_id"));
        assertEquals(4, rs.getInt("k3"));
        assertEquals("z", rs.getString("V1"));
        query = "SELECT v1,sum(k3) from " + tableName + " where v1 <='z'  group by v1 order by v1";
        rs = conn1.createStatement().executeQuery("EXPLAIN " + query);
        assertEquals("CLIENT PARALLEL " + numRegions + "-WAY RANGE SCAN OVER " + indexPhysicalTableName + " [1,*] - [1,'z']\n" + "    SERVER FILTER BY FIRST KEY ONLY\n" + "    SERVER AGGREGATE INTO ORDERED DISTINCT ROWS BY [\"V1\"]\nCLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
        PhoenixStatement stmt = conn1.createStatement().unwrap(PhoenixStatement.class);
        rs = stmt.executeQuery(query);
        QueryPlan plan = stmt.getQueryPlan();
        assertEquals(indexTableName, plan.getContext().getCurrentTable().getTable().getName().getString());
        assertEquals(BaseScannerRegionObserver.KEY_ORDERED_GROUP_BY_EXPRESSIONS, plan.getGroupBy().getScanAttribName());
        assertTrue(rs.next());
        assertEquals("a", rs.getString(1));
        assertEquals(5, rs.getInt(2));
        assertTrue(rs.next());
        assertEquals("c", rs.getString(1));
        assertEquals(1, rs.getInt(2));
        assertTrue(rs.next());
        assertEquals("z", rs.getString(1));
        assertEquals(4, rs.getInt(2));
    } finally {
        conn1.close();
    }
}

Example 17 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class QuerySchemaParserFunction method apply.

@Override
public Pair<String, String> apply(final String selectStatement) {
    Preconditions.checkNotNull(selectStatement);
    Preconditions.checkArgument(!selectStatement.isEmpty(), "Select Query is empty!!");
    Connection connection = null;
    try {
        connection = ConnectionUtil.getInputConnection(this.configuration);
        final Statement statement = connection.createStatement();
        final PhoenixStatement pstmt = statement.unwrap(PhoenixStatement.class);
        final QueryPlan queryPlan = pstmt.compileQuery(selectStatement);
        isValidStatement(queryPlan);
        final String tableName = queryPlan.getTableRef().getTable().getName().getString();
        final List<? extends ColumnProjector> projectedColumns = queryPlan.getProjector().getColumnProjectors();
        final List<String> columns = Lists.transform(projectedColumns, new Function<ColumnProjector, String>() {

            @Override
            public String apply(ColumnProjector column) {
                return column.getName();
            }
        });
        final String columnsAsStr = Joiner.on(",").join(columns);
        return new Pair<String, String>(tableName, columnsAsStr);
    } catch (SQLException e) {
        LOG.error(String.format(" Error [%s] parsing SELECT query [%s] ", e.getMessage(), selectStatement));
        throw new RuntimeException(e);
    } finally {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException sqle) {
                LOG.error(" Error closing connection ");
                throw new RuntimeException(sqle);
            }
        }
    }
}

Example 18 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class PhoenixInputFormat method getQueryPlan.

/**
     * Returns the query plan associated with the select query.
     * @param context
     * @return
     * @throws IOException
     * @throws SQLException
     */
private QueryPlan getQueryPlan(final JobContext context, final Configuration configuration) throws IOException {
    Preconditions.checkNotNull(context);
    try {
        final String txnScnValue = configuration.get(PhoenixConfigurationUtil.TX_SCN_VALUE);
        final String currentScnValue = configuration.get(PhoenixConfigurationUtil.CURRENT_SCN_VALUE);
        final Properties overridingProps = new Properties();
        if (txnScnValue == null && currentScnValue != null) {
            overridingProps.put(PhoenixRuntime.CURRENT_SCN_ATTRIB, currentScnValue);
        }
        final Connection connection = ConnectionUtil.getInputConnection(configuration, overridingProps);
        final String selectStatement = PhoenixConfigurationUtil.getSelectStatement(configuration);
        Preconditions.checkNotNull(selectStatement);
        final Statement statement = connection.createStatement();
        final PhoenixStatement pstmt = statement.unwrap(PhoenixStatement.class);
        // Optimize the query plan so that we potentially use secondary indexes            
        final QueryPlan queryPlan = pstmt.optimizeQuery(selectStatement);
        final Scan scan = queryPlan.getContext().getScan();
        // since we can't set a scn on connections with txn set TX_SCN attribute so that the max time range is set by BaseScannerRegionObserver 
        if (txnScnValue != null) {
            scan.setAttribute(BaseScannerRegionObserver.TX_SCN, Bytes.toBytes(Long.valueOf(txnScnValue)));
        }
        // Initialize the query plan so it sets up the parallel scans
        queryPlan.iterator(MapReduceParallelScanGrouper.getInstance());
        return queryPlan;
    } catch (Exception exception) {
        LOG.error(String.format("Failed to get the query plan with error [%s]", exception.getMessage()));
        throw new RuntimeException(exception);
    }
}

Example 19 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class QueryOptimizerTest method testChooseIndexFromCaseSensitiveHint2.

@Test
public void testChooseIndexFromCaseSensitiveHint2() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    conn.createStatement().execute("CREATE TABLE \"t\" (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true");
    conn.createStatement().execute("CREATE INDEX \"idx1\" ON \"t\"(v1) INCLUDE(v2)");
    conn.createStatement().execute("CREATE INDEX \"idx2\" ON \"t\"(v1,v2)");
    PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
    QueryPlan plan = stmt.optimizeQuery("SELECT /*+ INDEX(\"t\" \"idx1\") */ k FROM \"t\" WHERE v1 = 'foo' AND v2 = 'bar'");
    assertEquals("idx1", plan.getTableRef().getTable().getTableName().getString());
    plan = stmt.optimizeQuery("SELECT k FROM \"t\" WHERE v1 = 'foo' AND v2 = 'bar'");
    assertEquals("idx2", plan.getTableRef().getTable().getTableName().getString());
}

Example 20 with PhoenixStatement

use of org.apache.phoenix.jdbc.PhoenixStatement in project phoenix by apache.

the class QueryOptimizerTest method testOrderByDropped.

@Test
public void testOrderByDropped() throws Exception {
    Connection conn = DriverManager.getConnection(getUrl());
    try {
        conn.createStatement().execute("CREATE TABLE foo (k VARCHAR NOT NULL PRIMARY KEY, v VARCHAR) IMMUTABLE_ROWS=true");
        PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class);
        QueryPlan plan = stmt.optimizeQuery("SELECT * FROM foo ORDER BY 'a','b','c'");
        assertTrue(plan.getOrderBy().getOrderByExpressions().isEmpty());
    } finally {
        conn.close();
    }
}