Home | About | Sematext search-lucene.com search-hadoop.com
 Search Hadoop and all its subprojects:

Switch to Plain View
Hive >> mail # user >> Issue with multi insert


Copy link to this message
-
Issue with multi insert
Hi,

I am having issues to execute the following multi insert query:

FROM
  ${tmp_users_table} u
  JOIN
  ${user_evens_table} ue
  ON (
    u.id = ue.user
  )
INSERT OVERWRITE TABLE ${dau_table} PARTITION (dt='${date}')
    SELECT
      u.country,
      u.platform,
      u.gender,
      COUNT(DISTINCT(u.id))
    WHERE
      ue.dt = '${date}'
    GROUP BY
      u.country,
      u.platform,
      u.gender
INSERT OVERWRITE TABLE ${wau_table} PARTITION (dt='${date}')
    SELECT
      u.country,
      u.platform,
      u.gender,
      COUNT(DISTINCT(u.id))
    WHERE
      ue.dt BETWEEN date_sub('${date}', 7) AND '${date}'
    GROUP BY
      u.country,
      u.platform,
      u.gender
INSERT OVERWRITE TABLE ${mau_table} PARTITION (dt='${date}')
    SELECT
      u.country,
      u.platform,
      u.gender,
      COUNT(DISTINCT(u.id))
    WHERE
      ue.dt BETWEEN date_sub('${date}', 30) AND '${date}'
    GROUP BY
      u.country,
      u.platform,
      u.gender;

I got the error: FAILED: SemanticException [Error 10025]: Line 15:6
Expression not in GROUP BY key 'dt'

If I remove the second and third insert the query works.
Can anyone explain me why it's not working? Why I need to add the dt
field to GROUP BY?

Thanks & regards,
Thomas