Unable to view Hive records in Spark SQL, but can view them on Hive CLI

Question

I am unable to view Hive records in Spark SQL, but can view them on Hive CLI.

Where the Hive CLI shows 2 records, the same query using the hive context shows 0

In Hive

hive> select ord_id from order;
OK
157434411
157435932
Time taken: 0.389 seconds, Fetched: 2 row(s)

In Spark SQL

   hiveCtx.sql("select ord_id from order").show()
   +------------+
   |ord_id      |
   +------------+
   +------------+

I have attempted to refresh the table, and restarted Hive, but the issue remains.

I have checked the solution from unable-to-view-data-of-hive-tables-after-update-in-spark but nothing seems to work.

Any advice would be gratefully received.

EDIT:

Corrected the name of the columns above. I am also providing the output from desc:

hive> desc rpt_derived.rpttradeleg;
OK                                  
ord_id              string                                      
ord_date            timestamp                                                                   
cust_buy_sell       string                                      
ord_cust_entity     string                                                              
ord_deal_year           int                                         
ord_deal_month          int                                         
ord_deal_day            int                                         

# Partition Information      
# col_name              data_type               comment             

ord_year        int                                         
ord_month       int                                         
ord_day         int                                         
Time taken: 2.036 seconds, Fetched: 16 row(s)
hive> 

From Spark SQL:

scala> hiveContext.sql("desc rpt_derived.rpttradeleg").show()
+--------------------+---------+-------+
|            col_name|data_type|comment|
+--------------------+---------+-------+
|              ord_id|   string|       |
|            ord_date|timestamp|       |
|       cust_buy_sell|   string|       |
|     ord_cust_entity|   string|       |
|       ord_deal_year|      int|       |
|      ord_deal_month|      int|       |
|        ord_deal_day|      int|       |
+--------------------+---------+-------+

Spark version: spark-1.5.0+cdh5.5.2+114

Hive version: hive-1.1.0+cdh5.5.2+377

Answer 1

To connect to hive metastore you need to copy the hive-site.xml file into 
spark/conf directory.After that spark will be able to connect to hive 
metastore.
so run the  following command after log in as root user   



cp  /usr/lib/hive/conf/hive-site.xml    /usr/lib/spark/conf/

or use hiveCtx.sql("select *from databasename.tablename").show()

I hope, it will work for you.