/** * This method creates the Upsert statement and the Column Metadata * for the Pig query using {@link PhoenixHBaseStorage}. It also * determines the batch size based on user provided options. * * @param conn * @throws SQLException */ public void setup(Connection conn) throws SQLException { // Reset batch size long batchSize = getBatchSize() <= 0 ? ((PhoenixConnection) conn).getMutateBatchSize() : getBatchSize(); conf.setLong(UPSERT_BATCH_SIZE, batchSize); if (columnMetadataList == null) { columnMetadataList = new ArrayList<ColumnInfo>(); String[] tableMetadata = getTableMetadata(getTableName()); ResultSet rs = conn.getMetaData().getColumns(null, tableMetadata[0], tableMetadata[1], null); while (rs.next()) { columnMetadataList.add(new ColumnInfo(rs.getString(QueryUtil.COLUMN_NAME_POSITION), rs.getInt(QueryUtil.DATA_TYPE_POSITION))); } } // Generating UPSERT statement without column name information. String upsertStmt = QueryUtil.constructUpsertStatement(null, getTableName(), columnMetadataList.size()); LOG.info("Phoenix Upsert Statement: " + upsertStmt); conf.set(UPSERT_STATEMENT, upsertStmt); }
/** * Creates a {@link Connection} with autoCommit set to false. * @throws SQLException */ public Connection getConnection() throws SQLException { Properties props = new Properties(); conn = DriverManager.getConnection(QueryUtil.getUrl(this.conf.get(SERVER_NAME)), props).unwrap(PhoenixConnection.class); conn.setAutoCommit(false); setup(conn); return conn; }
private static void assertIndexUsed (Connection conn, String query, List<Object> binds, String indexName, boolean expectedToBeUsed) throws SQLException { PreparedStatement stmt = conn.prepareStatement("EXPLAIN " + query); for (int i = 0; i < binds.size(); i++) { stmt.setObject(i+1, binds.get(i)); } ResultSet rs = stmt.executeQuery(); String explainPlan = QueryUtil.getExplainPlan(rs); assertEquals(expectedToBeUsed, explainPlan.contains(" SCAN OVER " + indexName)); }
this.upsertStatement = QueryUtil.constructUpsertStatement(columnMetadata, fullTableName, columnMetadata.length); logger.info(" the upsert statement is {} " ,this.upsertStatement);
@Test public void testSelectForceRangeScanForEH() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); conn.createStatement().execute("create table eh (organization_id char(15) not null,parent_id char(15) not null, created_date date not null, entity_history_id char(15) not null constraint pk primary key (organization_id, parent_id, created_date, entity_history_id))"); ResultSet rs = conn.createStatement().executeQuery("explain select /*+ RANGE_SCAN */ ORGANIZATION_ID, PARENT_ID, CREATED_DATE, ENTITY_HISTORY_ID from eh where ORGANIZATION_ID='111111111111111' and SUBSTR(PARENT_ID, 1, 3) = 'foo' and TO_DATE ('2012-0-1 00:00:00') <= CREATED_DATE and CREATED_DATE <= TO_DATE ('2012-11-31 00:00:00') order by ORGANIZATION_ID, PARENT_ID, CREATED_DATE DESC, ENTITY_HISTORY_ID limit 100"); assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER EH ['111111111111111','foo','2011-12-01 00:00:00.000'] - ['111111111111111','fop','2012-12-01 00:00:00.000']\n" + " SERVER FILTER BY (CREATED_DATE >= 2011-11-30 AND CREATED_DATE <= 2012-11-30)\n" + " SERVER TOP 100 ROWS SORTED BY [ORGANIZATION_ID, PARENT_ID, CREATED_DATE DESC, ENTITY_HISTORY_ID]\n" + "CLIENT MERGE SORT",QueryUtil.getExplainPlan(rs)); } }
this.jdbcUrl = QueryUtil.getUrl(zookeeperQuorum);
stmtCache = new PreparedStatement[columnInfo.length]; } else { String upsertStatement = QueryUtil.constructUpsertStatement(columnInfo, tableName, columnInfo.length - unfoundColumnCount); stmt = conn.prepareStatement(upsertStatement); stmt = stmtCache[nextLine.length-1]; if (stmt == null) { String upsertStatement = QueryUtil.constructUpsertStatement(columnInfo, tableName, nextLine.length); stmt = conn.prepareStatement(upsertStatement); stmtCache[nextLine.length-1] = stmt;
@Test public void testExplainPlan() throws Exception { Connection conn = DriverManager.getConnection(getUrl()); String query = "EXPLAIN SELECT s.supplier_id, order_id, c.name, i.name, quantity, o.date FROM " + JOIN_ORDER_TABLE + " o LEFT JOIN " + JOIN_CUSTOMER_TABLE + " c ON o.customer_id = c.customer_id AND c.name LIKE 'C%' LEFT JOIN " + JOIN_ITEM_TABLE + " i ON o.item_id = i.item_id RIGHT JOIN " + JOIN_SUPPLIER_TABLE + " s ON s.supplier_id = i.supplier_id WHERE i.name LIKE 'T%'"; ResultSet rs = conn.createStatement().executeQuery(query); assertEquals( "CLIENT PARALLEL 1-WAY FULL SCAN OVER JOIN_SUPPLIER_TABLE\n" + " SERVER FILTER BY FIRST KEY ONLY\n" + " PARALLEL EQUI-JOIN 1 HASH TABLES:\n" + " BUILD HASH TABLE 0\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER JOIN_ORDER_TABLE\n" + " PARALLEL EQUI-JOIN 2 HASH TABLES:\n" + " BUILD HASH TABLE 0\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER JOIN_CUSTOMER_TABLE\n" + " SERVER FILTER BY NAME LIKE 'C%'\n" + " BUILD HASH TABLE 1\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER JOIN_ITEM_TABLE\n" + " AFTER-JOIN SERVER FILTER BY I.NAME LIKE 'T%'", QueryUtil.getExplainPlan(rs)); }
@Test public void testIndexWithNullableDateCol() throws Exception { Properties props = new Properties(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(getUrl(), props); conn.setAutoCommit(false); try { Date date = new Date(System.currentTimeMillis()); createTestTable(); populateTestTable(date); String ddl = "CREATE INDEX " + INDEX_TABLE_NAME + " ON " + DATA_TABLE_FULL_NAME + " (date_col)"; PreparedStatement stmt = conn.prepareStatement(ddl); stmt.execute(); String query = "SELECT int_pk from " + DATA_TABLE_FULL_NAME ; ResultSet rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); rs = conn.createStatement().executeQuery(query); assertTrue(rs.next()); assertEquals(2, rs.getInt(1)); assertTrue(rs.next()); assertEquals(1, rs.getInt(1)); assertTrue(rs.next()); assertEquals(3, rs.getInt(1)); assertFalse(rs.next()); } finally { conn.close(); } }
@Test public void testJoinWithSkipMergeOptimization() throws Exception { String query = "SELECT s.name FROM " + JOIN_ITEM_TABLE + " i JOIN " + JOIN_ORDER_TABLE + " o ON o.item_id = i.item_id AND quantity < 5000 JOIN " + JOIN_SUPPLIER_TABLE + " s ON i.supplier_id = s.supplier_id"; Properties props = new Properties(TEST_PROPERTIES); Connection conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props); try { PreparedStatement statement = conn.prepareStatement(query); ResultSet rs = statement.executeQuery(); assertTrue (rs.next()); assertEquals(rs.getString(1), "S1"); assertTrue (rs.next()); assertEquals(rs.getString(1), "S1"); assertTrue (rs.next()); assertEquals(rs.getString(1), "S6"); assertTrue (rs.next()); assertEquals(rs.getString(1), "S6"); assertFalse(rs.next()); rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertEquals(plans[2], QueryUtil.getExplainPlan(rs)); } finally { conn.close(); } }
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + DATA_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); rs = conn.createStatement().executeQuery(query); assertTrue(rs.next());
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER INDEX_TEST.IDX", QueryUtil.getExplainPlan(rs));
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " +INDEX_TABLE_FULL_NAME + " [~'1']\n" + " SERVER TOP -1 ROWS SORTED BY [V1]\n" + "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs));
rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs)); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
rs = conn.createStatement().executeQuery("EXPLAIN " + query); assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
assertEquals("CLIENT PARALLEL 1-WAY FULL SCAN OVER " + INDEX_TABLE_FULL_NAME, QueryUtil.getExplainPlan(rs));
assertEquals(plans[0], QueryUtil.getExplainPlan(rs)); assertEquals(plans[1], QueryUtil.getExplainPlan(rs)); } finally { conn.close();
ResultSet ers = upsertStmt.executeQuery(); assertTrue(ers.next()); String explainPlan = QueryUtil.getExplainPlan(ers); assertTrue(explainPlan.contains(" SCAN OVER " + indexName));