Home | About | Sematext search-lucene.com search-hadoop.com
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB
 Search Hadoop and all its subprojects:

Switch to Threaded 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
NEW: Monitor These Apps!
elasticsearch, apache solr, apache hbase, hadoop, redis, casssandra, amazon cloudwatch, mysql, memcached, apache kafka, apache zookeeper, apache storm, ubuntu, centOS, red hat, debian, puppet labs, java, senseiDB