Github user bharatm363 commented on a diff in the pull request:
https://github.com/apache/bigtop/pull/370#discussion_r216026538
--- Diff: bigtop-tests/smoke-tests/hive/HiveJdbcGeneralTest.java ---
@@ -0,0 +1,390 @@
+
+/**
+ * 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
+ * <p/>
+ * http://www.apache.org/licenses/LICENSE-2.0
+ * <p/>
+ * 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.
+ */
+import static org.junit.Assert.assertEquals;
+import static org.junit.Assert.assertFalse;
+import static org.junit.Assert.assertTrue;
+import static org.junit.Assert.fail;
+
+import java.io.File;
+import java.sql.Connection;
+import java.sql.DriverManager;
+import java.sql.ResultSet;
+import java.sql.ResultSetMetaData;
+import java.sql.SQLException;
+import java.sql.Statement;
+import java.util.Properties;
+
+import org.apache.hive.service.cli.HiveSQLException;
+import org.junit.AfterClass;
+import org.junit.BeforeClass;
+import org.junit.Test;
+
+/**
+ *
+ * THIS CLASS TESTS THE FOLLOWING ASPECTS OF HIVE:
+ *
+ * Connect to hive; Drop the tables if already there; Test Show Tables;
Create
+ * blank tables, both transactional and non, along with ORC format and
+ * partitions; Reprint the list of tables; Load File into HDFS; Load data
into
+ * tables; describe tables; Delete Uploaded File; Print table contents with
+ * various queries; Test Prepared and Callable Statements; Test Fetch
Sizes;
+ * Test ACID (UPDATE/INSERT/DELETE); View Partitions;
+ */
+public class HiveJdbcGeneralTest extends TestMethods {
+
+ static String hivePort = System.getenv("HIVE_PORT");
+ static String jdbcConnection = System.getenv("HIVE_JDBC_URL");
+ static Connection con;
+ String newTableName = "btest";
+
+ @BeforeClass
+ public static void onTimeSetup() throws Exception {
+ String username = System.getenv("HIVE_USER");
+ String password = System.getenv("HIVE_PASSWORD");
+ Properties connectionProps = new Properties();
+ connectionProps.put("user", username);
+ connectionProps.put("password", password);
+ Class.forName("org.apache.hive.jdbc.HiveDriver");
+ con = DriverManager.getConnection(
+ jdbcConnection + ":" + hivePort + "/default;", connectionProps);
+ }
+
+ @AfterClass
+ public static void oneTimeTearDown() throws Exception {
+ con.close();
+ }
+
+ @Test // (expected=java.sql.SQLDataException.class)
+ public void testTableCreation() throws Exception {
+ final File f = new File(HiveJdbcGeneralTest.class.getProtectionDomain()
+ .getCodeSource().getLocation().getPath());
+ String hdfsConnection =
+ propertyValue("hdfs-site.xml", "dfs.namenode.rpc-address");
+ try (Statement stmt = con.createStatement()) {
+ String columnNames =
+ "(Flight int, Dates varchar(255), Depart varchar(10), Orig
varchar(5), Dest varchar(5), Miles int, Boarded int, Capacity double)";
+ String partitionedColumns =
+ "(Flight int, Dates varchar(255), Depart varchar(10), Orig
varchar(5), Dest varchar(5), Miles int, Boarded int)";
+ String localFilepath = f + "/samdat1.csv";
+ String HdfsURI = "hdfs://" + hdfsConnection;
+ String filePath = "/tmp/htest/00000_";
+ String fileDestination = HdfsURI + filePath;
+ assertFalse(con.getMetaData().supportsRefCursors());
+ assertTrue(con.getMetaData().allTablesAreSelectable());
+ assertEquals("Apache Hive",
con.getMetaData().getDatabaseProductName());
+ System.out.println(
+ "Hive Version: " + con.getMetaData().getDatabaseMajorVersion() +
"."
+ + con.getMetaData().getDatabaseMinorVersion());
+ getTables(con, newTableName);
+ dropTable(stmt, newTableName);
+ dropTable(stmt, newTableName + "NT");
+ dropTable(stmt, newTableName + "T");
+ dropTable(stmt, newTableName + "P");
+ dropTable(stmt, newTableName + "V");
+ showTables(stmt, "show tables like 'b*'");
+ createTable(stmt, newTableName, columnNames, ",", "");
+ try {
+ createTable(stmt, newTableName + "NT", columnNames, ",",
+ "TBLPROPERTIES(\"transactional\"=\"true\")");
+ fail("shouldn't get here");
+ } catch (SQLException e) {
+
+ }
+ createTable(stmt, newTableName + "T", columnNames, ",",
+ "STORED AS ORC TBLPROPERTIES(\"transactional\"=\"true\")");
+ createPartitionedTable(stmt, newTableName + "P", partitionedColumns,
+ "(Capacity double)", ",", "STORED AS ORC");
+ createTable(stmt, newTableName + "V", "(Dest varchar(5))", ",",
+ "STORED AS ORC");
+ showTables(stmt, "show tables like 'b*'");
+ loadFile(localFilepath, HdfsURI, fileDestination + ".txt");
+ loadData(stmt, filePath + ".txt", newTableName);
+ describeTable(stmt, newTableName);
+ updateTable(stmt, "Insert into table btestt SELECT * from btest");
+ updateTable(stmt, "Insert into table btestv SELECT Dest from btest");
+ updateTable(stmt,
+ "Insert into table btestp PARTITION (Capacity) SELECT * from
btest");
+ deleteFile(stmt, filePath + ".txt", HdfsURI);
+ deleteFile(stmt, filePath + "0.orc", HdfsURI);
+ assertEquals("302", printResults(stmt, "Select * from btest"));
+ assertEquals("114", printResults(stmt,
+ "Select * from btest where Dest = 'LAX' order by boarded desc"));
+ assertEquals("622", printResults(stmt,
+ "Select * from btest where boarded between 160 and 180 order by
boarded asc"));
+ assertEquals("202", printResults(stmt,
+ "Select * from btest where Dest='LAX' or Dest='ORD'"));
+ assertEquals("114", printResults(stmt,
+ "Select * from btest where Dest= 'LAX' and boarded >= 180"));
+ assertEquals("114", printResults(stmt,
+ "Select * from btest where Dest ='LAX' and boarded = 197"));
+ assertEquals("219",
+ printResults(stmt, "Select * from btest LIMIT 10 --"));
+ assertEquals("114", preparedStatement(con,
+ "Select * from btest where Dest ='LAX' and boarded = 197"));
+ assertEquals(15, setFetchSizeStatement(stmt));
+ assertEquals(15, setFetchSizePreparedStatement(con));
+ // assertEquals(callableStatement(con, -20), -1);
+ assertEquals("95487", printResults(stmt, "Select SUM(Miles) from
btest"));
+ assertEquals("302", printResults(stmt, "Select * from btest"));
+ assertEquals(0, updateTable(stmt,
+ "Update btestt set Orig= 'test' where Dest= 'LAX'"));
+ try {
+ loadData(stmt, filePath + ".txt", newTableName + "T");
+ fail("shouldn't get here");
+ } catch (HiveSQLException e) {
+ System.out.println("File does not exist in specified location");
+ }
+ assertEquals("132",
+ printResults(stmt, "Select * from btestt order by Dest"));
+ assertEquals("capacity=250.0",
+ printResults(stmt, "show partitions btestp"));
+ assertEquals("LAX", printResults(stmt,
+ "select MIN(Dest), boarded from btest where Dest ='LAX' group by
Dest, boarded"));
+ assertEquals("19", printResults(stmt,
+ "select count(*) from btest where capacity = 250.0 group by
capacity"));
+ executeStatement(stmt, "Drop view testview");
+ executeStatement(stmt,
+ "Create view testview AS select SUM(btest.boarded) BOARDED from
btest, btestv where btest.Dest=btestv.Dest");
+ assertEquals("45072", printResults(stmt, "Select * from testview"));
+ executeStatement(stmt, "Drop view testview");
+ printResults(stmt, "Describe formatted btest");
+ setNegativeFetchSize(stmt);
+ }
+ }
+ @Test public void testTableDeletion() throws Exception{
+ try (Statement stmt = con.createStatement()) {
+ dropTable(stmt, newTableName);
+ dropTable(stmt, newTableName + "NT");
+ dropTable(stmt, newTableName + "T");
+ dropTable(stmt, newTableName + "P");
+ dropTable(stmt, newTableName + "V");
+ }
+ }
+
+ @Test
+ public void testFunctionsandMetaData() throws Exception {
+
+ String queryValues = "insert into table btestm values"
+ + " ('Alfred', 'M', 69.0, 122.5, 'AJH', 1),"
+ + " ('Alfred', 'M', 71.0, 130.5, 'AJH', 2),"
+ + " ('Alicia', 'F', 56.5, 84.0, 'BJH', 1),"
+ + " ('Alicia', 'F', 60.5, 86.9, 'BJH', 2),"
+ + " ('Benicia', 'F', 65.3, 98.0, 'BJH', 1),"
+ + " ('Benicia', 'F', 69.3, 99.1, 'BJH', 2),"
+ + " ('Bennett', 'F', 63.2, 96.2, 'AJH', 1),"
+ + " ('Bennett', 'F', 69.2, 98.2, 'AJH', 2),"
+ + " ('Carol', 'F', 62.8, 102.5, 'BJH', 1),"
+ + " ('Carol', 'F', 65.3, 105.4, 'BJH', 2),"
+ + " ('Carlos', 'M', 63.7, 102.9, 'AJH', 1),"
+ + " ('Carlos', 'M', 70.3, 106.9, 'AJH', 2),"
+ + " ('Henry', 'M', 63.5, 102.5, 'AJH', 1),"
+ + " ('Henry', 'M', 68.9, 108.6, 'AJH', 2),"
+ + " ('Jaime', 'M', 57.3, 86.0, 'BJH', 1),"
+ + " ('Jaime', 'M', 62.9, 90.0, 'BJH', 2),"
+ + " ('Janet', 'F', 59.8, 84.5, 'AJH', 1),"
+ + " ('Janet', 'F', 62.5, 86.5, 'AJH', 2),"
+ + " ('Jean', 'M', 68.2, 113.4, 'AJH', 1),"
+ + " ('Jean', 'M', 70.3, 116.0, 'AJH', 2),"
+ + " ('Joyce', 'M', 51.3, 50.5, 'BJH', 1),"
+ + " ('Joyce', 'M', 55.5, 53.5, 'BJH', 2),"
+ + " ('Luc', 'M', 66.3, 77.0, 'AJH', 1),"
+ + " ('Luc', 'M', 69.3, 82.9, 'AJH', 2),"
+ + " ('Marie', 'F', 66.5, 112.0, 'BJH', 1),"
+ + " ('Marie', 'F', 69.5, 114.9, 'BJH', 2),"
+ + " ('Medford', 'M', 64.9, 114.0, 'AJH', 1),"
+ + " ('Medford', 'M', NULL, NULL, NULL, NULL),"
+ + " ('Philip', 'M', 69.0, 115.0, 'AJH', 1),"
+ + " ('Philip', 'M', 70.0, 118.0, 'AJH', 2),"
+ + " ('Robert', 'M', 64.8, 128.0, 'BJH', 1),"
+ + " ('Robert', 'M', 68.3, NULL, 'BJH', 2),"
+ + " ('Thomas', 'M', 57.5, 85.0, 'AJH', 1),"
+ + " ('Thomas', 'M', 59.1, 92.3, 'AJH', 2),"
+ + " ('Wakana', 'F', 61.3, 99.0, 'AJH', 1),"
+ + " ('Wakana', 'F', 63.8, 102.9, 'AJH', 2),"
+ + " ('William', 'M', 66.5, 112.0, 'BJH', 1),"
+ + " ('William', 'M', 68.3, 118.2, 'BJH', 2)";
+
+ // failing query from SAS Institute, code generated by PROC MEANS
+ String failingQuery =
+ " select COUNT(*) as ZSQL1, MIN(TXT_1.`school`) as ZSQL2,
MIN(TXT_1.`time`) as"
+ + " ZSQL3, COUNT(*) as ZSQL4, COUNT(TXT_1.`height`) as ZSQL5,
MIN(TXT_1.`height`)"
+ + " as ZSQL6, MAX(TXT_1.`height`) as ZSQL7,
SUM(TXT_1.`height`) as ZSQL8,"
+ + "
COALESCE(VAR_SAMP(TXT_1.`height`)*(COUNT(TXT_1.`height`)-1),0) as ZSQL9,"
+ + " COUNT(TXT_1.`weight`) as ZSQL10, MIN(TXT_1.`weight`) as
ZSQL11,"
+ + " MAX(TXT_1.`weight`) as ZSQL12, SUM(TXT_1.`weight`) as
ZSQL13,"
+ + "
COALESCE(VAR_SAMP(TXT_1.`weight`)*(COUNT(TXT_1.`weight`)-1),0) as ZSQL14 from"
+ + " `btestm` TXT_1 where TXT_1.`school` = 'AJH' group by"
+ + " TXT_1.`school`, TXT_1.`time`";
+
+ try (Statement stmt = con.createStatement()) {
+ dropTable(stmt, newTableName + "M");
+ stmt.executeUpdate(
+ "create table btestm (name varchar(8), sex varchar(8), height
double, weight double, school varchar(8), `time` double)
TBLPROPERTIES('SASFMT:t'='TIME(10.0)')");
+ stmt.executeUpdate(queryValues);
+
+ try (ResultSet res = stmt.executeQuery(failingQuery)) {
+ assertTrue(res.next());
+ assertEquals(null, res.getString(2));
+ ResultSetMetaData rsmd = res.getMetaData();
+ assertEquals(19, rsmd.getPrecision(1));
+ assertEquals(-5, rsmd.getColumnType(1));
+ assertEquals(14, rsmd.getColumnCount());
+ printResults(stmt, "describe formatted btestm");
+ ResultSet res2 = stmt.executeQuery("describe formatted btestm");
+ while (res2.next()) {
+ String section = res2.getString(1);
+ if (section.contains("Detailed"))
+ break;
+ }
+ boolean found = false;
+ while (res2.next()) {
+ String value = res2.getString(2);
+ if (value != null && value.contains("SASFMT"))
+
+ {
--- End diff --
Fixed
---
