Hi Royles,   I am using hiveql for creating the table, altering the table for adding new columns. Doing all the operations like msck repair table,add partition to table everything I am doing from hiveql only.Only we are reading table from sparksql.   After reading your reply,I tried to create external table,do msck repair,alter table to add new columns everything  from sparksql. I got the below results 1.No results from spark when reading data from table 2.No results from hive shell when reading table 3.If I see the tblproperties,parquet schema is not matching .So there are no results from hiveql and from spark   The only solution which I am following till now is(for adding new columns to external tbls) 1.Drop and create table using hiveql from hiveshell with all columns(old + new) 2.add latest partition manually which has data for all new columns added so far apart from beginning creation of table from hiveshell 3.query table from spark.Then check for tblproperties and parquet schema should be reflecting and mapped with hive columns 4.If the schema is not matching like testData in parquet is reflecting as testdata in hive tblproperties then we will get null values form spark 5.If both the schemas are matching,then we can see results from spark 4.then do msck repair which is giving me results in both spark 2.2 and 2.3   But I feel there must be some other way of adding new columns instead of dropping table and recreating it.       ... View more

from spark or pyspark shell use the below commands to access hive database objects.   spark.sql("show databases;") spark.sql("select * from databasename.tablename;") or spark.read.table("databasename.tablename")   You can give any query inside spark.sql which will give you results. ... View more

please find the code.UDF will take username as input and gives customer details to which user has access. You can excute in hive or impala just chaning the connection string with different ports.   import java.io.PrintWriter; import java.io.StringWriter; 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 org.apache.commons.lang.StringUtils; import org.apache.hadoop.hive.ql.exec.UDF; import org.apache.hadoop.io.Text; import org.apache.hive.jdbc.HiveDriver; import com.cloudera.impala.jdbc41.Driver; public class GetData extends UDF {          Text t = new Text();          StringWriter sw = new StringWriter();     PrintWriter pw = new PrintWriter(sw);          private static final String IMPALA_HOST = "";//server name on which impala is running     private static final String IMPALA_JDBC_PORT = "21050";     //     Define  a    string     as the     connection     URL     //public static final String IMPALA_CONNECTION_URL = "jdbc:hive2://servername:10000/;";     public static final String IMPALA_CONNECTION_URL = "jdbc:impala://" + IMPALA_HOST + ":" + IMPALA_JDBC_PORT;     //public static final String JDBC_DRIVER_NAME = "org.apache.hive.jdbc.HiveDriver";     public static final String JDBC_DRIVER_NAME = "com.cloudera.impala.jdbc41.Driver";               public Text evaluate(Text input){              String PREP_QUERY = "SELECT  customer from GetCustomer where userid ='"+input+"'";              String customer="";     Connection connection = null;     Statement statement = null;     ResultSet resultSet = null;          String strack1="";     String strack2="";     String strack3="";               try {         // Register the jdbc driver here         Class.forName(JDBC_DRIVER_NAME);         // Ensure that the user provided is present in the cluster/sandbox         connection = DriverManager.getConnection(IMPALA_CONNECTION_URL);         statement = connection.createStatement();         System.out.println("Query: " + PREP_QUERY);         // Execute and measure the execution time for query         //long startTime = System.currentTimeMillis();         resultSet = statement.executeQuery(PREP_QUERY);         //customer="Hello";         //customer = resultSet.getString(1);         //System.out.println("cust"+customer);         // Print the result in console         ResultSetMetaData rsmd = resultSet.getMetaData();         int columnsNumber = rsmd.getColumnCount();         int counter = 0;         while (resultSet.next()) {             counter++;             for (int i = 1; i <= columnsNumber; i++) {                 if (i > 1)                     //System.out.print(",  ");                 System.out.println("");                     customer = resultSet.getString(i);                 //System.out.print(rsmd.getColumnName(i) + ":" + customer);             }             //System.out.println("");         }         //long endTime = System.currentTimeMillis();         // Print query execution time         //System.out.println(String.format("\t %d found in %d ms", counter, (endTime - startTime)));     }     catch(SQLException e){         // Handle errors that are encountered during the interaction with the data store         e.printStackTrace(pw);         strack3=sw.toString();     }     catch (Exception e) {         // Handle other errors         e.printStackTrace(pw);         strack2=sw.toString();     }finally {         try {             if (resultSet!= null) {                 resultSet.close();             }         }catch (Exception s1) {             s1.printStackTrace(pw);             strack1=sw.toString();         }         try {             if (statement != null) {                 statement.close();             }         } catch (SQLException s2) {             s2.printStackTrace(pw);                                   }         try {             if (connection != null) {                 connection.close();             }         } catch (SQLException s3) {             s3.printStackTrace(pw);                      } // End try     }     //System.out.println(customer);     t.set(StringUtils.strip(customer.toString()));     //System.out.println(t);     return t;     } }   ... View more

Thank You.It worked. ... View more

I am able to achieve it in udf.   Thank You. ... View more