{"id":4979,"date":"2020-09-27T01:42:35","date_gmt":"2020-09-26T17:42:35","guid":{"rendered":"https:\/\/ixyzero.com\/blog\/?p=4979"},"modified":"2020-09-25T09:10:16","modified_gmt":"2020-09-25T01:10:16","slug":"clickhouse%e4%b8%ad%e7%9a%84%e4%b8%80%e4%ba%9b%e5%8a%9f%e8%83%bd%e7%82%b9%e5%ad%a6%e4%b9%a0","status":"publish","type":"post","link":"https:\/\/ixyzero.com\/blog\/archives\/4979.html","title":{"rendered":"ClickHouse\u4e2d\u7684\u4e00\u4e9b\u529f\u80fd\u70b9\u5b66\u4e60"},"content":{"rendered":"\n<p>=Start=<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u7f18\u7531\uff1a<\/h4>\n\n\n\n<p>\u6574\u7406\u8bb0\u5f55\u4e00\u4e0b\u6700\u8fd1\u4f7f\u7528ClickHouse\u7684\u7ecf\u9a8c\uff0c\u65b9\u4fbf\u4ee5\u540e\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\u3002<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">\u6b63\u6587\uff1a<\/h4>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u89e3\u7b54\uff1a<\/h5>\n\n\n\n<h5 class=\"wp-block-heading\">0. \u501f\u52a9 clickhouse-cli \u8fd9\u4e2a\u5de5\u5177\u8fdb\u884c\u547d\u4ee4\u884c\u64cd\u4f5c<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>$ pip3 install clickhouse-cli\n\n$ vim ~\/.clickhouse-cli.rc\n\n$ clickhouse-cli\ncommand not found: clickhouse-cli\n\n# \u6211\u7684\u73af\u5883\u662f macOS Catalina 10.15.6 \uff0c\u7528pip3\u5b89\u88c5\u6210\u529f\uff0c\u4f46\u662f\u5728 $PATH \u91cc\u9762\u627e\u4e0d\u5230\u8fd9\u4e2a\u7a0b\u5e8f\n# \u540e\u6765\u53d1\u73b0\u662f\u5728 $HOME \u76ee\u5f55\u4e0b\u7684 Library \u7684 Python \u76ee\u5f55\u4e2d\n$ ls -lt .\/Library\/Python\/3.8\/bin\n\n$ ~\/Library\/Python\/3.8\/bin\/clickhouse-cli<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">1. \u4e00\u4e9b\u73af\u5883\u719f\u6089\u64cd\u4f5c\/\u547d\u4ee4<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>$ ~\/Library\/Python\/3.8\/bin\/clickhouse-cli\nclickhouse-cli version: 0.3.6\nConnecting to x.x.x.x:80\nConnected to ClickHouse server v19.13.1.\n\n :) help\n\nclickhouse-cli's custom commands:\n---------------------------------\nUSE     Change the current database.\nSET     Set an option for the current CLI session.\nQUIT    Exit clickhouse-cli.\nHELP    Show this help message.\n\nPostgreSQL-like custom commands:\n--------------------------------\n\\l      Show databases.\n\\c      Change the current database.\n\\d, \\dt Show tables in the current database.\n\\d+     Show table's schema.\n\\ps     Show current queries.\n\\kill   Kill query by its ID.\n\nQuery suffixes:\n---------------\n\\g, \\G  Use the Vertical format.\n\\p      Enable the pager.\n :)\n\n# \u67e5\u770b\u6709\u54ea\u4e9b\u6570\u636e\u5e93\nshow databases\n\\l\n\n# \u5207\u6362\u6570\u636e\u5e93\nuse db_name\n\n# \u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u6709\u54ea\u4e9b\u8868\nshow tables\n\\d\n\\dt\n\n# \u67e5\u770b\u7279\u5b9a\u6570\u636e\u8868\u7684\u8868\u7ed3\u6784\ndescribe table table_name\n\\d+ table_name<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">2. \u65f6\u95f4\u65e5\u671f\u51fd\u6570<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>WITH\n    toDate('2019-01-01') AS date,\n    toDateTime('2019-01-01 00:00:00') AS date_time\nSELECT\n    date,\n    subtractYears(date, 1) AS subtract_years_with_date,\n    date_time,\n    subtractYears(date_time, 1) AS subtract_years_with_date_time\n;<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">3. \u5b57\u7b26\u4e32\u5904\u7406\u51fd\u6570<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT splitByChar(',', '1,2,3,abcde');\nSELECT splitByString(', ', '1, 2 3, 4,5, abcde');\n\n-- arrayStringConcat() \u6570\u7ec4\u5143\u7d20\u62fc\u63a5\u7684\u529f\u80fd\u548cHive\u4e2d\u7684 concat_ws() \u7c7b\u4f3c\u3002\n\nWITH\n\tsplitByChar(',', '1,2,3,abcde') as arr1\nselect\n\tarr1,\n\tarrayStringConcat(arr1, '#') as arr1str\n;\n\n\u250c\u2500arr1\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500arr1str\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 &#91;'1','2','3','abcde'] \u2502 1#2#3#abcde \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- \u5b57\u7b26\u4e32\u67e5\u627e\nposition() \/ locate()\n\n-- \u5b57\u7b26\u4e32\u66ff\u6362\nSELECT replaceRegexpAll('Hello, World!', '.', '\\\\0\\\\0') AS res\n\n\u250c\u2500res\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 HHeelllloo,,  WWoorrlldd!! \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\n-- \u5b57\u7b26\u4e32\u63d0\u53d6\nsubstring() \/ extractallgroups()<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">4. \u5e38\u7528\u7684\u4e00\u4e9b\u805a\u5408\u529f\u80fd<\/h5>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Hive\u4e2d\u7684 collect_set \u529f\u80fd\uff0c\u5c06\u67d0\u4e00\u5217\u4e2d\u7684value\u53d6\u503c\u8f6c\u6362\u6210\u4e00\u4e2a list\/set \u5728 ClickHouse \u4e2d\u53ef\u4ee5\u901a\u8fc7 groupUniqArray() \u6765\u5b9e\u73b0\u3002\nselect user_id,\n       count(1) as cnt,\n       count(distinct user_agent) as ua_cnt,\n       groupUniqArray(toDate(timestamp))\n  from table_name\n where status = 200\n   and http_host = 'domain_name'\n   and uri = '\/path\/'\n   and args like '%keyword%'\n   and user_id in ('user1', 'user2', 'user3', 'user4', 'user5')\n   and timestamp between '2020-06-01 00:00:00' and '2020-07-31 23:59:59'\n group by user_id\n limit 20000\n;\n\n\u250c\u2500user_id\u2500\u2500\u2500\u2500\u2500\u252c\u2500cnt\u2500\u252c\u2500ua_cnt\u2500\u252c\u2500groupUniqArray(toDate(timestamp))\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 user1       \u2502  32 \u2502      1 \u2502 &#91;'2020-07-03','2020-07-09','2020-07-02','2020-07-08'] \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\u250c\u2500user_id\u2500\u252c\u2500cnt\u2500\u252c\u2500ua_cnt\u2500\u252c\u2500groupUniqArray(toDate(timestamp))\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 user4   \u2502  18 \u2502      1 \u2502 &#91;'2020-07-06','2020-07-03','2020-07-09'] \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\u250c\u2500user_id\u2500\u252c\u2500cnt\u2500\u252c\u2500ua_cnt\u2500\u252c\u2500groupUniqArray(toDate(timestamp))\u2500\u2510\n\u2502 user2   \u2502   4 \u2502      1 \u2502 &#91;'2020-07-13']                    \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\n\n-- Hive\u4e2d\u7684 count(distinct col_name) \u5728 ClickHouse \u4e2d\u53ef\u4ee5\u4f7f\u7528 uniq(col_name) \u6216\u8005 uniqExact(col_name) \u6765\u5b9e\u73b0\u3002<\/code><\/pre>\n\n\n\n<h5 class=\"wp-block-heading\">5. \u5176\u4ed6\u529f\u80fd<\/h5>\n\n\n\n<ul class=\"wp-block-list\"><li>URL\u76f8\u5173\u51fd\u6570<\/li><li>JSON\u5904\u7406\u76f8\u5173\u51fd\u6570<\/li><li>IP\u5904\u7406\u76f8\u5173\u51fd\u6570<\/li><\/ul>\n\n\n\n<p><\/p>\n\n\n\n<h5 class=\"wp-block-heading\">\u53c2\u8003\u94fe\u63a5\uff1a<\/h5>\n\n\n\n<p><a rel=\"noreferrer noopener\" href=\"https:\/\/github.com\/hatarist\/clickhouse-cli\" target=\"_blank\">https:\/\/github.com\/hatarist\/clickhouse-cli<\/a><\/p>\n\n\n\n<p><a rel=\"noreferrer noopener\" href=\"https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/date-time-functions\/\" target=\"_blank\">https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/date-time-functions\/<\/a><\/p>\n\n\n\n<p>Is there any function like hive&#8217;s concat_ws or explode or collect_list\/collect_array #6664<br><a href=\"https:\/\/github.com\/ClickHouse\/ClickHouse\/issues\/6664\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/github.com\/ClickHouse\/ClickHouse\/issues\/6664<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/splitting-merging-functions\/\">https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/splitting-merging-functions\/<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/string-search-functions\/#position\">https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/string-search-functions\/#position<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/string-functions\/#substring\">https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/string-functions\/#substring<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/splitting-merging-functions\/#extractallgroups\">https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/splitting-merging-functions\/#extractallgroups<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/url-functions\/\">https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/url-functions\/<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/json-functions\/\">https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/json-functions\/<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/ip-address-functions\/#ipv4stringtonums\">https:\/\/clickhouse.tech\/docs\/en\/sql-reference\/functions\/ip-address-functions\/#ipv4stringtonums<\/a><\/p>\n\n\n\n<p><a href=\"https:\/\/clickhouse.tech\/docs\/zh\/sql-reference\/statements\/select\/array-join\/\">https:\/\/clickhouse.tech\/docs\/zh\/sql-reference\/statements\/select\/array-join\/<\/a><\/p>\n\n\n\n<p>=END=<\/p>\n","protected":false},"excerpt":{"rendered":"<p>=Start= \u7f18\u7531\uff1a \u6574\u7406\u8bb0\u5f55\u4e00\u4e0b\u6700\u8fd1\u4f7f\u7528ClickHouse\u7684\u7ecf\u9a8c\uff0c\u65b9\u4fbf\u4ee5\u540e\u6709\u9700\u8981\u7684\u65f6\u5019\u53c2\u8003\u3002 \u6b63\u6587\uff1a \u53c2 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[14,23,7,12],"tags":[1651,1653,1261,421,1262],"class_list":["post-4979","post","type-post","status-publish","format-standard","hentry","category-database","category-knowledgebase-2","category-programing","category-tools","tag-clickhouse","tag-groupuniqarray","tag-hive","tag-join","tag-sql"],"views":8493,"_links":{"self":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4979","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/comments?post=4979"}],"version-history":[{"count":1,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4979\/revisions"}],"predecessor-version":[{"id":4980,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/posts\/4979\/revisions\/4980"}],"wp:attachment":[{"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/media?parent=4979"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/categories?post=4979"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ixyzero.com\/blog\/wp-json\/wp\/v2\/tags?post=4979"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}