@Test public void testDynamicPartitionsMerge() throws Exception { d.destroy(); hiveConf.setVar(HiveConf.ConfVars.DYNAMICPARTITIONINGMODE, "nonstrict"); runStatementOnDriver("insert into " + Table.ACIDTBLPART + " partition(p) values(1,1,'p1'),(2,2,'p1'),(3,3,'p1'),(4,4,'p2')"); List<String> r1 = runStatementOnDriver("select count(*) from " + Table.ACIDTBLPART); Assert.assertEquals("4", r1.get(0)); int[][] sourceVals = {{2,15},{4,44},{5,5},{11,11}}; runStatementOnDriver("insert into " + Table.NONACIDORCTBL + " " + makeValuesClause(sourceVals)); runStatementOnDriver("merge into " + Table.ACIDTBLPART + " using " + Table.NONACIDORCTBL + " as s ON " + Table.ACIDTBLPART + ".a = s.a " + "when matched then update set b = s.b " + "when not matched then insert values(s.a, s.b, 'new part')"); r1 = runStatementOnDriver("select p,a,b from " + Table.ACIDTBLPART + " order by p, a, b"); String result= r1.toString(); Assert.assertEquals("[new part\t5\t5, new part\t11\t11, p1\t1\t1, p1\t2\t15, p1\t3\t3, p2\t4\t44]", result); //note: inserts go into 'new part'... so this won't fail assertUniqueID(Table.ACIDTBLPART); } /**
@Test public void testMultiInsert() throws Exception { runStatementOnDriver("create temporary table if not exists data1 (x int)"); runStatementOnDriver("insert into data1 values (1),(2),(1)"); d.destroy(); hiveConf.setVar(HiveConf.ConfVars.DYNAMICPARTITIONINGMODE, "nonstrict"); d = new Driver(hiveConf); runStatementOnDriver(" from data1 " + "insert into " + Table.ACIDTBLPART + " partition(p) select 0, 0, 'p' || x " + "insert into " + Table.ACIDTBLPART + " partition(p='p1') select 0, 1"); /** * Using {@link BucketCodec.V0} the output * is missing 1 of the (p1,0,1) rows because they have the same ROW__ID and only differ by * StatementId so {@link org.apache.hadoop.hive.ql.io.orc.OrcRawRecordMerger} skips one. * With split update (and V0), the data is read correctly (insert deltas are now the base) but we still * should get duplicate ROW__IDs. */ List<String> r = runStatementOnDriver("select p,a,b from " + Table.ACIDTBLPART + " order by p, a, b"); Assert.assertEquals("[p1\t0\t0, p1\t0\t0, p1\t0\t1, p1\t0\t1, p1\t0\t1, p2\t0\t0]", r.toString()); assertUniqueID(Table.ACIDTBLPART); /** * this delete + select covers VectorizedOrcAcidRowBatchReader */ runStatementOnDriver("delete from " + Table.ACIDTBLPART); r = runStatementOnDriver("select p,a,b from " + Table.ACIDTBLPART + " order by p, a, b"); Assert.assertEquals("[]", r.toString()); } /**
/** * Using nested partitions and thus DummyPartition * @throws Exception */ @Test public void testDynamicPartitionsMerge2() throws Exception { d.destroy(); hiveConf.setVar(HiveConf.ConfVars.DYNAMICPARTITIONINGMODE, "nonstrict"); int[][] targetVals = {{1,1,1},{2,2,2},{3,3,1},{4,4,2}}; runStatementOnDriver("insert into " + Table.ACIDNESTEDPART + " partition(p=1,q) " + makeValuesClause(targetVals)); List<String> r1 = runStatementOnDriver("select count(*) from " + Table.ACIDNESTEDPART); Assert.assertEquals("4", r1.get(0)); int[][] sourceVals = {{2,15},{4,44},{5,5},{11,11}}; runStatementOnDriver("insert into " + Table.NONACIDORCTBL + " " + makeValuesClause(sourceVals)); runStatementOnDriver("merge into " + Table.ACIDNESTEDPART + " using " + Table.NONACIDORCTBL + " as s ON " + Table.ACIDNESTEDPART + ".a = s.a " + "when matched then update set b = s.b " + "when not matched then insert values(s.a, s.b, 3,4)"); r1 = runStatementOnDriver("select p,q,a,b from " + Table.ACIDNESTEDPART + " order by p,q, a, b"); Assert.assertEquals(stringifyValues(new int[][] {{1,1,1,1},{1,1,3,3},{1,2,2,15},{1,2,4,44},{3,4,5,5},{3,4,11,11}}), r1); //insert of merge lands in part (3,4) - no updates land there assertUniqueID(Table.ACIDNESTEDPART); } @Ignore("Covered elsewhere")
@Test public void testMergeWithPredicate() throws Exception { int[][] baseValsOdd = {{2,2},{5,5},{8,8},{11,11}}; runStatementOnDriver("insert into " + Table.NONACIDPART2 + " PARTITION(p2='odd') " + makeValuesClause(baseValsOdd)); int[][] vals = {{2,1},{4,3},{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + " " + makeValuesClause(vals)); List<String> r = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals(stringifyValues(vals), r); String query = "merge into " + Table.ACIDTBL + " t using " + Table.NONACIDPART2 + " s ON t.a = s.a2 " + "WHEN MATCHED AND t.b between 1 and 3 THEN UPDATE set b = s.b2 " + "WHEN NOT MATCHED and s.b2 >= 8 THEN INSERT VALUES(s.a2, s.b2)"; runStatementOnDriver(query); r = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); int[][] rExpected = {{2,2},{4,3},{5,6},{7,8},{8,8},{11,11}}; Assert.assertEquals(stringifyValues(rExpected), r); assertUniqueID(Table.ACIDTBL); }
/** * Test combines update + insert clauses * @throws Exception */ @Test public void testMerge2() throws Exception { int[][] baseValsOdd = {{5,5},{11,11}}; int[][] baseValsEven = {{2,2},{4,44}}; runStatementOnDriver("insert into " + Table.NONACIDPART2 + " PARTITION(p2='odd') " + makeValuesClause(baseValsOdd)); runStatementOnDriver("insert into " + Table.NONACIDPART2 + " PARTITION(p2='even') " + makeValuesClause(baseValsEven)); int[][] vals = {{2,1},{4,3},{5,6},{7,8}}; runStatementOnDriver("insert into " + Table.ACIDTBL + " " + makeValuesClause(vals)); List<String> r = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); Assert.assertEquals(stringifyValues(vals), r); String query = "merge into " + Table.ACIDTBL + " using " + Table.NONACIDPART2 + " source ON " + Table.ACIDTBL + ".a = source.a2 " + "WHEN MATCHED THEN UPDATE set b = source.b2 " + "WHEN NOT MATCHED THEN INSERT VALUES(source.a2, source.b2) ";//AND b < 1 r = runStatementOnDriver(query); //r = runStatementOnDriver("explain " + query); //logResuts(r, "Explain logical1", ""); r = runStatementOnDriver("select a,b from " + Table.ACIDTBL + " order by a,b"); int[][] rExpected = {{2,2},{4,44},{5,5},{7,8},{11,11}}; Assert.assertEquals(stringifyValues(rExpected), r); assertUniqueID(Table.ACIDTBL); }