Using sqlContext.sql for subqueries in scala

Question

I have created the following HIVE code and require it to be translated for use in scala. From what I understand, we need to use sqlContext.sql. Examples available on the Internet have only simple select statements. As shown below.

For example, to run a simple SQL query in scala:

val tableA = sqlContext.sql("Select * from game"); 

I cannot use the same syntax for the code below. What is the syntax for translating the code below so that it matches the above use.

  DROP TABLE ADW.TERA_BARCODE_LOOKUP_TABLE_RAW ; CREATE TABLE ADW.TERA_BARCODE_LOOKUP_TABLE_RAW AS SELECT CAST(BRCDE_REF_I AS STRING) AS BARCODE, MAX(TRIM(GST_SRC_ID)) AS GST_SRC_ID,MAX(SRC_ACTV_TS) AS SRC_ACTV_TS FROM (SELECT RANKED.* FROM (SELECT BRCDE_REF_I,GST_SRC_ID,SRC_ACTV_TS, RANK() over (partition by BRCDE_REF_I ORDER BY SRC_ACTV_TS DESC) AS RANK FROM ADW.GST_SRC_ID_BRCDE_LKUP_TABLE X WHERE UPPER(X.CURR_ACTV_F) = 'Y' AND TRIM(X.GST_SRC_ID) IN (SELECT TRIM(GST_SRC_I) FROM ADW.CANDIDATE_GST_ID_SRC_TABLE GROUP BY TRIM(GST_SRC_I)) ) RANKED WHERE RANKED.RANK = 1 ) X GROUP BY BRCDE_REF_I ; 

Answer 1

There are two SQL commands, and since Apache Hive does not support BEGIN and COMMIT , you can put it in two commands.

You did not submit your error. I suggested that you might also get the error on DROP TABLE , so I changed it to DROP TABLE IF EXISTS .

In addition, in Scala, to use multi-line strings, you must wrap them in """ rather than " .

 sqlContext.sql("DROP TABLE IF EXISTS ADW.TERA_BARCODE_LOOKUP_TABLE_RAW") sqlContext.sql(""" CREATE TABLE ADW.TERA_BARCODE_LOOKUP_TABLE_RAW AS SELECT CAST(BRCDE_REF_I AS STRING) AS BARCODE, MAX(TRIM(GST_SRC_ID)) AS GST_SRC_ID,MAX(SRC_ACTV_TS) AS SRC_ACTV_TS FROM (SELECT RANKED.* FROM (SELECT BRCDE_REF_I,GST_SRC_ID,SRC_ACTV_TS, RANK() over (partition by BRCDE_REF_I ORDER BY SRC_ACTV_TS DESC) AS RANK FROM ADW.GST_SRC_ID_BRCDE_LKUP_TABLE X WHERE UPPER(X.CURR_ACTV_F) = 'Y' AND TRIM(X.GST_SRC_ID) IN (SELECT TRIM(GST_SRC_I) FROM ADW.CANDIDATE_GST_ID_SRC_TABLE GROUP BY TRIM(GST_SRC_I)) ) RANKED WHERE RANKED.RANK = 1 ) X GROUP BY BRCDE_REF_I """)