I am a newbie with Hive and I am having tremendous difficulty trying to figure out how to solve the following problem using Hive. I am using the hortonworks VM 2.4 for virtualbox. I have imported some csv data into Hive and having lots of trouble doing the following.
Problem I desperately need assistance in figuring out how to query my data to obtain an aggregate heart rate for n seconds. For example, how can I get the mean heart rate for each participant every 4 seconds? Data Consider the following data that represents a simple table I have timestamp hr participant 15:30:12 95 1 15:30:13 96 1 15:30:14 97 1 15:30:15 98 1 15:30:16 95 1 15:30:17 96 1 15:30:18 97 1 15:30:19 95 1 15:30:20 92 1 15:30:21 93 1 15:30:23 89 1 15:30:11 80 2
Desired Output
timestamp hr participant mean 15:30:12 95 1 96.5 15:30:13 96 1 96.5 15:30:14 97 1 96.5 15:30:15 98 1 96.5 15:30:16 95 1 95.75 15:30:17 96 1 95.75 15:30:18 97 1 95.75 15:30:19 95 1 95.75 15:30:20 92 1 91.33 15:30:21 93 1 91.33 15:30:23 89 1 91.33 15:30:20 80 2 96.3 Consider the desired output above. For participant 1, there is 3 mean temperatures given. We have 96.5 for the first 4 seconds, 95.75 for the second 4 seconds, and 91.33 as the mean for the last 3 seconds. The last part is tricky because we don't have 4 seconds. Ideally, we would take the mean of what we have. Since we only have 3 seconds, we have the mean of that. It's possible we could have 2 seconds or 1 seconds. I just don't know. In addition, notice that there is a missing second for participant 1, we have 15:30:21 and then it jumps to 15:30:23.
Issues I think the only reasonable way to solve this problem is to first assign a unique primary key to the data that labels each row a number such as 1, 2, 3, 4, 5, and somehow use that unique identifier by modulo n or something to get our mean for every 4 seconds. The problem is that I don't know if that will fall apart when we get to the end of the data for a participant and need to get the mean of the remaining data. Simply put, is Hive not worth the trouble for what I need to do? I have no primary id because the csv data that I was given is missing it AFAIK, there is no HH:MM:SS data type in Hive. My timestamp column is a string type Is their an easier way to do this outside of Hive and then get the data back into hive? Could I try this with R? The problem there is that RJDBC can read Hive but can't write back to it. So that's out. rHive also is very unstable and I won't use it. Could I use python to do this? If so, what library can I use to read and write to Hive? If I can use Hive, geeze where do I even start? Please help. Thank you so much for your patience in reading this question. I am grateful for any assistance rendered and will do my best to answer any questions or comments to better qualify my question.
... View more
