@Erik Louie​ :

To efficiently perform RANGE BETWEEN queries on your joined data frame, you can try bucketing the data based on the timestamp column. Bucketing ensures that data with similar timestamps are stored together in the same file, making range queries faster as Spark can skip over files that are outside the range being queried.

Here's an example of how to bucket your data:

from pyspark.sql.functions import bucket
 
# Bucket the data on the timestamp column into 100 buckets
num_buckets = 100
bucket_col = "timestamp_bucket"
df_bucketed = df_all.withColumn(bucket_col, bucket("timestamp", num_buckets))
 
# Write the bucketed data to a table
df_bucketed.write.mode("overwrite").bucketBy(num_buckets, bucket_col).sortBy("timestamp").saveAsTable("bucketed_table")
 
# Query the bucketed data using a range query
from pyspark.sql.window import Window
from pyspark.sql.functions import row_number
 
w = Window.orderBy("timestamp")
df_query = (
    spark.table("bucketed_table")
    .filter("timestamp >= {start_time} AND timestamp <= {end_time}")
    .withColumn("row_num", row_number().over(w))
    .filter("row_num BETWEEN {start_row} AND {end_row}")
)

In this example, we first bucket the data on the timestamp column into 100 buckets using the bucket function. We then write the bucketed data to a table using bucketBy and sortBy. Finally, we perform a range query on the bucketed data using filter and row_number.

Note that you will need to adjust the number of buckets to fit the size of your data and the range of timestamps you are querying. You may also need to experiment with different bucketing strategies to find the best performance for your use case.