CODEWITHSUNDEEP
apache-sparkpysparkapache-spark-sql
Андрей Смирнов
Андрей Смирнов

Reputation: 9

How to get variable by select in spark

I want get variable with sql query:

Dt = spark.sql("""select max(dt) from table""")

Script = """select * from table where dt > """ + dt

Spark.sql(script)

but when I try to substitute a variable in the request I get error: "Can only concatenate str (not dataframe) to str"

How do I get the variable as a string and not a dataframe?

Upvotes: 0

Views: 2520

Answers (1)

samkart
samkart

Reputation: 6644

To get the result in a variable, you can use collect() and extract the value. Here's an example that pulls the max date-month (YYYYMM) from a table and stores it in a variable.

max_mth = spark.sql('select max(mth) from table').collect()[0][0]

print(max_mth)
# 202202

You can either cast the value to string in the sql statement, or use str() on the variable while using to convert the integer value to string.

P.S. - the [0][0] is to select the first row-column

Upvotes: 2

Related Questions