Repository: hive Updated Branches: refs/heads/master c44f2b531 -> 80fd0ec33
HIVE-20095: Fix feature to push computation to jdbc external tables (Jonathan Doron, reviewed by Jesus Camacho Rodriguez) Project: http://git-wip-us.apache.org/repos/asf/hive/repo Commit: http://git-wip-us.apache.org/repos/asf/hive/commit/80fd0ec3 Tree: http://git-wip-us.apache.org/repos/asf/hive/tree/80fd0ec3 Diff: http://git-wip-us.apache.org/repos/asf/hive/diff/80fd0ec3 Branch: refs/heads/master Commit: 80fd0ec33aad1fa855924a3848e0a097288fb33e Parents: c44f2b5 Author: Jonathan Doron <[email protected]> Authored: Sat Sep 22 12:52:30 2018 -0700 Committer: Jesus Camacho Rodriguez <[email protected]> Committed: Sun Sep 23 08:38:28 2018 -0700 ---------------------------------------------------------------------- .../test/resources/testconfiguration.properties | 1 + .../org/apache/hive/storage/jdbc/JdbcSerDe.java | 120 ++++- .../hive/storage/jdbc/dao/DatabaseAccessor.java | 2 + .../jdbc/dao/GenericJdbcDatabaseAccessor.java | 65 ++- .../storage/jdbc/dao/JdbcRecordIterator.java | 57 +- .../clientpositive/external_jdbc_table.q | 187 +++++++ .../test/queries/clientpositive/jdbc_handler.q | 2 +- .../llap/external_jdbc_table.q.out | 524 +++++++++++++++++++ .../clientpositive/llap/jdbc_handler.q.out | 6 +- .../clientpositive/llap/resourceplan.q.out | 96 ++-- .../results/clientpositive/llap/sysdb.q.out | 8 +- 11 files changed, 943 insertions(+), 125 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/itests/src/test/resources/testconfiguration.properties ---------------------------------------------------------------------- diff --git a/itests/src/test/resources/testconfiguration.properties b/itests/src/test/resources/testconfiguration.properties index 3672c7a..0a25903 100644 --- a/itests/src/test/resources/testconfiguration.properties +++ b/itests/src/test/resources/testconfiguration.properties @@ -512,6 +512,7 @@ minillaplocal.query.files=\ explainanalyze_2.q,\ explainuser_1.q,\ explainuser_4.q,\ + external_jdbc_table.q,\ fullouter_mapjoin_1_optimized.q,\ groupby2.q,\ groupby_groupingset_bug.q,\ http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/JdbcSerDe.java ---------------------------------------------------------------------- diff --git a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/JdbcSerDe.java b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/JdbcSerDe.java index f5472a0..8bed9f2 100644 --- a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/JdbcSerDe.java +++ b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/JdbcSerDe.java @@ -15,6 +15,9 @@ package org.apache.hive.storage.jdbc; import org.apache.hadoop.conf.Configuration; +import org.apache.hadoop.hive.conf.Constants; +import org.apache.hadoop.hive.common.type.Date; +import org.apache.hadoop.hive.common.type.Timestamp; import org.apache.hadoop.hive.serde.serdeConstants; import org.apache.hadoop.hive.serde2.AbstractSerDe; import org.apache.hadoop.hive.serde2.SerDeException; @@ -38,6 +41,7 @@ import org.apache.hive.storage.jdbc.conf.JdbcStorageConfigManager; import org.apache.hive.storage.jdbc.dao.DatabaseAccessor; import org.apache.hive.storage.jdbc.dao.DatabaseAccessorFactory; +import java.math.BigDecimal; import java.util.ArrayList; import java.util.Arrays; import java.util.List; @@ -66,6 +70,7 @@ public class JdbcSerDe extends AbstractSerDe { LOGGER.trace("Initializing the SerDe"); if (tbl.containsKey(JdbcStorageConfig.DATABASE_TYPE.getPropertyName())) { + final boolean hiveQueryExecution = tbl.containsKey(Constants.HIVE_JDBC_QUERY); Configuration tableConfig = JdbcStorageConfigManager.convertPropertiesToConfiguration(tbl); @@ -73,17 +78,24 @@ public class JdbcSerDe extends AbstractSerDe { columnNames = dbAccessor.getColumnNames(tableConfig); numColumns = columnNames.size(); List<String> hiveColumnNames; - - String[] hiveColumnNameArray = parseProperty(tbl.getProperty(serdeConstants.LIST_COLUMNS), ","); - if (numColumns != hiveColumnNameArray.length) { - throw new SerDeException("Expected " + numColumns + " columns. Table definition has " - + hiveColumnNameArray.length + " columns"); - } - hiveColumnNames = Arrays.asList(hiveColumnNameArray); - - hiveColumnTypeArray = parseProperty(tbl.getProperty(serdeConstants.LIST_COLUMN_TYPES), ":"); - if (hiveColumnTypeArray.length == 0) { - throw new SerDeException("Received an empty Hive column type definition"); + if (hiveQueryExecution) { + hiveColumnNames = columnNames; + final List<String> columnTypes = dbAccessor.getColumnTypes(tableConfig); + hiveColumnTypeArray = new String[columnTypes.size()]; + hiveColumnTypeArray = columnTypes.toArray(hiveColumnTypeArray); + } else { + + String[] hiveColumnNameArray = parseProperty(tbl.getProperty(serdeConstants.LIST_COLUMNS), ","); + if (numColumns != hiveColumnNameArray.length) { + throw new SerDeException("Expected " + numColumns + " columns. Table definition has " + + hiveColumnNameArray.length + " columns"); + } + hiveColumnNames = Arrays.asList(hiveColumnNameArray); + + hiveColumnTypeArray = parseProperty(tbl.getProperty(serdeConstants.LIST_COLUMN_TYPES), ":"); + if (hiveColumnTypeArray.length == 0) { + throw new SerDeException("Received an empty Hive column type definition"); + } } List<ObjectInspector> fieldInspectors = new ArrayList<ObjectInspector>(numColumns); @@ -94,8 +106,8 @@ public class JdbcSerDe extends AbstractSerDe { } objectInspector = - ObjectInspectorFactory.getStandardStructObjectInspector(hiveColumnNames, - fieldInspectors); + ObjectInspectorFactory.getStandardStructObjectInspector(hiveColumnNames, + fieldInspectors); row = new ArrayList<Object>(numColumns); } } @@ -133,9 +145,87 @@ public class JdbcSerDe extends AbstractSerDe { for (int i = 0; i < numColumns; i++) { columnKey.set(columnNames.get(i)); Writable value = input.get(columnKey); - row.add(value instanceof NullWritable ? null : ((ObjectWritable)value).get()); + Object rowVal; + + if(value instanceof NullWritable) { + rowVal = null; + } else { + rowVal = ((ObjectWritable)value).get(); + + switch (hiveColumnTypeArray[i].toLowerCase()) { + case "int": + case "integer": + case "smallint": + case "tinyint": + if (rowVal instanceof Number) { + rowVal = ((Number)rowVal).intValue(); + } else { + rowVal = Integer.valueOf(rowVal.toString()); + } + break; + case "bigint": + if (rowVal instanceof Long) { + rowVal = ((Number)rowVal).longValue(); + } else { + rowVal = Long.valueOf(rowVal.toString()); + } + break; + case "float": + if (rowVal instanceof Number) { + rowVal = ((Number)rowVal).floatValue(); + } else { + rowVal = Float.valueOf(rowVal.toString()); + } + break; + case "double": + if (rowVal instanceof Number) { + rowVal = ((Number)rowVal).doubleValue(); + } else { + rowVal = Double.valueOf(rowVal.toString()); + } + break; + case "bigdecimal": + if (!(rowVal instanceof BigDecimal)) { + rowVal = new BigDecimal(rowVal.toString()); + } + break; + case "boolean": + if (rowVal instanceof Number) { + rowVal = ((Number) value).intValue() != 0; + } else { + rowVal = Boolean.valueOf(value.toString()); + } + break; + case "string": + case "char": + case "varchar": + case "long varchar": + rowVal = rowVal.toString(); + break; + case "datetime": + case "time": + if (rowVal instanceof java.sql.Date) { + java.sql.Date dateRowVal = (java.sql.Date) rowVal; + rowVal = Date.ofEpochMilli(dateRowVal.getTime()); + } else { + rowVal = Date.valueOf (rowVal.toString()); + } + break; + case "timestamp": + if (rowVal instanceof java.sql.Timestamp) { + java.sql.Timestamp timestampRowVal = (java.sql.Timestamp) rowVal; + rowVal = Timestamp.ofEpochMilli(timestampRowVal.getTime(), timestampRowVal.getNanos()); + } else { + rowVal = Timestamp.valueOf (rowVal.toString()); + } + break; + default: + //do nothing + break; + } + } + row.add(rowVal); } - return row; } http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/DatabaseAccessor.java ---------------------------------------------------------------------- diff --git a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/DatabaseAccessor.java b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/DatabaseAccessor.java index f2712b8..fdaa794 100644 --- a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/DatabaseAccessor.java +++ b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/DatabaseAccessor.java @@ -24,6 +24,8 @@ public interface DatabaseAccessor { List<String> getColumnNames(Configuration conf) throws HiveJdbcDatabaseAccessException; + List<String> getColumnTypes(Configuration conf) throws HiveJdbcDatabaseAccessException; + int getTotalNumberOfRecords(Configuration conf) throws HiveJdbcDatabaseAccessException; JdbcRecordIterator http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java ---------------------------------------------------------------------- diff --git a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java index af27c48..b2ad9a6 100644 --- a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java +++ b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/GenericJdbcDatabaseAccessor.java @@ -35,6 +35,7 @@ import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; +import java.sql.Types; import java.util.ArrayList; import java.util.List; import java.util.Map; @@ -99,6 +100,68 @@ public class GenericJdbcDatabaseAccessor implements DatabaseAccessor { } @Override + public List<String> getColumnTypes(Configuration conf) throws HiveJdbcDatabaseAccessException { + Connection conn = null; + PreparedStatement ps = null; + ResultSet rs = null; + + try { + initializeDatabaseConnection(conf); + String metadataQuery = getMetaDataQuery(conf); + LOGGER.debug("Query to execute is [{}]", metadataQuery); + + conn = dbcpDataSource.getConnection(); + ps = conn.prepareStatement(metadataQuery); + rs = ps.executeQuery(); + + ResultSetMetaData metadata = rs.getMetaData(); + int numColumns = metadata.getColumnCount(); + List<String> columnTypes = new ArrayList<String>(numColumns); + for (int i = 0; i < numColumns; i++) { + switch (metadata.getColumnType(i + 1)) { + case Types.CHAR: + columnTypes.add(serdeConstants.STRING_TYPE_NAME); + break; + case Types.INTEGER: + columnTypes.add(serdeConstants.INT_TYPE_NAME); + break; + case Types.BIGINT: + columnTypes.add(serdeConstants.BIGINT_TYPE_NAME); + break; + case Types.DECIMAL: + columnTypes.add(serdeConstants.DECIMAL_TYPE_NAME); + break; + case Types.FLOAT: + case Types.REAL: + columnTypes.add(serdeConstants.FLOAT_TYPE_NAME); + break; + case Types.DOUBLE: + columnTypes.add(serdeConstants.DOUBLE_TYPE_NAME); + break; + case Types.DATE: + columnTypes.add(serdeConstants.DATE_TYPE_NAME); + break; + case Types.TIMESTAMP: + columnTypes.add(serdeConstants.TIMESTAMP_TYPE_NAME); + break; + + default: + columnTypes.add(metadata.getColumnTypeName(i+1)); + break; + } + } + + return columnTypes; + } catch (Exception e) { + LOGGER.error("Error while trying to get column names.", e); + throw new HiveJdbcDatabaseAccessException("Error while trying to get column names: " + e.getMessage(), e); + } finally { + cleanupResources(conn, ps, rs); + } + } + + + @Override public int getTotalNumberOfRecords(Configuration conf) throws HiveJdbcDatabaseAccessException { Connection conn = null; PreparedStatement ps = null; @@ -153,7 +216,7 @@ public class GenericJdbcDatabaseAccessor implements DatabaseAccessor { ps.setFetchSize(getFetchSize(conf)); rs = ps.executeQuery(); - return new JdbcRecordIterator(conn, ps, rs, conf.get(serdeConstants.LIST_COLUMN_TYPES)); + return new JdbcRecordIterator(conn, ps, rs); } catch (Exception e) { LOGGER.error("Caught exception while trying to execute query", e); http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java ---------------------------------------------------------------------- diff --git a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java index d6c2736..a95aca2 100644 --- a/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java +++ b/jdbc-handler/src/main/java/org/apache/hive/storage/jdbc/dao/JdbcRecordIterator.java @@ -14,11 +14,6 @@ */ package org.apache.hive.storage.jdbc.dao; -import org.apache.hadoop.hive.common.type.HiveDecimal; -import org.apache.hadoop.hive.serde2.typeinfo.PrimitiveTypeInfo; -import org.apache.hadoop.hive.serde2.typeinfo.TypeInfo; -import org.apache.hadoop.hive.serde2.typeinfo.TypeInfoUtils; -import org.apache.hadoop.io.NullWritable; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -26,7 +21,6 @@ import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; -import java.util.ArrayList; import java.util.HashMap; import java.util.Iterator; import java.util.Map; @@ -41,15 +35,12 @@ public class JdbcRecordIterator implements Iterator<Map<String, Object>> { private Connection conn; private PreparedStatement ps; private ResultSet rs; - private ArrayList<TypeInfo> columnTypes = null; - public JdbcRecordIterator(Connection conn, PreparedStatement ps, ResultSet rs, String typeString) { + + public JdbcRecordIterator(Connection conn, PreparedStatement ps, ResultSet rs) { this.conn = conn; this.ps = ps; this.rs = rs; - if (typeString != null) { - this.columnTypes = TypeInfoUtils.getTypeInfosFromTypeString(typeString); - } } @@ -73,48 +64,8 @@ public class JdbcRecordIterator implements Iterator<Map<String, Object>> { Map<String, Object> record = new HashMap<String, Object>(numColumns); for (int i = 0; i < numColumns; i++) { String key = metadata.getColumnName(i + 1); - Object value; - if (columnTypes!=null && columnTypes.get(i) instanceof PrimitiveTypeInfo) { - // This is not a complete list, barely make information schema work - switch (((PrimitiveTypeInfo)columnTypes.get(i)).getTypeName()) { - case "int": - case "smallint": - case "tinyint": - value = rs.getInt(i + 1); - break; - case "bigint": - value = rs.getLong(i + 1); - break; - case "float": - value = rs.getFloat(i + 1); - break; - case "double": - value = rs.getDouble(i + 1); - break; - case "bigdecimal": - value = HiveDecimal.create(rs.getBigDecimal(i + 1)); - break; - case "boolean": - value = rs.getBoolean(i + 1); - break; - case "string": - case "char": - case "varchar": - value = rs.getString(i + 1); - break; - case "datetime": - value = rs.getDate(i + 1); - break; - case "timestamp": - value = rs.getTimestamp(i + 1); - break; - default: - value = rs.getObject(i + 1); - break; - } - } else { - value = rs.getObject(i + 1); - } + Object value = rs.getObject(i + 1); + record.put(key, value); } http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/ql/src/test/queries/clientpositive/external_jdbc_table.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/external_jdbc_table.q b/ql/src/test/queries/clientpositive/external_jdbc_table.q new file mode 100644 index 0000000..3e629d2 --- /dev/null +++ b/ql/src/test/queries/clientpositive/external_jdbc_table.q @@ -0,0 +1,187 @@ +--! qt:dataset:src + +set hive.strict.checks.cartesian.product= false; + + +CREATE TABLE simple_hive_table1 (ikey INT, bkey BIGINT, fkey FLOAT, dkey DOUBLE ); + +CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput'; + + +FROM src + +SELECT + +dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'CREATE TABLE SIMPLE_DERBY_TABLE1 ("ikey" INTEGER, "bkey" BIGINT, "fkey" REAL, "dkey" DOUBLE)' ), + +dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','20','20','20.0','20.0'), + +dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','-20','-20','-20.0','-20.0'), + +dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','100','-15','65.0','-74.0'), + +dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','44','53','-455.454','330.76') + +limit 1; + +FROM src + +SELECT + +dboutput ( 'jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'CREATE TABLE SIMPLE_DERBY_TABLE2 ("ikey" INTEGER, "bkey" BIGINT, "fkey" REAL, "dkey" DOUBLE )' ), + +dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','20','20','20.0','20.0'), + +dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','-20','8','9.0','11.0'), + +dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','101','-16','66.0','-75.0'), + +dboutput('jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true','','', +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','40','50','-455.4543','330.767') + +limit 1; + + +CREATE EXTERNAL TABLE ext_simple_derby_table1 +( + ikey int, + bkey bigint, + fkey float, + dkey double +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", + "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true;collation=TERRITORY_BASED:PRIMARY", + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "SIMPLE_DERBY_TABLE1", + "hive.sql.dbcp.maxActive" = "1" +); + + +CREATE EXTERNAL TABLE ext_simple_derby_table2 +( + ikey int, + bkey bigint, + fkey float, + dkey double +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", + "hive.sql.jdbc.url" = "jdbc:derby:;databaseName=${system:test.tmp.dir}/test_derby_as_external_table_db;create=true;collation=TERRITORY_BASED:PRIMARY", + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "SIMPLE_DERBY_TABLE2", + "hive.sql.dbcp.maxActive" = "1" +); + + +select * from ext_simple_derby_table1; + +--Test projection +select dkey,fkey,bkey,ikey from ext_simple_derby_table1; +select bkey+ikey,fkey+dkey from ext_simple_derby_table1; +select abs(dkey),abs(ikey),abs(fkey),abs(bkey) from ext_simple_derby_table1; + + + +--Test aggregation +select count(*) from ext_simple_derby_table1; +select count(distinct bkey) from ext_simple_derby_table1; +select count(ikey), sum(bkey), avg(dkey), max(fkey) from ext_simple_derby_table1; + + +--Test sort +select dkey from ext_simple_derby_table1 order by dkey; +select SUM_IKEY,bkey from (select sum(-ikey) as SUM_IKEY, bkey from ext_simple_derby_table1 group by bkey) ttt order by bkey; + +--Test filter +explain select bkey from ext_simple_derby_table1 where 100 < ext_simple_derby_table1.ikey; +select bkey from ext_simple_derby_table1 where 100 < ext_simple_derby_table1.ikey; + +SELECT distinct dkey from ext_simple_derby_table1 where ikey = '100'; +SELECT count(*) FROM (select * from ext_simple_derby_table1) v WHERE ikey = 100; +SELECT count(*) from ext_simple_derby_table1 having count(*) > 0; +select sum(8),8 from ext_simple_derby_table1 where ikey = 1 group by 2; + + +--Test join +explain select ext_simple_derby_table1.fkey, ext_simple_derby_table2.dkey from ext_simple_derby_table1 join ext_simple_derby_table2 on +(ext_simple_derby_table1.ikey = ext_simple_derby_table2.ikey); + +select ext_simple_derby_table1.fkey, ext_simple_derby_table2.dkey from ext_simple_derby_table1 join ext_simple_derby_table2 on +(ext_simple_derby_table1.ikey = ext_simple_derby_table2.ikey); + + +explain select simple_hive_table1.fkey, ext_simple_derby_table2.dkey from simple_hive_table1 join ext_simple_derby_table2 on +(simple_hive_table1.ikey = ext_simple_derby_table2.ikey); + +select simple_hive_table1.fkey, ext_simple_derby_table2.dkey from simple_hive_table1 join ext_simple_derby_table2 on +(simple_hive_table1.ikey = ext_simple_derby_table2.ikey); + + +--Test union + +SELECT ikey FROM simple_hive_table1 +UNION +SELECT bkey FROM ext_simple_derby_table2; + + + + + + + + + + + + +----FAILURES---- + +--The following does not work due to invalid generated derby syntax: +--SELECT "dkey", COUNT("bkey") AS "$f1" FROM "SIMPLE_DERBY_TABLE1" GROUP BY "dkey" OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY {LIMIT 1} + +--SELECT dkey,count(bkey) from ext_simple_derby_table1 group by dkey limit 10; + + + + + +--Fails parse.CalcitePlanner: CBO failed, skipping CBO. +--select sum(fkey) from ext_simple_derby_table1 where bkey in (10, 100); + + + + +--Fails to ClassCastException +-- + + + + +--SELECT ikey FROM ext_simple_derby_table1 +--UNION +--SELECT bkey FROM ext_simple_derby_table2; + + + +--Fails due to cast exception in SqlImplementor line 539: +--select sum(bkey) from ext_simple_derby_table1 where ikey = 2450894 OR ikey = 2450911; + + + +--select dkey from ext_simple_derby_table1 order by dkey limit 10 offset 60; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/ql/src/test/queries/clientpositive/jdbc_handler.q ---------------------------------------------------------------------- diff --git a/ql/src/test/queries/clientpositive/jdbc_handler.q b/ql/src/test/queries/clientpositive/jdbc_handler.q index 4d7effd..d086735 100644 --- a/ql/src/test/queries/clientpositive/jdbc_handler.q +++ b/ql/src/test/queries/clientpositive/jdbc_handler.q @@ -21,7 +21,7 @@ limit 1; CREATE EXTERNAL TABLE ext_simple_derby_table ( - kkey bigint + kkey int ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/ql/src/test/results/clientpositive/llap/external_jdbc_table.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/external_jdbc_table.q.out b/ql/src/test/results/clientpositive/llap/external_jdbc_table.q.out new file mode 100644 index 0000000..4a1d874 --- /dev/null +++ b/ql/src/test/results/clientpositive/llap/external_jdbc_table.q.out @@ -0,0 +1,524 @@ +PREHOOK: query: CREATE TABLE simple_hive_table1 (ikey INT, bkey BIGINT, fkey FLOAT, dkey DOUBLE ) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@simple_hive_table1 +POSTHOOK: query: CREATE TABLE simple_hive_table1 (ikey INT, bkey BIGINT, fkey FLOAT, dkey DOUBLE ) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@simple_hive_table1 +PREHOOK: query: CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput' +PREHOOK: type: CREATEFUNCTION +PREHOOK: Output: dboutput +POSTHOOK: query: CREATE TEMPORARY FUNCTION dboutput AS 'org.apache.hadoop.hive.contrib.genericudf.example.GenericUDFDBOutput' +POSTHOOK: type: CREATEFUNCTION +POSTHOOK: Output: dboutput +PREHOOK: query: FROM src + +SELECT + +#### A masked pattern was here #### +'CREATE TABLE SIMPLE_DERBY_TABLE1 ("ikey" INTEGER, "bkey" BIGINT, "fkey" REAL, "dkey" DOUBLE)' ), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','20','20','20.0','20.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','-20','-20','-20.0','-20.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','100','-15','65.0','-74.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','44','53','-455.454','330.76') + +limit 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: FROM src + +SELECT + +#### A masked pattern was here #### +'CREATE TABLE SIMPLE_DERBY_TABLE1 ("ikey" INTEGER, "bkey" BIGINT, "fkey" REAL, "dkey" DOUBLE)' ), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','20','20','20.0','20.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','-20','-20','-20.0','-20.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','100','-15','65.0','-74.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE1 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','44','53','-455.454','330.76') + +limit 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +0 0 0 0 0 +PREHOOK: query: FROM src + +SELECT + +#### A masked pattern was here #### +'CREATE TABLE SIMPLE_DERBY_TABLE2 ("ikey" INTEGER, "bkey" BIGINT, "fkey" REAL, "dkey" DOUBLE )' ), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','20','20','20.0','20.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','-20','8','9.0','11.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','101','-16','66.0','-75.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','40','50','-455.4543','330.767') + +limit 1 +PREHOOK: type: QUERY +PREHOOK: Input: default@src +#### A masked pattern was here #### +POSTHOOK: query: FROM src + +SELECT + +#### A masked pattern was here #### +'CREATE TABLE SIMPLE_DERBY_TABLE2 ("ikey" INTEGER, "bkey" BIGINT, "fkey" REAL, "dkey" DOUBLE )' ), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','20','20','20.0','20.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','-20','8','9.0','11.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','101','-16','66.0','-75.0'), + +#### A masked pattern was here #### +'INSERT INTO SIMPLE_DERBY_TABLE2 ("ikey","bkey","fkey","dkey") VALUES (?,?,?,?)','40','50','-455.4543','330.767') + +limit 1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@src +#### A masked pattern was here #### +0 0 0 0 0 +PREHOOK: query: CREATE EXTERNAL TABLE ext_simple_derby_table1 +( + ikey int, + bkey bigint, + fkey float, + dkey double +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "SIMPLE_DERBY_TABLE1", + "hive.sql.dbcp.maxActive" = "1" +) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@ext_simple_derby_table1 +POSTHOOK: query: CREATE EXTERNAL TABLE ext_simple_derby_table1 +( + ikey int, + bkey bigint, + fkey float, + dkey double +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "SIMPLE_DERBY_TABLE1", + "hive.sql.dbcp.maxActive" = "1" +) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@ext_simple_derby_table1 +PREHOOK: query: CREATE EXTERNAL TABLE ext_simple_derby_table2 +( + ikey int, + bkey bigint, + fkey float, + dkey double +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "SIMPLE_DERBY_TABLE2", + "hive.sql.dbcp.maxActive" = "1" +) +PREHOOK: type: CREATETABLE +PREHOOK: Output: database:default +PREHOOK: Output: default@ext_simple_derby_table2 +POSTHOOK: query: CREATE EXTERNAL TABLE ext_simple_derby_table2 +( + ikey int, + bkey bigint, + fkey float, + dkey double +) +STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' +TBLPROPERTIES ( + "hive.sql.database.type" = "DERBY", + "hive.sql.jdbc.driver" = "org.apache.derby.jdbc.EmbeddedDriver", +#### A masked pattern was here #### + "hive.sql.dbcp.username" = "APP", + "hive.sql.dbcp.password" = "mine", + "hive.sql.table" = "SIMPLE_DERBY_TABLE2", + "hive.sql.dbcp.maxActive" = "1" +) +POSTHOOK: type: CREATETABLE +POSTHOOK: Output: database:default +POSTHOOK: Output: default@ext_simple_derby_table2 +PREHOOK: query: select * from ext_simple_derby_table1 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select * from ext_simple_derby_table1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +20 20 20.0 20.0 +-20 -20 -20.0 -20.0 +100 -15 65.0 -74.0 +44 53 -455.454 330.76 +PREHOOK: query: select dkey,fkey,bkey,ikey from ext_simple_derby_table1 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select dkey,fkey,bkey,ikey from ext_simple_derby_table1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +20.0 20.0 20 20 +-20.0 -20.0 -20 -20 +-74.0 65.0 -15 100 +330.76 -455.454 53 44 +PREHOOK: query: select bkey+ikey,fkey+dkey from ext_simple_derby_table1 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select bkey+ikey,fkey+dkey from ext_simple_derby_table1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +40 40.0 +-40 -40.0 +85 -9.0 +97 -124.69401000976563 +PREHOOK: query: select abs(dkey),abs(ikey),abs(fkey),abs(bkey) from ext_simple_derby_table1 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select abs(dkey),abs(ikey),abs(fkey),abs(bkey) from ext_simple_derby_table1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +20.0 20 20.0 20 +20.0 20 20.0 20 +74.0 100 65.0 15 +330.76 44 455.454 53 +PREHOOK: query: select count(*) from ext_simple_derby_table1 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select count(*) from ext_simple_derby_table1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +4 +PREHOOK: query: select count(distinct bkey) from ext_simple_derby_table1 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select count(distinct bkey) from ext_simple_derby_table1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +4 +PREHOOK: query: select count(ikey), sum(bkey), avg(dkey), max(fkey) from ext_simple_derby_table1 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select count(ikey), sum(bkey), avg(dkey), max(fkey) from ext_simple_derby_table1 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +4 38 64.19 65.0 +PREHOOK: query: select dkey from ext_simple_derby_table1 order by dkey +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select dkey from ext_simple_derby_table1 order by dkey +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +-74.0 +-20.0 +20.0 +330.76 +PREHOOK: query: select SUM_IKEY,bkey from (select sum(-ikey) as SUM_IKEY, bkey from ext_simple_derby_table1 group by bkey) ttt order by bkey +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select SUM_IKEY,bkey from (select sum(-ikey) as SUM_IKEY, bkey from ext_simple_derby_table1 group by bkey) ttt order by bkey +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +20 -20 +-100 -15 +-20 20 +-44 53 +PREHOOK: query: explain select bkey from ext_simple_derby_table1 where 100 < ext_simple_derby_table1.ikey +PREHOOK: type: QUERY +POSTHOOK: query: explain select bkey from ext_simple_derby_table1 where 100 < ext_simple_derby_table1.ikey +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + TableScan + alias: ext_simple_derby_table1 + properties: + hive.sql.generated.query SELECT "bkey" +FROM "SIMPLE_DERBY_TABLE1" +WHERE 100 < "ikey" + hive.sql.query SELECT "bkey" +FROM "SIMPLE_DERBY_TABLE1" +WHERE 100 < "ikey" + Select Operator + expressions: bkey (type: bigint) + outputColumnNames: _col0 + ListSink + +PREHOOK: query: select bkey from ext_simple_derby_table1 where 100 < ext_simple_derby_table1.ikey +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select bkey from ext_simple_derby_table1 where 100 < ext_simple_derby_table1.ikey +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +PREHOOK: query: SELECT distinct dkey from ext_simple_derby_table1 where ikey = '100' +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: SELECT distinct dkey from ext_simple_derby_table1 where ikey = '100' +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +-74.0 +PREHOOK: query: SELECT count(*) FROM (select * from ext_simple_derby_table1) v WHERE ikey = 100 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: SELECT count(*) FROM (select * from ext_simple_derby_table1) v WHERE ikey = 100 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +1 +PREHOOK: query: SELECT count(*) from ext_simple_derby_table1 having count(*) > 0 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: SELECT count(*) from ext_simple_derby_table1 having count(*) > 0 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +4 +PREHOOK: query: select sum(8),8 from ext_simple_derby_table1 where ikey = 1 group by 2 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +POSTHOOK: query: select sum(8),8 from ext_simple_derby_table1 where ikey = 1 group by 2 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +#### A masked pattern was here #### +PREHOOK: query: explain select ext_simple_derby_table1.fkey, ext_simple_derby_table2.dkey from ext_simple_derby_table1 join ext_simple_derby_table2 on +(ext_simple_derby_table1.ikey = ext_simple_derby_table2.ikey) +PREHOOK: type: QUERY +POSTHOOK: query: explain select ext_simple_derby_table1.fkey, ext_simple_derby_table2.dkey from ext_simple_derby_table1 join ext_simple_derby_table2 on +(ext_simple_derby_table1.ikey = ext_simple_derby_table2.ikey) +POSTHOOK: type: QUERY +STAGE DEPENDENCIES: + Stage-0 is a root stage + +STAGE PLANS: + Stage: Stage-0 + Fetch Operator + limit: -1 + Processor Tree: + TableScan + alias: ext_simple_derby_table1 + properties: + hive.sql.generated.query SELECT "t"."fkey", "t0"."dkey" +FROM (SELECT * +FROM "SIMPLE_DERBY_TABLE1" +WHERE "ikey" IS NOT NULL) AS "t" +INNER JOIN (SELECT * +FROM "SIMPLE_DERBY_TABLE2" +WHERE "ikey" IS NOT NULL) AS "t0" ON "t"."ikey" = "t0"."ikey" + hive.sql.query SELECT "t"."fkey", "t0"."dkey" +FROM (SELECT * +FROM "SIMPLE_DERBY_TABLE1" +WHERE "ikey" IS NOT NULL) AS "t" +INNER JOIN (SELECT * +FROM "SIMPLE_DERBY_TABLE2" +WHERE "ikey" IS NOT NULL) AS "t0" ON "t"."ikey" = "t0"."ikey" + Select Operator + expressions: fkey (type: float), dkey (type: double) + outputColumnNames: _col0, _col1 + ListSink + +PREHOOK: query: select ext_simple_derby_table1.fkey, ext_simple_derby_table2.dkey from ext_simple_derby_table1 join ext_simple_derby_table2 on +(ext_simple_derby_table1.ikey = ext_simple_derby_table2.ikey) +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table1 +PREHOOK: Input: default@ext_simple_derby_table2 +#### A masked pattern was here #### +POSTHOOK: query: select ext_simple_derby_table1.fkey, ext_simple_derby_table2.dkey from ext_simple_derby_table1 join ext_simple_derby_table2 on +(ext_simple_derby_table1.ikey = ext_simple_derby_table2.ikey) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table1 +POSTHOOK: Input: default@ext_simple_derby_table2 +#### A masked pattern was here #### +20.0 20.0 +-20.0 11.0 +PREHOOK: query: explain select simple_hive_table1.fkey, ext_simple_derby_table2.dkey from simple_hive_table1 join ext_simple_derby_table2 on +(simple_hive_table1.ikey = ext_simple_derby_table2.ikey) +PREHOOK: type: QUERY +POSTHOOK: query: explain select simple_hive_table1.fkey, ext_simple_derby_table2.dkey from simple_hive_table1 join ext_simple_derby_table2 on +(simple_hive_table1.ikey = ext_simple_derby_table2.ikey) +POSTHOOK: type: QUERY +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: simple_hive_table1 + filterExpr: ikey is not null (type: boolean) + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + Filter Operator + predicate: ikey is not null (type: boolean) + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: ikey (type: int), fkey (type: float) + outputColumnNames: _col0, _col1 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: _col0 (type: int) + sort order: + + Map-reduce partition columns: _col0 (type: int) + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + value expressions: _col1 (type: float) + Execution mode: vectorized, llap + LLAP IO: no inputs + Map 3 + Map Operator Tree: + TableScan + alias: ext_simple_derby_table2 + properties: + hive.sql.generated.query SELECT * +FROM "SIMPLE_DERBY_TABLE2" +WHERE "ikey" IS NOT NULL + hive.sql.query SELECT * +FROM "SIMPLE_DERBY_TABLE2" +WHERE "ikey" IS NOT NULL + Statistics: Num rows: 1 Data size: 12 Basic stats: COMPLETE Column stats: NONE + Reduce Output Operator + key expressions: ikey (type: int) + sort order: + + Map-reduce partition columns: ikey (type: int) + Statistics: Num rows: 1 Data size: 12 Basic stats: COMPLETE Column stats: NONE + value expressions: dkey (type: double) + 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 _col0 (type: int) + 1 ikey (type: int) + outputColumnNames: _col1, _col5 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + Select Operator + expressions: _col1 (type: float), _col5 (type: double) + outputColumnNames: _col0, _col1 + Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: NONE + File Output Operator + compressed: false + Statistics: Num rows: 1 Data size: 8 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 simple_hive_table1.fkey, ext_simple_derby_table2.dkey from simple_hive_table1 join ext_simple_derby_table2 on +(simple_hive_table1.ikey = ext_simple_derby_table2.ikey) +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table2 +PREHOOK: Input: default@simple_hive_table1 +#### A masked pattern was here #### +POSTHOOK: query: select simple_hive_table1.fkey, ext_simple_derby_table2.dkey from simple_hive_table1 join ext_simple_derby_table2 on +(simple_hive_table1.ikey = ext_simple_derby_table2.ikey) +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table2 +POSTHOOK: Input: default@simple_hive_table1 +#### A masked pattern was here #### +PREHOOK: query: SELECT ikey FROM simple_hive_table1 +UNION +SELECT bkey FROM ext_simple_derby_table2 +PREHOOK: type: QUERY +PREHOOK: Input: default@ext_simple_derby_table2 +PREHOOK: Input: default@simple_hive_table1 +#### A masked pattern was here #### +POSTHOOK: query: SELECT ikey FROM simple_hive_table1 +UNION +SELECT bkey FROM ext_simple_derby_table2 +POSTHOOK: type: QUERY +POSTHOOK: Input: default@ext_simple_derby_table2 +POSTHOOK: Input: default@simple_hive_table1 +#### A masked pattern was here #### +8 +-16 +20 +50 http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out b/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out index f55bda5..ade8d46 100644 --- a/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out +++ b/ql/src/test/results/clientpositive/llap/jdbc_handler.q.out @@ -37,7 +37,7 @@ POSTHOOK: Input: default@src 0 0 0 PREHOOK: query: CREATE EXTERNAL TABLE ext_simple_derby_table ( - kkey bigint + kkey int ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -54,7 +54,7 @@ PREHOOK: Output: database:default PREHOOK: Output: default@ext_simple_derby_table POSTHOOK: query: CREATE EXTERNAL TABLE ext_simple_derby_table ( - kkey bigint + kkey int ) STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler' TBLPROPERTIES ( @@ -101,7 +101,7 @@ WHERE 100 < "kkey" FROM "SIMPLE_DERBY_TABLE" WHERE 100 < "kkey" Select Operator - expressions: kkey (type: bigint) + expressions: kkey (type: int) outputColumnNames: _col0 ListSink http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/ql/src/test/results/clientpositive/llap/resourceplan.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/resourceplan.q.out b/ql/src/test/results/clientpositive/llap/resourceplan.q.out index 0d4236e..c11daf7 100644 --- a/ql/src/test/results/clientpositive/llap/resourceplan.q.out +++ b/ql/src/test/results/clientpositive/llap/resourceplan.q.out @@ -3195,7 +3195,7 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 DISABLED 0 default +plan_1 DISABLED NULL default PREHOOK: query: CREATE RESOURCE PLAN plan_2 WITH QUERY_PARALLELISM=5 PREHOOK: type: CREATE RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3229,7 +3229,7 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 DISABLED 0 default +plan_1 DISABLED NULL default plan_2 DISABLED 10 default PREHOOK: query: CREATE RESOURCE PLAN plan_2 PREHOOK: type: CREATE RESOURCEPLAN @@ -3253,7 +3253,7 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 DISABLED 0 default +plan_1 DISABLED NULL default plan_2 DISABLED 10 default PREHOOK: query: ALTER RESOURCE PLAN plan_1 RENAME TO plan_3 PREHOOK: type: ALTER RESOURCEPLAN @@ -3269,7 +3269,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 DISABLED 0 default +plan_3 DISABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 SET QUERY_PARALLELISM = 4 PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3299,7 +3299,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 DISABLED 0 default +plan_3 DISABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 SET QUERY_PARALLELISM = 30, DEFAULT POOL = default1 PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3313,7 +3313,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 DISABLED 0 default +plan_3 DISABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 ENABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3341,7 +3341,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 DISABLED 0 default +plan_3 DISABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 ACTIVATE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3355,7 +3355,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 DISABLED 0 default +plan_3 DISABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 DISABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3370,7 +3370,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 DISABLED 0 default +plan_3 DISABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 ENABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3385,7 +3385,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 ENABLED 0 default +plan_3 ENABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 ACTIVATE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3400,7 +3400,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 ACTIVE 0 default +plan_3 ACTIVE NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 ACTIVATE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3415,7 +3415,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 ACTIVE 0 default +plan_3 ACTIVE NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 ENABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3429,7 +3429,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 ACTIVE 0 default +plan_3 ACTIVE NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 DISABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3443,7 +3443,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 ACTIVE 0 default +plan_3 ACTIVE NULL default PREHOOK: query: DISABLE WORKLOAD MANAGEMENT PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3458,7 +3458,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 ENABLED 0 default +plan_3 ENABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 DISABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3478,7 +3478,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 DISABLED 10 default -plan_3 ACTIVE 0 default +plan_3 ACTIVE NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_2 ENABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3493,7 +3493,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 ENABLED 10 default -plan_3 ACTIVE 0 default +plan_3 ACTIVE NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_2 ACTIVATE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3508,7 +3508,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 ACTIVE 10 default -plan_3 ENABLED 0 default +plan_3 ENABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 ENABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3523,7 +3523,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 ACTIVE 10 default -plan_3 ENABLED 0 default +plan_3 ENABLED NULL default PREHOOK: query: ALTER RESOURCE PLAN plan_3 DISABLE PREHOOK: type: ALTER RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3538,7 +3538,7 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### plan_2 ACTIVE 10 default -plan_3 DISABLED 0 default +plan_3 DISABLED NULL default PREHOOK: query: DROP RESOURCE PLAN plan_2 PREHOOK: type: DROP RESOURCEPLAN PREHOOK: Output: dummyHostnameForTest @@ -3765,7 +3765,7 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ENABLED 0 default +plan_1 ENABLED NULL default plan_2 ACTIVE 10 default table DISABLED 1 default PREHOOK: query: DROP TRIGGER plan_1.trigger_2 @@ -3789,7 +3789,7 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ACTIVE 0 default +plan_1 ACTIVE NULL default plan_2 ENABLED 10 default table DISABLED 1 default PREHOOK: query: DROP TRIGGER plan_1.trigger_2 @@ -4095,7 +4095,7 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ACTIVE 0 default +plan_1 ACTIVE NULL default plan_2 DISABLED 10 def table DISABLED 1 default PREHOOK: query: ALTER RESOURCE PLAN `table` SET DEFAULT POOL = `table`.pool @@ -4137,7 +4137,7 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ACTIVE 0 default +plan_1 ACTIVE NULL default plan_2 DISABLED 10 def table DISABLED 1 NULL PREHOOK: query: ALTER POOL plan_2.def.c1 ADD TRIGGER trigger_1 @@ -4433,9 +4433,9 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ENABLED 0 default -plan_2 DISABLED 0 default -plan_4 ACTIVE 0 default +plan_1 ENABLED NULL default +plan_2 DISABLED NULL default +plan_4 ACTIVE NULL default table DISABLED 1 NULL PREHOOK: query: SELECT * FROM SYS.WM_POOLS PREHOOK: type: QUERY @@ -4528,11 +4528,11 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ENABLED 0 default -plan_2 DISABLED 0 default -plan_4 ACTIVE 0 default -plan_4a DISABLED 0 default -plan_4b DISABLED 0 default +plan_1 ENABLED NULL default +plan_2 DISABLED NULL default +plan_4 ACTIVE NULL default +plan_4a DISABLED NULL default +plan_4b DISABLED NULL default table DISABLED 1 NULL PREHOOK: query: SELECT * FROM SYS.WM_POOLS PREHOOK: type: QUERY @@ -4603,11 +4603,11 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ENABLED 0 default -plan_2 DISABLED 0 default -plan_4 ACTIVE 0 default -plan_4a DISABLED 0 default -plan_4a_old_0 DISABLED 0 default +plan_1 ENABLED NULL default +plan_2 DISABLED NULL default +plan_4 ACTIVE NULL default +plan_4a DISABLED NULL default +plan_4a_old_0 DISABLED NULL default table DISABLED 1 NULL PREHOOK: query: SELECT * FROM SYS.WM_POOLS PREHOOK: type: QUERY @@ -4653,11 +4653,11 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ENABLED 0 default -plan_2 DISABLED 0 default -plan_4 ACTIVE 0 default -plan_4_old_0 DISABLED 0 default -plan_4a_old_0 DISABLED 0 default +plan_1 ENABLED NULL default +plan_2 DISABLED NULL default +plan_4 ACTIVE NULL default +plan_4_old_0 DISABLED NULL default +plan_4a_old_0 DISABLED NULL default table DISABLED 1 NULL PREHOOK: query: CREATE RESOURCE PLAN plan_4a LIKE plan_4 PREHOOK: type: CREATE RESOURCEPLAN @@ -4682,12 +4682,12 @@ POSTHOOK: query: SELECT * FROM SYS.WM_RESOURCEPLANS POSTHOOK: type: QUERY POSTHOOK: Input: sys@wm_resourceplans #### A masked pattern was here #### -plan_1 ENABLED 0 default -plan_2 DISABLED 0 default -plan_4 ACTIVE 0 default -plan_4_old_0 DISABLED 0 default -plan_4_old_1 DISABLED 0 default -plan_4a_old_0 DISABLED 0 default +plan_1 ENABLED NULL default +plan_2 DISABLED NULL default +plan_4 ACTIVE NULL default +plan_4_old_0 DISABLED NULL default +plan_4_old_1 DISABLED NULL default +plan_4a_old_0 DISABLED NULL default table DISABLED 1 NULL PREHOOK: query: SELECT * FROM SYS.WM_POOLS PREHOOK: type: QUERY http://git-wip-us.apache.org/repos/asf/hive/blob/80fd0ec3/ql/src/test/results/clientpositive/llap/sysdb.q.out ---------------------------------------------------------------------- diff --git a/ql/src/test/results/clientpositive/llap/sysdb.q.out b/ql/src/test/results/clientpositive/llap/sysdb.q.out index c6cae95..172be1c 100644 --- a/ql/src/test/results/clientpositive/llap/sysdb.q.out +++ b/ql/src/test/results/clientpositive/llap/sysdb.q.out @@ -4110,8 +4110,8 @@ POSTHOOK: type: QUERY POSTHOOK: Input: sys@tab_col_stats #### A masked pattern was here #### alltypesorc cbigint 3115 5917 -alltypesorc cboolean1 3114 0 -alltypesorc cboolean2 3115 0 +alltypesorc cboolean1 3114 NULL +alltypesorc cboolean2 3115 NULL alltypesorc cdouble 3114 5527 alltypesorc cfloat 3115 131 alltypesorc cint 3115 6104 @@ -4127,12 +4127,12 @@ POSTHOOK: query: select table_name, partition_name, column_name, num_nulls, num_ POSTHOOK: type: QUERY POSTHOOK: Input: sys@part_col_stats #### A masked pattern was here #### -cbo_t1 dt=2014 c_boolean 2 0 +cbo_t1 dt=2014 c_boolean 2 NULL cbo_t1 dt=2014 c_float 2 1 cbo_t1 dt=2014 c_int 2 1 cbo_t1 dt=2014 key 2 4 cbo_t1 dt=2014 value 2 4 -cbo_t2 dt=2014 c_boolean 2 0 +cbo_t2 dt=2014 c_boolean 2 NULL cbo_t2 dt=2014 c_float 2 2 cbo_t2 dt=2014 c_int 2 2 cbo_t2 dt=2014 key 2 5
