In Redwood, we should use the latest version of Clickhouse

[waseem-blended]

Hi,

I'm trying to add a new connector to a third party provider with cairn clickhouse. However, to make this work we need to upgrade the clickhouse server version used.

FROM docker.io/yandex/clickhouse-server:22.1.3.7

However, I checked this repo in docker hub and it seems like there are no latest image of clickhouse released. Please upgrade the version to latest.

[regisb]

It appears that the clickhouse Docker image has moved from https://hub.docker.com/r/yandex/clickhouse-server to https://hub.docker.com/r/clickhouse/clickhouse-server/

Yet, we cannot bump the clickhouse version in the current stable release. Otherwise we might break people's databases. This will only happen in the next release, Redwood, scheduled for June. We can make this change now in the nightly branch but will not be able to backport it to the master branch just yet.

[FahadKhalid210]

Issues with Latest Version (24.2.1.2248)

Deprecation of Live View

We've encountered issues with the latest version of ClickHouse server (24.2.1.2248) due to the deprecation of Live View feature (link). This has caused errors when attempting to access the course_enrollments table.

Alternative: Refreshable Materialized View
While Refreshable Materialized View is indeed an alternative, it's worth noting that this feature is currently in-progress.

Workarounds

• Create Normal View for course_enrollments:
  Creating a normal view for course_enrollments is a workaround, but it utilizes private tables. Since users don't have access to private tables, they will encounter errors when attempting to access course_enrollments. Using the openedx DB will resolve this issue.

• Divide the Live Query into Two Views:
  An alternative workaround(hack) is to divide the live query into two live views, which seems to resolve the issue.

[FahadKhalid210]

@regisb As mentioned earlier, Live views are deprecated. Are we good to proceed with implementing a normal view for course_enrollments instead?

[regisb]

I don't know. What do you suggest? Why?

[FahadKhalid210]

@regisb Given the deprecation of Live Views and the limitations of materialized views in updating existing data, implementing a Normal View seems like a more feasible approach. With a Normal View, we can efficiently track changes without encountering the limitations associated with Live Views or materialized views.

[regisb]

That sounds great. I suggest you create a proof of concept to migrate a single live view to a normal view. This will help you decide if we should migrate all live views.

[FahadKhalid210]

@DawoudSheraz
We currently have 2 live views course_enrollments & course_block_completion. After upgrading clickhouse version, we encountered errors due to deprecation of live views.

Error we got:

After transitioning from live views to normal views to resolve the issue, we encountered a new challenge: course_enrollment table is only accessible in main db(openedx) and we get error on staff specified db’s. It's because staff user's don't have access to the tables that view statement is utilizing(_openedx_course_enrollments, _openedx_user_profiles & _openedx_users). We need to give access to staff user's for those tables as well and also need to change the row policy. Granting access appears justified, as the live view already show data from these tables through join operations.

Please share your thoughts or any additional considerations you believe are important to address moving forward.

Thanks

[DawoudSheraz]

@FahadKhalid210 Alright, if we are already using Live view by providing staff access on certain tables, we can use that here. Though it would be good if we can document under what conditions do the staff users require access.

[FahadKhalid210]

PR #38

[DawoudSheraz]

@FahadKhalid210 Does this require any further actions? Thanks

[FahadKhalid210]

@DawoudSheraz, @waseem-blended PR is merged. Closing this issue