Run SQL query on dataframe via Pyspark

Question

I would like to run sql query on dataframe but do I have to create a view on this dataframe? Is there any easier way?

df = spark.createDataFrame([
('a', 1, 1), ('a',1, None), ('b', 1, 1),
('c',1, None), ('d', None, 1),('e', 1, 1)
]).toDF('id', 'foo', 'bar')

and the query I want to run some complex queries against this dataframe. For example I can do

df.createOrReplaceTempView("temp_view")
df_new = pyspark.sql("select id,max(foo) from temp_view group by id")

but do I have to convert it to view first before querying it? I know there is a dataframe equivalent operation. The above query is only an example.

fuzzy-memory · Accepted Answer · 2022-05-15 14:59:42Z

2

You can just do

df.select('id', 'foo')

This will return a new Spark DataFrame with columns id and foo.

answered May 15, 2022 at 14:59

fuzzy-memory

3051 silver badge11 bronze badges

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

8 Comments

Thank you. I would like to run a more complex query against this table. Above is an example.

Could you elaborate on the exact operation (s) you're looking to perform?

Besides converting the dataframe to a view then querying it, do we have different way to run SQL query aginst this dataframe?

You can explore using expr from pyspark.sql.functions. More info can be found in the docs: spark.apache.org/docs/latest/api/python/reference/api/…

Thanks. I have also seen under spark document, the spark.sql has been used interchangeably with pyspark.sql, are they the same? spark.apache.org/docs/latest/api/python/reference/api/…

|