title: Hive query GROUP BY error; Invalid table alias or column reference
body: <p>Kindest, </p>
  
  <p>I am trying to extend some working HIVE queries, but seem to fall short. Just wanting to test GROUP BY function, which is common to a number of queries that I need to complete. Here is the query that I am trying to execute: </p>
  
  <pre><code>DROP table CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary;
  
  CREATE EXTERNAL TABLE IF NOT EXISTS CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary ( messageRowID STRING, payload_sensor INT, messagetimestamp BIGINT, payload_temp FLOAT, payload_timestamp BIGINT, payload_timestampmysql STRING, payload_watt INT, payload_wattseconds INT ) 
  STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
  WITH SERDEPROPERTIES ( "cassandra.host" = "127.0.0.1",
      "cassandra.port" = "9160",
      "cassandra.ks.name" = "EVENT_KS",
      "cassandra.ks.username" = "admin",
      "cassandra.ks.password" = "admin",
      "cassandra.cf.name" = "currentcost_stream",
      "cassandra.columns.mapping" = ":key, payload_sensor, Timestamp, payload_temp, payload_timestamp, payload_timestampmysql, payload_watt, payload_wattseconds" );
  
  select messageRowID, payload_sensor, messagetimestamp, payload_temp, payload_timestamp, payload_timestampmysql, payload_watt, payload_wattseconds, hour(from_unixtime(payload_timestamp)) AS hourly
  FROM CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary 
  WHERE payload_timestamp &gt; unix_timestamp() - 3024*60*60
  GROUP BY hourly;
  </code></pre>
  
  <p>This yields the following error: </p>
  
  <blockquote>
    <p>ERROR: Error while executing Hive script.Query returned non-zero code:
    10, cause: FAILED: Error in semantic analysis: Line 1:320 Invalid
    table alias or column reference 'hourly': (possible column names are:
    messagerowid, payload_sensor, messagetimestamp, payload_temp,
    payload_timestamp, payload_timestampmysql, payload_watt,
    payload_wattseconds)</p>
  </blockquote>
  
  <p>The intention is to end up with a timebound query (say last 24 hours) broken in by SUM() on payload_wattsecond etc. To get started breaking then out the creation of the summary tables, I started building a group by query which were going to derive the hourly anchor for the select query. </p>
  
  <p>Problem though is the error above. Would greatly appreciate any pointers to what is wrong here.. can't seem to find it myself, but then again I am a newbie on HIVE. </p>
  
  <p>Thanks in advance .. </p>
  
  <p>UPDATE: Tried to update the query. Here is the query that I just tried to run: </p>
  
  <pre><code>DROP table CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary;
  
  CREATE EXTERNAL TABLE IF NOT EXISTS CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary ( messageRowID STRING, payload_sensor INT, messagetimestamp BIGINT, payload_temp FLOAT, payload_timestamp BIGINT, payload_timestampmysql STRING, payload_watt INT, payload_wattseconds INT ) 
  STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
  WITH SERDEPROPERTIES ( "cassandra.host" = "127.0.0.1",
      "cassandra.port" = "9160",
      "cassandra.ks.name" = "EVENT_KS",
      "cassandra.ks.username" = "admin",
      "cassandra.ks.password" = "admin",
      "cassandra.cf.name" = "currentcost_stream",
      "cassandra.columns.mapping" = ":key, payload_sensor, Timestamp, payload_temp, payload_timestamp, payload_timestampmysql, payload_watt, payload_wattseconds" );
  
  select messageRowID, payload_sensor, messagetimestamp, payload_temp, payload_timestamp, payload_timestampmysql, payload_watt, payload_wattseconds, hour(from_unixtime(payload_timestamp))
  FROM CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary 
  WHERE payload_timestamp &gt; unix_timestamp() - 3024*60*60
  GROUP BY hour(from_unixtime(payload_timestamp)); 
  </code></pre>
  
  <p>.. that however gives another error, which is: </p>
  
  <pre><code>ERROR: Error while executing Hive script.Query returned non-zero code: 10, cause: FAILED: Error in semantic analysis: Line 1:7 Expression not in GROUP BY key 'messageRowID'
  </code></pre>
  
  <p>Thoughts?</p>
  
  <p>UPDATE #2) The following is a quick dump of a few samples that are derived into the EVENT_KS CF in WSO2BAM. The last column is a calculated (in the perl daemon..) #watt_seconds, which will be used in a query to calculate the aggregate sum totalled into kwH, which then will be dumped into MySQL tables for sync to the application that holds the ui/ux layer.. </p>
  
  <pre><code>[12:03:00] [jskogsta@enterprise ../Product Centric Opco Modelling]$ ~/local/apache-cassandra-2.0.8/bin/cqlsh localhost 9160 -u admin -p admin --cqlversion="3.0.5"
  Connected to Test Cluster at localhost:9160.
  [cqlsh 4.1.1 | Cassandra 1.2.13 | CQL spec 3.0.5 | Thrift protocol 19.36.2]
  Use HELP for help.
  cqlsh&gt; use "EVENT_KS";
  cqlsh:EVENT_KS&gt; select * from currentcost_stream limit 5;
  
   key                                       | Description               | Name               | Nick_Name            | StreamId                  | Timestamp     | Version | payload_sensor | payload_temp | payload_timestamp | payload_timestampmysql | payload_watt | payload_wattseconds
  -------------------------------------------+---------------------------+--------------------+----------------------+---------------------------+---------------+---------+----------------+--------------+-------------------+------------------------+--------------+---------------------
    1403365575174::10.11.205.218::9443::9919 | Sample data from CC meter | currentcost.stream | Currentcost Realtime | currentcost.stream:1.0.18 | 1403365575174 |  1.0.18 |              1 |        13.16 |        1403365575 |    2014-06-21 23:46:15 |         6631 |               19893
    1403354553932::10.11.205.218::9443::2663 | Sample data from CC meter | currentcost.stream | Currentcost Realtime | currentcost.stream:1.0.18 | 1403354553932 |  1.0.18 |              1 |         14.1 |        1403354553 |    2014-06-21 20:42:33 |        28475 |                   0
   1403374113341::10.11.205.218::9443::11852 | Sample data from CC meter | currentcost.stream | Currentcost Realtime | currentcost.stream:1.0.18 | 1403374113341 |  1.0.18 |              1 |        10.18 |        1403374113 |    2014-06-22 02:08:33 |        17188 |              154692
    1403354501924::10.11.205.218::9443::1894 | Sample data from CC meter | currentcost.stream | Currentcost Realtime | currentcost.stream:1.0.18 | 1403354501924 |  1.0.18 |              1 |        10.17 |        1403354501 |    2014-06-21 20:41:41 |        26266 |                   0
   1403407054092::10.11.205.218::9443::15527 | Sample data from CC meter | currentcost.stream | Currentcost Realtime | currentcost.stream:1.0.18 | 1403407054092 |  1.0.18 |              1 |        17.16 |        1403407054 |    2014-06-22 11:17:34 |         6332 |                6332
  
  (5 rows)
  
  cqlsh:EVENT_KS&gt;
  </code></pre>
  
  <p>What I will be trying to do is to issue a query against this table (actually multiples depending on the various presentation aggregations that are required..), and present a view based on hourly sum's, 10-minute sum's, daily sum's, monthly sum's etc. etc. Depending on the query, the GROUP BY was intended to give this 'index' so to speak. Right now just testing this.. so will see how it ends up in the end. Hope this makes sense?! </p>
  
  <p>So not trying to remove duplicates...</p>
  
  <p>UPDATE 3) Was going about this all wrong.. and thought a bit more on the tip that was given below. Hence just simplifying the whole query gave the right results. The following query gives the total amount of kwH on an hourly basis for the WHOLE dataset. With this, I can create the various iterations of kwH spent over various time periods like </p>
  
  <ul>
  <li>Hourly over the last 24 hours</li>
  <li>Daily over the last year</li>
  <li>Minute over the last hour</li>
  </ul>
  
  <p>.. etc. etc. </p>
  
  <p>Here is the query: </p>
  
  <pre><code>DROP table CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary;
  
  CREATE EXTERNAL TABLE IF NOT EXISTS CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary ( messageRowID STRING, payload_sensor INT, messagetimestamp BIGINT, payload_temp FLOAT, payload_timestamp BIGINT, payload_timestampmysql STRING, payload_watt INT, payload_wattseconds INT ) 
  STORED BY 'org.apache.hadoop.hive.cassandra.CassandraStorageHandler'
  WITH SERDEPROPERTIES ( "cassandra.host" = "127.0.0.1",
      "cassandra.port" = "9160",
      "cassandra.ks.name" = "EVENT_KS",
      "cassandra.ks.username" = "admin",
      "cassandra.ks.password" = "admin",
      "cassandra.cf.name" = "currentcost_stream",
      "cassandra.columns.mapping" = ":key, payload_sensor, Timestamp, payload_temp, payload_timestamp, payload_timestampmysql, payload_watt, payload_wattseconds" );
  
  select hour(from_unixtime(payload_timestamp)) AS hourly, (sum(payload_wattseconds)/(60*60)/1000)
  FROM CurrentCostDataSamples_MySQL_Dump_Last_1_Hour_Summary 
  GROUP BY hour(from_unixtime(payload_timestamp));
  </code></pre>
  
  <p>This query yields the following based on the sample data: </p>
  
  <pre><code>hourly  _c1
  0   16.91570472222222
  1   16.363228888888887
  2   15.446414166666667
  3   11.151388055555556
  4   18.10564666666667
  5   2.2734924999999997
  6   17.370668055555555
  7   17.991484444444446
  8   38.632728888888884
  9   16.001440555555554
  10  15.887023888888889
  11  12.709341944444445
  12  23.052629722222225
  13  14.986092222222222
  14  16.182284722222224
  15  5.881564999999999
  18  2.8149172222222223
  19  17.484405
  20  15.888274166666665
  21  15.387210833333333
  22  16.088641666666668
  23  16.49990916666667
  </code></pre>
  
  <p>Which is aggregate kwH per hourly timeframe over the entire dataset.. </p>
  
  <p>So, now on to the next problem. ;-) </p>