如何解决Quick BI图表加载数据失败,后台报错INTERNAL:ru.yandex.clickhouse.except.ClickHouseException:ClickHouse exception, Missing columns:'toDate(create

问题描述

饼图加载数据报错，看后台错误报错如下：

INTERNAL: ru.yandex.clickhouse.except.ClickHouseException:ClickHouse exception, code: 47, host: *.*.*.*, port: 8123; 
Code: 47, e.displayText() = DB::Exception: Missing columns:'toDate(create_at)' while processing query: 
'SELECT
  openid,
  is_teacher,
  is_parent,
  is_student,
  service_wx,
  toDate(create_at),
  toDate(update_at),
  toDateOrNull(blvt),
  classid,
  tech_class_number,
  child_class_number,
  sub_info,
  publish_count,
  feedback_count,
  channel
FROM
  user_tags
WHERE
  formatDateTime(`toDate(create_at)`, '%Y%m%d') = '20211215''
required columns: ' openid ' ' service_wx ' ' create_at ' ' is_teacher ' ' child_class_number ' ' is_parent ' ' tech_class_number ' ' sub_info ' ' is_student ' ' feedback_count ' ' publish_count ' ' classid ' ' update_at ' ' channel ' ' toDate(create_at) ' ' blvt ', source columns: ' benchmark_level ' ' phone ' ' model ' ' system ' ' class_trust_valids ' ' class_create_at ' ' app_channel ' ' sub_info ' ' tech_class_number ' ' class_format_name ' ' app_last_visit_at ' ' blvt ' ' country ' ' app_create_at ' ' create_at ' ' city ' ' update_at ' ' is_parent ' ' open_gid ' ' last_subject ' ' school_name ' ' brand ' ' class_parent_join_count ' ' class_publish_count ' ' class_number ' ' grade ' ' class_name ' ' openid ' ' is_teach_class ' ' app_blvt ' ' ds ' ' first_launch_scene ' ' channel ' ' is_student ' ' first_launch_path ' ' classid ' ' publish_count ' ' gender ' ' child_class_number ' ' active_time ' ' feedback_count ' ' unionid ' ' family_status ' ' wxname ' ' is_teacher ' ' province ' ' class_update_at ' ' service_wx ' (version *.*.*.* (official build))

问题原因

生成数据集的SQL语句中from子句包含toDate()函数，但toDate()函数未添加别名导致。

解决方案

生成数据集的SQL语句中from子句包含的toDate()函数后面加上 as '别名'，然后保存数据集，图表中重新选择字段问题解决。

适用于

Quick BI V4.1.3