2

Is there a way to preserve the order on a query from Athena? Assume the data in the s3 bucket or data lake are partitioned and are in parquet files. Every time I query something, the order is different each time. I am not sure how Athena works, but it makes sense to have multiple workers performing the query for performance and just combining the results together, which will make sense why the order is different each time. But is it possible to preserve the order of the results if all data is just coming from a single parquet file?

user1179317
  • 2,693
  • 3
  • 34
  • 62
  • can you try by setting parquet.column.index.access to true on serde properties as mentioned in https://docs.aws.amazon.com/athena/latest/ug/handling-schema-updates-chapter.html#parquet-read-by-name ? – Prabhakar Reddy Apr 28 '21 at 15:29
  • Why would you need to preserve the order? – Guy Apr 29 '21 at 11:45
  • Its just a requirement. Say the data are partitioned, and a single parquet file is already sorted (by time). If I want to select all data from that single parquet file, its already sorted so I dont want to SELECT all then still have to sort/order by time (which can be expensive) – user1179317 Apr 29 '21 at 14:47
  • @PrabhakarReddy from the docs, it seems like parquet.column.index.access controls how athena finds the columns in parquet files, whether by name or by index. – onlynone Feb 06 '23 at 15:56
  • @Guy Another situation where this would be helpful is for logs where multiple log entries might come in at the same millisecond. If you sort that data by time, it would be arbitrary which log line you'll get first in the output (unless the sort used is a stable sort, which I don't think athena uses). – onlynone Feb 06 '23 at 15:58
  • To be more accurate, you need to handle many use cases, such as users in different timezones and, therefore, different 5 AM, for example. Or people at different distances from the server (again in milliseconds), and the server time is inconsistent with the client time, etc. It is better to understand the business requirement and not to stick to a general technical requirement that can be impossible or too expensive to achieve. – Guy Feb 06 '23 at 21:48

1 Answers1

1

If the data in your original files is already time sorted, adding an order by time_column won't add complexity to the query. Conceptually, each worker will sort a small fraction of the data, then merge-sort results from workers. For data that's already sorted these are inexpensive operations.

Nicolas Busca
  • 1,100
  • 7
  • 14