{"id":2103,"date":"2020-11-25T10:27:13","date_gmt":"2020-11-25T02:27:13","guid":{"rendered":"https:\/\/www.gbase8.cn\/?p=2103"},"modified":"2022-05-30T17:27:27","modified_gmt":"2022-05-30T09:27:27","slug":"gbase-8a%e9%80%9a%e8%bf%87python%e4%bb%8e%e5%ae%a2%e6%88%b7%e7%ab%af%e6%89%a7%e8%a1%8csql%e8%af%ad%e5%8f%a5","status":"publish","type":"post","link":"https:\/\/www.gbase8.cn\/en\/2103","title":{"rendered":"\u5357\u5927\u901a\u7528GBase 8a\u901a\u8fc7python UDF\u4ece\u5ba2\u6237\u7aef\u6267\u884cSQL\u8bed\u53e5"},"content":{"rendered":"<p>GBase 8a \u6570\u636e\u5e93\u96c6\u7fa4\u4ece862Build43\u5f00\u59cb\uff0c\u652f\u6301python\u7684UDF\u81ea\u5b9a\u4e49\u51fd\u6570\u3002\u672c\u6587\u901a\u8fc7python\u529f\u80fd\u5728\u547d\u4ee4\u884c\u8c03\u7528gccli\u6b63\u5728\u6267\u884cSQL\u8bed\u53e5\uff0c\u5305\u62ec\u67d0\u4e9b\u4e0d\u5141\u8bb8\u5728\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u6267\u884c\u7684\u3002<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_84 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Directory Navigation<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6a2ad745040d5\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6a2ad745040d5\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.gbase8.cn\/en\/2103\/#python%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0\" >python\u81ea\u5b9a\u4e49\u51fd\u6570<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.gbase8.cn\/en\/2103\/#%E6%89%A7%E8%A1%8C%E6%95%88%E6%9E%9C\" >\u6267\u884c\u6548\u679c<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.gbase8.cn\/en\/2103\/#%E5%AE%A1%E8%AE%A1%E6%97%A5%E5%BF%97%E5%BD%92%E6%A1%A3%E7%9A%84%E5%BA%94%E7%94%A8%E6%A0%B7%E4%BE%8B\" >\u5ba1\u8ba1\u65e5\u5fd7\u5f52\u6863\u7684\u5e94\u7528\u6837\u4f8b<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"python%E8%87%AA%E5%AE%9A%E4%B9%89%E5%87%BD%E6%95%B0\"><\/span>python\u81ea\u5b9a\u4e49\u51fd\u6570<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>drop function if exists executePythonSQL;\ncreate function executePythonSQL(sql_str varchar(8000))\nreturns varchar\n$$\ntry:\n    import commands\n    output = commands.getoutput(\"gccli -ugbase -pgbase20110531 -N -vvv -e\\\\\\\"%s\\\\\\\"\" %sql_str)\nexcept:\n    return None\nreturn str(output)\n\n$$ language plpythonu;<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E6%89%A7%E8%A1%8C%E6%95%88%E6%9E%9C\"><\/span>\u6267\u884c\u6548\u679c<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>gbase&gt; select executePythonSQL('load data infile ''sftp:\/\/gbase:gbase1234@10.0.2.101\/home\/gbase\/t.txt'' into table testdb.t1 fields terminated by '',''') a;\n+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| a                                                                                                                                                                                                                                                                |\n+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| --------------\nload data infile 'sftp:\/\/gbase:gbase1234@10.0.2.101\/home\/gbase\/t.txt' into table testdb.t1 fields terminated by ','\n--------------\n\nQuery OK, 0 rows affected (Elapsed: 00:00:01.29)\nTask 6154 finished, Loaded 0 records, Skipped 1 records\n\nBye |\n+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+\n1 row in set (Elapsed: 00:00:01.32)\n\ngbase&gt; select executePythonSQL('select now()');\n+--------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| executePythonSQL('select now()')                                                                                                                             |\n+--------------------------------------------------------------------------------------------------------------------------------------------------------------+\n| --------------\nselect now()\n--------------\n\n+---------------------+\n| 2020-11-25 10:25:17 |\n+---------------------+\n1 row in set (Elapsed: 00:00:00.00)\n\nBye |\n+--------------------------------------------------------------------------------------------------------------------------------------------------------------+\n1 row in set (Elapsed: 00:00:00.04)\n\ngbase&gt;\n<\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"%E5%AE%A1%E8%AE%A1%E6%97%A5%E5%BF%97%E5%BD%92%E6%A1%A3%E7%9A%84%E5%BA%94%E7%94%A8%E6%A0%B7%E4%BE%8B\"><\/span>\u5ba1\u8ba1\u65e5\u5fd7\u5f52\u6863\u7684\u5e94\u7528\u6837\u4f8b<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>drop function if exists executePythonSQL;\ncreate function executePythonSQL(sql_str varchar(8000))\nreturns varchar\n$$\ntry:\n    import commands\n    output = commands.getoutput(\"gccli -ugbase -pgbase20110531 -N -vvv -e\\\\\\\"%s\\\\\\\"\" %sql_str)\nexcept:\n    return None\nreturn str(output)\n\n$$ language plpythonu;\n\n-- \n-- \u6b64\u529f\u80fd\u4e00\u5b9a\u8981\u786e\u4fdd\u6bcf\u4e2a\u8282\u70b9,\u5305\u62ec\u7ba1\u7406\u548c\u6570\u636e,\u4e3b\u673a\u540d\u4e0d\u540c\uff0c\u5e76\u5168\u90e8\u914d\u7f6e\u4e86DNS\uff0c\u80fd\u6309\u7167\u4e3b\u673a\u540d\u8bbf\u95ee\uff0c\u5426\u5219\u52a0\u8f7d\u4f1a\u62a5\u9519\u627e\u4e0d\u5230\u670d\u52a1\u5668\u6216\u6587\u4ef6\n-- \ndrop procedure if exists audit_log_archive;\ndelimiter \/\/\ncreate procedure audit_log_archive()\nbegin\n    select   \n      @@hostname as hostname, \n      thread_id, \n      taskid        , \n      start_time, \n      uid, \n      user, \n      host_ip, \n      query_time, \n      rows, \n      substr(table_list, 0, 4096), \n      substr(sql_text, 0, 8191),   -- utf8mb4 charset\n      sql_type, sql_command, \n      operators, \n      status, \n      conn_type  \n    from gbase.audit_log_bak1 \n    into outfile '\/home\/gbase\/audit_log.txt' -- \u6587\u4ef6\u8def\u5f84\u9009\u62e9\u4e00\u4e2a\u5408\u9002\u7684\n    fields terminated by '\\xFF\\xFE\\xFD' -- \u81ea\u5b9a\u4e49\u5217\u5206\u5272\u7b26\uff0c\u591a\u4e2a\u4e0d\u53ef\u89c1\u5b57\u7b26\n    lines terminated by '\\xFF\\xFD\\xFE' -- \u7528\u6237\u5b9a\u4e49\u7684\u884c\u5206\u5272\u7b26\uff0c\u591a\u4e2a\u4e0d\u53ef\u89c1\u5b57\u7b26\n    writemode by overwrites;  -- \u8986\u76d6\u73b0\u6709\u7684\n\n\n-- SQL\u8bed\u53e5\u91cc\u7684\u6700\u5916\u9762\u7684\u5355\u5f15\u53f7\uff0c\u7528\u4e24\u4e2a\u5355\u5f15\u53f7\u8f6c\u4e49\n-- SQL\u8bed\u53e5\u91cc\u7684\u53f3\u659c\u6760\\, \u7528\u4e24\u4e2a\u53f3\u659c\u6760\\\\\u4ee3\u66ff\n    select executePythonSQL(concat('load data infile ''sftp:\/\/gbase:gbase1234@',\n      @@hostname,  -- \u6b64\u5904\u7528\u4e3b\u673a\u540d\uff0c\u4e00\u5b9a\u8981\u786e\u4fdd\u6bcf\u4e2a\u8282\u70b9\u4e3b\u673a\u540d\u4e0d\u540c\uff0c\u5e76\u914d\u7f6e\u4e86DNS\n      '\/home\/gbase\/audit_log.txt'' into table gclusterdb.audit_log_express fields terminated by ''\\\\xFF\\\\xFE\\\\xFD'' lines terminated by ''\\\\xFF\\\\xFD\\\\xFE'' time format ''%H:%i:%s.%f'''));\n\nend \/\/\ndelimiter ;\n\ndrop event if exists import_audit_log;\ndelimiter \/\/\nCREATE EVENT \"import_audit_log\" ON SCHEDULE EVERY 10 MINUTE ENABLE LOCAL DO \nbegin \n    DECLARE insert_sign INT;\n\tDECLARE errno INT;\n\tDECLARE msg TEXT;\n\tDECLARE EXIT handler FOR sqlexception BEGIN\n\t\tget diagnostics condition 1 errno = gbase_errno,msg = message_text;\n\t\tCREATE TABLE IF NOT EXISTS import_audit_log_errors(err_time DATETIME, hostname VARCHAR(64), err_no INT, msg_txt VARCHAR(1024)) CHARSET = utf8;\n\t\tINSERT INTO import_audit_log_errors VALUES (now(),@@hostname,errno,substr(msg, 0, 1024));\n\tEND;\n    \n    drop self table if exists gbase.audit_log_bak1;\n\tdrop self table if exists gbase.audit_log_bak2;\n    CREATE TABLE if not exists audit_log_express (hostname varchar(512) DEFAULT NULL,thread_id int(11) DEFAULT NULL,taskid bigint(20) DEFAULT NULL,start_time datetime DEFAULT NULL,end_time datetime DEFAULT NULL,user_host text,user text,host_ip text,query_time time DEFAULT NULL,rows bigint(20) DEFAULT NULL,db varchar(512) DEFAULT NULL,table_list text,sql_text text,sql_type text,sql_command text,algorithms text,status text,conn_type text) CHARSET = utf8;\n\n    SET SELF sql_mode = '';\n\tCREATE SELF TABLE gbase.audit_log_bak2 LIKE gbase.audit_log;\n\tSET SELF sql_mode = DEFAULT;\n\trename SELF TABLE gbase.audit_log TO gbase.audit_log_bak1,gbase.audit_log_bak2 TO gbase.audit_log;\n\n    call audit_log_archive();\n    \n    DROP SELF TABLE gbase.audit_log_bak1;\nend\/\/\ndelimiter ;\n\n<\/code><\/pre>","protected":false},"excerpt":{"rendered":"<p>\u5357\u5927\u901a\u7528GBase 8a \u6570\u636e\u5e93\u96c6\u7fa4\u4ece862Build43\u5f00\u59cb\uff0c\u652f\u6301python\u7684UDF\u81ea\u5b9a\u4e49\u51fd\u6570\u3002\u672c\u6587\u901a\u8fc7python\u529f\u80fd\u5728\u547d\u4ee4\u884c\u8c03\u7528gccli\u6b63\u5728\u6267\u884cSQL\u8bed\u53e5\uff0c\u5305\u62ec\u67d0\u4e9b\u4e0d\u5141\u8bb8\u5728\u5b58\u50a8\u8fc7\u7a0b\u4e2d\u6267\u884c\u7684\u3002<\/p>","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[98],"class_list":["post-2103","post","type-post","status-publish","format-standard","hentry","category-gbase8a","tag-python"],"_links":{"self":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/2103","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/comments?post=2103"}],"version-history":[{"count":6,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/2103\/revisions"}],"predecessor-version":[{"id":9527,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/posts\/2103\/revisions\/9527"}],"wp:attachment":[{"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/media?parent=2103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/categories?post=2103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gbase8.cn\/en\/wp-json\/wp\/v2\/tags?post=2103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}