FAQ
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

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupcommits @
categorieshive, hadoop
postedFeb 13, '15 at 6:27p
activeFeb 13, '15 at 6:27p
posts1
users1
websitehive.apache.org

1 user in discussion

Ekoifman: 1 post

People

Translate

site design / logo © 2021 Grokbase