Author: ekoifman Date: Fri Feb 13 18:27:13 2015 New Revision: 1659640 URL: http://svn.apache.org/r1659640 Log: HIVE-9481 allow column list specification in INSERT statement
Added: hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema.q hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema1.q hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema2.q hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema3.q hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema4.q hive/trunk/ql/src/test/queries/clientpositive/insert_into_with_schema.q hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema.q.out hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema1.q.out hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema2.q.out hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema3.q.out hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema4.q.out hive/trunk/ql/src/test/results/clientpositive/insert_into_with_schema.q.out Modified: hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBMetaData.java hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBParseInfo.java hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java hive/trunk/ql/src/test/org/apache/hadoop/hive/ql/parse/TestIUD.java Modified: hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g?rev=1659640&r1=1659639&r2=1659640&view=diff ============================================================================== --- hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g (original) +++ hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/HiveParser.g Fri Feb 13 18:27:13 2015 @@ -2284,8 +2284,8 @@ insertClause @after { popMsg(state); } : KW_INSERT KW_OVERWRITE destination ifNotExists? -> ^(TOK_DESTINATION destination ifNotExists?) - | KW_INSERT KW_INTO KW_TABLE? tableOrPartition - -> ^(TOK_INSERT_INTO tableOrPartition) + | KW_INSERT KW_INTO KW_TABLE? tableOrPartition (LPAREN targetCols=columnNameList RPAREN)? + -> ^(TOK_INSERT_INTO tableOrPartition $targetCols?) ; destination Modified: hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBMetaData.java URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBMetaData.java?rev=1659640&r1=1659639&r2=1659640&view=diff ============================================================================== --- hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBMetaData.java (original) +++ hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBMetaData.java Fri Feb 13 18:27:13 2015 @@ -103,6 +103,9 @@ public class QBMetaData { return nameToDestType.get(alias.toLowerCase()); } + /** + * @param alias this is actually dest name, like insclause-0 + */ public Table getDestTableForAlias(String alias) { return nameToDestTable.get(alias.toLowerCase()); } Modified: hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBParseInfo.java URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBParseInfo.java?rev=1659640&r1=1659639&r2=1659640&view=diff ============================================================================== --- hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBParseInfo.java (original) +++ hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/QBParseInfo.java Fri Feb 13 18:27:13 2015 @@ -43,7 +43,15 @@ public class QBParseInfo { private ASTNode joinExpr; private ASTNode hints; private final HashMap<String, ASTNode> aliasToSrc; + /** + * insclause-0 -> TOK_TAB ASTNode + */ private final HashMap<String, ASTNode> nameToDest; + /** + * For 'insert into FOO(x,y) select ...' this stores the + * insclause-0 -> x,y mapping + */ + private final Map<String, List<String>> nameToDestSchema; private final HashMap<String, TableSample> nameToSample; private final Map<ASTNode, String> exprToColumnAlias; private final Map<String, ASTNode> destToSelExpr; @@ -111,6 +119,7 @@ public class QBParseInfo { public QBParseInfo(String alias, boolean isSubQ) { aliasToSrc = new HashMap<String, ASTNode>(); nameToDest = new HashMap<String, ASTNode>(); + nameToDestSchema = new HashMap<String, List<String>>(); nameToSample = new HashMap<String, TableSample>(); exprToColumnAlias = new HashMap<ASTNode, String>(); destToLateralView = new HashMap<String, ASTNode>(); @@ -234,6 +243,13 @@ public class QBParseInfo { nameToDest.put(clause, ast); } + List<String> setDestSchemaForClause(String clause, List<String> columnList) { + return nameToDestSchema.put(clause, columnList); + } + List<String> getDestSchemaForClause(String clause) { + return nameToDestSchema.get(clause); + } + /** * Set the Cluster By AST for the clause. * Modified: hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java?rev=1659640&r1=1659639&r2=1659640&view=diff ============================================================================== --- hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java (original) +++ hive/trunk/ql/src/java/org/apache/hadoop/hive/ql/parse/SemanticAnalyzer.java Fri Feb 13 18:27:13 2015 @@ -1176,7 +1176,7 @@ public class SemanticAnalyzer extends Ba } qbp.setDestForClause(ctx_1.dest, (ASTNode) ast.getChild(0)); - + handleInsertStatementSpecPhase1(ast, qbp, ctx_1); if (qbp.getClauseNamesForDest().size() > 1) { queryProperties.setMultiDestQuery(true); } @@ -1406,6 +1406,96 @@ public class SemanticAnalyzer extends Ba return phase1Result; } + /** + * This is phase1 of supporting specifying schema in insert statement + * insert into foo(z,y) select a,b from bar; + * @see #handleInsertStatementSpec(java.util.List, String, RowResolver, RowResolver, QB, ASTNode) + * @throws SemanticException + */ + private void handleInsertStatementSpecPhase1(ASTNode ast, QBParseInfo qbp, Phase1Ctx ctx_1) throws SemanticException { + ASTNode tabColName = (ASTNode)ast.getChild(1); + if(ast.getType() == HiveParser.TOK_INSERT_INTO && tabColName != null && tabColName.getType() == HiveParser.TOK_TABCOLNAME) { + //we have "insert into foo(a,b)..."; parser will enforce that 1+ columns are listed if TOK_TABCOLNAME is present + List<String> targetColNames = new ArrayList<String>(); + for(Node col : tabColName.getChildren()) { + assert ((ASTNode)col).getType() == HiveParser.Identifier : + "expected token " + HiveParser.Identifier + " found " + ((ASTNode)col).getType(); + targetColNames.add(((ASTNode)col).getText()); + } + String fullTableName = getUnescapedName((ASTNode) ast.getChild(0).getChild(0), + SessionState.get().getCurrentDatabase()); + qbp.setDestSchemaForClause(ctx_1.dest, targetColNames); + Set<String> targetColumns = new HashSet<String>(); + targetColumns.addAll(targetColNames); + if(targetColNames.size() != targetColumns.size()) { + throw new SemanticException(generateErrorMessage(tabColName, + "Duplicate column name detected in " + fullTableName + " table schema specification")); + } + Table targetTable = null; + try { + targetTable = db.getTable(fullTableName, false); + } + catch (HiveException ex) { + LOG.error("Error processing HiveParser.TOK_DESTINATION: " + ex.getMessage(), ex); + throw new SemanticException(ex); + } + if(targetTable == null) { + throw new SemanticException(generateErrorMessage(ast, + "Unable to access metadata for table " + fullTableName)); + } + for(FieldSchema f : targetTable.getCols()) { + //parser only allows foo(a,b), not foo(foo.a, foo.b) + targetColumns.remove(f.getName()); + } + if(!targetColumns.isEmpty()) {//here we need to see if remaining columns are dynamic partition columns + /* We just checked the user specified schema columns among regular table column and found some which are not + 'regular'. Now check is they are dynamic partition columns + For dynamic partitioning, + Given "create table multipart(a int, b int) partitioned by (c int, d int);" + for "insert into multipart partition(c='1',d)(d,a) values(2,3);" we expect parse tree to look like this + (TOK_INSERT_INTO + (TOK_TAB + (TOK_TABNAME multipart) + (TOK_PARTSPEC + (TOK_PARTVAL c '1') + (TOK_PARTVAL d) + ) + ) + (TOK_TABCOLNAME d a) + )*/ + List<String> dynamicPartitionColumns = new ArrayList<String>(); + if(ast.getChild(0) != null && ast.getChild(0).getType() == HiveParser.TOK_TAB) { + ASTNode tokTab = (ASTNode)ast.getChild(0); + ASTNode tokPartSpec = (ASTNode)tokTab.getFirstChildWithType(HiveParser.TOK_PARTSPEC); + if(tokPartSpec != null) { + for(Node n : tokPartSpec.getChildren()) { + ASTNode tokPartVal = null; + if(n instanceof ASTNode) { + tokPartVal = (ASTNode)n; + } + if(tokPartVal != null && tokPartVal.getType() == HiveParser.TOK_PARTVAL && tokPartVal.getChildCount() == 1) { + assert tokPartVal.getChild(0).getType() == HiveParser.Identifier : + "Expected column name; found tokType=" + tokPartVal.getType(); + dynamicPartitionColumns.add(tokPartVal.getChild(0).getText()); + } + } + } + } + for(String colName : dynamicPartitionColumns) { + targetColumns.remove(colName); + } + if(!targetColumns.isEmpty()) { + //Found some columns in user specified schema which are neither regular not dynamic partition columns + throw new SemanticException(generateErrorMessage(tabColName, + "'" + (targetColumns.size() == 1 ? targetColumns.iterator().next() : targetColumns) + + "' in insert schema specification " + (targetColumns.size() == 1 ? "is" : "are") + + " not found among regular columns of " + + fullTableName + " nor dynamic partition columns.")); + } + } + } + } + private void getMetaData(QBExpr qbexpr, ReadEntity parentInput) throws SemanticException { if (qbexpr.getOpcode() == QBExpr.Opcode.NULLOP) { @@ -3493,7 +3583,7 @@ public class SemanticAnalyzer extends Ba private Operator<?> genSelectPlan(String dest, QB qb, Operator<?> input, Operator<?> inputForSelectStar) throws SemanticException { ASTNode selExprList = qb.getParseInfo().getSelForClause(dest); - Operator<?> op = genSelectPlan(selExprList, qb, input, inputForSelectStar, false); + Operator<?> op = genSelectPlan(dest, selExprList, qb, input, inputForSelectStar, false); if (LOG.isDebugEnabled()) { LOG.debug("Created Select Plan for clause: " + dest); @@ -3503,7 +3593,7 @@ public class SemanticAnalyzer extends Ba } @SuppressWarnings("nls") - private Operator<?> genSelectPlan(ASTNode selExprList, QB qb, Operator<?> input, + private Operator<?> genSelectPlan(String dest, ASTNode selExprList, QB qb, Operator<?> input, Operator<?> inputForSelectStar, boolean outerLV) throws SemanticException { if (LOG.isDebugEnabled()) { @@ -3741,6 +3831,8 @@ public class SemanticAnalyzer extends Ba } selectStar = selectStar && exprList.getChildCount() == posn + 1; + handleInsertStatementSpec(col_list, dest, out_rwsch, inputRR, qb, selExprList); + ArrayList<String> columnNames = new ArrayList<String>(); Map<String, ExprNodeDesc> colExprMap = new HashMap<String, ExprNodeDesc>(); for (int i = 0; i < col_list.size(); i++) { @@ -3768,6 +3860,100 @@ public class SemanticAnalyzer extends Ba return output; } + /** + * This modifies the Select projections when the Select is part of an insert statement and + * the insert statement specifies a column list for the target table, e.g. + * create table source (a int, b int); + * create table target (x int, y int, z int); + * insert into target(z,x) select * from source + * + * Once the * is resolved to 'a,b', this list needs to rewritten to 'b,null,a' so that it looks + * as if the original query was written as + * insert into target select b, null, a from source + * + * if target schema is not specified, this is no-op + * + * @see #handleInsertStatementSpecPhase1(ASTNode, QBParseInfo, org.apache.hadoop.hive.ql.parse.SemanticAnalyzer.Phase1Ctx) + * @throws SemanticException + */ + private void handleInsertStatementSpec(List<ExprNodeDesc> col_list, String dest, + RowResolver out_rwsch, RowResolver inputRR, QB qb, + ASTNode selExprList) throws SemanticException { + //(z,x) + List<String> targetTableSchema = qb.getParseInfo().getDestSchemaForClause(dest);//specified in the query + if(targetTableSchema == null) { + //no insert schema was specified + return; + } + if(targetTableSchema.size() != col_list.size()) { + Table target = qb.getMetaData().getDestTableForAlias(dest); + Partition partition = target == null ? qb.getMetaData().getDestPartitionForAlias(dest) : null; + throw new SemanticException(generateErrorMessage(selExprList, + "Expected " + targetTableSchema.size() + " columns for " + dest + + (target != null ? "/" + target.getCompleteName() : (partition != null ? "/" + partition.getCompleteName() : "")) + + "; select produces " + col_list.size() + " columns")); + } + //e.g. map z->expr for a + Map<String, ExprNodeDesc> targetCol2Projection = new HashMap<String, ExprNodeDesc>(); + //e.g. map z->ColumnInfo for a + Map<String, ColumnInfo> targetCol2ColumnInfo = new HashMap<String, ColumnInfo>(); + int colListPos = 0; + for(String targetCol : targetTableSchema) { + targetCol2ColumnInfo.put(targetCol, out_rwsch.getColumnInfos().get(colListPos)); + targetCol2Projection.put(targetCol, col_list.get(colListPos++)); + } + Table target = qb.getMetaData().getDestTableForAlias(dest); + Partition partition = target == null ? qb.getMetaData().getDestPartitionForAlias(dest) : null; + if(target == null && partition == null) { + throw new SemanticException(generateErrorMessage(selExprList, + "No table/partition found in QB metadata for dest='" + dest + "'")); + } + ArrayList<ExprNodeDesc> new_col_list = new ArrayList<ExprNodeDesc>(); + ArrayList<ColumnInfo> newSchema = new ArrayList<ColumnInfo>(); + colListPos = 0; + List<FieldSchema> targetTableCols = target != null ? target.getCols() : partition.getCols(); + List<String> targetTableColNames = new ArrayList<String>(); + for(FieldSchema fs : targetTableCols) { + targetTableColNames.add(fs.getName()); + } + Map<String, String> partSpec = qb.getMetaData().getPartSpecForAlias(dest); + if(partSpec != null) { + //find dynamic partition columns + //relies on consistent order via LinkedHashMap + for(Map.Entry<String, String> partKeyVal : partSpec.entrySet()) { + if (partKeyVal.getValue() == null) { + targetTableColNames.add(partKeyVal.getKey());//these must be after non-partition cols + } + } + } + //now make the select produce <regular columns>,<dynamic partition columns> with + //where missing columns are NULL-filled + for(String f : targetTableColNames) { + if(targetCol2Projection.containsKey(f)) { + //put existing column in new list to make sure it is in the right position + new_col_list.add(targetCol2Projection.get(f)); + ColumnInfo ci = targetCol2ColumnInfo.get(f);//todo: is this OK? + ci.setInternalName(getColumnInternalName(colListPos)); + newSchema.add(ci); + } + else { + //add new 'synthetic' columns for projections not provided by Select + TypeCheckCtx tcCtx = new TypeCheckCtx(inputRR); + CommonToken t = new CommonToken(HiveParser.TOK_NULL); + t.setText("TOK_NULL"); + ExprNodeDesc exp = genExprNodeDesc(new ASTNode(t), inputRR, tcCtx); + new_col_list.add(exp); + final String tableAlias = "";//is this OK? this column doesn't come from any table + ColumnInfo colInfo = new ColumnInfo(getColumnInternalName(colListPos), + exp.getWritableObjectInspector(), tableAlias, false); + newSchema.add(colInfo); + } + colListPos++; + } + col_list.clear(); + col_list.addAll(new_col_list); + out_rwsch.setRowSchema(new RowSchema(newSchema)); + } String recommendName(ExprNodeDesc exp, String colAlias) { if (!colAlias.startsWith(autogenColAliasPrfxLbl)) { return null; @@ -9624,7 +9810,7 @@ public class SemanticAnalyzer extends Ba int allColumns = allPathRR.getColumnInfos().size(); // Get the UDTF Path QB blankQb = new QB(null, null, false); - Operator udtfPath = genSelectPlan((ASTNode) lateralViewTree + Operator udtfPath = genSelectPlan(null, (ASTNode) lateralViewTree .getChild(0), blankQb, lvForward, null, lateralViewTree.getType() == HiveParser.TOK_LATERAL_VIEW_OUTER); // add udtf aliases to QB Modified: hive/trunk/ql/src/test/org/apache/hadoop/hive/ql/parse/TestIUD.java URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/org/apache/hadoop/hive/ql/parse/TestIUD.java?rev=1659640&r1=1659639&r2=1659640&view=diff ============================================================================== --- hive/trunk/ql/src/test/org/apache/hadoop/hive/ql/parse/TestIUD.java (original) +++ hive/trunk/ql/src/test/org/apache/hadoop/hive/ql/parse/TestIUD.java Fri Feb 13 18:27:13 2015 @@ -207,6 +207,26 @@ public class TestIUD { "(TOK_WHERE (= (TOK_TABLE_OR_COL b) 9))))", ast.toStringTree()); } + /** + * same as testInsertIntoTableAsSelectFromNamedVirtTable but with column list on target table + * @throws ParseException + */ + @Test + public void testInsertIntoTableAsSelectFromNamedVirtTableNamedCol() throws ParseException { + ASTNode ast = parse("insert into page_view(c1,c2) select a,b as c from (values (1,2),(3,4)) as VC(a,b) where b = 9"); + Assert.assertEquals("AST doesn't match", + "(TOK_QUERY " + + "(TOK_FROM " + + "(TOK_VIRTUAL_TABLE " + + "(TOK_VIRTUAL_TABREF (TOK_TABNAME VC) (TOK_COL_NAME a b)) " + + "(TOK_VALUES_TABLE (TOK_VALUE_ROW 1 2) (TOK_VALUE_ROW 3 4)))) " + + "(TOK_INSERT (TOK_INSERT_INTO (TOK_TAB (TOK_TABNAME page_view)) (TOK_TABCOLNAME c1 c2)) " + + "(TOK_SELECT " + + "(TOK_SELEXPR (TOK_TABLE_OR_COL a)) " + + "(TOK_SELEXPR (TOK_TABLE_OR_COL b) c)) " + + "(TOK_WHERE (= (TOK_TABLE_OR_COL b) 9))))", + ast.toStringTree()); + } @Test public void testInsertIntoTableFromAnonymousTable1Row() throws ParseException { ASTNode ast = parse("insert into page_view values(1,2)"); @@ -220,6 +240,32 @@ public class TestIUD { "(TOK_SELECT (TOK_SELEXPR TOK_ALLCOLREF))))", ast.toStringTree()); } + /** + * Same as testInsertIntoTableFromAnonymousTable1Row but with column list on target table + * @throws ParseException + */ + @Test + public void testInsertIntoTableFromAnonymousTable1RowNamedCol() throws ParseException { + ASTNode ast = parse("insert into page_view(a,b) values(1,2)"); + Assert.assertEquals("AST doesn't match", + "(TOK_QUERY " + + "(TOK_FROM " + + "(TOK_VIRTUAL_TABLE " + + "(TOK_VIRTUAL_TABREF TOK_ANONYMOUS) " + + "(TOK_VALUES_TABLE (TOK_VALUE_ROW 1 2))" + + ")" + + ") " + + "(TOK_INSERT " + + "(TOK_INSERT_INTO " + + "(TOK_TAB (TOK_TABNAME page_view)) " + + "(TOK_TABCOLNAME a b)" +//this is "extra" piece we get vs previous query + ") " + + "(TOK_SELECT " + + "(TOK_SELEXPR TOK_ALLCOLREF)" + + ")" + + ")" + + ")", ast.toStringTree()); + } @Test public void testInsertIntoTableFromAnonymousTable() throws ParseException { ASTNode ast = parse("insert into table page_view values(-1,2),(3,+4)"); Added: hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema.q URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema.q?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema.q (added) +++ hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema.q Fri Feb 13 18:27:13 2015 @@ -0,0 +1,9 @@ +-- set of tests HIVE-9481 +drop database if exists x314n cascade; +create database x314n; +use x314n; +create table source(s1 int, s2 int); +--column number mismatch +insert into source(s2) values(2,1); + +drop database if exists x314n cascade; Added: hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema1.q URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema1.q?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema1.q (added) +++ hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema1.q Fri Feb 13 18:27:13 2015 @@ -0,0 +1,10 @@ +-- set of tests HIVE-9481 +drop database if exists x314n cascade; +create database x314n; +use x314n; +create table source(s1 int, s2 int); + +--number of columns mismatched +insert into source(s2,s1) values(1); + +drop database if exists x314n cascade; \ No newline at end of file Added: hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema2.q URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema2.q?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema2.q (added) +++ hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema2.q Fri Feb 13 18:27:13 2015 @@ -0,0 +1,11 @@ +-- set of tests HIVE-9481 +drop database if exists x314n cascade; +create database x314n; +use x314n; +create table source(s1 int, s2 int); +create table target1(x int, y int, z int); + +--number of columns mismatched +insert into target1(x,y,z) select * from source; + +drop database if exists x314n cascade; \ No newline at end of file Added: hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema3.q URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema3.q?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema3.q (added) +++ hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema3.q Fri Feb 13 18:27:13 2015 @@ -0,0 +1,12 @@ +-- set of tests HIVE-9481 +drop database if exists x314n cascade; +create database x314n; +use x314n; +create table target1(x int, y int, z int); +create table source(s1 int, s2 int); + +--invalid column name +insert into target1(a,z) select * from source; + + +drop database if exists x314n cascade; \ No newline at end of file Added: hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema4.q URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema4.q?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema4.q (added) +++ hive/trunk/ql/src/test/queries/clientnegative/insert_into_with_schema4.q Fri Feb 13 18:27:13 2015 @@ -0,0 +1,10 @@ +-- set of tests HIVE-9481 +drop database if exists x314n cascade; +create database x314n; +use x314n; + +CREATE TABLE pageviews (userid VARCHAR(64), link STRING, source STRING) PARTITIONED BY (datestamp STRING, i int) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC; +--datestamp is a static partition thus should not be supplied by producer side +INSERT INTO TABLE pageviews PARTITION (datestamp='2014-09-23',i)(userid,i,datestamp,link) VALUES ('jsmith', 7, '2014-07-12', '7mail.com'); + +drop database if exists x314n cascade; Added: hive/trunk/ql/src/test/queries/clientpositive/insert_into_with_schema.q URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/queries/clientpositive/insert_into_with_schema.q?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/queries/clientpositive/insert_into_with_schema.q (added) +++ hive/trunk/ql/src/test/queries/clientpositive/insert_into_with_schema.q Fri Feb 13 18:27:13 2015 @@ -0,0 +1,66 @@ +-- set of tests HIVE-9481 + +drop database if exists x314 cascade; +create database x314; +use x314; +create table source(s1 int, s2 int); +create table target1(x int, y int, z int); +create table target2(x int, y int, z int); + +insert into source(s2,s1) values(2,1); +-- expect source to contain 1 row (1,2) +select * from source; +insert into target1(z,x) select * from source; +-- expect target1 to contain 1 row (2,NULL,1) +select * from target1; + +-- note that schema spec for target1 and target2 are different +from source insert into target1(x,y) select * insert into target2(x,z) select s2,s1; +--expect target1 to have 2rows (2,NULL,1), (1,2,NULL) +select * from target1 order by x,y,z; +-- expect target2 to have 1 row: (2,NULL,1) +select * from target2; + + +from source insert into target1(x,y,z) select null as x, * insert into target2(x,y,z) select null as x, source.*; +-- expect target1 to have 3 rows: (2,NULL,1), (1,2,NULL), (NULL, 1,2) +select * from target1 order by x,y,z; +-- expect target2 to have 2 rows: (2,NULL,1), (NULL, 1,2) +select * from target2 order by x,y,z; + +truncate table target1; +create table source2(s1 int, s2 int); +insert into target1 (x,z) select source.s1,source2.s2 from source left outer join source2 on source.s1=source2.s2; +--expect target1 to have 1 row (1,NULL,NULL) +select * from target1; + + +-- partitioned tables +CREATE TABLE pageviews (userid VARCHAR(64), link STRING, source STRING) PARTITIONED BY (datestamp STRING, i int) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC; +INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23', i = 1)(userid,link) VALUES ('jsmith', 'mail.com'); +-- expect 1 row: ('jsmith', 'mail.com', NULL) in partition '2014-09-23'/'1' +select * from pageviews; + + +-- dynamic partitioning + + + +INSERT INTO TABLE pageviews PARTITION (datestamp='2014-09-23',i)(userid,i,link) VALUES ('jsmith', 7, '7mail.com'); + +set hive.exec.dynamic.partition.mode=nonstrict; + +INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link,datestamp) VALUES ('jsmith', 17, '17mail.com', '2014-09-23'); +INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link,datestamp) VALUES ('jsmith', 19, '19mail.com', '2014-09-24'); +-- here the 'datestamp' partition column is not provided and will be NULL-filled +INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link) VALUES ('jsmith', 23, '23mail.com'); +-- expect 5 rows: +-- expect ('jsmith', 'mail.com', NULL) in partition '2014-09-23'/'1' +-- expect ('jsmith', '7mail.com', NULL) in partition '2014-09-23'/'7' +-- expect ('jsmith', '17mail.com', NULL) in partition '2014-09-23'/'17' +-- expect ('jsmith', '19mail.com', NULL) in partition '2014-09-24'/'19' +-- expect ('jsmith', '23mail.com', NULL) in partition '__HIVE_DEFAULT_PARTITION__'/'23' +select * from pageviews order by link; + + +drop database if exists x314 cascade; Added: hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema.q.out URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema.q.out?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema.q.out (added) +++ hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema.q.out Fri Feb 13 18:27:13 2015 @@ -0,0 +1,27 @@ +PREHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +PREHOOK: type: DROPDATABASE +POSTHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +POSTHOOK: type: DROPDATABASE +PREHOOK: query: create database x314n +PREHOOK: type: CREATEDATABASE +PREHOOK: Output: database:x314n +POSTHOOK: query: create database x314n +POSTHOOK: type: CREATEDATABASE +POSTHOOK: Output: database:x314n +PREHOOK: query: use x314n +PREHOOK: type: SWITCHDATABASE +PREHOOK: Input: database:x314n +POSTHOOK: query: use x314n +POSTHOOK: type: SWITCHDATABASE +POSTHOOK: Input: database:x314n +PREHOOK: query: create table source(s1 int, s2 int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314n +PREHOOK: Output: x314n@source +POSTHOOK: query: create table source(s1 int, s2 int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314n +POSTHOOK: Output: x314n@source +FAILED: SemanticException 0:0 Expected 1 columns for insclause-0/x314n@source; select produces 2 columns. Error encountered near token 'values__tmp__table__1.tmp_values_col2' Added: hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema1.q.out URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema1.q.out?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema1.q.out (added) +++ hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema1.q.out Fri Feb 13 18:27:13 2015 @@ -0,0 +1,27 @@ +PREHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +PREHOOK: type: DROPDATABASE +POSTHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +POSTHOOK: type: DROPDATABASE +PREHOOK: query: create database x314n +PREHOOK: type: CREATEDATABASE +PREHOOK: Output: database:x314n +POSTHOOK: query: create database x314n +POSTHOOK: type: CREATEDATABASE +POSTHOOK: Output: database:x314n +PREHOOK: query: use x314n +PREHOOK: type: SWITCHDATABASE +PREHOOK: Input: database:x314n +POSTHOOK: query: use x314n +POSTHOOK: type: SWITCHDATABASE +POSTHOOK: Input: database:x314n +PREHOOK: query: create table source(s1 int, s2 int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314n +PREHOOK: Output: x314n@source +POSTHOOK: query: create table source(s1 int, s2 int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314n +POSTHOOK: Output: x314n@source +FAILED: SemanticException 0:0 Expected 2 columns for insclause-0/x314n@source; select produces 1 columns. Error encountered near token 'values__tmp__table__1.tmp_values_col1' Added: hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema2.q.out URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema2.q.out?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema2.q.out (added) +++ hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema2.q.out Fri Feb 13 18:27:13 2015 @@ -0,0 +1,35 @@ +PREHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +PREHOOK: type: DROPDATABASE +POSTHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +POSTHOOK: type: DROPDATABASE +PREHOOK: query: create database x314n +PREHOOK: type: CREATEDATABASE +PREHOOK: Output: database:x314n +POSTHOOK: query: create database x314n +POSTHOOK: type: CREATEDATABASE +POSTHOOK: Output: database:x314n +PREHOOK: query: use x314n +PREHOOK: type: SWITCHDATABASE +PREHOOK: Input: database:x314n +POSTHOOK: query: use x314n +POSTHOOK: type: SWITCHDATABASE +POSTHOOK: Input: database:x314n +PREHOOK: query: create table source(s1 int, s2 int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314n +PREHOOK: Output: x314n@source +POSTHOOK: query: create table source(s1 int, s2 int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314n +POSTHOOK: Output: x314n@source +PREHOOK: query: create table target1(x int, y int, z int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314n +PREHOOK: Output: x314n@target1 +POSTHOOK: query: create table target1(x int, y int, z int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314n +POSTHOOK: Output: x314n@target1 +FAILED: SemanticException 0:0 Expected 3 columns for insclause-0/x314n@target1; select produces 2 columns. Error encountered near token 'source.s2' Added: hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema3.q.out URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema3.q.out?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema3.q.out (added) +++ hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema3.q.out Fri Feb 13 18:27:13 2015 @@ -0,0 +1,35 @@ +PREHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +PREHOOK: type: DROPDATABASE +POSTHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +POSTHOOK: type: DROPDATABASE +PREHOOK: query: create database x314n +PREHOOK: type: CREATEDATABASE +PREHOOK: Output: database:x314n +POSTHOOK: query: create database x314n +POSTHOOK: type: CREATEDATABASE +POSTHOOK: Output: database:x314n +PREHOOK: query: use x314n +PREHOOK: type: SWITCHDATABASE +PREHOOK: Input: database:x314n +POSTHOOK: query: use x314n +POSTHOOK: type: SWITCHDATABASE +POSTHOOK: Input: database:x314n +PREHOOK: query: create table target1(x int, y int, z int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314n +PREHOOK: Output: x314n@target1 +POSTHOOK: query: create table target1(x int, y int, z int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314n +POSTHOOK: Output: x314n@target1 +PREHOOK: query: create table source(s1 int, s2 int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314n +PREHOOK: Output: x314n@source +POSTHOOK: query: create table source(s1 int, s2 int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314n +POSTHOOK: Output: x314n@source +FAILED: SemanticException 4:20 'a' in insert schema specification is not found among regular columns of x314n.target1 nor dynamic partition columns.. Error encountered near token 'z' Added: hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema4.q.out URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema4.q.out?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema4.q.out (added) +++ hive/trunk/ql/src/test/results/clientnegative/insert_into_with_schema4.q.out Fri Feb 13 18:27:13 2015 @@ -0,0 +1,27 @@ +PREHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +PREHOOK: type: DROPDATABASE +POSTHOOK: query: -- set of tests HIVE-9481 +drop database if exists x314n cascade +POSTHOOK: type: DROPDATABASE +PREHOOK: query: create database x314n +PREHOOK: type: CREATEDATABASE +PREHOOK: Output: database:x314n +POSTHOOK: query: create database x314n +POSTHOOK: type: CREATEDATABASE +POSTHOOK: Output: database:x314n +PREHOOK: query: use x314n +PREHOOK: type: SWITCHDATABASE +PREHOOK: Input: database:x314n +POSTHOOK: query: use x314n +POSTHOOK: type: SWITCHDATABASE +POSTHOOK: Input: database:x314n +PREHOOK: query: CREATE TABLE pageviews (userid VARCHAR(64), link STRING, source STRING) PARTITIONED BY (datestamp STRING, i int) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314n +PREHOOK: Output: x314n@pageviews +POSTHOOK: query: CREATE TABLE pageviews (userid VARCHAR(64), link STRING, source STRING) PARTITIONED BY (datestamp STRING, i int) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314n +POSTHOOK: Output: x314n@pageviews +FAILED: SemanticException 3:65 'datestamp' in insert schema specification is not found among regular columns of x314n.pageviews nor dynamic partition columns.. Error encountered near token 'link' Added: hive/trunk/ql/src/test/results/clientpositive/insert_into_with_schema.q.out URL: http://svn.apache.org/viewvc/hive/trunk/ql/src/test/results/clientpositive/insert_into_with_schema.q.out?rev=1659640&view=auto ============================================================================== --- hive/trunk/ql/src/test/results/clientpositive/insert_into_with_schema.q.out (added) +++ hive/trunk/ql/src/test/results/clientpositive/insert_into_with_schema.q.out Fri Feb 13 18:27:13 2015 @@ -0,0 +1,360 @@ +PREHOOK: query: -- set of tests HIVE-9481 + +drop database if exists x314 cascade +PREHOOK: type: DROPDATABASE +POSTHOOK: query: -- set of tests HIVE-9481 + +drop database if exists x314 cascade +POSTHOOK: type: DROPDATABASE +PREHOOK: query: create database x314 +PREHOOK: type: CREATEDATABASE +PREHOOK: Output: database:x314 +POSTHOOK: query: create database x314 +POSTHOOK: type: CREATEDATABASE +POSTHOOK: Output: database:x314 +PREHOOK: query: use x314 +PREHOOK: type: SWITCHDATABASE +PREHOOK: Input: database:x314 +POSTHOOK: query: use x314 +POSTHOOK: type: SWITCHDATABASE +POSTHOOK: Input: database:x314 +PREHOOK: query: create table source(s1 int, s2 int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314 +PREHOOK: Output: x314@source +POSTHOOK: query: create table source(s1 int, s2 int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314 +POSTHOOK: Output: x314@source +PREHOOK: query: create table target1(x int, y int, z int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314 +PREHOOK: Output: x314@target1 +POSTHOOK: query: create table target1(x int, y int, z int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314 +POSTHOOK: Output: x314@target1 +PREHOOK: query: create table target2(x int, y int, z int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314 +PREHOOK: Output: x314@target2 +POSTHOOK: query: create table target2(x int, y int, z int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314 +POSTHOOK: Output: x314@target2 +PREHOOK: query: insert into source(s2,s1) values(2,1) +PREHOOK: type: QUERY +PREHOOK: Input: x314@values__tmp__table__1 +PREHOOK: Output: x314@source +POSTHOOK: query: insert into source(s2,s1) values(2,1) +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@values__tmp__table__1 +POSTHOOK: Output: x314@source +POSTHOOK: Lineage: source.s1 EXPRESSION [(values__tmp__table__1)values__tmp__table__1.FieldSchema(name:tmp_values_col2, type:string, comment:), ] +POSTHOOK: Lineage: source.s2 EXPRESSION [(values__tmp__table__1)values__tmp__table__1.FieldSchema(name:tmp_values_col1, type:string, comment:), ] +PREHOOK: query: -- expect source to contain 1 row (1,2) +select * from source +PREHOOK: type: QUERY +PREHOOK: Input: x314@source +#### A masked pattern was here #### +POSTHOOK: query: -- expect source to contain 1 row (1,2) +select * from source +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@source +#### A masked pattern was here #### +1 2 +PREHOOK: query: insert into target1(z,x) select * from source +PREHOOK: type: QUERY +PREHOOK: Input: x314@source +PREHOOK: Output: x314@target1 +POSTHOOK: query: insert into target1(z,x) select * from source +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@source +POSTHOOK: Output: x314@target1 +POSTHOOK: Lineage: target1.x SIMPLE [(source)source.FieldSchema(name:s2, type:int, comment:null), ] +POSTHOOK: Lineage: target1.y EXPRESSION [] +POSTHOOK: Lineage: target1.z SIMPLE [(source)source.FieldSchema(name:s1, type:int, comment:null), ] +PREHOOK: query: -- expect target1 to contain 1 row (2,NULL,1) +select * from target1 +PREHOOK: type: QUERY +PREHOOK: Input: x314@target1 +#### A masked pattern was here #### +POSTHOOK: query: -- expect target1 to contain 1 row (2,NULL,1) +select * from target1 +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@target1 +#### A masked pattern was here #### +2 NULL 1 +PREHOOK: query: -- note that schema spec for target1 and target2 are different +from source insert into target1(x,y) select * insert into target2(x,z) select s2,s1 +PREHOOK: type: QUERY +PREHOOK: Input: x314@source +PREHOOK: Output: x314@target1 +PREHOOK: Output: x314@target2 +POSTHOOK: query: -- note that schema spec for target1 and target2 are different +from source insert into target1(x,y) select * insert into target2(x,z) select s2,s1 +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@source +POSTHOOK: Output: x314@target1 +POSTHOOK: Output: x314@target2 +POSTHOOK: Lineage: target1.x SIMPLE [(source)source.FieldSchema(name:s1, type:int, comment:null), ] +POSTHOOK: Lineage: target1.y SIMPLE [(source)source.FieldSchema(name:s2, type:int, comment:null), ] +POSTHOOK: Lineage: target1.z EXPRESSION [] +POSTHOOK: Lineage: target2.x SIMPLE [(source)source.FieldSchema(name:s2, type:int, comment:null), ] +POSTHOOK: Lineage: target2.y EXPRESSION [] +POSTHOOK: Lineage: target2.z SIMPLE [(source)source.FieldSchema(name:s1, type:int, comment:null), ] +PREHOOK: query: --expect target1 to have 2rows (2,NULL,1), (1,2,NULL) +select * from target1 order by x,y,z +PREHOOK: type: QUERY +PREHOOK: Input: x314@target1 +#### A masked pattern was here #### +POSTHOOK: query: --expect target1 to have 2rows (2,NULL,1), (1,2,NULL) +select * from target1 order by x,y,z +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@target1 +#### A masked pattern was here #### +1 2 NULL +2 NULL 1 +PREHOOK: query: -- expect target2 to have 1 row: (2,NULL,1) +select * from target2 +PREHOOK: type: QUERY +PREHOOK: Input: x314@target2 +#### A masked pattern was here #### +POSTHOOK: query: -- expect target2 to have 1 row: (2,NULL,1) +select * from target2 +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@target2 +#### A masked pattern was here #### +2 NULL 1 +PREHOOK: query: from source insert into target1(x,y,z) select null as x, * insert into target2(x,y,z) select null as x, source.* +PREHOOK: type: QUERY +PREHOOK: Input: x314@source +PREHOOK: Output: x314@target1 +PREHOOK: Output: x314@target2 +POSTHOOK: query: from source insert into target1(x,y,z) select null as x, * insert into target2(x,y,z) select null as x, source.* +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@source +POSTHOOK: Output: x314@target1 +POSTHOOK: Output: x314@target2 +POSTHOOK: Lineage: target1.x EXPRESSION [] +POSTHOOK: Lineage: target1.y SIMPLE [(source)source.FieldSchema(name:s1, type:int, comment:null), ] +POSTHOOK: Lineage: target1.z SIMPLE [(source)source.FieldSchema(name:s2, type:int, comment:null), ] +POSTHOOK: Lineage: target2.x EXPRESSION [] +POSTHOOK: Lineage: target2.y SIMPLE [(source)source.FieldSchema(name:s1, type:int, comment:null), ] +POSTHOOK: Lineage: target2.z SIMPLE [(source)source.FieldSchema(name:s2, type:int, comment:null), ] +PREHOOK: query: -- expect target1 to have 3 rows: (2,NULL,1), (1,2,NULL), (NULL, 1,2) +select * from target1 order by x,y,z +PREHOOK: type: QUERY +PREHOOK: Input: x314@target1 +#### A masked pattern was here #### +POSTHOOK: query: -- expect target1 to have 3 rows: (2,NULL,1), (1,2,NULL), (NULL, 1,2) +select * from target1 order by x,y,z +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@target1 +#### A masked pattern was here #### +NULL 1 2 +1 2 NULL +2 NULL 1 +PREHOOK: query: -- expect target2 to have 2 rows: (2,NULL,1), (NULL, 1,2) +select * from target2 order by x,y,z +PREHOOK: type: QUERY +PREHOOK: Input: x314@target2 +#### A masked pattern was here #### +POSTHOOK: query: -- expect target2 to have 2 rows: (2,NULL,1), (NULL, 1,2) +select * from target2 order by x,y,z +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@target2 +#### A masked pattern was here #### +NULL 1 2 +2 NULL 1 +PREHOOK: query: truncate table target1 +PREHOOK: type: TRUNCATETABLE +PREHOOK: Output: x314@target1 +POSTHOOK: query: truncate table target1 +POSTHOOK: type: TRUNCATETABLE +POSTHOOK: Output: x314@target1 +PREHOOK: query: create table source2(s1 int, s2 int) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314 +PREHOOK: Output: x314@source2 +POSTHOOK: query: create table source2(s1 int, s2 int) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314 +POSTHOOK: Output: x314@source2 +PREHOOK: query: insert into target1 (x,z) select source.s1,source2.s2 from source left outer join source2 on source.s1=source2.s2 +PREHOOK: type: QUERY +PREHOOK: Input: x314@source +PREHOOK: Input: x314@source2 +PREHOOK: Output: x314@target1 +POSTHOOK: query: insert into target1 (x,z) select source.s1,source2.s2 from source left outer join source2 on source.s1=source2.s2 +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@source +POSTHOOK: Input: x314@source2 +POSTHOOK: Output: x314@target1 +POSTHOOK: Lineage: target1.x SIMPLE [(source)source.FieldSchema(name:s1, type:int, comment:null), ] +POSTHOOK: Lineage: target1.y EXPRESSION [] +POSTHOOK: Lineage: target1.z SIMPLE [(source2)source2.FieldSchema(name:s2, type:int, comment:null), ] +PREHOOK: query: --expect target1 to have 1 row (1,NULL,NULL) +select * from target1 +PREHOOK: type: QUERY +PREHOOK: Input: x314@target1 +#### A masked pattern was here #### +POSTHOOK: query: --expect target1 to have 1 row (1,NULL,NULL) +select * from target1 +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@target1 +#### A masked pattern was here #### +1 NULL NULL +PREHOOK: query: -- partitioned tables +CREATE TABLE pageviews (userid VARCHAR(64), link STRING, source STRING) PARTITIONED BY (datestamp STRING, i int) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:x314 +PREHOOK: Output: x314@pageviews +POSTHOOK: query: -- partitioned tables +CREATE TABLE pageviews (userid VARCHAR(64), link STRING, source STRING) PARTITIONED BY (datestamp STRING, i int) CLUSTERED BY (userid) INTO 256 BUCKETS STORED AS ORC +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:x314 +POSTHOOK: Output: x314@pageviews +PREHOOK: query: INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23', i = 1)(userid,link) VALUES ('jsmith', 'mail.com') +PREHOOK: type: QUERY +PREHOOK: Input: x314@values__tmp__table__2 +PREHOOK: Output: x314@pageviews@datestamp=2014-09-23/i=1 +POSTHOOK: query: INSERT INTO TABLE pageviews PARTITION (datestamp = '2014-09-23', i = 1)(userid,link) VALUES ('jsmith', 'mail.com') +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@values__tmp__table__2 +POSTHOOK: Output: x314@pageviews@datestamp=2014-09-23/i=1 +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=1).link SIMPLE [(values__tmp__table__2)values__tmp__table__2.FieldSchema(name:tmp_values_col2, type:string, comment:), ] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=1).source EXPRESSION [] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=1).userid EXPRESSION [(values__tmp__table__2)values__tmp__table__2.FieldSchema(name:tmp_values_col1, type:string, comment:), ] +PREHOOK: query: -- expect 1 row: ('jsmith', 'mail.com', NULL) in partition '2014-09-23'/'1' +select * from pageviews +PREHOOK: type: QUERY +PREHOOK: Input: x314@pageviews +PREHOOK: Input: x314@pageviews@datestamp=2014-09-23/i=1 +#### A masked pattern was here #### +POSTHOOK: query: -- expect 1 row: ('jsmith', 'mail.com', NULL) in partition '2014-09-23'/'1' +select * from pageviews +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@pageviews +POSTHOOK: Input: x314@pageviews@datestamp=2014-09-23/i=1 +#### A masked pattern was here #### +jsmith mail.com NULL 2014-09-23 1 +PREHOOK: query: -- dynamic partitioning + + + +INSERT INTO TABLE pageviews PARTITION (datestamp='2014-09-23',i)(userid,i,link) VALUES ('jsmith', 7, '7mail.com') +PREHOOK: type: QUERY +PREHOOK: Input: x314@values__tmp__table__3 +PREHOOK: Output: x314@pageviews@datestamp=2014-09-23 +POSTHOOK: query: -- dynamic partitioning + + + +INSERT INTO TABLE pageviews PARTITION (datestamp='2014-09-23',i)(userid,i,link) VALUES ('jsmith', 7, '7mail.com') +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@values__tmp__table__3 +POSTHOOK: Output: x314@pageviews@datestamp=2014-09-23/i=7 +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=7).link SIMPLE [(values__tmp__table__3)values__tmp__table__3.FieldSchema(name:tmp_values_col3, type:string, comment:), ] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=7).source EXPRESSION [] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=7).userid EXPRESSION [(values__tmp__table__3)values__tmp__table__3.FieldSchema(name:tmp_values_col1, type:string, comment:), ] +PREHOOK: query: INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link,datestamp) VALUES ('jsmith', 17, '17mail.com', '2014-09-23') +PREHOOK: type: QUERY +PREHOOK: Input: x314@values__tmp__table__4 +PREHOOK: Output: x314@pageviews +POSTHOOK: query: INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link,datestamp) VALUES ('jsmith', 17, '17mail.com', '2014-09-23') +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@values__tmp__table__4 +POSTHOOK: Output: x314@pageviews@datestamp=2014-09-23/i=17 +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=17).link SIMPLE [(values__tmp__table__4)values__tmp__table__4.FieldSchema(name:tmp_values_col3, type:string, comment:), ] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=17).source EXPRESSION [] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-23,i=17).userid EXPRESSION [(values__tmp__table__4)values__tmp__table__4.FieldSchema(name:tmp_values_col1, type:string, comment:), ] +PREHOOK: query: INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link,datestamp) VALUES ('jsmith', 19, '19mail.com', '2014-09-24') +PREHOOK: type: QUERY +PREHOOK: Input: x314@values__tmp__table__5 +PREHOOK: Output: x314@pageviews +POSTHOOK: query: INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link,datestamp) VALUES ('jsmith', 19, '19mail.com', '2014-09-24') +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@values__tmp__table__5 +POSTHOOK: Output: x314@pageviews@datestamp=2014-09-24/i=19 +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-24,i=19).link SIMPLE [(values__tmp__table__5)values__tmp__table__5.FieldSchema(name:tmp_values_col3, type:string, comment:), ] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-24,i=19).source EXPRESSION [] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=2014-09-24,i=19).userid EXPRESSION [(values__tmp__table__5)values__tmp__table__5.FieldSchema(name:tmp_values_col1, type:string, comment:), ] +PREHOOK: query: -- here the 'datestamp' partition column is not provided and will be NULL-filled +INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link) VALUES ('jsmith', 23, '23mail.com') +PREHOOK: type: QUERY +PREHOOK: Input: x314@values__tmp__table__6 +PREHOOK: Output: x314@pageviews +POSTHOOK: query: -- here the 'datestamp' partition column is not provided and will be NULL-filled +INSERT INTO TABLE pageviews PARTITION (datestamp,i)(userid,i,link) VALUES ('jsmith', 23, '23mail.com') +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@values__tmp__table__6 +POSTHOOK: Output: x314@pageviews@datestamp=__HIVE_DEFAULT_PARTITION__/i=23 +POSTHOOK: Lineage: pageviews PARTITION(datestamp=__HIVE_DEFAULT_PARTITION__,i=23).link SIMPLE [(values__tmp__table__6)values__tmp__table__6.FieldSchema(name:tmp_values_col3, type:string, comment:), ] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=__HIVE_DEFAULT_PARTITION__,i=23).source EXPRESSION [] +POSTHOOK: Lineage: pageviews PARTITION(datestamp=__HIVE_DEFAULT_PARTITION__,i=23).userid EXPRESSION [(values__tmp__table__6)values__tmp__table__6.FieldSchema(name:tmp_values_col1, type:string, comment:), ] +PREHOOK: query: -- expect 5 rows: +-- expect ('jsmith', 'mail.com', NULL) in partition '2014-09-23'/'1' +-- expect ('jsmith', '7mail.com', NULL) in partition '2014-09-23'/'7' +-- expect ('jsmith', '17mail.com', NULL) in partition '2014-09-23'/'17' +-- expect ('jsmith', '19mail.com', NULL) in partition '2014-09-24'/'19' +-- expect ('jsmith', '23mail.com', NULL) in partition '__HIVE_DEFAULT_PARTITION__'/'23' +select * from pageviews order by link +PREHOOK: type: QUERY +PREHOOK: Input: x314@pageviews +PREHOOK: Input: x314@pageviews@datestamp=2014-09-23/i=1 +PREHOOK: Input: x314@pageviews@datestamp=2014-09-23/i=17 +PREHOOK: Input: x314@pageviews@datestamp=2014-09-23/i=7 +PREHOOK: Input: x314@pageviews@datestamp=2014-09-24/i=19 +PREHOOK: Input: x314@pageviews@datestamp=__HIVE_DEFAULT_PARTITION__/i=23 +#### A masked pattern was here #### +POSTHOOK: query: -- expect 5 rows: +-- expect ('jsmith', 'mail.com', NULL) in partition '2014-09-23'/'1' +-- expect ('jsmith', '7mail.com', NULL) in partition '2014-09-23'/'7' +-- expect ('jsmith', '17mail.com', NULL) in partition '2014-09-23'/'17' +-- expect ('jsmith', '19mail.com', NULL) in partition '2014-09-24'/'19' +-- expect ('jsmith', '23mail.com', NULL) in partition '__HIVE_DEFAULT_PARTITION__'/'23' +select * from pageviews order by link +POSTHOOK: type: QUERY +POSTHOOK: Input: x314@pageviews +POSTHOOK: Input: x314@pageviews@datestamp=2014-09-23/i=1 +POSTHOOK: Input: x314@pageviews@datestamp=2014-09-23/i=17 +POSTHOOK: Input: x314@pageviews@datestamp=2014-09-23/i=7 +POSTHOOK: Input: x314@pageviews@datestamp=2014-09-24/i=19 +POSTHOOK: Input: x314@pageviews@datestamp=__HIVE_DEFAULT_PARTITION__/i=23 +#### A masked pattern was here #### +jsmith 17mail.com NULL 2014-09-23 17 +jsmith 19mail.com NULL 2014-09-24 19 +jsmith 23mail.com NULL __HIVE_DEFAULT_PARTITION__ 23 +jsmith 7mail.com NULL 2014-09-23 7 +jsmith mail.com NULL 2014-09-23 1 +PREHOOK: query: drop database if exists x314 cascade +PREHOOK: type: DROPDATABASE +PREHOOK: Input: database:x314 +PREHOOK: Output: database:x314 +PREHOOK: Output: x314@pageviews +PREHOOK: Output: x314@source +PREHOOK: Output: x314@source2 +PREHOOK: Output: x314@target1 +PREHOOK: Output: x314@target2 +PREHOOK: Output: x314@values__tmp__table__1 +PREHOOK: Output: x314@values__tmp__table__2 +PREHOOK: Output: x314@values__tmp__table__3 +PREHOOK: Output: x314@values__tmp__table__4 +PREHOOK: Output: x314@values__tmp__table__5 +PREHOOK: Output: x314@values__tmp__table__6 +POSTHOOK: query: drop database if exists x314 cascade +POSTHOOK: type: DROPDATABASE +POSTHOOK: Input: database:x314 +POSTHOOK: Output: database:x314 +POSTHOOK: Output: x314@pageviews +POSTHOOK: Output: x314@source +POSTHOOK: Output: x314@source2 +POSTHOOK: Output: x314@target1 +POSTHOOK: Output: x314@target2 +POSTHOOK: Output: x314@values__tmp__table__1 +POSTHOOK: Output: x314@values__tmp__table__2 +POSTHOOK: Output: x314@values__tmp__table__3 +POSTHOOK: Output: x314@values__tmp__table__4 +POSTHOOK: Output: x314@values__tmp__table__5 +POSTHOOK: Output: x314@values__tmp__table__6