Is your feature request related to a problem?

In MySQL one can retrieve partition information about a table, which can later be used to target specific partitions during query execution.

The following is an example of a query that can be used to retrieve partition information of a specific table.

"SELECT DISTINCT partition_name FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = <table_name> AND TABLE_SCHEMA = <table_schema> " +
            "AND partition_name IS NOT NULL"

Next is an example of a query targeting a specific partition in a table.

SELECT * FROM table PARTITION (partitionName);

When applying this to opensearch, partitions could be treated as the equivalent of shards for our use case.

What solution would you like?
It would be great to be able to treat shards in Opensearch as the equivalent to MySQL Partitions and be able to query individual shards.

What alternatives have you considered?
We've considered generating splits based on hashing a key or tuple of keys and then modulo that against some fixed number of splits that we want to generate.

Example:
For example for 3 splits:

split 1:
SELECT *
FROM some_unpartitioned_table
WHERE hash(col1, col2, col3) % 10 == 0

split 2:
SELECT *
FROM some_unpartitioned_table
WHERE hash(col1, col2, col3) % 10 == 1

split 3:
SELECT *
FROM some_unpartitioned_table
WHERE hash(col1, col2, col3) % 10 == 2

However, it doesn't seem like there is a hashing function like that available.

Therefore, the above mentioned solution would be a great behavior to add for all sql users, to more closely mimic the behavior and syntax of MySQL.