Unless overridden, Hive will prevent users from running queries on partitioned data without any partition predicates in their WHERE clauses.
https://wikitech.wikimedia.org/wiki/Analytics/Systems/Cluster/Hive/Queries#Use_partitions
I can run queries in Presto without partition predicates, and it will attempt to read all data for the table.
If possible, we should make it so Presto users can't run (or are at least warned about) queries without partition predicates.
| Status | Subtype | Assigned | Task | ||
|---|---|---|---|---|---|
| Duplicate | None | T266639 Analytics Presto improvements | |||
| Resolved | • odimitrijevic | T294259 Presto/Superset User Experience Improvement | |||
| Declined | • razzi | T273004 Presto should warn or prevent users from querying without Hive partition predicates |
One way to go about this may be to use hive.max-partitions-per-scan. From the docs:
| hive.max-partitions-per-scan | Maximum number of partitions for a single table scan. | 100,000 |
https://prestodb.io/docs/current/connector/hive.html#configuration
We could set this to something like the number of hours in a month, so that users will have to specify a month when working with hourly data, but I'm not sure that this setting will work for all use cases.
Setting the parameter above to number of hours in a month works fro hourly data, not for snapshot data.
I don't think we'll have a better solution though.
(answering since Joal is out for the eve)
Joal is referring to Hive tables with 'snapshot' partitions, like the Sqooped mediawiki tables, and mediawiki_history. These are 'snapshots' of the MediaWiki data taken once a month, so there is no time based 'hourly' partitioning. Instead, each snapshot partition has all of the data, and we keep several snapshots around at once.
https://wikitech.wikimedia.org/wiki/Analytics/Data_Lake/Edits/MediaWiki_history#Schema
Thanks @Ottomata for the fast answer :)
Something to note: we currently don't have homogeneous partitioning strategies in term of datasize. What this means is that we partition data for functional needs, not data size. This is sub-optimal, and prevents for instance to provide a setting for this presto config that would be globally correct. Using Apache Iceberg and a data-governance tool should help here (Iceberg will provide incremental aspect of data and will hide partitioning, and data governance will let systems know about partition schemes).
One way to go about this may be to use hive.max-partitions-per-scan
In lieu of better option, this seems to be better than nothing. How about setting this to the number of hours in a 5 weeks, to allow for some monthly querying with some overlap. At least this will restrict people from just querying everything (for hourly data).
How about setting this to the number of hours in a 5 weeks
Let's do it. While the span might be too small for cases where data is small and therefore could be no more than a few Gb, this will at least set some limit. We'll readjust if we get negative feedback :)
Change 661209 had a related patch set uploaded (by Razzi; owner: Razzi):
[operations/puppet@production] presto: require partitions predicate
Change 661990 had a related patch set uploaded (by Razzi; owner: Razzi):
[operations/puppet@production] presto: set hive.max-pertitions-per-scan for test cluster
Change 661990 merged by Razzi:
[operations/puppet@production] presto: set hive.max-pertitions-per-scan for test cluster
Change 662011 had a related patch set uploaded (by Razzi; owner: Razzi):
[operations/puppet@production] Fix typo in hive.max-partitions-per-scan
Change 662011 merged by Razzi:
[operations/puppet@production] Fix typo in hive.max-partitions-per-scan
@razzi is a roll restart of the Presto coordinator/workers requested to get the new setting applied?
@elukey we tried to applied this to the test cluster and did some restarts there but had some trouble getting presto queries to run, also it seemed puppet was disabled on one of the nodes? razzi can fill you in.
I am testing 0.246 in there, sadly it is broken see https://phabricator.wikimedia.org/T266640
We have decided not to pursue this road: putting a limit to the number of partitions that presto can quesry at once will break superset-presto usage, as dashboards don't specify partitioning but use a single timestamp field for filtering (therefore scanning all partitions at every query!). We need to wait wait for Iceberg before being able to apply this type of limitations. closing for now.
FYI! Just noticed that we closed this task without really undoing. This setting was applied in the test cluster, and I was about to apply it in prod!