Dynamically passing Query String for selecting columns in PySpark Dataframe method selectExpr()

Question

I am generating a query string dynamically as follows and passing it to selectExpr().

queryString=''''category_id as cat_id','category_department_id as cat_dpt_id','category_name as cat_name''''
df.selectExpr(queryString)

As per document

selectExpr(*expr) : Projects a set of SQL expressions and returns a new DataFrame. This is a variant of select() that accepts SQL expressions.

The issue is that the variable "queryString" is being treated as a single string instead of three separate columns ( and rightly so ). Following is the error:

: org.apache.spark.sql.catalyst.parser.ParseException: .........

== SQL ==

'category_id as cat_id', 'category_department_id as cat_dpt_id', 'category_name as cat_name'

------------------------^^^

Is there any way I can pass the dynamically generated "queryString" as an argument of selectExpr().

pansen · Accepted Answer · 2018-10-18 13:47:30Z

5

If possible, while generating your query string, try to put the individual column expressions in a list right away instead of concatenating them into one string.

If not possible, you can split your query string to have seperated column expressions which can be passed to selectExpr.

# generate some dummy data
data= pd.DataFrame(np.random.randint(0, 5, size=(5, 3)), columns=list("abc"))
df = spark.createDataFrame(data)

df.show()

+---+---+---+
|  a|  b|  c|
+---+---+---+
|  1|  1|  4|
|  1|  2|  1|
|  3|  3|  2|
|  3|  2|  2|
|  2|  0|  2|
+---+---+---+

# create example query string
query_string="'a as aa','b as bb','c as cc'"

# split and pass
column_expr = query_string.replace("'", "").split(",")

df.selectExpr(column_expr).show()

+---+---+---+
| aa| bb| cc|
+---+---+---+
|  1|  1|  4|
|  1|  2|  1|
|  3|  3|  2|
|  3|  2|  2|
|  2|  0|  2|
+---+---+---+

answered Oct 18, 2018 at 13:47

pansen

6,7034 gold badges21 silver badges33 bronze badges

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

1 Comment

Ali Yesilli Over a year ago

great solution!

Collectives™ on Stack Overflow

Dynamically passing Query String for selecting columns in PySpark Dataframe method selectExpr()

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related