{"id":8350,"date":"2022-06-01T15:48:38","date_gmt":"2022-06-01T07:48:38","guid":{"rendered":"https:\/\/nj.transwarp.cn:8180\/?p=8350"},"modified":"2022-07-28T17:10:02","modified_gmt":"2022-07-28T09:10:02","slug":"inceptor-%e5%b1%82%e6%ac%a1%e5%8c%96%e6%9f%a5%e8%af%a2%e4%bb%8b%e7%bb%8d%e5%8f%8a%e7%a4%ba%e4%be%8b","status":"publish","type":"post","link":"https:\/\/kbwp.transwarp.cn\/?p=8350","title":{"rendered":"Inceptor \u5c42\u6b21\u5316\u67e5\u8be2\u4ecb\u7ecd\u53ca\u793a\u4f8b"},"content":{"rendered":"<h3>\u6982\u8981\u63cf\u8ff0<\/h3>\n<hr \/>\n<p>\u672c\u6587\u4e3b\u8981\u4ecb\u7ecd\u5728 Inceptor \u4e2d\u5c42\u6b21\u5316\u67e5\u8be2\u7684\u4f7f\u7528\u793a\u4f8b\u3002<\/p>\n<h3>\u8be6\u7ec6\u8bf4\u660e<\/h3>\n<hr \/>\n<h4>\u6570\u636e\u51c6\u5907<\/h4>\n<p>\u672c\u6848\u4f8b\u4e2d\u7684\u57fa\u672c\u6570\u636e\u53ef\u901a\u8fc7\u5982\u4e0b SQL \u6765\u521b\u5efa\u3002<\/p>\n<pre><code class=\"language-sql\">DROP TABLE IF EXISTS no_loop_employee;\nCREATE TABLE no_loop_employee(\nemployee_id INT,\nname STRING,\nmanager_id INT\n)\nCLUSTERED BY (employee_id) INTO 3 BUCKETS\nSTORED AS ORC\nTBLPROPERTIES (\"transactional\"=\"true\");\nINSERT INTO no_loop_employee(employee_id, name, manager_id) VALUES (1, 'kochhr', NULL);\nINSERT INTO no_loop_employee(employee_id, name, manager_id) VALUES (2, 'greenberg', 1);\nINSERT INTO no_loop_employee(employee_id, name, manager_id) VALUES (3, 'faviet', 1);\nINSERT INTO no_loop_employee(employee_id, name, manager_id) VALUES (4, 'chen', 2);\nINSERT INTO no_loop_employee(employee_id, name, manager_id) VALUES (5, 'sciarra', 2);\nINSERT INTO no_loop_employee(employee_id, name, manager_id) VALUES (6, 'urman', 3);\nINSERT INTO no_loop_employee(employee_id, name, manager_id) VALUES (7, 'popp', 2);\nINSERT INTO no_loop_employee(employee_id, name, manager_id) VALUES (8, 'whlen', 6);\n\nDROP TABLE IF EXISTS loop_employee;\nCREATE TABLE loop_employee(\nemployee_id INT,\nname STRING,\nmanager_id INT\n)\nCLUSTERED BY (employee_id) INTO 3 BUCKETS\nSTORED AS ORC\nTBLPROPERTIES (\"transactional\"=\"true\");\nINSERT INTO loop_employee(employee_id, name, manager_id) VALUES (1, 'kochhr', NULL);\nINSERT INTO loop_employee(employee_id, name, manager_id) VALUES (2, 'greenberg', 1);\nINSERT INTO loop_employee(employee_id, name, manager_id) VALUES (3, 'faviet', 8);\nINSERT INTO loop_employee(employee_id, name, manager_id) VALUES (4, 'chen', 2);\nINSERT INTO loop_employee(employee_id, name, manager_id) VALUES (5, 'sciarra', 2);\nINSERT INTO loop_employee(employee_id, name, manager_id) VALUES (6, 'urman', 3);\nINSERT INTO loop_employee(employee_id, name, manager_id) VALUES (7, 'popp', 2);\nINSERT INTO loop_employee(employee_id, name, manager_id) VALUES (8, 'whlen', 6);<\/code><\/pre>\n<p>\u6839\u636e\u7ec4\u7ec7\u5173\u7cfb\uff0c<code>no_loop_employee<\/code>\u8fd9\u5f20\u8868\u5f62\u6210\u4e86\u5982\u4e0b\u7684\u6811\u5f62\u7ed3\u6784\u3002<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2022\/06\/image-1654068681272.png\" alt=\"file\" \/><\/p>\n<h4>\u57fa\u672c\u7528\u6cd5<\/h4>\n<p>\u5c42\u6b21\u5316\u67e5\u8be2\u7684\u6838\u5fc3\u662f\u7528<code>CONNECT BY<\/code>\u5b57\u53e5\u6307\u5b9a\u7236\u884c\u548c\u5b50\u884c\u7684\u5bf9\u5e94\u5173\u7cfb\uff0c\u4ece\u800c\u5efa\u7acb\u6811\u5f62\u7ed3\u6784\uff0c\u4f8b\u5982\u4ee5\u4e0b\u7684\u8bed\u53e5\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT employee_id, name, manager_id, hq__level\nFROM no_loop_employee\nCONNECT BY manager_id = PRIOR employee_id;<\/code><\/pre>\n<p>\u6ce8\u610f\u5176\u4e2d\u7684<code>hq__level<\/code>\u662f\u4e00\u4e2a\u4f2a\u5217\uff0c\u6307\u660e\u884c\u5728\u6811\u4e2d\u7684\u5c42\u6b21\uff0c\u4ece1\u5f00\u59cb\u3002<code>CONNECT BY<\/code>\u4e2d\u7684<code>PRIOR<\/code>\u8868\u660e\u540e\u9762\u7684\u8fd9\u4e2a\u5217\u662f\u7236\u884c\u7684\u5217\uff0c\u6240\u4ee5\u8fd9\u4e2a\u4f8b\u5b50\u4e2d\u5b57\u53e5\u7684\u610f\u601d\u662f\u7236\u884c\u7684<code>employee_id<\/code>\u548c\u5f53\u524d\u884c\u7684<code>manager_id<\/code>\u76f8\u7b49\uff0c\u901a\u8fc7\u8fd9\u4e2a\u5efa\u7acb\u6811\u5f62\u5173\u7cfb\u3002<\/p>\n<p>\u8f93\u51fa\u5927\u81f4\u5982\u4e0b\u56fe\u3002<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2022\/06\/image-1654068846517.png\" alt=\"file\" \/><\/p>\n<p>\u8fd9\u91cc\u4f1a\u5217\u51fa\u4ee5\u8868\u4e2d\u7684\u6240\u6709\u884c\u4f5c\u4e3a\u6839\u8282\u70b9\u5f62\u6210\u7684\u6811\u3002<\/p>\n<h4>\u6307\u5b9a\u6839\u8282\u70b9<\/h4>\n<p>\u53ef\u4ee5\u7528START WITH\u5b50\u53e5\u6307\u5b9a\u6811\u7684\u6839\u8282\u70b9\uff0c\u4f8b\u5982\u4ee5\u8001\u677f\u4f5c\u4e3a\u6839\u8282\u70b9\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT employee_id, name, manager_id, hq__level\nFROM no_loop_employee\nSTART WITH name='kochhr'\nCONNECT BY manager_id = PRIOR employee_id;<\/code><\/pre>\n<p>\u8fd9\u6837\u5c31\u8f93\u51fa\u4e86\u6574\u4e2a\u7ec4\u7ec7\u7684\u67b6\u6784\uff1a<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2022\/06\/image-1654069166449.png\" alt=\"file\" \/><\/p>\n<p>\u6ce8\u610f\u8fd9\u91cc\u6211\u4eec\u662f\u53ef\u4ee5\u6307\u5b9a\u591a\u4e2a\u6839\u8282\u70b9\u7684\u3002<\/p>\n<h4>\u4ea4\u6362\u6811\u7684\u987a\u5e8f<\/h4>\n<p>\u524d\u9762\u7684\u4f8b\u5b50\u91cc\u9762\uff0c\u6811\u90fd\u662f\u4ee5\u4e0a\u53f8\u4e3a\u6839\u8282\u70b9\u6216\u7236\u8282\u70b9\uff0c\u4e0b\u5c5e\u4e3a\u5b50\u8282\u70b9\uff0c\u4e5f\u53ef\u4ee5\u8ba9\u4e0b\u5c5e\u6210\u4e3a\u6839\u548c\u7236\u8282\u70b9\uff0c\u53ea\u8981\u8c03\u6574<code>PRIOR<\/code>\u7684\u4f4d\u7f6e\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT employee_id, name, manager_id, hq__level\nFROM no_loop_employee\nSTART WITH name='whlen'\nCONNECT BY employee_id = PRIOR manager_id;<\/code><\/pre>\n<p>\u8fd9\u91cc\u6211\u4eec\u6307\u5b9a\u7236\u884c\u7684<code>manager_id<\/code>\u662f\u5f53\u524d\u884c\u7684<code>employee_id<\/code>\uff0c\u8fd9\u6837\u5c31\u8ba9\u4e0b\u5c5e\u6210\u4e3a\u7236\u884c\uff0c\u8f93\u51fa\u5982\u4e0b\u3002<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2022\/06\/image-1654069271823.png\" alt=\"file\" \/><\/p>\n<h4>\u6570\u636e\u8fc7\u6ee4<\/h4>\n<p>\u53ef\u4ee5\u901a\u8fc7<code>WHERE<\/code>\u5b57\u53e5\u8fc7\u6ee4\u6389\u6811\u4e2d\u7684\u67d0\u4e00\u4e2a\u8282\u70b9\uff0c\u4f8b\u5982\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT employee_id, name, manager_id, hq__level\nFROM no_loop_employee\nWHERE name != 'faviet'\nSTART WITH name='kochhr'\nCONNECT BY manager_id = PRIOR employee_id;<\/code><\/pre>\n<p>\u8f93\u51fa\u5927\u81f4\u5982\u4e0b\u3002<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2022\/06\/image-1654069338750.png\" alt=\"file\" \/><\/p>\n<p>\u53ef\u4ee5\u770b\u5230<code>name<\/code>\u662f<code>faviet<\/code>\u7684\u8282\u70b9\u90fd\u88ab\u8fc7\u6ee4\u6389\u4e86\u3002\u4f46\u662f\u8fd9\u4e2a\u5206\u652f\u8fd8\u5728\uff0c\u6211\u4eec\u53ef\u4ee5\u770b\u5230\u4ed6\u7684\u4e0b\u5c5e\u90fd\u5728\uff0c\u8981\u8fc7\u6ee4\u6389\u8fd9\u4e2a\u5206\u652f\uff0c\u9700\u8981\u5728<code>CONNECT BY<\/code>\u91cc\u8fc7\u6ee4\uff0c\u4f8b\u5982\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT employee_id, name, manager_id, hq__level\nFROM no_loop_employee\nSTART WITH name='kochhr'\nCONNECT BY name != 'faviet' AND manager_id = PRIOR employee_id;<\/code><\/pre>\n<p>\u8f93\u51fa\u5982\u4e0b\u3002<\/p>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/2022\/06\/image-1654069465751.png\" alt=\"file\" \/><\/p>\n<p>\u53ef\u4ee5\u770b\u5230\u6574\u4e2a\u4e0a\u53f8\u662f<code>faviet<\/code>\u7684\u5206\u652f\u90fd\u88ab\u8fc7\u6ee4\u6389\u4e86\u3002<\/p>\n<h4>\u5faa\u73af\u9519\u8bef<\/h4>\n<p>\u56e0\u4e3a\u5c42\u6b21\u5316\u67e5\u8be2\u751f\u6210\u4e00\u4e2a\u6811\uff0c\u6811\u662f\u4e0d\u80fd\u6709\u73af\u7684\uff0c\u5982\u679c\u6709\u73af\u4f1a\u62a5\u9519\uff0c\u4f8b\u5982\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT employee_id, name, manager_id\nFROM loop_employee\nSTART WITH employee_id = 3\nCONNECT BY PRIOR employee_id = manager_id;<\/code><\/pre>\n<p>\u4f1a\u62a5\u8fd9\u6837\u7684\u9519\u8bef\u3002<\/p>\n<p><strong><code>EXECUTION FAILED: Task MAPRED-SPARK error SparkException: [Error 1] Job aborted due to stage failure: Task 0 in stage 102550.0 failed 4 times, most recent failure: Lost task 0.3 in stage 102550.0 (TID 389802, baogang1): java.lang.RuntimeException: Loop founded in hierarchical query<\/code><\/strong><\/p>\n<p><em>\u672c\u6587\u662f\u4e00\u4e2a\u5c42\u6b21\u5316\u67e5\u8be2\u7684\u7b80\u5355demo\uff0c\u5982\u9700\u8981\u66f4\u591a\u7684\u7528\u6cd5\u548c\u9650\u5236\u4fe1\u606f\uff0c\u8bf7\u67e5\u9605\u76f8\u5173Inceptor\u7248\u672c\u7684\u4f7f\u7528\u624b\u518c\u3002<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u6982\u8981\u63cf\u8ff0 \u672c\u6587\u4e3b\u8981\u4ecb\u7ecd\u5728 Inceptor \u4e2d\u5c42\u6b21\u5316\u67e5\u8be2\u7684\u4f7f\u7528\u793a\u4f8b\u3002 \u8be6\u7ec6\u8bf4\u660e \u6570\u636e\u51c6\u5907 \u672c\u6848\u4f8b\u4e2d\u7684\u57fa\u672c\u6570\u636e\u53ef ..<\/p>\n<div class=\"clear-fix\"><\/div>\n<p><a href=\"https:\/\/kbwp.transwarp.cn\/?p=8350\" title=\"read more...\">Read more<\/a><\/p>\n","protected":false},"author":81,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38],"tags":[],"class_list":["post-8350","post","type-post","status-publish","format-standard","hentry","category-configuration"],"acf":[],"_links":{"self":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/8350","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/users\/81"}],"replies":[{"embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=8350"}],"version-history":[{"count":1,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/8350\/revisions"}],"predecessor-version":[{"id":8622,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=\/wp\/v2\/posts\/8350\/revisions\/8622"}],"wp:attachment":[{"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=8350"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=8350"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/kbwp.transwarp.cn\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=8350"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}