@Test public void testCompilerOptimization() throws Exception { String sequenceName = generateSequenceNameWithSchema(); String tableName = generateTableNameWithSchema(); conn.createStatement().execute("CREATE SEQUENCE " + sequenceName + " START WITH 3 INCREMENT BY 2"); conn.createStatement().execute("CREATE TABLE " + tableName + " (k INTEGER NOT NULL PRIMARY KEY, v1 VARCHAR, v2 VARCHAR) IMMUTABLE_ROWS=true"); conn.createStatement().execute("CREATE INDEX " + generateUniqueName() + " ON " + tableName + "(v1) INCLUDE (v2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); stmt.optimizeQuery("SELECT k, NEXT VALUE FOR " + sequenceName + " FROM " + tableName + " WHERE v1 = 'bar'"); }
public QueryPlan optimizeQuery(String sql) throws SQLException { QueryPlan plan = compileQuery(sql); return connection.getQueryServices().getOptimizer().optimize(this, plan); }
@Override public Statement createStatement() throws SQLException { checkOpen(); PhoenixStatement statement = new PhoenixStatement(this); statements.add(statement); return statement; }
public QueryPlan compileQuery(String sql) throws SQLException { CompilableStatement stmt = parseStatement(sql); return compileQuery(stmt, sql); }
public MutationPlan compileMutation(String sql) throws SQLException { if (logger.isDebugEnabled()) { logger.debug(LogUtil.addCustomAnnotations("Execute update: " + sql, connection)); } CompilableStatement stmt = parseStatement(sql); return compileMutation(stmt, sql); }
@Override public ResultSet executeQuery(String sql) throws SQLException { if (logger.isDebugEnabled()) { logger.debug(LogUtil.addCustomAnnotations("Execute query: " + sql, connection)); } CompilableStatement stmt = parseStatement(sql); if (stmt.getOperation().isMutation()) { throw new ExecuteQueryNotApplicableException(sql); } return executeQuery(stmt,createQueryLogger(stmt,sql)); }
@Override public boolean execute(String sql) throws SQLException { CompilableStatement stmt = parseStatement(sql); if (stmt.getOperation().isMutation()) { if (!batch.isEmpty()) { throw new SQLExceptionInfo.Builder(SQLExceptionCode.EXECUTE_UPDATE_WITH_NON_EMPTY_BATCH) .build().buildException(); } executeMutation(stmt); flushIfNecessary(); return false; } executeQuery(stmt,createQueryLogger(stmt,sql)); return true; }
private void verifyQueryPlanSourceRefs(String query, int refCount) throws SQLException { Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); try (Connection conn = DriverManager.getConnection(getUrl(), props)) { conn.createStatement().execute("CREATE TABLE A (\n" + " K1 VARCHAR(10) NOT NULL PRIMARY KEY,\n" + " V1 VARCHAR(10))"); conn.createStatement().execute("CREATE LOCAL INDEX IDX1 ON A(V1)"); conn.createStatement().execute("CREATE TABLE B (\n" + " K2 VARCHAR(10) NOT NULL PRIMARY KEY,\n" + " V2 VARCHAR(10))"); conn.createStatement().execute("CREATE LOCAL INDEX IDX2 ON B(V2)"); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); QueryPlan plan = stmt.compileQuery(query); Set<TableRef> sourceRefs = plan.getSourceRefs(); assertEquals(refCount, sourceRefs.size()); for (TableRef table : sourceRefs) { assertTrue(table.getTable().getType() == PTableType.TABLE); } plan = stmt.optimizeQuery(query); sourceRefs = plan.getSourceRefs(); assertEquals(refCount, sourceRefs.size()); for (TableRef table : sourceRefs) { assertTrue(table.getTable().getType() == PTableType.INDEX); } } }
private void assertRowCount(Connection conn, String fullTableName, String fullBaseName, int expectedCount) throws SQLException { PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); ResultSet rs = stmt.executeQuery("SELECT COUNT(*) FROM " + fullTableName); assertTrue(rs.next()); assertEquals(expectedCount, rs.getInt(1)); // Ensure that index is being used rs = stmt.executeQuery("EXPLAIN SELECT COUNT(*) FROM " + fullTableName); if (fullBaseName != null) { // Uses index and finds correct number of rows assertTrue(QueryUtil.getExplainPlan(rs).startsWith("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " + Bytes.toString(MetaDataUtil.getViewIndexPhysicalName(Bytes.toBytes(fullBaseName))))); } // Force it not to use index and still finds correct number of rows rs = stmt.executeQuery("SELECT /*+ NO_INDEX */ * FROM " + fullTableName); int count = 0; while (rs.next()) { count++; } assertEquals(expectedCount, count); // Ensure that the table, not index is being used assertEquals(fullTableName, stmt.getQueryPlan().getContext().getCurrentTable().getTable().getName().getString()); }
updateStmtStr.append(this.selectQuery); try (final PhoenixStatement statement = new PhoenixStatement(connection)) { DelegateMutationPlan delegate = new DelegateMutationPlan(statement.compileMutation(updateStmtStr.toString())) { @Override public MutationState execute() throws SQLException {
@Test public void testZeroCustomQueryTimeout() throws Exception { // Arrange Properties connectionProperties = new Properties(); connectionProperties.setProperty("phoenix.query.timeoutMs", "0"); Connection connection = DriverManager.getConnection(getUrl(), connectionProperties); PreparedStatement stmt = connection.prepareStatement("SELECT * FROM " + ATABLE); PhoenixStatement phoenixStmt = stmt.unwrap(PhoenixStatement.class); // Assert assertEquals(0, stmt.getQueryTimeout()); assertEquals(0, phoenixStmt.getQueryTimeoutInMillis()); }
public MutationPlan compile(PTable index) throws SQLException { try (final PhoenixStatement statement = new PhoenixStatement(connection)) { String query = "SELECT count(*) FROM " + tableName; final QueryPlan plan = statement.compileQuery(query); TableRef tableRef = plan.getTableRef(); Scan scan = plan.getContext().getScan();
/** * Execute the current batch of statements. If any exception occurs * during execution, a {@link org.apache.phoenix.exception.BatchUpdateException} * is thrown which includes the index of the statement within the * batch when the exception occurred. */ @Override public int[] executeBatch() throws SQLException { int i = 0; try { int[] returnCodes = new int [batch.size()]; for (i = 0; i < returnCodes.length; i++) { PhoenixPreparedStatement statement = batch.get(i); returnCodes[i] = statement.execute(true) ? Statement.SUCCESS_NO_INFO : statement.getUpdateCount(); } // Flush all changes in batch if auto flush is true flushIfNecessary(); // If we make it all the way through, clear the batch clearBatch(); return returnCodes; } catch (Throwable t) { throw new BatchUpdateExecution(t,i); } }
protected PhoenixResultSet executeQuery(final CompilableStatement stmt, final QueryLogger queryLogger) throws SQLException { return executeQuery(stmt, true, queryLogger); } private PhoenixResultSet executeQuery(final CompilableStatement stmt,
@Override public boolean execute(String sql, int[] columnIndexes) throws SQLException { return execute(sql); }
pstmt.setQueryTimeout(1); long startTime = System.currentTimeMillis(); try { ResultSet rs = pstmt.executeQuery("SELECT count(*) FROM " + tableName); assertTrue(pstmt.getQueryPlan().getSplits().size() > 1000); rs.next(); fail("Total time of query was " + (System.currentTimeMillis() - startTime) + " ms, but expected to be greater than 1000");
ResultSet rs = stmt.executeQuery("SELECT v_date, v_time, v_timestamp FROM " + tableName); assertFalse(rs.next()); StatementContext stmtContext = stmt.getQueryPlan().getContext(); verifyTimeZoneIDWithFormatter(stmtContext.getDateFormatter(), timeZoneId); verifyTimeZoneIDWithFormatter(stmtContext.getTimeFormatter(), timeZoneId); verifyTimeZoneIDWithFormatter(stmtContext.getTimestampFormatter(), timeZoneId); stmt.close(); } finally { conn1.close();
private PreparedStatement loadDataAndPrepareQuery(int timeoutMs, int timeoutSecs) throws Exception, SQLException { Properties props = new Properties(); props.setProperty(QueryServices.THREAD_TIMEOUT_MS_ATTRIB, String.valueOf(timeoutMs)); Connection conn = DriverManager.getConnection(getUrl(), props); PreparedStatement ps = conn.prepareStatement("SELECT * FROM " + tableName); PhoenixStatement phoenixStmt = ps.unwrap(PhoenixStatement.class); assertEquals(timeoutMs, phoenixStmt.getQueryTimeoutInMillis()); assertEquals(timeoutSecs, phoenixStmt.getQueryTimeout()); return ps; } }
@Test public void testHintInSubquery() throws Exception { try (Connection conn = DriverManager.getConnection(getUrl())) { setupTables(conn); PhoenixStatement stmt = conn.createStatement().unwrap(PhoenixStatement.class); String query = "UPSERT /*+ NO_INDEX */ INTO T(k, v1) SELECT /*+ NO_INDEX */ k,v1 FROM T WHERE v1 = '4'"; MutationPlan plan = stmt.compileMutation(query); assertEquals("T", plan.getQueryPlan().getTableRef().getTable().getTableName().getString()); query = "UPSERT INTO T(k, v1) SELECT /*+ NO_INDEX */ k,v1 FROM T WHERE v1 = '4'"; plan = stmt.compileMutation(query); // TODO the following should actually use data table T if we supported hints in subqueries assertEquals("IDX", plan.getQueryPlan().getTableRef().getTable().getTableName().getString()); } }
@Override public boolean execute(String sql) throws SQLException { CompilableStatement stmt = parseStatement(sql); if (stmt.getOperation().isMutation()) { if (!batch.isEmpty()) { throw new SQLExceptionInfo.Builder(SQLExceptionCode.EXECUTE_UPDATE_WITH_NON_EMPTY_BATCH) .build().buildException(); } executeMutation(stmt); flushIfNecessary(); return false; } executeQuery(stmt,createQueryLogger(stmt,sql)); return true; }