sorry for lack of comms from my side, will try to focus today on this feature and the proposals you’ve made @Rajat Venkatesh 🙏

I'm thinking the following: • Logical table can have optional schema and table config for query rewrite purpose. If they are not provided, pick an arbitrary one from the physical table • When logical table is queried, take precedence of logical table, then fall back to any physical table • For time boundary, we need to use the min(time boundary) across all offline tables. One challenge is we need to ensure offline table has time boundary maintained when it is included in a logical table with real-time counterpart. This requires more thought

For what it’s worth, in the logical table “pre-implementation” in the PR I had raised, a Logical Table could consist of multiple Hybrid tables where the timeboundary of each logical table would be processed the same as today i.e. querying a Hybrid table directly, or a Logical Table where that Hybrid table was present, would result in querying that Hybrid the same exact way. No special handling for min of time boundaries would be needed there. I’m unsure of whether that would present issues in cases with huge numbers of tables in a Logical Table given that this approach required multiple deep copies of BrokerRequest, but it does solve the problem of time boundary handling Edit: my intent was to have a way for users to add tables how they wanted. Users would specify when setting a Logical table that they wish to include a given table as a hybrid table. Ex imagine a http POST endpoint for setting Logical table mappings with a payload like so:

POST /LogicalTable/myLogicalTable

{
    "REALTIME": ["myTable", "myOtherTable_REALTIME"],
    "OFFLINE": [],
    "HYBRID": ["myHybrid"],
    "LOGICAL": []
}

We can do some validation to verify that

myHybrid

has both a realtime and offline component etc before finalizing the creation of

myLogicalTable

I would also want to do physical table resolution (recursively) at the time of handling this kind of http request. We would need to do it for all tables each time a Logical table was altered/created, but it would ensure that we don’t need to do that resolution at query time

is there a use case for multiple layers for logical tables ? I know from an engg. perspective it is possible but it also increases the scope for confusion and testing

I’m not sure it’s absolute necessary. I don’t know of a concrete use case. I mostly just want to stay as flexible as possible, but I think recursive logical tables could be pushed off to a later iteration. I would want to avoid a design that wasn’t reasonably easy to extend in order to support nested logical tables though

@Jackie @Evan Galpin

This is not a good constraint because it greatly limits the use cases

From a comment in the doc, what are use cases for physical tables to be part of multiple logical tables ?

I have rewritten the doc to compare and contrast logical vs federated tables. PTAL esp. the requirements I think should be applied to logical tables. I expect both of you to not like it 🙂 Ref: https://docs.google.com/document/d/1A2sM8xyz5CIKdbHMeGZX26P1YsONwFEuk2_Zfpm8arE/edit?tab=t.0#heading=h.32xl3safuxpx

My overall feeling on the “federated table Vs logical table” is that I would prefer to choose the approach which is more technically flexible (IMO that seems to be Logical tables) and introduce intentional constraints for the sake of user experience if deemed necessary (i.e. if things are too confusing for users) as opposed to a more strict approach that would leave a longer path to additional use cases. I agree that Logical tables is similar in many ways to a View, but I believe we would have the ability to implement flexible views (w/ UNION) within the SSE via Logical tables, which would be a major win IMO

@Jackie n00b q. what is the fn of time boundary ? Is it • to eliminate duplicate rows b/w realtime & offline tables (due to segment move from realtime to offline) ? • to eliminate segments from predicate pushdown (though it doesnt seem like a reasonable no. of segments will be removed) I read: https://docs.pinot.apache.org/basics/concepts/components/table/time-boundary https://dev.startree.ai/docs/pinot/recipes/time-boundary-hybrid-table IIUC,Ingestion frequency is the time period of moving segments from realtime to offline. I think the gap in my understanding is that a segment move should remove the segment from realtime table, but it isnt ?

Another q - Why do you want functions to accept

List<ServerRequest> offlineTableRequests, List<ServerRequest> realtimeTableRequests

instead of

List<ServerRequest> tableRequests

? Why is it more correct to keep the offline & table requests separate in classes like SingleConnectionBrokerRequestHandler ? I tried this approach in https://github.com/apache/pinot/compare/master...vrajat:rv-routing-hack-2

To eliminate duplicate because users also manage offline and realtime table themselves without using realtime to offline minion task. Also even with the task, segment move won’t remove the segment from realtime table. There is no way to atomically move a segment from realtime table to offline table

Hmm, it should be just one offline side request and one realtime side request, not 2 lists

admittedly, I’ve lost track of where things are at. Are we continuing at the phase of trying decide which approach (federated Vs logical) is preferred? Are both valuable in tandem?

@Evan Galpin sorry for the silence. PTAL https://docs.google.com/document/d/1iS0wtG_V2-W9sQsKkziMjTljT5qKAH_hhsksXr1Tvys/edit?usp=sharing which encapsulates the discussions going on. tl;dr the plan is to implement logical tables but with more restrictions.

One valid concern you raised is on transitioning an existing table to a logical table. I'll also add a runbook that simulates a migration. (Note that this is future looking). That will give us more signals on the practicality of implementation decisions.

I have a sometimes working version here: https://github.com/apache/pinot/compare/master...vrajat:logical-poc

❯ curl <http://localhost:9000/logicalTables/nation_l>                                                                                     
{
  "tableName" : "nation_l",
  "brokerTenant" : "DEFAULT_TENANT",
  "physicalTableNames" : [ "nation1", "nation2" ]
}%                                                                                                                                                                                                                                                                                                                 
❯ curl --request POST <http://localhost:8000/query/sql> --data '{"sql":"SELECT COUNT(*) FROM nation1"}' | jq '.resultTable, .tablesQueried'
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1238  100  1200  100    38   119k   3883 --:--:-- --:--:-- --:--:--  134k
{
  "dataSchema": {
    "columnNames": [
      "count(*)"
    ],
    "columnDataTypes": [
      "LONG"
    ]
  },
  "rows": [
    [
      100
    ]
  ]
}
[
  "nation1"
]
❯ curl --request POST <http://localhost:8000/query/sql> --data '{"sql":"SELECT COUNT(*) FROM nation2"}' | jq '.resultTable, .tablesQueried'
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1239  100  1201  100    38   160k   5194 --:--:-- --:--:-- --:--:--  172k
{
  "dataSchema": {
    "columnNames": [
      "count(*)"
    ],
    "columnDataTypes": [
      "LONG"
    ]
  },
  "rows": [
    [
      100
    ]
  ]
}
[
  "nation2"
]
❯ curl --request POST <http://localhost:8000/query/sql> --data '{"sql":"SELECT COUNT(*) FROM nation_l"}' | jq '.resultTable, .tablesQueried'
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  1241  100  1202  100    39   189k   6289 --:--:-- --:--:-- --:--:--  201k
{
  "dataSchema": {
    "columnNames": [
      "count(*)"
    ],
    "columnDataTypes": [
      "LONG"
    ]
  },
  "rows": [
    [
      200
    ]
  ]
}
[
  "nation_l"
]

@Jackie want to discuss how

ServerQueryExecutorV1Impl

should pick up

TableDataManagers

if a list consists of segments from multiple tables. Right now it uses the table in the query request. With logical tables, the query request will contain the logical table name. Changing the data structure will mean the protocol will change. Previously I had opened server channels per table. So I could send different query requests for each physical table. Ref: https://github.com/apache/pinot/blob/bc831733c5b7be20325e51bec52a2520fef2f2c6/pino[…]apache/pinot/core/query/executor/ServerQueryExecutorV1Impl.java

Not that I’ve had time to work on it recently, but I’m somewhat disconnected from whether or not https://docs.google.com/document/d/1WX5-3XJ4Nrsod0_rO_JEtvgN9XVKDi_pUySistXAOkY/edit?tab=t.0 / https://github.com/apache/pinot/pull/13742 should receive continued effort, or if they’ve been superseded by https://github.com/apache/pinot/pull/15297 ?

I've been successful implementing logical tables using a different approach. The complete implementation (POC) is in https://github.com/apache/pinot/pull/15151 The 3 main ideas are: • Use a

TableRouteProvider

interface to collect and calculate table routes. • Use a

TableRouteInfo

to store the route AND generate server requests. • Send a map of (Table, SegmentList) to the servers rather than just a list of segments. That change is in

requests.thrift

. Rest of the changes support these main ideas. We've started to break down that large POC PR and merging chunks to master. The current ongoing ones are: • https://github.com/apache/pinot/pull/15388 • https://github.com/apache/pinot/pull/15523 • https://github.com/apache/pinot/pull/15515

+ @Abhishek Bafna

@Jackie @Abhishek Bafna I checked how schema & table config - specifically: • query options • field config list Query planning

BaseSingleStageBrokerRequestHandler

requires many config parameters that are not & should not be part of logical table config. At the same time, it is too error prone to choose pseudo-random schema, realtime & offline table config from the list like the first listed one. I suggest to explicitly specify the schema, offline table & realtime tables to pick up the config from. for e.g.

{
  "schema": "<schemaName>",
  "offlineTableConfig": "<offlineTableName_OFFLINE>",
  "realtimeTableConfig": "<realtimeTableName_REALTIME>"
}

The table name should be part of the list and schema should either match the name of the logical table OR the name of the one of the physical tables.

@Rajat Venkatesh Can you list down all the query related configs? We probably need

QueryConfig

and

QuotaConfig

, anything else? I think we can support adding a reference table to use its schema and table config, but we also need to support having its own config for logical table. This allows us to have different quota, query rewrite and columns (useful for column access control) from physical table.

QuotaConfig has storage and max qps config. The storage config would not work OOTB and does it need to be implemented for the logical tables? The logical tables does not have its own data but works on the existing physical tables. So they are not in the ingestion path.

@Jackie the below table lists all the configs used in planning. The table does not list

RoutingConfig

used in

BrokerRoutingManager

used to build the routing table as that is not part of query planning. Note that all of these some of these are used from both offline and realtime tables. Ref: https://docs.google.com/document/d/1iS0wtG_V2-W9sQsKkziMjTljT5qKAH_hhsksXr1Tvys/edit?tab=t.0#heading=h.bi1j2rhyt991

As discussed offline, we can create a dummy physical table config and point to that if we want to override the behavior. So a reference to physical table config should be fine

@Jackie pasting my notes from our meeting on Friday: • Discuss the current interface of TableRouteProvider with Jackie. We decided that the current two method approach is fine. • Discuss logical table config. We agreed on an approach to specify which physical table configs to use. Override configs that make sense such as query config & qps. • Enforce Schema for Logical Tables. Specifing schema will leave dangling pointers and will require checks.

cc @Abhishek Bafna ☝️

Hi, Given, schema name for the logical tables would be same as the logical table name. The logical table names are unique across logical and physical tables. So users would need to create (or clone) the schema for the existing tables. This process can be simplified by providing a schema clone API. POST /schemas/{existingSchemaName}/clone newSchemaName: This can be a query or json body parameter. A similar API can be designed for table config too. For table config, it can accept additional

modifications

map which could be use to replace configs within the table config. e.g.

POST /tables/my_table/clone
Content-Type: application/json

{
  "newTableName": "my_new_table",
  "modifications": {
    "segmentsConfig": {
      "retentionTimeUnit": "DAYS",
      "retentionTimeValue": "10"
    },
    "tableIndexConfig": {
      "loadMode": "MMAP"
    }
  }
}

What you folks think about it? PS: This is not a P0/P1 at the moment, but something to keep in mind and develop to ease the onboarding/migration. These APIs will find other use cases too over the time. cc: @Rajat Venkatesh @Jackie

🧵 Hi @Rajat Venkatesh, we are evaluating the use of logical tables for an internal use case and had a few requirements and questions: • Our logical tables will span multiple Pinot clusters, potentially across different regions. • We need to support both SSE and MSE queries on these logical tables. The current documentation mentions that logical tables can span across tenants. How complex would it be to extend this capability to support cross-region logical tables? Any technical context, design considerations, or limitations you could share would be extremely helpful. Thanks! cc: @Ankit Sultana