How to build a dynamic query string to execute on a spark-sql 2.3.1 dataframe?

Question

I have a requirement to get the where condition passed by user as program arguments. Based on the where condition i need to query the source data base.

I am using spark-sql.2.3.1 How to construct and pass/executive dynamically build query?

Sample query:

select ProductId, COUNT(*) AS ProductSaleCount
 from productsale
 where to_date(Date) >= "2015-12-17"
 and to_date(Date) <= "2015-12-31"
 group by ProductId

@Alex ott , sir how are you editing it so neatly when i tried it is not in proper format... where/how can I format it like you did ? — BdEngineer
– BdEngineer, Commented Oct 31, 2018 at 17:23
just ident the code with 4 spaces, or select the code and press {} icon... Here is instructions: stackoverflow.com/editing-help — Alex Ott
– Alex Ott, Commented Oct 31, 2018 at 18:09

Pritish Kamath · Accepted Answer · 2018-10-31 14:37:39Z

1

All you have to do in your scenario is create a query string which would go something like:

val query = "select ProductId, COUNT(*) AS ProductSaleCount from productsale where to_date(Date) >= "+ fromDate +" and to_date(Date) <= " + toDate + " group by ProductId"

the fromDate and toDate, you would get from your arguments, perhaps.

To use this, however is a different issue and it depends on your database

For hive you can simply register your spark session with enableHiveSupport

val spark = SparkSession.builder().appName("My App").enableHiveSupport().config("spark.sql.warehouse.dir", warehouseLocation).getOrCreate()

val data = spark.sqlContext.sql(query)

If the data is in a dataframe and you want to query that, you would have to create a view and then run your query on that

finalDataFrame.createOrReplaceTempView("productsale")

val data = spark.sqlContext.sql(query)

Hope this helps

answered Oct 31, 2018 at 14:37

Pritish Kamath

1591 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BdEngineer Over a year ago

when i try to run after enableHaveSupport() it is giving error " Exception in thread "main" org.apache.spark.sql.AnalysisException: java.lang.RuntimeException: java.lang.RuntimeException: The root scratch dir: /tmp/hive on HDFS should be writable. Current permissions are: rw-rw-rw-; at org.apache.spark.sql.hive.HiveExternalCatalog.withClient(HiveExternalCatalog.scala:106) "

BdEngineer Over a year ago

i am still getting error stackoverflow.com/review/suggested-edits/21297036

Collectives™ on Stack Overflow

How to build a dynamic query string to execute on a spark-sql 2.3.1 dataframe?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related