如何找到路径流并使用pig或hive对它们进行排序?

hyrbngr7  于 2021-06-24  发布在  Pig
关注(0)|答案(2)|浏览(444)

下面是我的用例示例。

kxxlusnw

kxxlusnw1#

输入

  1. 990004916946605-1404157897784,S1,1404157898275
  2. 990004916946605-1404157897784,S1,1404157898286
  3. 990004916946605-1404157897784,S2,1404157898337
  4. 990004947764274-1435162269418,S1,1435162274044
  5. 990004947764274-1435162269418,S2,1435162274057
  6. 990004947764274-1435162269418,S3,1435162274081
  7. 990004947764274-1435162287965,S2,1435162690002
  8. 990004947764274-1435162287965,S1,1435162690001
  9. 990004947764274-1435162287965,S3,1435162690003
  10. 990004947764274-1435162287965,S1,1435162690004
  11. 990004947764274-1435162212345,S1,1435168768574
  12. 990004947764274-1435162212345,S2,1435168768585
  13. 990004947764274-1435162212345,S3,1435168768593
  14. register /home/cloudera/jar/ScreenFilter.jar;
  15. screen_records = LOAD '/user/cloudera/inputfiles/screen.txt' USING PigStorage(',') AS(session_id:chararray,screen_name:chararray,launch_time:long);
  16. screen_rec_order = ORDER screen_records by launch_time ASC;
  17. session_grped = GROUP screen_rec_order BY session_id;
  18. eached = FOREACH session_grped
  19. {
  20. ordered = ORDER screen_rec_order by launch_time;
  21. GENERATE group as session_id, REPLACE(BagToString(ordered.screen_name),'_','-->') as screen_str;
  22. };
  23. screen_each = FOREACH eached GENERATE session_id, GetOrderedScreen(screen_str) as screen_pattern;
  24. screen_grp = GROUP screen_each by screen_pattern;
  25. screen_final_each = FOREACH screen_grp GENERATE group as screen_pattern, COUNT(screen_each) as pattern_cnt;
  26. ranker = RANK screen_final_each BY pattern_cnt DESC DENSE;
  27. output_data = FOREACH ranker GENERATE screen_pattern, pattern_cnt, $0 as rank_value;
  28. dump output_data;

我找不到使用pig内置函数删除同一会话id的相邻屏幕的方法,因此我使用了javaudf来删除相邻的屏幕名称。
我创建了一个名为getorderedscreen的JavaUDF,将该udf合并到jar中,并将该jar命名为screenfilter.jar,并在这个pig脚本中注册了该jar
下面是getOrderedScreenJavaUDF的代码

  1. public class GetOrderedScreen extends EvalFunc<String> {
  2. @Override
  3. public String exec(Tuple input) throws IOException {
  4. String incoming_screen_str= (String)input.get(0);
  5. String outgoing_screen_str ="";
  6. String screen_array[] =incoming_screen_str.split("-->");
  7. String full_screen=screen_array[0];
  8. for (int i=0; i<screen_array.length;i++)
  9. {
  10. String prefix_screen= screen_array[i];
  11. String suffix_screen="";
  12. int j=i+1;
  13. if(j< screen_array.length)
  14. {
  15. suffix_screen = screen_array[j];
  16. }
  17. if (!prefix_screen.equalsIgnoreCase(suffix_screen))
  18. {
  19. full_screen = full_screen+ "-->" +suffix_screen;
  20. }
  21. }
  22. outgoing_screen_str =full_screen.substring(0, full_screen.lastIndexOf("-->"));
  23. return outgoing_screen_str;
  24. }

}
输出

  1. (S1-->S2-->S3,2,1)
  2. (S1-->S2,1,2)
  3. (S1-->S2-->S3-->S1,1,2)

希望这对你有帮助!。。还要再等一段时间,一些看到这个问题的好头脑会有效地回答(没有javaudf)

展开查看全部
6jygbczu

6jygbczu2#

你可以参考这个问题,一个特工问了类似的问题。如果我正确地理解了您的问题,那么您希望从路径中删除重复项,但仅当它们彼此相邻时。所以呢 1 -> 1 -> 2 -> 1 会变成 1 -> 2 -> 1 . 如果这是正确的,那么你不能只是分组和 distinct (我相信你已经注意到了)因为它会删除所有的副本。一个简单的解决方案是编写一个udf来删除那些重复项,同时保留用户的不同路径。
自定义项:

  1. package something;
  2. import java.util.ArrayList;
  3. import org.apache.hadoop.hive.ql.exec.UDF;
  4. import org.apache.hadoop.io.Text;
  5. public class RemoveSequentialDuplicatesUDF extends UDF {
  6. public ArrayList<Text> evaluate(ArrayList<Text> arr) {
  7. ArrayList<Text> newList = new ArrayList<Text>();
  8. newList.add(arr.get(0));
  9. for (int i = 1; i < arr.size(); i++) {
  10. String front = arr.get(i).toString();
  11. String back = arr.get(i-1).toString();
  12. if (!back.equals(front)) {
  13. newList.add(arr.get(i));
  14. }
  15. }
  16. return newList;
  17. }
  18. }

要建造这个jar,你需要一个 hive-core.jar 以及 hadoop-core.jar ,您可以在maven存储库中找到这些。确保您获得了在您的环境中使用的hive和hadoop版本。另外,如果您计划在生产环境中运行这个,我建议您向udf添加一些异常处理。构建jar之后,导入它并运行以下查询:
查询:

  1. add jar /path/to/jars/brickhouse-0.7.1.jar;
  2. add jar /path/to/jars/hive_common-SNAPSHOT.jar;
  3. create temporary function collect as "brickhouse.udf.collect.CollectUDAF";
  4. create temporary function remove_dups as "something.RemoveSequentialDuplicatesUDF";
  5. select screen_flow, count
  6. , dense_rank() over (order by count desc) rank
  7. from (
  8. select screen_flow
  9. , count(*) count
  10. from (
  11. select session_id
  12. , concat_ws("->", remove_dups(screen_array)) screen_flow
  13. from (
  14. select session_id
  15. , collect(screen_name) screen_array
  16. from (
  17. select *
  18. from database.table
  19. order by screen_launch_time ) a
  20. group by session_id ) b
  21. ) c
  22. group by screen_flow ) d

输出:

  1. s1->s2->s3 2 1
  2. s1->s2 1 2
  3. s1->s2->s3->s1 1 2

希望这有帮助。

展开查看全部

相关问题