Error when attempting to run Alembic migrations (tutor-contrib-aspects)

Working Groups Data
,
1

Hello,

I hope you’re all doing well!

I’ve been trying to run the Alembic migrations from the following script: (tutor-contrib-aspects/tutoraspects/templates/aspects/jobs/init/aspects/init-aspects.sh at v2.3.0 · openedx/tutor-contrib-aspects · GitHub). However, when executing these commands, I’m getting the following error:

14:29:56  Found 42 models, 21 data tests, 8 sources, 621 macros, 29 unit tests
14:29:56
14:29:57  Concurrency: 2 threads (target='prod')
14:29:57
14:29:57  1 of 32 START sql materialized_view model `xapi`.`xapi_events_all_parsed` ...... [RUN]
14:29:57  1 of 32 ERROR creating sql materialized_view model `xapi`.`xapi_events_all_parsed`  [ERROR in 0.13s]
14:29:57  2 of 32 SKIP relation xapi.enrollment_events ................................... [SKIP]
14:29:57  3 of 32 SKIP relation xapi.fact_instance_actors ................................ [SKIP]
14:29:57  4 of 32 SKIP relation xapi.fact_instance_courses ............................... [SKIP]
14:29:57  5 of 32 SKIP relation xapi.fact_instance_events ................................ [SKIP]
14:29:57  6 of 32 SKIP relation xapi.grading_events ...................................... [SKIP]
14:29:57  7 of 32 SKIP relation xapi.navigation_events ................................... [SKIP]
14:29:57  8 of 32 SKIP relation xapi.problem_events ...................................... [SKIP]
14:29:57  9 of 32 SKIP relation xapi.video_playback_events ............................... [SKIP]
14:29:57  10 of 32 SKIP relation reporting.dim_most_recent_enrollment .................... [SKIP]
14:29:57  11 of 32 SKIP relation reporting.fact_enrollments .............................. [SKIP]
14:29:57  12 of 32 SKIP relation reporting.fact_instance_enrollments ..................... [SKIP]
14:29:57  13 of 32 SKIP relation reporting.dim_learner_most_recent_course_grade .......... [SKIP]
14:29:57  14 of 32 SKIP relation reporting.dim_learner_most_recent_course_state .......... [SKIP]
14:29:57  15 of 32 SKIP relation reporting.dim_learner_last_course_visit ................. [SKIP]
14:29:57  16 of 32 SKIP relation reporting.fact_navigation_completion .................... [SKIP]
14:29:57  17 of 32 SKIP relation reporting.fact_section_page_engagement .................. [SKIP]
14:29:57  18 of 32 SKIP relation reporting.fact_subsection_page_engagement ............... [SKIP]
14:29:57  19 of 32 SKIP relation reporting.dim_learner_first_response .................... [SKIP]
14:29:57  20 of 32 SKIP relation reporting.dim_learner_first_success_response ............ [SKIP]
14:29:57  21 of 32 SKIP relation reporting.dim_learner_last_response ..................... [SKIP]
14:29:57  22 of 32 SKIP relation reporting.dim_learner_response_attempt .................. [SKIP]
14:29:57  23 of 32 SKIP relation reporting.fact_section_problem_engagement ............... [SKIP]
14:29:57  24 of 32 SKIP relation reporting.fact_subsection_problem_engagement ............ [SKIP]
14:29:57  25 of 32 SKIP relation reporting.fact_section_video_engagement ................. [SKIP]
14:29:57  26 of 32 SKIP relation reporting.fact_subsection_video_engagement .............. [SKIP]
14:29:57  27 of 32 SKIP relation reporting.fact_video_plays .............................. [SKIP]
14:29:57  28 of 32 SKIP relation reporting.fact_watched_video_duration ................... [SKIP]
14:29:57  29 of 32 SKIP relation reporting.dim_student_status ............................ [SKIP]
14:29:57  30 of 32 SKIP relation reporting.fact_pageview_engagement ...................... [SKIP]
14:29:57  31 of 32 SKIP relation reporting.fact_learner_response_attempts ................ [SKIP]
14:29:57  32 of 32 SKIP relation reporting.fact_video_engagement ......................... [SKIP]
14:29:57
14:29:57  Finished running 24 materialized view models, 8 view models in 0 hours 0 minutes and 0.77 seconds (0.77s).
14:29:57
14:29:57  Completed with 1 error and 0 warnings:
14:29:57
14:29:57    Database Error in model xapi_events_all_parsed (models/base/xapi_events_all_parsed.sql)
  Code: 53.
  DB::Exception: Cannot convert string VIEW to type UInt8: while executing 'FUNCTION equals(multiIf(is_temporary, 4, like(engine, '%View'), 2, like(engine, 'System%'), 5, equals(has_own_data, 0), 3, 1) : 21, 'VIEW' : 13) -> equals(multiIf(is_temporary, 4, like(engine, '%View'), 2, like(engine, 'System%'), 5, equals(has_own_data, 0), 3, 1), 'VIEW') UInt8 : 22'. Stack trace:
  0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c604bf7 in /usr/bin/clickhouse
  1. DB::Exception::Exception<String const&, String>(int, FormatStringHelperImpl<std::type_identity<String const&>::type, std::type_identity<String>::type>, String const&, String&&) @ 0x0000000007b6b247 in /usr/bin/clickhouse
  2. DB::(anonymous namespace)::convertFieldToTypeImpl(DB::Field const&, DB::IDataType const&, DB::IDataType const*) @ 0x0000000012274fcc in /usr/bin/clickhouse
  3. DB::convertFieldToType(DB::Field const&, DB::IDataType const&, DB::IDataType const*) @ 0x0000000012272062 in /usr/bin/clickhouse
  4. DB::FunctionComparison<DB::EqualsOp, DB::NameEquals>::executeImpl(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long) const @ 0x000000000889777f in /usr/bin/clickhouse
  5. DB::FunctionToExecutableFunctionAdaptor::executeImpl(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long) const @ 0x0000000007115d2e in /usr/bin/clickhouse
  6. DB::IExecutableFunction::executeWithoutLowCardinalityColumns(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x0000000010bdec89 in /usr/bin/clickhouse
  7. DB::IExecutableFunction::executeWithoutSparseColumns(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x0000000010bdf782 in /usr/bin/clickhouse
  8. DB::IExecutableFunction::execute(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x0000000010be0a79 in /usr/bin/clickhouse
  9. DB::ExpressionActions::execute(DB::Block&, unsigned long&, bool) const @ 0x000000001173a0b9 in /usr/bin/clickhouse
  10. DB::ExpressionActions::execute(DB::Block&, bool) const @ 0x000000001173b346 in /usr/bin/clickhouse
  11. DB::ExpressionAnalysisResult::ExpressionAnalysisResult(DB::SelectQueryExpressionAnalyzer&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, bool, bool, bool, std::shared_ptr<DB::FilterDAGInfo> const&, std::shared_ptr<DB::FilterDAGInfo> const&, DB::Block const&) @ 0x000000001175a1bd in /usr/bin/clickhouse
  12. DB::InterpreterSelectQuery::getSampleBlockImpl() @ 0x0000000011eca3e3 in /usr/bin/clickhouse
  13. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>)::$_0::operator()(bool) const @ 0x0000000011ebb3ba in /usr/bin/clickhouse
  14. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>) @ 0x0000000011eada97 in /usr/bin/clickhouse
  15. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x0000000011f5c4a8 in /usr/bin/clickhouse
  16. DB::StorageView::read(DB::QueryPlan&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageSnapshot> const&, DB::SelectQueryInfo&, std::shared_ptr<DB::Context const>, DB::QueryProcessingStage::Enum, unsigned long, unsigned long) @ 0x0000000012903276 in /usr/bin/clickhouse
  17. DB::InterpreterSelectQuery::executeFetchColumns(DB::QueryProcessingStage::Enum, DB::QueryPlan&) @ 0x0000000011ed560b in /usr/bin/clickhouse
  18. DB::InterpreterSelectQuery::executeImpl(DB::QueryPlan&, std::optional<DB::Pipe>) @ 0x0000000011ebce68 in /usr/bin/clickhouse
  19. DB::InterpreterSelectQuery::buildQueryPlan(DB::QueryPlan&) @ 0x0000000011ebc294 in /usr/bin/clickhouse
  20. DB::InterpreterSelectWithUnionQuery::buildQueryPlan(DB::QueryPlan&) @ 0x0000000011f604f6 in /usr/bin/clickhouse
  21. DB::InterpreterSelectWithUnionQuery::execute() @ 0x0000000011f61407 in /usr/bin/clickhouse
  22. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x00000000122a6095 in /usr/bin/clickhouse
  23. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum) @ 0x00000000122a17f5 in /usr/bin/clickhouse
  24. DB::TCPHandler::runImpl() @ 0x000000001310c5b9 in /usr/bin/clickhouse
  25. DB::TCPHandler::run() @ 0x000000001311e839 in /usr/bin/clickhouse
  26. Poco::Net::TCPServerConnection::start() @ 0x0000000015b104d4 in /usr/bin/clickhouse
  27. Poco::Net::TCPServerDispatcher::run() @ 0x0000000015b116d1 in /usr/bin/clickhouse
  28. Poco::PooledThread::run() @ 0x0000000015c47f07 in /usr/bin/clickhouse
  29. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000015c461dc in /usr/bin/clickhouse
  30. ? @ 0x00007f98e555d134 in ?
  31. ? @ 0x00007f98e55dd7dc in ?

I’m currently using version 2.3.0 of tutor-contrib-aspects and ClickHouse version 23.8.2.7. I would appreciate it if anyone who has experienced this or has an idea of what might be happening could help me understand why it’s occurring and how I can fix it.

Thanks a lot for your help!

2

Hi Deimer,

This is an error in the dbt part of the process. That version of things has been stable for quite a while. How are you running the shell script? Via a tutor do command or some other way?

You should be able to see the actual SQL being run if you’re doing it through Tutor via a command like:

tutor local do dbt -c “-d run”

Which will turn on debug logging. If you’re doing it some other way please let me know how you’re going about it so we can troubleshoot more.

1 Like
3

Hi TyHob,

Thank you very much for your response. In this case, I’m running them on a K8s cluster, and the shell script runs as part of an initialization routine. I have configured the job that executes the shell script to turn on debug logging. Below are the logs I was able to get.

23:11:34  Began running node model.aspects.xapi_events_all_parsed
23:11:34  1 of 32 START sql materialized_view model `xapi`.`xapi_events_all_parsed` ...... [RUN]
23:11:34  Re-using an available connection from the pool (formerly list__xapi, now model.aspects.xapi_events_all_parsed)
23:11:34  Began compiling node model.aspects.xapi_events_all_parsed
23:11:34  Writing injected SQL for node "model.aspects.xapi_events_all_parsed"
23:11:34  Began executing node model.aspects.xapi_events_all_parsed
23:11:34  Replacing existing materialized view xapi_events_all_parsed
23:11:34  Searching for existing materialized views with the pattern of xapi_events_all_parsed
23:11:34  Views dictionary contents: {'mv': '\n\nselect\n    event_id as event_id,\n    toLowCardinality(JSON_VALUE(event::String, \'$.verb.id\')) as verb_id,\n    COALESCE(\n        NULLIF(JSON_VALUE(event::String, \'$.actor.account.name\'), \'\'),\n        NULLIF(JSON_VALUE(event::String, \'$.actor.mbox\'), \'\'),\n        JSON_VALUE(event::String, \'$.actor.mbox_sha1sum\')\n    ) as actor_id,\n    JSON_VALUE(event::String, \'$.object.id\') as object_id,\n    -- If the contextActivities parent is a course, use that. It can be a "course"\n    -- type, or a "cmi.interaction" type for multiple question problem submissions.\n    -- Otherwise use the object id for the course id.\n    toLowCardinality(\n        multiIf(\n            -- If the contextActivities parent is a course, use that\n            JSON_VALUE(\n                event::String, \'$.context.contextActivities.parent[0].definition.type\'\n            )\n            = \'http://adlnet.gov/expapi/activities/course\',\n            JSON_VALUE(event::String, \'$.context.contextActivities.parent[0].id\'),\n            -- Else if the contextActivities parent is a GroupActivity, it\'s a multi\n            -- question problem and we use the grouping id\n            JSON_VALUE(\n                event::String, \'$.context.contextActivities.parent[0].objectType\'\n            )\n            in (\'Activity\', \'GroupActivity\'),\n            JSON_VALUE(event::String, \'$.context.contextActivities.grouping[0].id\'),\n            -- Otherwise use the object id\n            JSON_VALUE(event::String, \'$.object.id\')\n        )\n    ) as course_id,\n    toLowCardinality(splitByString(\'/\', course_id)[-1]) as course_key,\n    toLowCardinality(\n        coalesce(\n            get_org_from_course_url(course_id),\n            get_org_from_ccx_course_url(course_id),\n            \'\'\n        )\n    ) as org,\n    emission_time as emission_time,\n    event::String as event\nfrom `xapi`.`xapi_events_all`'}
23:11:34  dbt_clickhouse adapter: On model.aspects.xapi_events_all_parsed: /* {"app": "dbt", "dbt_version": "1.8.9", "profile_name": "aspects", "target_name": "prod", "node_id": "model.aspects.xapi_events_all_parsed"} */
            select table_name
            from information_schema.tables
            where table_schema = 'xapi'
              and table_name like '%xapi_events_all_parsed%'
              and table_type = 'VIEW'
  ...
23:11:34  dbt_clickhouse adapter: Error running SQL: /* {"app": "dbt", "dbt_version": "1.8.9", "profile_name": "aspects", "target_name": "prod", "node_id": "model.aspects.xapi_events_all_parsed"} */
            select table_name
            from information_schema.tables
            where table_schema = 'xapi'
              and table_name like '%xapi_events_all_parsed%'
              and table_type = 'VIEW'
23:11:34  Database Error in model xapi_events_all_parsed (models/base/xapi_events_all_parsed.sql)
  Code: 53.
  DB::Exception: Cannot convert string VIEW to type UInt8: while executing 'FUNCTION equals(multiIf(is_temporary, 4, like(engine, '%View'), 2, like(engine, 'System%'), 5, equals(has_own_data, 0), 3, 1) : 21, 'VIEW' : 13) -> equals(multiIf(is_temporary, 4, like(engine, '%View'), 2, like(engine, 'System%'), 5, equals(has_own_data, 0), 3, 1), 'VIEW') UInt8 : 22'. Stack trace:
  0. DB::Exception::Exception(DB::Exception::MessageMasked&&, int, bool) @ 0x000000000c604bf7 in /usr/bin/clickhouse
  1. DB::Exception::Exception<String const&, String>(int, FormatStringHelperImpl<std::type_identity<String const&>::type, std::type_identity<String>::type>, String const&, String&&) @ 0x0000000007b6b247 in /usr/bin/clickhouse
  2. DB::(anonymous namespace)::convertFieldToTypeImpl(DB::Field const&, DB::IDataType const&, DB::IDataType const*) @ 0x0000000012274fcc in /usr/bin/clickhouse
  3. DB::convertFieldToType(DB::Field const&, DB::IDataType const&, DB::IDataType const*) @ 0x0000000012272062 in /usr/bin/clickhouse
  4. DB::FunctionComparison<DB::EqualsOp, DB::NameEquals>::executeImpl(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long) const @ 0x000000000889777f in /usr/bin/clickhouse
  5. DB::FunctionToExecutableFunctionAdaptor::executeImpl(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long) const @ 0x0000000007115d2e in /usr/bin/clickhouse
  6. DB::IExecutableFunction::executeWithoutLowCardinalityColumns(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x0000000010bdec89 in /usr/bin/clickhouse
  7. DB::IExecutableFunction::executeWithoutSparseColumns(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x0000000010bdf782 in /usr/bin/clickhouse
  8. DB::IExecutableFunction::execute(std::vector<DB::ColumnWithTypeAndName, std::allocator<DB::ColumnWithTypeAndName>> const&, std::shared_ptr<DB::IDataType const> const&, unsigned long, bool) const @ 0x0000000010be0a79 in /usr/bin/clickhouse
  9. DB::ExpressionActions::execute(DB::Block&, unsigned long&, bool) const @ 0x000000001173a0b9 in /usr/bin/clickhouse
  10. DB::ExpressionActions::execute(DB::Block&, bool) const @ 0x000000001173b346 in /usr/bin/clickhouse
  11. DB::ExpressionAnalysisResult::ExpressionAnalysisResult(DB::SelectQueryExpressionAnalyzer&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, bool, bool, bool, std::shared_ptr<DB::FilterDAGInfo> const&, std::shared_ptr<DB::FilterDAGInfo> const&, DB::Block const&) @ 0x000000001175a1bd in /usr/bin/clickhouse
  12. DB::InterpreterSelectQuery::getSampleBlockImpl() @ 0x0000000011eca3e3 in /usr/bin/clickhouse
  13. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>)::$_0::operator()(bool) const @ 0x0000000011ebb3ba in /usr/bin/clickhouse
  14. DB::InterpreterSelectQuery::InterpreterSelectQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context> const&, std::optional<DB::Pipe>, std::shared_ptr<DB::IStorage> const&, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageInMemoryMetadata const> const&, std::shared_ptr<DB::PreparedSets>) @ 0x0000000011eada97 in /usr/bin/clickhouse
  15. DB::InterpreterSelectWithUnionQuery::InterpreterSelectWithUnionQuery(std::shared_ptr<DB::IAST> const&, std::shared_ptr<DB::Context>, DB::SelectQueryOptions const&, std::vector<String, std::allocator<String>> const&) @ 0x0000000011f5c4a8 in /usr/bin/clickhouse
  16. DB::StorageView::read(DB::QueryPlan&, std::vector<String, std::allocator<String>> const&, std::shared_ptr<DB::StorageSnapshot> const&, DB::SelectQueryInfo&, std::shared_ptr<DB::Context const>, DB::QueryProcessingStage::Enum, unsigned long, unsigned long) @ 0x0000000012903276 in /usr/bin/clickhouse
  17. DB::InterpreterSelectQuery::executeFetchColumns(DB::QueryProcessingStage::Enum, DB::QueryPlan&) @ 0x0000000011ed560b in /usr/bin/clickhouse
  18. DB::InterpreterSelectQuery::executeImpl(DB::QueryPlan&, std::optional<DB::Pipe>) @ 0x0000000011ebce68 in /usr/bin/clickhouse
  19. DB::InterpreterSelectQuery::buildQueryPlan(DB::QueryPlan&) @ 0x0000000011ebc294 in /usr/bin/clickhouse
  20. DB::InterpreterSelectWithUnionQuery::buildQueryPlan(DB::QueryPlan&) @ 0x0000000011f604f6 in /usr/bin/clickhouse
  21. DB::InterpreterSelectWithUnionQuery::execute() @ 0x0000000011f61407 in /usr/bin/clickhouse
  22. DB::executeQueryImpl(char const*, char const*, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum, DB::ReadBuffer*) @ 0x00000000122a6095 in /usr/bin/clickhouse
  23. DB::executeQuery(String const&, std::shared_ptr<DB::Context>, bool, DB::QueryProcessingStage::Enum) @ 0x00000000122a17f5 in /usr/bin/clickhouse
  24. DB::TCPHandler::runImpl() @ 0x000000001310c5b9 in /usr/bin/clickhouse
  25. DB::TCPHandler::run() @ 0x000000001311e839 in /usr/bin/clickhouse
  26. Poco::Net::TCPServerConnection::start() @ 0x0000000015b104d4 in /usr/bin/clickhouse
  27. Poco::Net::TCPServerDispatcher::run() @ 0x0000000015b116d1 in /usr/bin/clickhouse
  28. Poco::PooledThread::run() @ 0x0000000015c47f07 in /usr/bin/clickhouse
  29. Poco::ThreadImpl::runnableEntry(void*) @ 0x0000000015c461dc in /usr/bin/clickhouse
  30. ? @ 0x00007f98e555d134 in ?
  31. ? @ 0x00007f98e55dd7dc in ?
23:11:34  1 of 32 ERROR creating sql materialized_view model `xapi`.`xapi_events_all_parsed`  [ERROR in 0.10s]
23:11:34  Finished running node model.aspects.xapi_events_all_parsed

Thank you in advance for your help, I really appreciate it

4

Ahh, I think this is because you are on a quite old version of ClickHouse. Aspects 2.3.0 expects ClickHouse 25.3.x so I suspect the information_schema.tables schema has changed in the interim. Is there a reason you’re using an older version?

1 Like
5

Hi TyHob,

Thank you so much for your help! I don’t have a reason to use an older version of ClickHouse at the moment, so I’ll go ahead and take the necessary steps to perform an update. I’ll let you know if this solves the problem as soon as I’ve made those changes.

Thanks again for your help.