This is an automated email from the ASF dual-hosted git repository.
krisztiankasa pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/hive.git
The following commit(s) were added to refs/heads/master by this push:
new a1889d10693 HIVE-27080: Support project pushdown in JDBC storage
handler even when filters are not pushed (Soumyakanti Das, reviewed by
Krisztian Kasa)
a1889d10693 is described below
commit a1889d10693567c9366d96fa4bb4fe398ead5323
Author: Soumyakanti Das <[email protected]>
AuthorDate: Thu Sep 12 02:08:57 2024 -0700
HIVE-27080: Support project pushdown in JDBC storage handler even when
filters are not pushed (Soumyakanti Das, reviewed by Krisztian Kasa)
---
.../rules/HiveAggregateProjectMergeRule.java | 13 +-
.../optimizer/calcite/rules/RelFieldTrimmer.java | 170 +++++-
.../rules/jdbc/JDBCAggregateProjectMergeRule.java | 42 ++
.../calcite/translator/PlanModifierForASTConv.java | 9 +-
.../hadoop/hive/ql/parse/CalcitePlanner.java | 2 +
.../queries/clientpositive/jdbc_project_pushdown.q | 106 ++++
.../clientpositive/llap/cbo_jdbc_joincost.q.out | 12 +-
.../llap/external_jdbc_rowcount.q.out | 32 +-
.../clientpositive/llap/external_jdbc_table2.q.out | 3 +-
.../clientpositive/llap/external_jdbc_table3.q.out | 6 +-
.../clientpositive/llap/external_jdbc_table4.q.out | 6 +-
.../llap/jdbc_project_pushdown.q.out | 671 +++++++++++++++++++++
12 files changed, 1037 insertions(+), 35 deletions(-)
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveAggregateProjectMergeRule.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveAggregateProjectMergeRule.java
index 59b6a3b9a57..89669a5c68d 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveAggregateProjectMergeRule.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/HiveAggregateProjectMergeRule.java
@@ -19,7 +19,9 @@ package org.apache.hadoop.hive.ql.optimizer.calcite.rules;
import org.apache.calcite.plan.RelOptRuleCall;
import org.apache.calcite.rel.core.Aggregate;
import org.apache.calcite.rel.core.AggregateCall;
+import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.rules.AggregateProjectMergeRule;
+import org.apache.calcite.tools.RelBuilderFactory;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveAggregate;
import org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveGroupingID;
@@ -38,11 +40,14 @@ import
org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveProject;
*/
public class HiveAggregateProjectMergeRule extends AggregateProjectMergeRule {
public static final HiveAggregateProjectMergeRule INSTANCE =
- new HiveAggregateProjectMergeRule();
+ new HiveAggregateProjectMergeRule(HiveAggregate.class,
HiveProject.class, HiveRelFactories.HIVE_BUILDER);
- /** Private constructor. */
- private HiveAggregateProjectMergeRule() {
- super(HiveAggregate.class, HiveProject.class,
HiveRelFactories.HIVE_BUILDER);
+ protected HiveAggregateProjectMergeRule(
+ Class<? extends Aggregate> aggregateClass,
+ Class<? extends Project> projectClass,
+ RelBuilderFactory relBuilderFactory
+ ) {
+ super(aggregateClass, projectClass, relBuilderFactory);
}
@Override
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/RelFieldTrimmer.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/RelFieldTrimmer.java
index 5cefd025db2..a556ddf504d 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/RelFieldTrimmer.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/RelFieldTrimmer.java
@@ -16,6 +16,18 @@
*/
package org.apache.hadoop.hive.ql.optimizer.calcite.rules;
+import com.google.common.collect.ImmutableList;
+import com.google.common.collect.Iterables;
+import org.apache.calcite.adapter.jdbc.JdbcConvention;
+import org.apache.calcite.adapter.jdbc.JdbcRel;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcAggregate;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcAggregateRule;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcFilter;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcFilterRule;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoin;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcJoinRule;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcProject;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcProjectRule;
import org.apache.calcite.linq4j.Ord;
import org.apache.calcite.plan.RelOptCluster;
import org.apache.calcite.plan.RelOptUtil;
@@ -64,9 +76,10 @@ import org.apache.calcite.util.mapping.IntPair;
import org.apache.calcite.util.mapping.Mapping;
import org.apache.calcite.util.mapping.MappingType;
import org.apache.calcite.util.mapping.Mappings;
-
-import com.google.common.collect.ImmutableList;
-import com.google.common.collect.Iterables;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.jdbc.HiveJdbcConverter;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.jdbc.JdbcHiveTableScan;
+import org.slf4j.Logger;
+import org.slf4j.LoggerFactory;
import java.math.BigDecimal;
import java.util.ArrayList;
@@ -74,8 +87,6 @@ import java.util.Collections;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;
-import org.slf4j.Logger;
-import org.slf4j.LoggerFactory;
/**
* This class comes from Calcite almost as-is. The only change concerns
@@ -351,6 +362,62 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
Mappings.createIdentity(rel.getRowType().getFieldCount()));
}
+ public TrimResult trimFields(
+ JdbcRel rel,
+ ImmutableBitSet fieldsUsed,
+ Set<RelDataTypeField> extraFields) {
+ // We don't know how to trim this kind of relational expression, so give
+ // it back intact.
+ Util.discard(fieldsUsed);
+ return result(rel,
+ Mappings.createIdentity(rel.getRowType().getFieldCount()));
+ }
+
+ public TrimResult trimFields(
+ HiveJdbcConverter converter,
+ ImmutableBitSet fieldsUsed,
+ Set<RelDataTypeField> extraFields) {
+ final int fieldCount = converter.getRowType().getFieldCount();
+ final RelNode input = converter.getInput();
+
+ TrimResult trimResult = trimChild(converter, converter.getInput(),
fieldsUsed, extraFields);
+ RelNode newInput = trimResult.left;
+ final Mapping inputMapping = trimResult.right;
+
+ // If the input is unchanged, and we need to project all columns,
+ // there's nothing we can do.
+ if (newInput == input
+ && fieldsUsed.cardinality() == fieldCount) {
+ return result(converter, Mappings.createIdentity(fieldCount));
+ }
+
+ HiveJdbcConverter newConverter = (HiveJdbcConverter)
converter.copy(converter.getTraitSet(), newInput);
+
+ return result(newConverter, inputMapping);
+ }
+
+ public TrimResult trimFields(
+ final JdbcProject jdbcProject,
+ ImmutableBitSet fieldsUsed,
+ Set<RelDataTypeField> extraFields) {
+
+ TrimResult trimResult = trimFields((Project) jdbcProject, fieldsUsed,
extraFields);
+ RelNode newInput = trimResult.left;
+ final Mapping inputMapping = trimResult.right;
+
+ if (newInput instanceof JdbcProject) {
+ return trimResult;
+ }
+
+ if (newInput instanceof Project) {
+ JdbcProject newProject =
+ (JdbcProject) JdbcProjectRule.create((JdbcConvention)
jdbcProject.getConvention()).convert(newInput);
+ return result(newProject, inputMapping);
+ }
+
+ return trimFields((RelNode) jdbcProject, fieldsUsed, extraFields);
+ }
+
/**
* Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
* {@link org.apache.calcite.rel.logical.LogicalProject}.
@@ -446,6 +513,28 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
return result(relBuilder.build(), mapping);
}
+ public TrimResult trimFields(
+ final JdbcFilter jdbcFilter,
+ ImmutableBitSet fieldsUsed,
+ Set<RelDataTypeField> extraFields) {
+
+ TrimResult trimResult = trimFields((Filter) jdbcFilter, fieldsUsed,
extraFields);
+ RelNode newInput = trimResult.left;
+ final Mapping inputMapping = trimResult.right;
+
+ if (newInput instanceof JdbcFilter) {
+ return trimResult;
+ }
+
+ if (newInput instanceof Filter) {
+ JdbcFilter newFilter =
+ (JdbcFilter) JdbcFilterRule.create((JdbcConvention)
jdbcFilter.getConvention()).convert(newInput);
+ return result(newFilter, inputMapping);
+ }
+
+ return trimFields((RelNode) jdbcFilter, fieldsUsed, extraFields);
+ }
+
/**
* Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
* {@link org.apache.calcite.rel.logical.LogicalFilter}.
@@ -555,6 +644,28 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
return result(relBuilder.build(), inputMapping);
}
+ public TrimResult trimFields(
+ final JdbcJoin jdbcJoin,
+ ImmutableBitSet fieldsUsed,
+ Set<RelDataTypeField> extraFields) {
+
+ TrimResult trimResult = trimFields((Join) jdbcJoin, fieldsUsed,
extraFields);
+ RelNode newInput = trimResult.left;
+ final Mapping inputMapping = trimResult.right;
+
+ if (newInput instanceof JdbcJoin) {
+ return trimResult;
+ }
+
+ if (newInput instanceof Join) {
+ JdbcJoin newJoin =
+ (JdbcJoin) JdbcJoinRule.create((JdbcConvention)
jdbcJoin.getConvention()).convert(newInput);
+ return result(newJoin, inputMapping);
+ }
+
+ return trimFields((RelNode) jdbcJoin, fieldsUsed, extraFields);
+ }
+
/**
* Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
* {@link org.apache.calcite.rel.logical.LogicalJoin}.
@@ -781,6 +892,28 @@ public class RelFieldTrimmer implements ReflectiveVisitor {
return result(relBuilder.build(), mapping);
}
+ public TrimResult trimFields(
+ final JdbcAggregate jdbcAggregate,
+ ImmutableBitSet fieldsUsed,
+ Set<RelDataTypeField> extraFields) {
+
+ TrimResult trimResult = trimFields((Aggregate) jdbcAggregate, fieldsUsed,
extraFields);
+ RelNode newInput = trimResult.left;
+ final Mapping inputMapping = trimResult.right;
+
+ if (newInput instanceof JdbcAggregate) {
+ return trimResult;
+ }
+
+ if (newInput instanceof Aggregate) {
+ JdbcAggregate newAggregate =
+ (JdbcAggregate) JdbcAggregateRule.create((JdbcConvention)
jdbcAggregate.getConvention()).convert(newInput);
+ return result(newAggregate, inputMapping);
+ }
+
+ return trimFields((RelNode) jdbcAggregate, fieldsUsed, extraFields);
+ }
+
/**
* Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
* {@link org.apache.calcite.rel.logical.LogicalAggregate}.
@@ -1044,6 +1177,33 @@ public class RelFieldTrimmer implements
ReflectiveVisitor {
return mapping;
}
+ public TrimResult trimFields(
+ final JdbcHiveTableScan jdbcHiveTableScan,
+ ImmutableBitSet fieldsUsed,
+ Set<RelDataTypeField> extraFields) {
+
+ TrimResult trimResult = trimFields((TableScan) jdbcHiveTableScan,
fieldsUsed, extraFields);
+ RelNode newInput = trimResult.left;
+ final Mapping inputMapping = trimResult.right;
+
+ if (newInput instanceof JdbcHiveTableScan) {
+ return trimResult;
+ }
+
+ if (newInput instanceof Project && ((Project) newInput).getInput()
instanceof JdbcHiveTableScan) {
+ JdbcProject newProject = new JdbcProject(
+ newInput.getCluster(),
+ jdbcHiveTableScan.getTraitSet(),
+ ((Project) newInput).getInput(),
+ ((Project) newInput).getProjects(),
+ newInput.getRowType()
+ );
+ return result(newProject, inputMapping);
+ }
+
+ return trimFields((RelNode) jdbcHiveTableScan, fieldsUsed, extraFields);
+ }
+
/**
* Variant of {@link #trimFields(RelNode, ImmutableBitSet, Set)} for
* {@link org.apache.calcite.rel.logical.LogicalTableScan}.
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/jdbc/JDBCAggregateProjectMergeRule.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/jdbc/JDBCAggregateProjectMergeRule.java
new file mode 100644
index 00000000000..983a1b27e49
--- /dev/null
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/rules/jdbc/JDBCAggregateProjectMergeRule.java
@@ -0,0 +1,42 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to you under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+package org.apache.hadoop.hive.ql.optimizer.calcite.rules.jdbc;
+
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcAggregate;
+import org.apache.calcite.adapter.jdbc.JdbcRules.JdbcProject;
+import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelFactories;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveAggregateProjectMergeRule;
+
+/**
+ * Planner rule that recognizes a {@link JdbcAggregate}
+ * on top of a {@link JdbcProject} and if possible
+ * aggregate through the project or removes the project.
+ *
+ * <p>This is only possible when the grouping expressions and arguments to
+ * the aggregate functions are field references (i.e. not expressions).
+ *
+ * <p>In some cases, this rule has the effect of trimming: the aggregate will
+ * use fewer columns than the project did.
+ */
+public class JDBCAggregateProjectMergeRule extends
HiveAggregateProjectMergeRule {
+ public static final JDBCAggregateProjectMergeRule INSTANCE =
+ new JDBCAggregateProjectMergeRule();
+
+ private JDBCAggregateProjectMergeRule() {
+ super(JdbcAggregate.class, JdbcProject.class,
HiveRelFactories.HIVE_BUILDER);
+ }
+}
diff --git
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/PlanModifierForASTConv.java
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/PlanModifierForASTConv.java
index 7861cc20e95..f2f41f18133 100644
---
a/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/PlanModifierForASTConv.java
+++
b/ql/src/java/org/apache/hadoop/hive/ql/optimizer/calcite/translator/PlanModifierForASTConv.java
@@ -23,6 +23,9 @@ import java.util.List;
import java.util.stream.Collectors;
import org.apache.calcite.adapter.druid.DruidQuery;
+import org.apache.calcite.adapter.jdbc.JdbcConvention;
+import org.apache.calcite.adapter.jdbc.JdbcRel;
+import org.apache.calcite.adapter.jdbc.JdbcRules;
import org.apache.calcite.plan.RelOptUtil;
import org.apache.calcite.plan.hep.HepRelVertex;
import org.apache.calcite.plan.volcano.RelSubset;
@@ -266,8 +269,12 @@ public class PlanModifierForASTConv {
private static RelNode introduceDerivedTable(final RelNode rel) {
List<RexNode> projectList =
HiveCalciteUtil.getProjsFromBelowAsInputRef(rel);
- HiveProject select = HiveProject.create(rel.getCluster(), rel, projectList,
+ RelNode select = HiveProject.create(rel.getCluster(), rel, projectList,
rel.getRowType(), Collections.emptyList());
+
+ if (rel instanceof JdbcRel) {
+ select = JdbcRules.JdbcProjectRule.create((JdbcConvention)
rel.getConvention()).convert(select);
+ }
return select;
}
diff --git a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
index e9f5fde1a22..3e97b7d6331 100644
--- a/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
+++ b/ql/src/java/org/apache/hadoop/hive/ql/parse/CalcitePlanner.java
@@ -172,6 +172,7 @@ import
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveAggregateSortLimitR
import
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveJoinSwapConstraintsRule;
import
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveRemoveEmptySingleRules;
import
org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveSemiJoinProjectTransposeRule;
+import
org.apache.hadoop.hive.ql.optimizer.calcite.rules.jdbc.JDBCAggregateProjectMergeRule;
import
org.apache.hadoop.hive.ql.optimizer.calcite.rules.views.HiveMaterializationRelMetadataProvider;
import org.apache.hadoop.hive.ql.optimizer.calcite.HivePlannerContext;
import org.apache.hadoop.hive.ql.optimizer.calcite.HiveRelDistribution;
@@ -2293,6 +2294,7 @@ public class CalcitePlanner extends SemanticAnalyzer {
rules.add(JDBCFilterJoinRule.INSTANCE);
rules.add(JDBCFilterPushDownRule.INSTANCE);
rules.add(JDBCProjectPushDownRule.INSTANCE);
+ rules.add(JDBCAggregateProjectMergeRule.INSTANCE);
if (!conf.getBoolVar(ConfVars.HIVE_ENABLE_JDBC_SAFE_PUSHDOWN)) {
rules.add(JDBCJoinPushDownRule.INSTANCE);
rules.add(JDBCUnionPushDownRule.INSTANCE);
diff --git a/ql/src/test/queries/clientpositive/jdbc_project_pushdown.q
b/ql/src/test/queries/clientpositive/jdbc_project_pushdown.q
new file mode 100644
index 00000000000..3274a085898
--- /dev/null
+++ b/ql/src/test/queries/clientpositive/jdbc_project_pushdown.q
@@ -0,0 +1,106 @@
+--!qt:database:postgres:q_test_author_book_tables.sql
+
+CREATE EXTERNAL TABLE book
+(
+ id int,
+ title varchar(20),
+ author int
+)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "POSTGRES",
+ "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+ "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+ "hive.sql.dbcp.username" = "qtestuser",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "book"
+);
+
+CREATE EXTERNAL TABLE author
+( id int,
+fname string,
+lname string)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "POSTGRES",
+ "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+ "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+ "hive.sql.dbcp.username" = "qtestuser",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "author"
+);
+
+explain cbo select id from book where substring(title, 0, 4) = 'Les';
+explain select id from book where substring(title, 0, 4) = 'Les';
+select id from book where substring(title, 0, 4) = 'Les';
+----
+
+explain cbo select id from book where ucase(title) = 'LES MISERABLES';
+explain select id from book where ucase(title) = 'LES MISERABLES';
+select id from book where ucase(title) = 'LES MISERABLES';
+----
+
+explain cbo select book.title, author.fname from book join author on
book.author = author.id;
+explain select book.title, author.fname from book join author on book.author =
author.id;
+select book.title, author.fname from book join author on book.author =
author.id;
+----
+
+explain cbo select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug';
+
+explain select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug';
+
+select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug';
+----
+
+explain cbo
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname;
+
+explain
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname;
+
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname;
+----
+
+explain cbo
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5;
+
+explain
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5;
+
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5;
+----
diff --git a/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
b/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
index 0dc3effcef3..e7766ea6dfa 100644
--- a/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
+++ b/ql/src/test/results/clientpositive/llap/cbo_jdbc_joincost.q.out
@@ -84,10 +84,12 @@ CBO PLAN:
HiveJdbcConverter(convention=[JDBC.MYSQL]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
JdbcProject(lname=[$1], title=[$2]): rowcount = ###Masked###, cumulative
cost = ###Masked###, id = ###Masked###
JdbcJoin(condition=[=($0, $3)], joinType=[inner]): rowcount =
###Masked###, cumulative cost = ###Masked###, id = ###Masked###
- JdbcProject(id=[$0], lname=[$2]): rowcount = ###Masked###, cumulative
cost = ###Masked###, id = ###Masked###
+ JdbcProject(id=[$0], lname=[$1]): rowcount = ###Masked###, cumulative
cost = ###Masked###, id = ###Masked###
JdbcFilter(condition=[IS NOT NULL($0)]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
- JdbcHiveTableScan(table=[[default, author]], table:alias=[a]):
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
- JdbcProject(title=[$1], author=[$2]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
- JdbcFilter(condition=[IS NOT NULL($2)]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
- JdbcHiveTableScan(table=[[default, book]], table:alias=[b]):
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
+ JdbcProject(id=[$0], lname=[$2]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
+ JdbcHiveTableScan(table=[[default, author]], table:alias=[a]):
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
+ JdbcProject(title=[$0], author=[$1]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
+ JdbcFilter(condition=[IS NOT NULL($1)]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
+ JdbcProject(title=[$1], author=[$2]): rowcount = ###Masked###,
cumulative cost = ###Masked###, id = ###Masked###
+ JdbcHiveTableScan(table=[[default, book]], table:alias=[b]):
rowcount = ###Masked###, cumulative cost = ###Masked###, id = ###Masked###
diff --git
a/ql/src/test/results/clientpositive/llap/external_jdbc_rowcount.q.out
b/ql/src/test/results/clientpositive/llap/external_jdbc_rowcount.q.out
index 6cb4f73652c..10b6e1f93cd 100644
--- a/ql/src/test/results/clientpositive/llap/external_jdbc_rowcount.q.out
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_rowcount.q.out
@@ -271,21 +271,25 @@ HiveJdbcConverter(convention=[JDBC.DERBY])
JdbcFilter(condition=[IS NULL($3)])
JdbcJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[left])
JdbcProject(fkey=[$1], ikey=[$0])
- JdbcAggregate(group=[{0, 2}])
- JdbcFilter(condition=[IS NULL($6)])
- JdbcJoin(condition=[=($0, $4)], joinType=[left])
- JdbcHiveTableScan(table=[[default,
ext_simple_derby_table11]], table:alias=[ext_simple_derby_table11])
- HiveProject(ikey2=[$0], bkey2=[$1], fkey2=[$2], dkey2=[$3])
+ JdbcAggregate(group=[{0, 1}])
+ JdbcFilter(condition=[IS NULL($4)])
+ JdbcJoin(condition=[=($0, $3)], joinType=[left])
+ JdbcProject(ikey=[$0], fkey=[$2], dkey=[$3])
+ JdbcHiveTableScan(table=[[default,
ext_simple_derby_table11]], table:alias=[ext_simple_derby_table11])
+ JdbcProject(ikey2=[$0], fkey2=[$1], dkey2=[$2])
JdbcFilter(condition=[IS NOT NULL($0)])
- JdbcHiveTableScan(table=[[default,
ext_simple_derby_table13]], table:alias=[ext_simple_derby_table13])
+ JdbcProject(ikey2=[$0], fkey2=[$2], dkey2=[$3])
+ JdbcHiveTableScan(table=[[default,
ext_simple_derby_table13]], table:alias=[ext_simple_derby_table13])
JdbcProject(datekey=[$2], fkey=[$1], ikey=[$0])
- JdbcAggregate(group=[{0, 2, 4}])
- JdbcFilter(condition=[IS NULL($7)])
- JdbcJoin(condition=[=($0, $5)], joinType=[left])
- HiveProject(ikey=[$0], bkey=[$1], fkey=[$2], dkey=[$3],
datekey=[$4])
- JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT
NULL($2))])
- JdbcHiveTableScan(table=[[default,
ext_simple_derby_table12]], table:alias=[ext_simple_derby_table12])
- HiveProject(ikey2=[$0], bkey2=[$1], fkey2=[$2], dkey2=[$3],
datekey2=[$4])
+ JdbcAggregate(group=[{0, 1, 3}])
+ JdbcFilter(condition=[IS NULL($5)])
+ JdbcJoin(condition=[=($0, $4)], joinType=[left])
+ JdbcProject(ikey=[$0], fkey=[$1], dkey=[$2], datekey=[$3])
+ JdbcFilter(condition=[AND(IS NOT NULL($0), IS NOT
NULL($1))])
+ JdbcProject(ikey=[$0], fkey=[$2], dkey=[$3],
datekey=[$4])
+ JdbcHiveTableScan(table=[[default,
ext_simple_derby_table12]], table:alias=[ext_simple_derby_table12])
+ JdbcProject(ikey2=[$0], fkey2=[$1], dkey2=[$2])
JdbcFilter(condition=[IS NOT NULL($0)])
- JdbcHiveTableScan(table=[[default,
ext_simple_derby_table14]], table:alias=[ext_simple_derby_table14])
+ JdbcProject(ikey2=[$0], fkey2=[$2], dkey2=[$3])
+ JdbcHiveTableScan(table=[[default,
ext_simple_derby_table14]], table:alias=[ext_simple_derby_table14])
diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_table2.q.out
b/ql/src/test/results/clientpositive/llap/external_jdbc_table2.q.out
index 547dc662b36..f6b2e175825 100644
--- a/ql/src/test/results/clientpositive/llap/external_jdbc_table2.q.out
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_table2.q.out
@@ -714,7 +714,8 @@ STAGE PLANS:
alias: p
properties:
hive.sql.query SELECT "ikey"
-FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1"
+FROM (SELECT "ikey", "bkey", "dkey"
+FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1") AS "t"
WHERE ("bkey" = 10 AND "dkey" = 15.15 OR "bkey" = 20 AND "dkey" = 25.25) AND
"bkey" IN (10, 20) AND ("dkey" IN (15.15, 25.25) AND "ikey" IS NOT NULL)
hive.sql.query.fieldNames ikey
hive.sql.query.fieldTypes int
diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_table3.q.out
b/ql/src/test/results/clientpositive/llap/external_jdbc_table3.q.out
index d1ccf2ab928..e2f3dd048b7 100644
--- a/ql/src/test/results/clientpositive/llap/external_jdbc_table3.q.out
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_table3.q.out
@@ -204,7 +204,8 @@ STAGE PLANS:
alias: db1_ext_auth3_1
properties:
hive.sql.query SELECT "ikey", "fkey"
-FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1"
+FROM (SELECT "ikey", "fkey"
+FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1") AS "t"
WHERE "ikey" > 20
hive.sql.query.fieldNames ikey,fkey
hive.sql.query.fieldTypes int,float
@@ -229,7 +230,8 @@ WHERE "ikey" > 20
alias: db1_ext_auth3_2
properties:
hive.sql.query SELECT "ikey", "bkey"
-FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE2"
+FROM (SELECT "ikey", "bkey"
+FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE2") AS "t"
WHERE "ikey" > 20
hive.sql.query.fieldNames ikey,bkey
hive.sql.query.fieldTypes int,bigint
diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_table4.q.out
b/ql/src/test/results/clientpositive/llap/external_jdbc_table4.q.out
index a708655d2d1..a897df577b1 100644
--- a/ql/src/test/results/clientpositive/llap/external_jdbc_table4.q.out
+++ b/ql/src/test/results/clientpositive/llap/external_jdbc_table4.q.out
@@ -558,11 +558,11 @@ STAGE PLANS:
TableScan
alias: db1_ext_auth1
properties:
- hive.sql.query SELECT "ikey"
-FROM (SELECT "IKEY" AS "ikey"
+ hive.sql.query SELECT "IKEY"
+FROM (SELECT "IKEY"
FROM "EXTERNAL_JDBC_SIMPLE_DERBY2_TABLE1"
FETCH NEXT 10 ROWS ONLY) AS "t0"
- hive.sql.query.fieldNames ikey
+ hive.sql.query.fieldNames IKEY
hive.sql.query.fieldTypes int
hive.sql.query.split false
Select Operator
diff --git
a/ql/src/test/results/clientpositive/llap/jdbc_project_pushdown.q.out
b/ql/src/test/results/clientpositive/llap/jdbc_project_pushdown.q.out
new file mode 100644
index 00000000000..8525940bed8
--- /dev/null
+++ b/ql/src/test/results/clientpositive/llap/jdbc_project_pushdown.q.out
@@ -0,0 +1,671 @@
+PREHOOK: query: CREATE EXTERNAL TABLE book
+(
+ id int,
+ title varchar(20),
+ author int
+)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "POSTGRES",
+ "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+ "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+ "hive.sql.dbcp.username" = "qtestuser",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "book"
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@book
+POSTHOOK: query: CREATE EXTERNAL TABLE book
+(
+ id int,
+ title varchar(20),
+ author int
+)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "POSTGRES",
+ "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+ "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+ "hive.sql.dbcp.username" = "qtestuser",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "book"
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@book
+PREHOOK: query: CREATE EXTERNAL TABLE author
+( id int,
+fname string,
+lname string)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "POSTGRES",
+ "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+ "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+ "hive.sql.dbcp.username" = "qtestuser",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "author"
+)
+PREHOOK: type: CREATETABLE
+PREHOOK: Output: database:default
+PREHOOK: Output: default@author
+POSTHOOK: query: CREATE EXTERNAL TABLE author
+( id int,
+fname string,
+lname string)
+STORED BY
+'org.apache.hive.storage.jdbc.JdbcStorageHandler'
+TBLPROPERTIES (
+ "hive.sql.database.type" = "POSTGRES",
+ "hive.sql.jdbc.driver" = "org.postgresql.Driver",
+ "hive.sql.jdbc.url" = "jdbc:postgresql://localhost:5432/qtestDB",
+ "hive.sql.dbcp.username" = "qtestuser",
+ "hive.sql.dbcp.password" = "qtestpassword",
+ "hive.sql.table" = "author"
+)
+POSTHOOK: type: CREATETABLE
+POSTHOOK: Output: database:default
+POSTHOOK: Output: default@author
+PREHOOK: query: explain cbo select id from book where substring(title, 0, 4) =
'Les'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select id from book where substring(title, 0, 4)
= 'Les'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcProject(id=[$0])
+ JdbcFilter(condition=[=(SUBSTRING($1, 0, 4), _UTF-16LE'Les')])
+ JdbcProject(id=[$0], title=[$1])
+ JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+
+PREHOOK: query: explain select id from book where substring(title, 0, 4) =
'Les'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain select id from book where substring(title, 0, 4) =
'Les'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-0
+ Fetch Operator
+ limit: -1
+ Processor Tree:
+ TableScan
+ alias: book
+ properties:
+ hive.sql.query SELECT "id"
+FROM (SELECT "id", "title"
+FROM "book") AS "t"
+WHERE SUBSTRING("title" FROM 0 FOR 4) = 'Les'
+ hive.sql.query.fieldNames id
+ hive.sql.query.fieldTypes int
+ hive.sql.query.split true
+ Select Operator
+ expressions: id (type: int)
+ outputColumnNames: _col0
+ ListSink
+
+PREHOOK: query: select id from book where substring(title, 0, 4) = 'Les'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select id from book where substring(title, 0, 4) = 'Les'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+1
+PREHOOK: query: explain cbo select id from book where ucase(title) = 'LES
MISERABLES'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select id from book where ucase(title) = 'LES
MISERABLES'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(id=[$0])
+ HiveFilter(condition=[=(CAST(ucase($1)):VARCHAR(2147483647) CHARACTER SET
"UTF-16LE", _UTF-16LE'LES MISERABLES')])
+ HiveProject(id=[$0], title=[$1])
+ HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcProject(id=[$0], title=[$1])
+ JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+
+PREHOOK: query: explain select id from book where ucase(title) = 'LES
MISERABLES'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain select id from book where ucase(title) = 'LES
MISERABLES'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-0
+ Fetch Operator
+ limit: -1
+ Processor Tree:
+ TableScan
+ alias: book
+ properties:
+ hive.sql.query SELECT "id", "title"
+FROM "book"
+ hive.sql.query.fieldNames id,title
+ hive.sql.query.fieldTypes int,varchar(20)
+ hive.sql.query.split true
+ Select Operator
+ expressions: id (type: int), title (type: varchar(20))
+ outputColumnNames: _col0, _col1
+ Filter Operator
+ predicate: (CAST( upper(_col1) AS STRING) = 'LES MISERABLES')
(type: boolean)
+ Select Operator
+ expressions: _col0 (type: int)
+ outputColumnNames: _col0
+ ListSink
+
+PREHOOK: query: select id from book where ucase(title) = 'LES MISERABLES'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select id from book where ucase(title) = 'LES MISERABLES'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+1
+PREHOOK: query: explain cbo select book.title, author.fname from book join
author on book.author = author.id
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select book.title, author.fname from book join
author on book.author = author.id
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcProject(title=[$0], fname=[$3])
+ JdbcJoin(condition=[=($1, $2)], joinType=[inner])
+ JdbcProject(title=[$0], author=[$1])
+ JdbcFilter(condition=[IS NOT NULL($1)])
+ JdbcProject(title=[$1], author=[$2])
+ JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+ JdbcProject(id=[$0], fname=[$1])
+ JdbcFilter(condition=[IS NOT NULL($0)])
+ JdbcProject(id=[$0], fname=[$1])
+ JdbcHiveTableScan(table=[[default, author]], table:alias=[author])
+
+PREHOOK: query: explain select book.title, author.fname from book join author
on book.author = author.id
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain select book.title, author.fname from book join author
on book.author = author.id
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-0
+ Fetch Operator
+ limit: -1
+ Processor Tree:
+ TableScan
+ alias: book
+ properties:
+ hive.sql.query SELECT "t1"."title", "t4"."fname"
+FROM (SELECT "title", "author"
+FROM (SELECT "title", "author"
+FROM "book") AS "t"
+WHERE "author" IS NOT NULL) AS "t1"
+INNER JOIN (SELECT "id", "fname"
+FROM (SELECT "id", "fname"
+FROM "author") AS "t2"
+WHERE "id" IS NOT NULL) AS "t4" ON "t1"."author" = "t4"."id"
+ hive.sql.query.fieldNames title,fname
+ hive.sql.query.fieldTypes varchar(20),string
+ hive.sql.query.split false
+ Select Operator
+ expressions: title (type: varchar(20)), fname (type: string)
+ outputColumnNames: _col0, _col1
+ ListSink
+
+PREHOOK: query: select book.title, author.fname from book join author on
book.author = author.id
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select book.title, author.fname from book join author on
book.author = author.id
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+Les Miserables Victor
+The Count Of Monte C Alexandre
+PREHOOK: query: explain cbo select book.title, author.fname from book join
author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo select book.title, author.fname from book join
author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(title=[$0], fname=[$3])
+ HiveJoin(condition=[=($1, $2)], joinType=[inner], algorithm=[none],
cost=[not available])
+ HiveProject(title=[$0], author=[$1])
+ HiveFilter(condition=[=(CAST(ucase($0)):VARCHAR(2147483647) CHARACTER
SET "UTF-16LE", _UTF-16LE'LES MISERABLES')])
+ HiveProject(title=[$0], author=[$1])
+ HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcFilter(condition=[IS NOT NULL($1)])
+ JdbcProject(title=[$1], author=[$2])
+ JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+ HiveProject(id=[$0], fname=[$1])
+ HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcProject(id=[$0], fname=[$1])
+ JdbcFilter(condition=[AND(=(SUBSTRING($2, 0, 4), _UTF-16LE'Hug'), IS
NOT NULL($0))])
+ JdbcHiveTableScan(table=[[default, author]], table:alias=[author])
+
+PREHOOK: query: explain select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+ Stage-1 is a root stage
+ Stage-0 depends on stages: Stage-1
+
+STAGE PLANS:
+ Stage: Stage-1
+ Tez
+#### A masked pattern was here ####
+ Edges:
+ Reducer 2 <- Map 1 (SIMPLE_EDGE), Map 3 (SIMPLE_EDGE)
+#### A masked pattern was here ####
+ Vertices:
+ Map 1
+ Map Operator Tree:
+ TableScan
+ alias: book
+ properties:
+ hive.sql.query SELECT "title", "author"
+FROM (SELECT "title", "author"
+FROM "book") AS "t"
+WHERE "author" IS NOT NULL
+ hive.sql.query.fieldNames title,author
+ hive.sql.query.fieldTypes varchar(20),int
+ hive.sql.query.split true
+ Statistics: Num rows: 1 Data size: 108 Basic stats: COMPLETE
Column stats: NONE
+ Select Operator
+ expressions: title (type: varchar(20)), author (type: int)
+ outputColumnNames: _col0, _col1
+ Statistics: Num rows: 1 Data size: 108 Basic stats:
COMPLETE Column stats: NONE
+ Filter Operator
+ predicate: (CAST( upper(_col0) AS STRING) = 'LES
MISERABLES') (type: boolean)
+ Statistics: Num rows: 1 Data size: 108 Basic stats:
COMPLETE Column stats: NONE
+ Reduce Output Operator
+ key expressions: _col1 (type: int)
+ null sort order: z
+ sort order: +
+ Map-reduce partition columns: _col1 (type: int)
+ Statistics: Num rows: 1 Data size: 108 Basic stats:
COMPLETE Column stats: NONE
+ value expressions: _col0 (type: varchar(20))
+ Execution mode: vectorized, llap
+ LLAP IO: no inputs
+ Map 3
+ Map Operator Tree:
+ TableScan
+ alias: author
+ properties:
+ hive.sql.query SELECT "id", "fname"
+FROM "author"
+WHERE SUBSTRING("lname" FROM 0 FOR 4) = 'Hug' AND "id" IS NOT NULL
+ hive.sql.query.fieldNames id,fname
+ hive.sql.query.fieldTypes int,string
+ hive.sql.query.split true
+ Statistics: Num rows: 1 Data size: 188 Basic stats: COMPLETE
Column stats: NONE
+ Select Operator
+ expressions: id (type: int), fname (type: string)
+ outputColumnNames: _col0, _col1
+ Statistics: Num rows: 1 Data size: 188 Basic stats:
COMPLETE Column stats: NONE
+ Reduce Output Operator
+ key expressions: _col0 (type: int)
+ null sort order: z
+ sort order: +
+ Map-reduce partition columns: _col0 (type: int)
+ Statistics: Num rows: 1 Data size: 188 Basic stats:
COMPLETE Column stats: NONE
+ value expressions: _col1 (type: string)
+ Execution mode: vectorized, llap
+ LLAP IO: no inputs
+ Reducer 2
+ Execution mode: llap
+ Reduce Operator Tree:
+ Merge Join Operator
+ condition map:
+ Inner Join 0 to 1
+ keys:
+ 0 _col1 (type: int)
+ 1 _col0 (type: int)
+ outputColumnNames: _col0, _col3
+ Statistics: Num rows: 1 Data size: 118 Basic stats: COMPLETE
Column stats: NONE
+ Select Operator
+ expressions: _col0 (type: varchar(20)), _col3 (type: string)
+ outputColumnNames: _col0, _col1
+ Statistics: Num rows: 1 Data size: 118 Basic stats: COMPLETE
Column stats: NONE
+ File Output Operator
+ compressed: false
+ Statistics: Num rows: 1 Data size: 118 Basic stats:
COMPLETE Column stats: NONE
+ table:
+ input format:
org.apache.hadoop.mapred.SequenceFileInputFormat
+ output format:
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+ serde:
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+
+ Stage: Stage-0
+ Fetch Operator
+ limit: -1
+ Processor Tree:
+ ListSink
+
+PREHOOK: query: select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select book.title, author.fname from book join author
+where book.author = author.id
+and ucase(book.title) = 'LES MISERABLES'
+and substring(author.lname, 0, 4) = 'Hug'
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+Les Miserables Victor
+PREHOOK: query: explain cbo
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcAggregate(group=[{3}], agg#0=[count($0)])
+ JdbcJoin(condition=[=($1, $2)], joinType=[inner])
+ JdbcProject(title=[$0], author=[$1])
+ JdbcFilter(condition=[IS NOT NULL($1)])
+ JdbcProject(title=[$1], author=[$2])
+ JdbcHiveTableScan(table=[[default, book]], table:alias=[book])
+ JdbcProject(id=[$0], fname=[$1])
+ JdbcFilter(condition=[IS NOT NULL($0)])
+ JdbcProject(id=[$0], fname=[$1])
+ JdbcHiveTableScan(table=[[default, author]], table:alias=[author])
+
+PREHOOK: query: explain
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain
+select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+ Stage-0 is a root stage
+
+STAGE PLANS:
+ Stage: Stage-0
+ Fetch Operator
+ limit: -1
+ Processor Tree:
+ TableScan
+ alias: book
+ properties:
+ hive.sql.query SELECT "t4"."fname", COUNT("t1"."title") AS "$f1"
+FROM (SELECT "title", "author"
+FROM (SELECT "title", "author"
+FROM "book") AS "t"
+WHERE "author" IS NOT NULL) AS "t1"
+INNER JOIN (SELECT "id", "fname"
+FROM (SELECT "id", "fname"
+FROM "author") AS "t2"
+WHERE "id" IS NOT NULL) AS "t4" ON "t1"."author" = "t4"."id"
+GROUP BY "t4"."fname"
+ hive.sql.query.fieldNames fname,$f1
+ hive.sql.query.fieldTypes string,bigint
+ hive.sql.query.split false
+ Select Operator
+ expressions: fname (type: string), $f1 (type: bigint)
+ outputColumnNames: _col0, _col1
+ ListSink
+
+PREHOOK: query: select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select author.fname, count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+Victor 1
+Alexandre 1
+PREHOOK: query: explain cbo
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain cbo
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+CBO PLAN:
+HiveProject(_o__c0=[$0], books=[$1])
+ HiveSortLimit(sort0=[$2], dir0=[ASC], fetch=[5])
+ HiveProject(_o__c0=[ucase($0)], books=[$1], _o__col4=[ucase($0)])
+ HiveProject(fname=[$0], $f1=[$1])
+ HiveJdbcConverter(convention=[JDBC.POSTGRES])
+ JdbcAggregate(group=[{3}], agg#0=[count($0)])
+ JdbcJoin(condition=[=($1, $2)], joinType=[inner])
+ JdbcProject(title=[$0], author=[$1])
+ JdbcFilter(condition=[IS NOT NULL($1)])
+ JdbcProject(title=[$1], author=[$2])
+ JdbcHiveTableScan(table=[[default, book]],
table:alias=[book])
+ JdbcProject(id=[$0], fname=[$1])
+ JdbcFilter(condition=[IS NOT NULL($0)])
+ JdbcProject(id=[$0], fname=[$1])
+ JdbcHiveTableScan(table=[[default, author]],
table:alias=[author])
+
+PREHOOK: query: explain
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: explain
+select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+STAGE DEPENDENCIES:
+ Stage-1 is a root stage
+ Stage-0 depends on stages: Stage-1
+
+STAGE PLANS:
+ Stage: Stage-1
+ Tez
+#### A masked pattern was here ####
+ Edges:
+ Reducer 2 <- Map 1 (SIMPLE_EDGE)
+#### A masked pattern was here ####
+ Vertices:
+ Map 1
+ Map Operator Tree:
+ TableScan
+ alias: book
+ properties:
+ hive.sql.query SELECT "t4"."fname", COUNT("t1"."title") AS
"$f1"
+FROM (SELECT "title", "author"
+FROM (SELECT "title", "author"
+FROM "book") AS "t"
+WHERE "author" IS NOT NULL) AS "t1"
+INNER JOIN (SELECT "id", "fname"
+FROM (SELECT "id", "fname"
+FROM "author") AS "t2"
+WHERE "id" IS NOT NULL) AS "t4" ON "t1"."author" = "t4"."id"
+GROUP BY "t4"."fname"
+ hive.sql.query.fieldNames fname,$f1
+ hive.sql.query.fieldTypes string,bigint
+ hive.sql.query.split false
+ Statistics: Num rows: 1 Data size: 192 Basic stats: COMPLETE
Column stats: NONE
+ Top N Key Operator
+ sort order: +
+ keys: upper(fname) (type: string)
+ null sort order: z
+ Statistics: Num rows: 1 Data size: 192 Basic stats:
COMPLETE Column stats: NONE
+ top n: 5
+ Select Operator
+ expressions: $f1 (type: bigint), upper(fname) (type:
string)
+ outputColumnNames: _col1, _col2
+ Statistics: Num rows: 1 Data size: 192 Basic stats:
COMPLETE Column stats: NONE
+ Reduce Output Operator
+ key expressions: _col2 (type: string)
+ null sort order: z
+ sort order: +
+ Statistics: Num rows: 1 Data size: 192 Basic stats:
COMPLETE Column stats: NONE
+ value expressions: _col1 (type: bigint)
+ Execution mode: vectorized, llap
+ LLAP IO: no inputs
+ Reducer 2
+ Execution mode: vectorized, llap
+ Reduce Operator Tree:
+ Select Operator
+ expressions: KEY.reducesinkkey0 (type: string), VALUE._col0
(type: bigint)
+ outputColumnNames: _col0, _col1
+ Statistics: Num rows: 1 Data size: 192 Basic stats: COMPLETE
Column stats: NONE
+ Limit
+ Number of rows: 5
+ Statistics: Num rows: 1 Data size: 192 Basic stats: COMPLETE
Column stats: NONE
+ File Output Operator
+ compressed: false
+ Statistics: Num rows: 1 Data size: 192 Basic stats:
COMPLETE Column stats: NONE
+ table:
+ input format:
org.apache.hadoop.mapred.SequenceFileInputFormat
+ output format:
org.apache.hadoop.hive.ql.io.HiveSequenceFileOutputFormat
+ serde:
org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
+
+ Stage: Stage-0
+ Fetch Operator
+ limit: -1
+ Processor Tree:
+ ListSink
+
+PREHOOK: query: select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+PREHOOK: type: QUERY
+PREHOOK: Input: default@author
+PREHOOK: Input: default@book
+#### A masked pattern was here ####
+POSTHOOK: query: select ucase(author.fname), count(book.title) as books
+from book join author
+where book.author = author.id
+group by author.fname
+order by ucase(author.fname)
+limit 5
+POSTHOOK: type: QUERY
+POSTHOOK: Input: default@author
+POSTHOOK: Input: default@book
+#### A masked pattern was here ####
+ALEXANDRE 1
+VICTOR 1