Advise on using spark shell for Hive table sql queries

[Mich Talebzadeh <mi...@peridale.co.uk>]

Hi,

 

 

Pretty new to spark shell. 

 

So decided to write this piece of code to get the data from spark shell on
Hiver tables. The issue is that I don't really need to define the sqlContext
here as I can do a simple command like sql("select count(1) from t") WITHOUT
sqlContext. sql("select count(1) from t")

 

 

Also I found out that I can write multiple sql lines using three quotation
-->  """"code..........."""

 

Are there other ways of writing multi line sql code?

 

The code works fine, However, any improvements will be welcome

 

val sqlContext  = new org.apache.spark.sql.hive.HiveContext(sc)

var sqltext : String = ""

sqltext = "use oraclehadoop"

sqlContext.sql(sqltext)

sqlContext.sql("SELECT FROM_unixtime(unix_timestamp(), 'dd/MM/yyyy
HH:mm:ss.ss') AS StartTime").show()

println("\n Running the query \n")

sqltext = """

SELECT c.country_name AS nation,

       s.year,

       s.month,

       sum (amount_sold) AS salesamount

FROM  countries c

INNER JOIN customers cs

ON c.country_id = cs.country_id

INNER JOIN sales s

ON cs.cust_id = s.cust_id

GROUP BY country_name, s.year, s.month

ORDER BY country_name, s.year, s.month

"""

// sql(sqltext)show(99999999)

sqlContext.sql(sqltext).collect.foreach(println)

 

sqlContext.sql("SELECT FROM_unixtime(unix_timestamp(), 'dd/MM/yyyy
HH:mm:ss.ss') AS EndTime").show()

sys.exit()

 

 

Regards,

 

Mich

 

 <http://talebzadehmich.wordpress.com/> http://talebzadehmich.wordpress.com

 

NOTE: The information in this email is proprietary and confidential. This
message is for the designated recipient only, if you are not the intended
recipient, you should destroy it immediately. Any information in this
message shall not be understood as given or endorsed by Peridale Technology
Ltd, its subsidiaries or their employees, unless expressly so stated. It is
the responsibility of the recipient to ensure that this email is virus free,
therefore neither Peridale Technology Ltd, its subsidiaries nor their
employees accept any responsibility.