| /* |
| * Licensed to the Apache Software Foundation (ASF) under one |
| * or more contributor license agreements. See the NOTICE file |
| * distributed with this work for additional information |
| * regarding copyright ownership. The ASF licenses this file |
| * to you under the Apache License, Version 2.0 (the |
| * "License"); you may not use this file except in compliance |
| * with the License. You may obtain a copy of the License at |
| * |
| * http://www.apache.org/licenses/LICENSE-2.0 |
| * |
| * Unless required by applicable law or agreed to in writing, software |
| * distributed under the License is distributed on an "AS IS" BASIS, |
| * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| * See the License for the specific language governing permissions and |
| * limitations under the License. |
| */ |
| package org.apache.phoenix.end2end.index; |
| |
| import static org.apache.phoenix.util.MetaDataUtil.getViewIndexSequenceName; |
| import static org.apache.phoenix.util.MetaDataUtil.getViewIndexSequenceSchemaName; |
| import static org.junit.Assert.assertArrayEquals; |
| import static org.junit.Assert.assertEquals; |
| import static org.junit.Assert.assertFalse; |
| import static org.junit.Assert.assertNotNull; |
| import static org.junit.Assert.assertTrue; |
| import static org.junit.Assert.fail; |
| |
| import java.io.IOException; |
| import java.sql.Connection; |
| import java.sql.DriverManager; |
| import java.sql.ResultSet; |
| import java.sql.SQLException; |
| import java.sql.Statement; |
| import java.util.Collection; |
| import java.util.Iterator; |
| import java.util.List; |
| |
| import org.apache.hadoop.conf.Configuration; |
| import org.apache.hadoop.fs.FileSystem; |
| import org.apache.hadoop.fs.FileUtil; |
| import org.apache.hadoop.fs.Path; |
| import org.apache.hadoop.hbase.HColumnDescriptor; |
| import org.apache.hadoop.hbase.HRegionInfo; |
| import org.apache.hadoop.hbase.HTableDescriptor; |
| import org.apache.hadoop.hbase.TableName; |
| import org.apache.hadoop.hbase.client.HBaseAdmin; |
| import org.apache.hadoop.hbase.client.HTable; |
| import org.apache.hadoop.hbase.client.HTableInterface; |
| import org.apache.hadoop.hbase.client.Result; |
| import org.apache.hadoop.hbase.client.ResultScanner; |
| import org.apache.hadoop.hbase.client.Scan; |
| import org.apache.hadoop.hbase.util.Bytes; |
| import org.apache.hadoop.hbase.util.FSUtils; |
| import org.apache.hadoop.hbase.util.Pair; |
| import org.apache.phoenix.compile.QueryPlan; |
| import org.apache.phoenix.coprocessor.BaseScannerRegionObserver; |
| import org.apache.phoenix.hbase.index.IndexRegionSplitPolicy; |
| import org.apache.phoenix.jdbc.PhoenixConnection; |
| import org.apache.phoenix.jdbc.PhoenixStatement; |
| import org.apache.phoenix.query.QueryConstants; |
| import org.apache.phoenix.schema.PNameFactory; |
| import org.apache.phoenix.schema.PTable; |
| import org.apache.phoenix.schema.PTable.IndexType; |
| import org.apache.phoenix.schema.PTableKey; |
| import org.apache.phoenix.schema.TableNotFoundException; |
| import org.apache.phoenix.util.QueryUtil; |
| import org.apache.phoenix.util.SchemaUtil; |
| import org.apache.phoenix.util.TestUtil; |
| import org.junit.Ignore; |
| import org.junit.Test; |
| |
| public class LocalIndexIT extends BaseLocalIndexIT { |
| public LocalIndexIT(boolean isNamespaceMapped) { |
| super(isNamespaceMapped); |
| } |
| |
| @Ignore |
| //FIXME: PHOENIX-3496 |
| public void testLocalIndexRoundTrip() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| String indexTableName = schemaName + "." + indexName; |
| |
| createBaseTable(tableName, null, null); |
| Connection conn1 = DriverManager.getConnection(getUrl()); |
| conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)"); |
| conn1.createStatement().executeQuery("SELECT * FROM " + tableName).next(); |
| PTable localIndex = conn1.unwrap(PhoenixConnection.class).getTable(new PTableKey(null, indexTableName)); |
| assertEquals(IndexType.LOCAL, localIndex.getIndexType()); |
| assertNotNull(localIndex.getViewIndexId()); |
| String tableName2 = "test_table" + generateUniqueName(); |
| String indexName2 = "idx_test_table" + generateUniqueName(); |
| String createTable = |
| "CREATE TABLE IF NOT EXISTS " |
| + tableName2 |
| + " (user_time UNSIGNED_TIMESTAMP NOT NULL,user_id varchar NOT NULL,col1 varchar,col2 double," |
| + "CONSTRAINT pk PRIMARY KEY(user_time,user_id)) SALT_BUCKETS = 20"; |
| conn1.createStatement().execute(createTable); |
| conn1.createStatement().execute( |
| "CREATE local INDEX IF NOT EXISTS " + indexName2 + " on " + tableName2 |
| + "(\"HOUR\"(user_time))"); |
| conn1.createStatement().execute( |
| "upsert into " + tableName2 + " values(TO_TIME('2005-10-01 14:03:22.559'), 'foo')"); |
| conn1.commit(); |
| ResultSet rs = |
| conn1.createStatement() |
| .executeQuery( |
| "select substr(to_char(user_time), 0, 10) as ddate, \"HOUR\"(user_time) as hhour, user_id, col1,col2 from " |
| + tableName2 |
| + " where \"HOUR\"(user_time)=14 group by user_id, col1, col2, ddate, hhour limit 1"); |
| assertTrue(rs.next()); |
| } |
| |
| @Test |
| public void testCreationOfTableWithLocalIndexColumnFamilyPrefixShouldFail() throws Exception { |
| Connection conn1 = DriverManager.getConnection(getUrl()); |
| try { |
| conn1.createStatement().execute("CREATE TABLE T(L#a varchar primary key, aL# integer)"); |
| fail("Column families specified in the table creation should not have local colunm prefix."); |
| } catch (SQLException e) { } |
| } |
| |
| @Test |
| public void testLocalIndexCreationWithSplitsShouldFail() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| |
| createBaseTable(tableName, null, null); |
| Connection conn1 = getConnection(); |
| Connection conn2 = getConnection(); |
| try { |
| conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)"+" split on (1,2,3)"); |
| fail("Local index cannot be pre-split"); |
| } catch (SQLException e) { } |
| try { |
| conn2.createStatement().executeQuery("SELECT * FROM " + tableName).next(); |
| conn2.unwrap(PhoenixConnection.class).getTable(new PTableKey(null,indexName)); |
| fail("Local index should not be created."); |
| } catch (TableNotFoundException e) { } |
| } |
| |
| @Test |
| public void testLocalIndexCreationWithSaltingShouldFail() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| |
| createBaseTable(tableName, null, null); |
| Connection conn1 = getConnection(); |
| Connection conn2 = getConnection(); |
| try { |
| conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)"+" salt_buckets=16"); |
| fail("Local index cannot be salted."); |
| } catch (SQLException e) { } |
| try { |
| conn2.createStatement().executeQuery("SELECT * FROM " + tableName).next(); |
| conn2.unwrap(PhoenixConnection.class).getTable(new PTableKey(null,indexName)); |
| fail("Local index should not be created."); |
| } catch (TableNotFoundException e) { } |
| } |
| |
| @Test |
| public void testLocalIndexTableRegionSplitPolicyAndSplitKeys() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| TableName physicalTableName = SchemaUtil.getPhysicalTableName(tableName.getBytes(), isNamespaceMapped); |
| String indexPhysicalTableName = physicalTableName.getNameAsString(); |
| |
| createBaseTable(tableName, null,"('e','i','o')"); |
| Connection conn1 = getConnection(); |
| Connection conn2 = getConnection(); |
| conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)"); |
| conn2.createStatement().executeQuery("SELECT * FROM " + tableName).next(); |
| HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(), TestUtil.TEST_PROPERTIES).getAdmin(); |
| HTableDescriptor htd = admin |
| .getTableDescriptor(Bytes.toBytes(indexPhysicalTableName)); |
| assertEquals(IndexRegionSplitPolicy.class.getName(), htd.getValue(HTableDescriptor.SPLIT_POLICY)); |
| try (HTable userTable = new HTable(admin.getConfiguration(), |
| SchemaUtil.getPhysicalTableName(tableName.getBytes(), isNamespaceMapped))) { |
| try (HTable indexTable = new HTable(admin.getConfiguration(), Bytes.toBytes(indexPhysicalTableName))) { |
| assertArrayEquals("Both user table and index table should have same split keys.", |
| userTable.getStartKeys(), indexTable.getStartKeys()); |
| } |
| } |
| } |
| |
| @Test |
| public void testDropLocalIndexTable() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| createBaseTable(tableName, null, null); |
| |
| String sequenceName = getViewIndexSequenceName(PNameFactory.newName(tableName), null, isNamespaceMapped); |
| String sequenceSchemaName = getViewIndexSequenceSchemaName(PNameFactory.newName(tableName), isNamespaceMapped); |
| |
| Connection conn1 = getConnection(); |
| Connection conn2 = getConnection(); |
| conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)"); |
| verifySequenceValue(null, sequenceName, sequenceSchemaName,-32767); |
| conn2.createStatement().executeQuery("SELECT * FROM " + tableName).next(); |
| conn1.createStatement().execute("DROP TABLE "+ tableName); |
| verifySequenceNotExists(null, sequenceName, sequenceSchemaName); |
| } |
| |
| @Test |
| public void testPutsToLocalIndexTable() 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(); |
| conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)"); |
| conn1.createStatement().execute("UPSERT INTO "+tableName+" values('b',1,2,4,'z')"); |
| conn1.createStatement().execute("UPSERT INTO "+tableName+" values('f',1,2,3,'z')"); |
| 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(); |
| ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + indexTableName); |
| assertTrue(rs.next()); |
| assertEquals(4, rs.getInt(1)); |
| HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(), TestUtil.TEST_PROPERTIES).getAdmin(); |
| HTable indexTable = new HTable(admin.getConfiguration(), indexPhysicalTableName); |
| Pair<byte[][], byte[][]> startEndKeys = indexTable.getStartEndKeys(); |
| byte[][] startKeys = startEndKeys.getFirst(); |
| byte[][] endKeys = startEndKeys.getSecond(); |
| for (int i = 0; i < startKeys.length; i++) { |
| Scan s = new Scan(); |
| s.addFamily(QueryConstants.DEFAULT_LOCAL_INDEX_COLUMN_FAMILY_BYTES); |
| s.setStartRow(startKeys[i]); |
| s.setStopRow(endKeys[i]); |
| ResultScanner scanner = indexTable.getScanner(s); |
| int count = 0; |
| for(Result r:scanner){ |
| count++; |
| } |
| scanner.close(); |
| assertEquals(1, count); |
| } |
| indexTable.close(); |
| } |
| |
| @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(); |
| } |
| } |
| |
| @Test |
| public void testIndexPlanSelectionIfBothGlobalAndLocalIndexesHasSameColumnsAndOrder() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| String indexTableName = schemaName + "." + indexName; |
| |
| createBaseTable(tableName, null, "('e','i','o')"); |
| Connection conn1 = getConnection(); |
| 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,3,'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)"); |
| conn1.createStatement().execute("CREATE INDEX " + indexName + "2" + " ON " + tableName + "(v1)"); |
| String query = "SELECT t_id, k1, k2,V1 FROM " + tableName +" where v1='a'"; |
| ResultSet rs1 = conn1.createStatement().executeQuery("EXPLAIN "+ query); |
| assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER " |
| + SchemaUtil.getPhysicalTableName(Bytes.toBytes(indexTableName), isNamespaceMapped) + "2" + " ['a']\n" |
| + " SERVER FILTER BY FIRST KEY ONLY", QueryUtil.getExplainPlan(rs1)); |
| conn1.close(); |
| } |
| |
| |
| @Test |
| public void testDropLocalIndexShouldDeleteDataFromLocalIndexTable() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| |
| createBaseTable(tableName, null, "('e','i','o')"); |
| Connection conn1 = DriverManager.getConnection(getUrl()); |
| 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)"); |
| conn1.createStatement().execute("DROP INDEX " + indexName + " ON " + tableName); |
| HBaseAdmin admin = driver.getConnectionQueryServices(getUrl(), TestUtil.TEST_PROPERTIES).getAdmin(); |
| HTable table = new HTable(admin.getConfiguration() ,TableName.valueOf(tableName)); |
| Pair<byte[][], byte[][]> startEndKeys = table.getStartEndKeys(); |
| byte[][] startKeys = startEndKeys.getFirst(); |
| byte[][] endKeys = startEndKeys.getSecond(); |
| // No entry should be present in local index table after drop index. |
| for (int i = 0; i < startKeys.length; i++) { |
| Scan s = new Scan(); |
| s.setStartRow(startKeys[i]); |
| s.setStopRow(endKeys[i]); |
| Collection<HColumnDescriptor> families = table.getTableDescriptor().getFamilies(); |
| for(HColumnDescriptor cf: families) { |
| if(cf.getNameAsString().startsWith(QueryConstants.LOCAL_INDEX_COLUMN_FAMILY_PREFIX)){ |
| s.addFamily(cf.getName()); |
| } |
| } |
| ResultScanner scanner = table.getScanner(s); |
| int count = 0; |
| for(Result r:scanner){ |
| count++; |
| } |
| scanner.close(); |
| assertEquals(0, count); |
| } |
| table.close(); |
| } finally { |
| conn1.close(); |
| } |
| } |
| |
| @Test |
| public void testLocalIndexRowsShouldBeDeletedWhenUserTableRowsDeleted() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| String indexTableName = schemaName + "." + indexName; |
| |
| createBaseTable(tableName, null, "('e','i','o')"); |
| Connection conn1 = DriverManager.getConnection(getUrl()); |
| 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)"); |
| conn1.createStatement().execute("DELETE FROM " + tableName + " where v1='a'"); |
| conn1.commit(); |
| conn1 = DriverManager.getConnection(getUrl()); |
| ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + indexTableName); |
| assertTrue(rs.next()); |
| assertEquals(2, rs.getInt(1)); |
| } finally { |
| conn1.close(); |
| } |
| } |
| |
| @Test |
| public void testLocalIndexesOnTableWithImmutableRows() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| |
| createBaseTable(tableName, null, "('e','i','o')"); |
| Connection conn1 = getConnection(); |
| try { |
| conn1.createStatement().execute("ALTER TABLE "+ tableName + " SET IMMUTABLE_ROWS=true"); |
| conn1.createStatement().execute("CREATE LOCAL INDEX " + indexName + " ON " + tableName + "(v1)"); |
| conn1.createStatement().execute("CREATE INDEX " + indexName + "2 ON " + tableName + "(k3)"); |
| conn1.commit(); |
| 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 = DriverManager.getConnection(getUrl()); |
| ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(4, rs.getInt(1)); |
| rs = conn1.createStatement().executeQuery("SELECT v1 FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals("a", rs.getString("v1")); |
| assertTrue(rs.next()); |
| assertEquals("a", rs.getString("v1")); |
| assertTrue(rs.next()); |
| assertEquals("c", rs.getString("v1")); |
| assertTrue(rs.next()); |
| assertEquals("z", rs.getString("v1")); |
| assertFalse(rs.next()); |
| rs = conn1.createStatement().executeQuery("SELECT k3 FROM " + tableName); |
| assertTrue(rs.next()); |
| assertEquals(1, rs.getInt("k3")); |
| assertTrue(rs.next()); |
| assertEquals(2, rs.getInt("k3")); |
| assertTrue(rs.next()); |
| assertEquals(3, rs.getInt("k3")); |
| assertTrue(rs.next()); |
| assertEquals(4, rs.getInt("k3")); |
| assertFalse(rs.next()); |
| } finally { |
| conn1.close(); |
| } |
| } |
| |
| @Test |
| public void testLocalIndexScanWithInList() throws Exception { |
| String tableName = schemaName + "." + generateUniqueName(); |
| String indexName = "IDX_" + generateUniqueName(); |
| String indexTableName = schemaName + "." + indexName; |
| |
| createBaseTable(tableName, null, "('e','i','o')"); |
| Connection conn1 = DriverManager.getConnection(getUrl()); |
| 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) include (k3)"); |
| |
| ResultSet rs = conn1.createStatement().executeQuery("SELECT COUNT(*) FROM " + indexTableName); |
| assertTrue(rs.next()); |
| |
| String query = "SELECT t_id FROM " + tableName +" where (v1,k3) IN (('z',4),('a',2))"; |
| rs = conn1.createStatement().executeQuery(query); |
| assertTrue(rs.next()); |
| assertEquals("j", rs.getString("t_id")); |
| assertTrue(rs.next()); |
| assertEquals("b", rs.getString("t_id")); |
| assertFalse(rs.next()); |
| } finally { |
| conn1.close(); |
| } |
| } |
| |
| @Test |
| public void testLocalIndexCreationWithDefaultFamilyOption() throws Exception { |
| Connection conn1 = DriverManager.getConnection(getUrl()); |
| try{ |
| Statement statement = conn1.createStatement(); |
| String tableName = generateUniqueName(); |
| String indexName = generateUniqueName(); |
| statement.execute("create table " + tableName + " (id integer not null,fn varchar," |
| + "ln varchar constraint pk primary key(id)) DEFAULT_COLUMN_FAMILY='F'"); |
| statement.execute("upsert into " + tableName + " values(1,'fn','ln')"); |
| statement |
| .execute("create local index " + indexName + " on " + tableName + " (fn)"); |
| statement.execute("upsert into " + tableName + " values(2,'fn1','ln1')"); |
| ResultSet rs = statement.executeQuery("SELECT COUNT(*) FROM " + indexName ); |
| assertTrue(rs.next()); |
| } finally { |
| conn1.close(); |
| } |
| } |
| |
| @Test |
| public void testLocalIndexAutomaticRepair() throws Exception { |
| if (isNamespaceMapped) { return; } |
| PhoenixConnection conn = DriverManager.getConnection(getUrl()).unwrap(PhoenixConnection.class); |
| try (HTableInterface metaTable = conn.getQueryServices().getTable(TableName.META_TABLE_NAME.getName()); |
| HBaseAdmin admin = conn.getQueryServices().getAdmin();) { |
| Statement statement = conn.createStatement(); |
| final String tableName = "T_AUTO_MATIC_REPAIR"; |
| String indexName = "IDX_T_AUTO_MATIC_REPAIR"; |
| String indexName1 = "IDX_T_AUTO_MATIC_REPAIR_1"; |
| statement.execute("create table " + tableName + " (id integer not null,fn varchar," |
| + "cf1.ln varchar constraint pk primary key(id)) split on (1,2,3,4,5)"); |
| statement.execute("create local index " + indexName + " on " + tableName + " (fn,cf1.ln)"); |
| statement.execute("create local index " + indexName1 + " on " + tableName + " (fn)"); |
| for (int i = 0; i < 7; i++) { |
| statement.execute("upsert into " + tableName + " values(" + i + ",'fn" + i + "','ln" + i + "')"); |
| } |
| conn.commit(); |
| ResultSet rs = statement.executeQuery("SELECT COUNT(*) FROM " + indexName); |
| assertTrue(rs.next()); |
| assertEquals(7, rs.getLong(1)); |
| List<HRegionInfo> tableRegions = admin.getTableRegions(TableName.valueOf(tableName)); |
| admin.disableTable(tableName); |
| copyLocalIndexHFiles(config, tableRegions.get(0), tableRegions.get(1), false); |
| copyLocalIndexHFiles(config, tableRegions.get(3), tableRegions.get(0), false); |
| |
| admin.enableTable(tableName); |
| |
| int count=getCount(conn, tableName, "L#0"); |
| assertTrue(count > 14); |
| admin.majorCompact(tableName); |
| int tryCount = 5;// need to wait for rebuilding of corrupted local index region |
| while (tryCount-- > 0 && count != 14) { |
| Thread.sleep(30000); |
| count = getCount(conn, tableName, "L#0"); |
| } |
| assertEquals(14, count); |
| rs = statement.executeQuery("SELECT COUNT(*) FROM " + indexName1); |
| assertTrue(rs.next()); |
| assertEquals(7, rs.getLong(1)); |
| } |
| } |
| |
| private void copyLocalIndexHFiles(Configuration conf, HRegionInfo fromRegion, HRegionInfo toRegion, boolean move) |
| throws IOException { |
| Path root = FSUtils.getRootDir(conf); |
| |
| Path seondRegion = new Path(HTableDescriptor.getTableDir(root, fromRegion.getTableName()) + Path.SEPARATOR |
| + fromRegion.getEncodedName() + Path.SEPARATOR + "L#0/"); |
| Path hfilePath = FSUtils.getCurrentFileSystem(conf).listFiles(seondRegion, true).next().getPath(); |
| Path firstRegionPath = new Path(HTableDescriptor.getTableDir(root, toRegion.getTableName()) + Path.SEPARATOR |
| + toRegion.getEncodedName() + Path.SEPARATOR + "L#0/"); |
| FileSystem currentFileSystem = FSUtils.getCurrentFileSystem(conf); |
| assertTrue(FileUtil.copy(currentFileSystem, hfilePath, currentFileSystem, firstRegionPath, move, conf)); |
| } |
| |
| private int getCount(PhoenixConnection conn, String tableName, String columnFamily) |
| throws IOException, SQLException { |
| Iterator<Result> iterator = conn.getQueryServices().getTable(Bytes.toBytes(tableName)) |
| .getScanner(Bytes.toBytes(columnFamily)).iterator(); |
| int count = 0; |
| while (iterator.hasNext()) { |
| iterator.next(); |
| count++; |
| } |
| return count; |
| } |
| |
| |
| } |
