Databricks windows group by using sql

Question

I'm having trouble finding how to convert a pyspark window statement into the equivalent SQL format.

Example:

  eventsDF \
    .groupBy(
      "deviceId",
      window("eventTime", "10 minutes", "5 minutes")) \
    .count()

It should be something like:

   select window, deviceId, count(deviceId)
   from events
   group by window eventTime 10 minutes 5 minutes, deviceId

I'm not sure if SQL could handle the group by in the syntax you mentioned. What's your current situation? do you get any error or the result is different or ...? — Amir Maleki
– Amir Maleki, Commented Feb 2, 2021 at 4:45

mck · Accepted Answer · 2021-02-02 08:15:33Z

1

You need to fix some syntax errors. The window should be wrapped in parentheses.

select
    window(eventTime, '10 minutes', '5 minutes'),
    deviceId,
    count(deviceId)
from events
group by
    window(eventTime, '10 minutes', '5 minutes'),
    deviceId

answered Feb 2, 2021 at 8:15

mck

42.7k13 gold badges44 silver badges62 bronze badges

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

2 Comments

Emer Over a year ago

Thanks @mck, do you know if there is any documentation about the window syntax? Looks like the official doc is incomplete: spark.apache.org/docs/latest/api/sql/#window

mck Over a year ago

@Emer see the pyspark docs: spark.apache.org/docs/3.0.2/api/python/…

Amir Maleki · Accepted Answer · 2021-02-02 05:24:56Z

0

I reckon the result of the following query is what you expected:

select deviceId, count(deviceId)
from events
group by `eventTime`, `10 minutes`,  `5 minutes`, `deviceId`

the group by will group the rows on all the windows you expect

answered Feb 2, 2021 at 5:24

Amir Maleki

4291 gold badge4 silver badges14 bronze badges

Collectives™ on Stack Overflow

Databricks windows group by using sql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related