[Subtask] save the SQL session metadata into MetadataManager

[lightning-L]

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

Search before asking

• [X] I have searched in the issues and found no similar issues.

Describe the subtask

save the SQL session metadata into MetadataManager

Parent issue

https://github.com/apache/incubator-kyuubi/issues/3554

Are you willing to submit PR?

• [ ] Yes. I can submit a PR independently to improve.
• [X] Yes. I would be willing to submit a PR with guidance from the Kyuubi community to improve.
• [ ] No. I cannot submit a PR at this time.

[lightning-L]

1. save the SQL session metadata into MetadataManager
2. list the live sessions from the MetadataManager
3. support to list sessions with filter condition, such as user

[EricGao888]

@lightning-L Hi, this feature seems significant when using kyuubi restful sessions in HA deployment. May I ask why it is not merged into main branch? Thanks : )

[z1131392774]

Hi all, cc @lightning-L,

I notice this feature is quite significant, as mentioned by EricGao888, especially for HA deployments. It seems this issue has been inactive for a while. Thanks to @lightning-L for the initial work and commits.

Given that the main branch has evolved significantly, the original commits (725e20c, 8521d52) can no longer be merged directly.

I would be happy to take over this task. I plan to re-implement the feature on top of the current main branch, using the previous work as a reference, and then submit a new Pull Request.

Is this okay with the maintainers? Are there any new suggestions or concerns about the implementation? If there are no objections, I will start working on it. Thanks!

[z1131392774]

Hi @pan3793 @yaooqinn ,sorry to bother you.

Following up on my previous comment, I'd like to take over this inactive issue. I have some initial thoughts on the implementation and would appreciate your feedback.

Here are my preliminary ideas:

1. SessionState Definition

I propose creating a new SessionState enum, similar to OperationState. My initial thought is to include the following states:

• INITIALIZED: Session is initialized.
• RUNNING: Session is active and running operations.
• PENDING: Session is pending, waiting for the engine to start.
• CLOSED: Session is closed by a user request.
• TIMEOUT: Session is closed due to inactivity timeout.
• ERROR: Session terminated with an error.

Are these states sufficient, or do you have other suggestions?

2. Fields to Persist for Session Metadata

To store session metadata in the MetadataStore, I think we should persist the following core fields, which are essential for management and troubleshooting:

• Session Identity: identifier, sessionType
• User & Connection: realUser, username, ipAddress
• State & Timestamps: state, createTime, endTime
• Kyuubi Instance: kyuubiInstance
• Configuration: requestConf
• Engine Details: engineType, engineId, engineName, engineUrl, engineError
• Session/Job Name: requestName

Does this list seem complete? Is there anything to add or remove?

I will wait for your feedback before starting the work.

By the way, should I open a new issue for this implementation, or is it fine to continue the discussion here? Thanks!

[z1131392774]

Hi @turboFei, just wanted to follow up on this. I posted some initial implementation ideas a few days ago (in the comment above) to help move your parent issue #3554 forward.

Any thoughts on the proposed direction? I'm ready to start working once we have alignment. Thanks!

[yaooqinn]

cc @turboFei

[turboFei]

Hi @z1131392774 thanks for the proposal, it sounds good.

You can persist the interactive session info into metadata table, see https://github.com/apache/kyuubi/blob/master/kyuubi-server/src/main/resources/sql/mysql/metadata-store-schema-1.8.0.mysql.sql

Below columns should be sufficient.

    key_id bigint PRIMARY KEY AUTO_INCREMENT COMMENT 'the auto increment key id',
    identifier varchar(36) NOT NULL COMMENT 'the identifier id, which is an UUID',
    session_type varchar(32) NOT NULL COMMENT 'the session type, SQL or BATCH',
    real_user varchar(255) NOT NULL COMMENT 'the real user',
    user_name varchar(255) NOT NULL COMMENT 'the user name, might be a proxy user',
    ip_address varchar(128) COMMENT 'the client ip address',
    kyuubi_instance varchar(1024) COMMENT 'the kyuubi instance that creates this',
    state varchar(128) NOT NULL COMMENT 'the session state',
    resource varchar(1024) COMMENT 'the main resource',
    class_name varchar(1024) COMMENT 'the main class name',
    request_name varchar(1024) COMMENT 'the request name',
    request_conf mediumtext COMMENT 'the request config map',
    request_args mediumtext COMMENT 'the request arguments',
    create_time BIGINT NOT NULL COMMENT 'the metadata create time',
    engine_type varchar(32) NOT NULL COMMENT 'the engine type',
    cluster_manager varchar(128) COMMENT 'the engine cluster manager',
    engine_open_time bigint COMMENT 'the engine open time',
    engine_id varchar(128) COMMENT 'the engine application id',
    engine_name mediumtext COMMENT 'the engine application name',
    engine_url varchar(1024) COMMENT 'the engine tracking url',
    engine_state varchar(32) COMMENT 'the engine application state',
    engine_error mediumtext COMMENT 'the engine application diagnose',
    end_time bigint COMMENT 'the metadata end time',

Thanks, have assigned this ticket to you.