@Test public void testMergeUpdateDelete() throws Exception { int[][] baseValsOdd = {{2,2},{4,44},{5,5},{11,11}}; runStatementOnDriver("insert into " + Table.NONACIDORCTBL + " " + makeValuesClause(baseValsOdd)); int[][] vals = {{2,1},{4,3},{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + " " + makeValuesClause(vals)); String query = "merge into " + Table.ACIDTBL + " as t using " + Table.NONACIDORCTBL + " s ON t.a = s.a " + "WHEN MATCHED AND s.a < 3 THEN update set b = 0 " + //updates (2,1) -> (2,0) "WHEN MATCHED and t.a > 3 and t.a < 5 THEN DELETE " +//deletes (4,3) "WHEN NOT MATCHED THEN INSERT VALUES(s.a, s.b) ";//inserts (11,11) runStatementOnDriver(query); List<String> r = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); int[][] rExpected = {{2,0},{5,6},{7,8},{11,11}}; Assert.assertEquals(stringifyValues(rExpected), r); } @Test
@Test public void testMergeDeleteUpdate() throws Exception { int[][] sourceVals = {{2,2},{4,44},{5,5},{11,11}}; runStatementOnDriver("insert into " + Table.NONACIDORCTBL + " " + makeValuesClause(sourceVals)); int[][] targetVals = {{2,1},{4,3},{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + " " + makeValuesClause(targetVals)); String query = "merge into " + Table.ACIDTBL + " as t using " + Table.NONACIDORCTBL + " s ON t.a = s.a " + "WHEN MATCHED and s.a < 5 THEN DELETE " + "WHEN MATCHED AND s.a < 3 THEN update set b = 0 " + "WHEN NOT MATCHED THEN INSERT VALUES(s.a, s.b) "; runStatementOnDriver(query); List<String> r = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); int[][] rExpected = {{5,6},{7,8},{11,11}}; Assert.assertEquals(stringifyValues(rExpected), r); }
/** * see https://issues.apache.org/jira/browse/HIVE-14949 for details * @throws Exception */ @Test public void testMergeCardinalityViolation() throws Exception { int[][] sourceVals = {{2,2},{2,44},{5,5},{11,11}}; runStatementOnDriver("insert into " + Table.NONACIDORCTBL + " " + makeValuesClause(sourceVals)); int[][] targetVals = {{2,1},{4,3},{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + " " + makeValuesClause(targetVals)); String query = "merge into " + Table.ACIDTBL + " as t using " + Table.NONACIDORCTBL + " s ON t.a = s.a " + "WHEN MATCHED and s.a < 5 THEN DELETE " + "WHEN MATCHED AND s.a < 3 THEN update set b = 0 " + "WHEN NOT MATCHED THEN INSERT VALUES(s.a, s.b) "; runStatementOnDriverNegative(query); runStatementOnDriver("insert into " + Table.ACIDTBLPART + " partition(p) values(1,1,'p1'),(2,2,'p1'),(3,3,'p1'),(4,4,'p2')"); query = "merge into " + Table.ACIDTBLPART + " as t using " + Table.NONACIDORCTBL + " s ON t.a = s.a " + "WHEN MATCHED and s.a < 5 THEN DELETE " + "WHEN MATCHED AND s.a < 3 THEN update set b = 0 " + "WHEN NOT MATCHED THEN INSERT VALUES(s.a, s.b, 'p1') "; runStatementOnDriverNegative(query); } @Test
@Test public void testMultipleInserts() throws Exception { runStatementOnDriver("START TRANSACTION"); int[][] rows1 = {{1,2},{3,4}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows1)); int[][] rows2 = {{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows2)); List<String> allData = stringifyValues(rows1); allData.addAll(stringifyValues(rows2)); List<String> rs = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Content didn't match before commit rs", allData, rs); runStatementOnDriver("commit"); dumpTableData(Table.ACIDTBL, 1, 0); dumpTableData(Table.ACIDTBL, 1, 1); List<String> rs1 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Content didn't match after commit rs1", allData, rs1); } @Test
int[][] data = {{1, 2}}; runStatementOnDriver("insert into T" + makeValuesClause(data)); AcidUtils.OrcAcidVersion.ORC_ACID_VERSION, versionFromMetaFile); runStatementOnDriver("insert into T" + makeValuesClause(data)); runStatementOnDriver("alter table T compact 'major'"); TestTxnCommands2.runWorker(hiveConf);
tableName, makeValuesClause(rows1))); runStatementOnDriver(String.format("insert into %s (a,b) %s", tableName, makeValuesClause(rows1))); IMetaStoreClient msClient = new HiveMetaStoreClient(hiveConf); org.apache.hadoop.hive.metastore.api.Table table = msClient.getTable("default", tableName); tableName, makeValuesClause(rows2))); fs.delete(exportPath, true); runStatementOnDriver(String.format("export table %s to '%s'", tableName, exportPath));
@Test public void testMergeUpdateDeleteNoCardCheck() throws Exception { d.destroy(); HiveConf hc = new HiveConf(hiveConf); hc.setBoolVar(HiveConf.ConfVars.MERGE_CARDINALITY_VIOLATION_CHECK, false); d = new Driver(hc); d.setMaxRows(10000); int[][] baseValsOdd = {{2,2},{4,44},{5,5},{11,11}}; runStatementOnDriver("insert into " + Table.NONACIDORCTBL + " " + makeValuesClause(baseValsOdd)); int[][] vals = {{2,1},{4,3},{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + " " + makeValuesClause(vals)); String query = "merge into " + Table.ACIDTBL + " as t using " + Table.NONACIDORCTBL + " s ON t.a = s.a " + "WHEN MATCHED AND s.a < 3 THEN update set b = 0 " + "WHEN MATCHED and t.a > 3 and t.a < 5 THEN DELETE "; runStatementOnDriver(query); List<String> r = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); int[][] rExpected = {{2,0},{5,6},{7,8}}; Assert.assertEquals(stringifyValues(rExpected), r); } @Test
runStatementOnDriver("create table target (key int, data int, cur int) clustered by (key) into " + BUCKET_COUNT + " buckets stored as orc TBLPROPERTIES ('transactional'='true')"); int[][] targetVals = {{1, 5, 1}, {2, 6, 1}, {1, 18, 0}}; runStatementOnDriver("insert into target " + makeValuesClause(targetVals)); int[][] sourceVals = {{1, 7}, {3, 8}}; runStatementOnDriver("insert into source " + makeValuesClause(sourceVals));
@Test public void testSimpleAcidInsert() throws Exception { int[][] rows1 = {{1,2},{3,4}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows1)); //List<String> rs = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); //Assert.assertEquals("Data didn't match in autocommit=true (rs)", stringifyValues(rows1), rs); runStatementOnDriver("START TRANSACTION"); int[][] rows2 = {{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows2)); List<String> allData = stringifyValues(rows1); allData.addAll(stringifyValues(rows2)); List<String> rs0 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Data didn't match inside tx (rs0)", allData, rs0); runStatementOnDriver("COMMIT WORK"); dumpTableData(Table.ACIDTBL, 1, 0); dumpTableData(Table.ACIDTBL, 2, 0); runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); CommandProcessorResponse cpr = runStatementOnDriverNegative("COMMIT");//txn started implicitly by previous statement Assert.assertEquals("Error didn't match: " + cpr, ErrorMsg.OP_NOT_ALLOWED_WITHOUT_TXN.getErrorCode(), cpr.getErrorCode()); List<String> rs1 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Data didn't match inside tx (rs0)", allData, rs1); }
@Test public void testDeleteIn() throws Exception { runStatementOnDriver("delete from " + Table.ACIDTBL + " where a IN (SELECT A.a from " + Table.ACIDTBL + " A)"); int[][] tableData = {{1,2},{3,2},{5,2},{1,3},{3,3},{5,3}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(tableData)); runStatementOnDriver("insert into " + Table.ACIDTBL2 + "(a,b,c) values(1,7,17),(3,7,17)"); // runStatementOnDriver("select b from " + Table.ACIDTBL + " where a in (select b from " + Table.NONACIDORCTBL + ")"); runStatementOnDriver("delete from " + Table.ACIDTBL + " where a in(select a from " + Table.ACIDTBL2 + ")"); // runStatementOnDriver("delete from " + Table.ACIDTBL + " where a in(select a from " + Table.NONACIDORCTBL + ")"); runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) select a,b from " + Table.ACIDTBL2); List<String> rs = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); int[][] updatedData = {{1,7},{3,7},{5,2},{5,3}}; Assert.assertEquals("Bulk update failed", stringifyValues(updatedData), rs); } @Test
@Test public void testUpdateOfInserts() throws Exception { int[][] rows1 = {{1,2},{3,4}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows1)); List<String> rs0 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Content didn't match rs0", stringifyValues(rows1), rs0); runStatementOnDriver("START TRANSACTION"); int[][] rows2 = {{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows2)); List<String> rs1 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); List<String> allData = stringifyValues(rows1); allData.addAll(stringifyValues(rows2)); Assert.assertEquals("Content didn't match rs1", allData, rs1); runStatementOnDriver("update " + Table.ACIDTBL + " set b = 1 where b != 1"); int[][] updatedData = {{1,1},{3,1},{5,1},{7,1}}; List<String> rs2 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after update", stringifyValues(updatedData), rs2); runStatementOnDriver("commit"); List<String> rs4 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after commit", stringifyValues(updatedData), rs4); } @Test
runStatementOnDriver("create table target (key int, data int, cur int) clustered by (key) into " + BUCKET_COUNT + " buckets stored as orc TBLPROPERTIES ('transactional'='true')"); int[][] targetVals = {{1, 5, 1}, {2, 6, 1}, {1, 18, 0}}; runStatementOnDriver("insert into target " + makeValuesClause(targetVals)); int[][] sourceVals = {{1, 7}, {3, 8}}; runStatementOnDriver("insert into source " + makeValuesClause(sourceVals));
@Test public void testDelete() throws Exception { int[][] rows1 = {{1,2},{3,4}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows1)); List<String> rs0 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Content didn't match rs0", stringifyValues(rows1), rs0); runStatementOnDriver("START TRANSACTION"); runStatementOnDriver("delete from " + Table.ACIDTBL + " where b = 4"); int[][] updatedData2 = {{1,2}}; List<String> rs3 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after delete", stringifyValues(updatedData2), rs3); runStatementOnDriver("commit"); List<String> rs4 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after commit", stringifyValues(updatedData2), rs4); }
@Test public void testUpdateDeleteOfInserts() throws Exception { int[][] rows1 = {{1,2},{3,4}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows1)); List<String> rs0 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Content didn't match rs0", stringifyValues(rows1), rs0); runStatementOnDriver("START TRANSACTION"); int[][] rows2 = {{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows2)); List<String> rs1 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); List<String> allData = stringifyValues(rows1); allData.addAll(stringifyValues(rows2)); Assert.assertEquals("Content didn't match rs1", allData, rs1); runStatementOnDriver("update " + Table.ACIDTBL + " set b = 1 where b != 1"); int[][] updatedData = {{1,1},{3,1},{5,1},{7,1}}; List<String> rs2 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after update", stringifyValues(updatedData), rs2); runStatementOnDriver("delete from " + Table.ACIDTBL + " where a = 7 and b = 1"); dumpTableData(Table.ACIDTBL, 1, 0); dumpTableData(Table.ACIDTBL, 2, 0); dumpTableData(Table.ACIDTBL, 2, 2); dumpTableData(Table.ACIDTBL, 2, 4); int[][] updatedData2 = {{1,1},{3,1},{5,1}}; List<String> rs3 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after delete", stringifyValues(updatedData2), rs3); runStatementOnDriver("commit"); List<String> rs4 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after commit", stringifyValues(updatedData2), rs4); } @Test
@Test public void testMultipleDelete() throws Exception { int[][] rows1 = {{1,2},{3,4},{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + "(a,b) " + makeValuesClause(rows1)); List<String> rs0 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Content didn't match rs0", stringifyValues(rows1), rs0); runStatementOnDriver("START TRANSACTION"); runStatementOnDriver("delete from " + Table.ACIDTBL + " where b = 8"); int[][] updatedData2 = {{1,2},{3,4},{5,6}}; List<String> rs2 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after delete", stringifyValues(updatedData2), rs2); runStatementOnDriver("delete from " + Table.ACIDTBL + " where b = 4"); int[][] updatedData3 = {{1, 2}, {5, 6}}; List<String> rs3 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after delete2", stringifyValues(updatedData3), rs3); runStatementOnDriver("update " + Table.ACIDTBL + " set b=3"); dumpTableData(Table.ACIDTBL, 1, 0); //nothing actually hashes to bucket0, so update/delete deltas don't have it dumpTableData(Table.ACIDTBL, 2, 0); dumpTableData(Table.ACIDTBL, 2, 2); dumpTableData(Table.ACIDTBL, 2, 4); List<String> rs5 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); int [][] updatedData4 = {{1,3},{5,3}}; Assert.assertEquals("Wrong data after delete", stringifyValues(updatedData4), rs5); runStatementOnDriver("commit"); List<String> rs4 = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals("Wrong data after commit", stringifyValues(updatedData4), rs4); } @Test