Repartition in Spark - SQL API

Question

We use the SQL API of Spark to execute queries on Hive tables on the cluster. How can I perform a REPARTITION on a column in my query in SQL-API ?. Please note that we do not use the Dataframe API but instead we use the SQL API (for e.g SELECT * from table WHERE col = 1).

I understand that PySpark-SQL offers a function for the same in the Dataframe API.

However, I want to know the syntax to specify a REPARTITION (on a specific column) in a SQL query via the SQL-API (thru a SELECT statement).

Consider the following query :

select a.x, b.y
from a
JOIN b
on a.id = b.id

Any help is appreciated.

We use Spark 2.4

Thanks

Answer 1

You can provide hints to enable repartition in spark sql

spark.sql('''SELECT /*+ REPARTITION(colname) */ col1,col2 from table''')

Answer 2

You can use both, but using %sql, use from the manuals:

DISTRIBUTE BY

Repartition rows in the relation based on a set of expressions. Rows with the same expression values will be hashed to the same worker. You cannot use this with ORDER BY or CLUSTER BY.

It all amounts to the same thing. I.e. shuffle occurs, that is to say you cannot eliminate it, just alternative interfaces. Of course, only possible due to 'lazy' evaluation employed.

%sql
SELECT * FROM boxes DISTRIBUTE BY width  
SELECT * FROM boxes DISTRIBUTE BY width SORT BY width

This is the alternative in %sql approach for hint as per other answer.