{"action":"created","issue":{"url":"https://api.github.com/repos/vieyahn2017/javaway/issues/55","repository_url":"https://api.github.com/repos/vieyahn2017/javaway","labels_url":"https://api.github.com/repos/vieyahn2017/javaway/issues/55/labels{/name}","comments_url":"https://api.github.com/repos/vieyahn2017/javaway/issues/55/comments","events_url":"https://api.github.com/repos/vieyahn2017/javaway/issues/55/events","html_url":"https://github.com/vieyahn2017/javaway/issues/55","id":381645694,"node_id":"MDU6SXNzdWUzODE2NDU2OTQ=","number":55,"title":"11.15 Hive初体验","user":{"login":"vieyahn2017","id":31752705,"node_id":"MDQ6VXNlcjMxNzUyNzA1","avatar_url":"https://avatars1.githubusercontent.com/u/31752705?v=4","gravatar_id":"","url":"https://api.github.com/users/vieyahn2017","html_url":"https://github.com/vieyahn2017","followers_url":"https://api.github.com/users/vieyahn2017/followers","following_url":"https://api.github.com/users/vieyahn2017/following{/other_user}","gists_url":"https://api.github.com/users/vieyahn2017/gists{/gist_id}","starred_url":"https://api.github.com/users/vieyahn2017/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vieyahn2017/subscriptions","organizations_url":"https://api.github.com/users/vieyahn2017/orgs","repos_url":"https://api.github.com/users/vieyahn2017/repos","events_url":"https://api.github.com/users/vieyahn2017/events{/privacy}","received_events_url":"https://api.github.com/users/vieyahn2017/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":12,"created_at":"2018-11-16T15:28:16Z","updated_at":"2018-11-17T02:28:59Z","closed_at":null,"author_association":"OWNER","body":"Hive初体验"},"comment":{"url":"https://api.github.com/repos/vieyahn2017/javaway/issues/comments/439580532","html_url":"https://github.com/vieyahn2017/javaway/issues/55#issuecomment-439580532","issue_url":"https://api.github.com/repos/vieyahn2017/javaway/issues/55","id":439580532,"node_id":"MDEyOklzc3VlQ29tbWVudDQzOTU4MDUzMg==","user":{"login":"vieyahn2017","id":31752705,"node_id":"MDQ6VXNlcjMxNzUyNzA1","avatar_url":"https://avatars1.githubusercontent.com/u/31752705?v=4","gravatar_id":"","url":"https://api.github.com/users/vieyahn2017","html_url":"https://github.com/vieyahn2017","followers_url":"https://api.github.com/users/vieyahn2017/followers","following_url":"https://api.github.com/users/vieyahn2017/following{/other_user}","gists_url":"https://api.github.com/users/vieyahn2017/gists{/gist_id}","starred_url":"https://api.github.com/users/vieyahn2017/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/vieyahn2017/subscriptions","organizations_url":"https://api.github.com/users/vieyahn2017/orgs","repos_url":"https://api.github.com/users/vieyahn2017/repos","events_url":"https://api.github.com/users/vieyahn2017/events{/privacy}","received_events_url":"https://api.github.com/users/vieyahn2017/received_events","type":"User","site_admin":false},"created_at":"2018-11-17T02:28:59Z","updated_at":"2018-11-17T02:28:59Z","author_association":"OWNER","body":"Hive实现oracle的Minus函数\r\nhttps://www.cnblogs.com/yxwkf/p/5180451.html\r\n在Oracle中minus运算的主要功能是： 在进行两个表格或者两个查询结果的时候，返回在第一个表格/查询结果中与第二个表格/查询结果不同样的记录。\r\n\r\n结果不同样的记录包括两种情况：A，B 表中某一行的内容不同和A表中的数据在B表中不存在。总之返回的是A表的数据。\r\n\r\nHive中没有实现minus功能的函数，仅仅好分步实现。\r\n\r\n一、找出在Ａ表中的数据在Ｂ表中不存在的数据\r\n\r\n```sql\r\ninsert overwrite table tmp_A partition(name='A_innot_B')\r\n\r\nselect   a.*\r\n\r\nfrom A a left outer join  B b on (a.id = b.id)  where b.id is NULL; \r\n```\r\n\r\n二、找出在A表和B表都存在但内容不同的数据\r\n\r\n\r\nUDF函数例如以下：\r\n\r\n\r\n```java\r\nimport org.apache.Hadoop.hive.ql.exec.UDF\r\n\r\npublic class Minus extends UDF {\r\n\r\n    String A =\"\";\r\n\r\n    String B =\"\";\r\n\r\n    public Text evaluate(String... strs){\r\n\r\n        for(int i=0;i<strs.length/2;i++){\r\n\r\n            A = A +strs[i];\r\n\r\n        }\r\n\r\n        for(int i=strs.length/2;i<strs.length;i++){\r\n\r\n            B = B +strs[i];\r\n\r\n        }\r\n\r\n        if(A.replace(\" \", \"\").equals(B.replace(\" \", \"\"))){\r\n\r\n            return new Text(\"NULL\");\r\n\r\n        }else{\r\n\r\n            return new Text(strs[0].replace(\" \", \"\"));\r\n\r\n        }\r\n\r\n}\r\n```\r\n\r\n把程序打包放到目标机器上去\r\n•进入hive客户端\r\n•添加jar包：hive>add jar /run/jar/udf_test.jar;\r\n•创建临时函数：hive> CREATE TEMPORARY FUNCTION iminus AS 'com.hive.udf.Minus'\r\n\r\n\r\n相应的查询例如以下：\r\n```sql\r\ninsert overwrite table tmp_A_diff\r\n\r\nselect iminus(a.*,b.*) from A a join B b on (a.id=b.id);\r\n```\r\n上面的sql会执行Minus的java程序，改程序语句中有循环。假设数据量非常大非常耗时间。job进度卡着不动。也能够使用hive自带的函数实现\r\n\r\n```sql\r\ninsert overwrite table tmp_A_diff\r\nselect if(\r\n        regexp_replace(\r\n                  concat(                        \r\n                        a.*\r\n                        ),\r\n                          \" \",\"\")\r\n       =\r\n        regexp_replace(\r\n                 concat(\r\n                        b.*\r\n                        ),\r\n                          \" \",\"\")\r\n       ,NULL,b.id)\r\n    from A a\r\n             join \r\n         B b \r\n             on (a.id=b.id);\r\n```\r\n这样效率好些。\r\n\r\n\r\ntmp_A_diff存储的是A表和B表都存在但内容不同的数据的id和一些“NULL”。\r\n\r\n依据id获得每行数据\r\n```sql\r\ninsert overwrite table tmp_A partition(name=\"A_in_B\")\r\n\r\nselect a.*\r\n\r\nfrom tmp_A_diff b join A a on (a.id=b.id);\r\n```\r\n如今tmp_A中分区A_innot_B和分区A_in_B的数据就是oracle中（select * from A）minus (select * from B)的数据。\r\n\r\n"}}
{"actor":{"display_login":"vieyahn2017"}}