zzzzming95 commented on pull request #2947:
URL: https://github.com/apache/hive/pull/2947#issuecomment-1074123353
> 600k partitions are always a pain. We are working on integrating Iceberg
that would help there. I would guess that the solution is this case should be a
configuration to turn off DB level filtering on query level. Maybe we already
have a config for that.
@pvary
I don't quite understand "turn off DB level filtering"
Let me give an example to detail my issue, the hql is :
```
select * from src_play_day WHERE dt=20211125 AND u_gtype='activity_workshop'
limit 10;
```
the `dt` and `u_gtype` are both partition field , **and column type are both
string.**
Before this change , metastore **not push down partition** to HMS DB, it
will get all partitions and prune partition by expression.**Because in
`dt=20211125`, dt on the left is of type string and 20211125 on the right is of
type Long.** Finally,metastore get the required partition information in the
HMS DB.
In MYSQL ,it will run **two sql** :
```
SELECT `A0`.`PART_NAME` AS NUCORDER0
FROM `PARTITIONS` `A0`
LEFT JOIN `TBLS` `B0` ON `A0`.`TBL_ID` = `B0`.`TBL_ID`
LEFT JOIN `DBS` `C0` ON `B0`.`DB_ID` = `C0`.`DB_ID`
WHERE `C0`.`NAME` = 'default'
AND `B0`.`TBL_NAME` = 'src_play_day'
ORDER BY NUCORDER0;
+
SELECT "PARTITIONS"."PART_ID"
FROM "PARTITIONS"
INNER JOIN "TBLS"
ON "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID"
AND "TBLS"."TBL_NAME" = 'src_play_day'
INNER JOIN "DBS"
ON "TBLS"."DB_ID" = "DBS"."DB_ID"
AND "DBS"."NAME" = 'default'
WHERE "PARTITIONS"."PART_NAME" IN ('dt=20211125/u_gtype=activity_workshop');
```
Get all partitions will easy increase the load on the HMS DB.
After this change, metastore will push down partition to HMS DB, and not get
all partitions.
In MYSQL ,it will run just one sql :
```
SELECT "PARTITIONS"."PART_ID"
FROM "PARTITIONS"
INNER JOIN "TBLS"
ON "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID"
AND "TBLS"."TBL_NAME" = 'src_play_day'
INNER JOIN "DBS"
ON "TBLS"."DB_ID" = "DBS"."DB_ID"
AND "DBS"."NAME" = 'default'
INNER JOIN "PARTITION_KEY_VALS" "FILTER0"
ON "FILTER0"."PART_ID" = "PARTITIONS"."PART_ID"
AND "FILTER0"."INTEGER_IDX" = 0
INNER JOIN "PARTITION_KEY_VALS" "FILTER1"
ON "FILTER1"."PART_ID" = "PARTITIONS"."PART_ID"
AND "FILTER1"."INTEGER_IDX" = 1
WHERE "FILTER0"."PART_KEY_VAL" = '20211125'
AND "FILTER1"."PART_KEY_VAL" = 'activity_workshop';
```
So this issue is to allow the partition to be pushed down to the HMS DB in
some cases, so as to avoid getting all the partition of the table.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
