DRILL-735: Add a unittest to test reading from Hive table with all supported data types of partitions and fields.
Project: http://git-wip-us.apache.org/repos/asf/incubator-drill/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-drill/commit/3bcead76 Tree: http://git-wip-us.apache.org/repos/asf/incubator-drill/tree/3bcead76 Diff: http://git-wip-us.apache.org/repos/asf/incubator-drill/diff/3bcead76 Branch: refs/heads/master Commit: 3bcead76387e0cc63d2dde04bce28db5b1cf0942 Parents: c3dfe37 Author: vkorukanti <[email protected]> Authored: Fri Jun 13 14:44:12 2014 -0700 Committer: Jacques Nadeau <[email protected]> Committed: Mon Jun 16 07:53:13 2014 -0700 ---------------------------------------------------------------------- .../exec/store/hive/HiveTestDataGenerator.java | 76 +++++++++++++++++--- .../apache/drill/jdbc/test/TestHiveStorage.java | 52 +++++++++++++- .../apache/drill/jdbc/test/TestMetadataDDL.java | 10 +-- 3 files changed, 123 insertions(+), 15 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/3bcead76/exec/java-exec/src/test/java/org/apache/drill/exec/store/hive/HiveTestDataGenerator.java ---------------------------------------------------------------------- diff --git a/exec/java-exec/src/test/java/org/apache/drill/exec/store/hive/HiveTestDataGenerator.java b/exec/java-exec/src/test/java/org/apache/drill/exec/store/hive/HiveTestDataGenerator.java index f914661..e051abb 100644 --- a/exec/java-exec/src/test/java/org/apache/drill/exec/store/hive/HiveTestDataGenerator.java +++ b/exec/java-exec/src/test/java/org/apache/drill/exec/store/hive/HiveTestDataGenerator.java @@ -90,15 +90,75 @@ public class HiveTestDataGenerator { // create a Hive table that has columns with data types which are supported for reading in Drill. testDataFile = generateAllTypesDataFile(); - executeQuery("CREATE TABLE IF NOT EXISTS allReadSupportedHiveDataTypes (c1 INT, c2 BOOLEAN, c3 DOUBLE, c4 STRING, " + - "c9 TINYINT, c10 SMALLINT, c11 FLOAT, c12 BIGINT, c19 BINARY) " + - "ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE"); - executeQuery(String.format("LOAD DATA LOCAL INPATH '%s' OVERWRITE INTO TABLE " + - "default.allReadSupportedHiveDataTypes", testDataFile)); + executeQuery( + "CREATE TABLE IF NOT EXISTS readtest (" + + " binary_field BINARY," + + " boolean_field BOOLEAN," + + " tinyint_field TINYINT," + + " decimal_field DECIMAL," + + " double_field DOUBLE," + + " float_field FLOAT," + + " int_field INT," + + " bigint_field BIGINT," + + " smallint_field SMALLINT," + + " string_field STRING," + + " varchar_field VARCHAR(50)," + + " timestamp_field TIMESTAMP," + + " date_field DATE" + + ") PARTITIONED BY (" + + " binary_part BINARY," + + " boolean_part BOOLEAN," + + " tinyint_part TINYINT," + + " decimal_part DECIMAL," + + " double_part DOUBLE," + + " float_part FLOAT," + + " int_part INT," + + " bigint_part BIGINT," + + " smallint_part SMALLINT," + + " string_part STRING," + + " varchar_part VARCHAR(50)," + + " timestamp_part TIMESTAMP," + + " date_part DATE" + + ") ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' STORED AS TEXTFILE" + ); + + // Add a partition to table 'readtest' + executeQuery( + "ALTER TABLE readtest ADD IF NOT EXISTS PARTITION ( " + + " binary_part='binary', " + + " boolean_part='true', " + + " tinyint_part='64', " + + " decimal_part='3489423929323435243', " + + " double_part='8.345', " + + " float_part='4.67', " + + " int_part='123456', " + + " bigint_part='234235', " + + " smallint_part='3455', " + + " string_part='string', " + + " varchar_part='varchar', " + + " timestamp_part='2013-07-05 17:01:00', " + + " date_part='2013-07-05')" + ); + + // Load data into table 'readtest' + executeQuery(String.format("LOAD DATA LOCAL INPATH '%s' OVERWRITE INTO TABLE default.readtest PARTITION (" + + " binary_part='binary', " + + " boolean_part='true', " + + " tinyint_part='64', " + + " decimal_part='3489423929323435243', " + + " double_part='8.345', " + + " float_part='4.67', " + + " int_part='123456', " + + " bigint_part='234235', " + + " smallint_part='3455', " + + " string_part='string', " + + " varchar_part='varchar', " + + " timestamp_part='2013-07-05 17:01:00', " + + " date_part='2013-07-05')", testDataFile)); // create a table that has all Hive types. This is to test how hive tables metadata is populated in // Drill's INFORMATION_SCHEMA. - executeQuery("CREATE TABLE IF NOT EXISTS allHiveDataTypes(" + + executeQuery("CREATE TABLE IF NOT EXISTS infoschematest(" + "booleanType BOOLEAN, " + "tinyintType TINYINT, " + "smallintType SMALLINT, " + @@ -176,9 +236,7 @@ public class HiveTestDataGenerator { File file = getTempFile(); PrintWriter printWriter = new PrintWriter(file); - printWriter.println("\\N,\\N,\\N,\\N,\\N,\\N,\\N,\\N,\\N"); - printWriter.println("-1,false,-1.1,,-1,-1,-1.0,-1,\\N"); - printWriter.println("1,true,1.1,1,1,1,1.0,1,YWJjZA=="); + printWriter.println("YmluYXJ5ZmllbGQ=,false,34,3489423929323435243,8.345,4.67,123456,234235,3455,stringfield,varcharfield,2013-07-05 17:01:00,2013-07-05"); printWriter.close(); return file.getPath(); http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/3bcead76/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestHiveStorage.java ---------------------------------------------------------------------- diff --git a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestHiveStorage.java b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestHiveStorage.java index e4ef332..1f70942 100644 --- a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestHiveStorage.java +++ b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestHiveStorage.java @@ -32,7 +32,6 @@ public class TestHiveStorage extends JdbcTestQueryBase{ public void testHiveReadWithDb() throws Exception{ testQuery("select * from hive.`default`.kv"); testQuery("select key from hive.`default`.kv group by key"); - testQuery("select * from hive.`default`.allreadsupportedhivedatatypes"); } @Test @@ -46,4 +45,55 @@ public class TestHiveStorage extends JdbcTestQueryBase{ testQuery("SELECT * FROM hive.`default`.empty_table"); } + @Test + public void testReadAllSupportedHiveDataTypes() throws Exception { + // There are known issues with displaying VarBinary in JDBC. So for now just execute the query and do not + // verify the results until display issues with VarBinary are resolved. + testQuery("SELECT * FROM hive.`default`.readtest"); + + /* + JdbcAssert.withFull("hive.default") + .sql("SELECT * FROM readtest") + .returns( + "binary_field=[B@7005f08f; " + // know issues with binary display + "boolean_field=false; " + + "tinyint_field=34; " + + "decimal_field=3489423929323435243; " + + "double_field=8.345; " + + "float_field=4.67; " + + "int_field=123456; " + + "bigint_field=234235; " + + "smallint_field=3455; " + + "string_field=stringfield; " + + "varchar_field=varcharfield; " + + "timestamp_field=2013-07-05T17:01:00.000-07:00; " + + "date_field=2013-07-05T00:00:00.000-07:00; " + + "binary_part=[B@7008383e; " + // know issues with binary display + "boolean_part=true; " + + "tinyint_part=64; " + + "decimal_part=3489423929323435243; " + + "double_part=8.345; " + + "float_part=4.67; " + + "int_part=123456; " + + "bigint_part=234235; " + + "smallint_part=3455; " + + "string_part=string; " + + "varchar_part=varchar; " + + "timestamp_part=2013-07-05T17:01:00.000-07:00; " + + "date_part=2013-07-05T00:00:00.000-07:00"); + */ + } + + @Test + public void testOrderByOnHiveTable() throws Exception { + JdbcAssert.withFull("hive.default") + .sql("SELECT * FROM kv ORDER BY `value` DESC") + .returns( + "key=5; value= key_5\n" + + "key=4; value= key_4\n" + + "key=3; value= key_3\n" + + "key=2; value= key_2\n" + + "key=1; value= key_1\n" + ); + } } http://git-wip-us.apache.org/repos/asf/incubator-drill/blob/3bcead76/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestMetadataDDL.java ---------------------------------------------------------------------- diff --git a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestMetadataDDL.java b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestMetadataDDL.java index 228bf84..e6e540a 100644 --- a/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestMetadataDDL.java +++ b/exec/jdbc/src/test/java/org/apache/drill/jdbc/test/TestMetadataDDL.java @@ -58,10 +58,10 @@ public class TestMetadataDDL extends JdbcTestQueryBase { JdbcAssert.withFull("hive.default") .sql("SHOW TABLES") .returns( + "TABLE_SCHEMA=hive.default; TABLE_NAME=readtest\n" + "TABLE_SCHEMA=hive.default; TABLE_NAME=empty_table\n" + - "TABLE_SCHEMA=hive.default; TABLE_NAME=allhivedatatypes\n" + + "TABLE_SCHEMA=hive.default; TABLE_NAME=infoschematest\n" + "TABLE_SCHEMA=hive.default; TABLE_NAME=hiveview\n" + - "TABLE_SCHEMA=hive.default; TABLE_NAME=allreadsupportedhivedatatypes\n" + "TABLE_SCHEMA=hive.default; TABLE_NAME=kv\n" + "TABLE_SCHEMA=hive.default; TABLE_NAME=foodate\n" ); @@ -82,10 +82,10 @@ public class TestMetadataDDL extends JdbcTestQueryBase { JdbcAssert.withFull("dfs.tmp") .sql("SHOW TABLES IN hive.`default`") .returns( + "TABLE_SCHEMA=hive.default; TABLE_NAME=readtest\n" + "TABLE_SCHEMA=hive.default; TABLE_NAME=empty_table\n" + - "TABLE_SCHEMA=hive.default; TABLE_NAME=allhivedatatypes\n" + + "TABLE_SCHEMA=hive.default; TABLE_NAME=infoschematest\n" + "TABLE_SCHEMA=hive.default; TABLE_NAME=hiveview\n" + - "TABLE_SCHEMA=hive.default; TABLE_NAME=allreadsupportedhivedatatypes\n" + "TABLE_SCHEMA=hive.default; TABLE_NAME=kv\n" + "TABLE_SCHEMA=hive.default; TABLE_NAME=foodate\n"); } @@ -247,7 +247,7 @@ public class TestMetadataDDL extends JdbcTestQueryBase { JdbcAssert.withNoDefaultSchema() .sql("SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION " + "FROM INFORMATION_SCHEMA.`COLUMNS` " + - "WHERE TABLE_SCHEMA = 'hive.default' AND TABLE_NAME = 'allhivedatatypes' AND " + + "WHERE TABLE_SCHEMA = 'hive.default' AND TABLE_NAME = 'infoschematest' AND " + "(COLUMN_NAME = 'stringtype' OR COLUMN_NAME = 'varchartype' OR " + "COLUMN_NAME = 'inttype' OR COLUMN_NAME = 'decimaltype')") .returns(
