1"use strict";(self.webpackChunkquickwit_io=self.webpackChunkquickwit_io||[]).push([[10998],{3905:(e,t,n)=>{n.d(t,{Zo:()=>u,kt:()=>m});var a=n(67294);function i(e,t,n){return t in e?Object.defineProperty(e,t,{value:n,enumerable:!0,configurable:!0,writable:!0}):e[t]=n,e}function r(e,t){var n=Object.keys(e);if(Object.getOwnPropertySymbols){var a=Object.getOwnPropertySymbols(e);t&&(a=a.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),n.push.apply(n,a)}return n}function s(e){for(var t=1;t<arguments.length;t++){var n=null!=arguments[t]?arguments[t]:{};t%2?r(Object(n),!0).forEach((function(t){i(e,t,n[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(n)):r(Object(n)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(n,t))}))}return e}function o(e,t){if(null==e)return{};var n,a,i=function(e,t){if(null==e)return{};var n,a,i={},r=Object.keys(e);for(a=0;a<r.length;a++)n=r[a],t.indexOf(n)>=0||(i[n]=e[n]);return i}(e,t);if(Object.getOwnPropertySymbols){var r=Object.getOwnPropertySymbols(e);for(a=0;a<r.length;a++)n=r[a],t.indexOf(n)>=0||Object.prototype.propertyIsEnumerable.call(e,n)&&(i[n]=e[n])}return i}var l=a.createContext({}),c=function(e){var t=a.useContext(l),n=t;return e&&(n="function"==typeof e?e(t):s(s({},t),e)),n},u=function(e){var t=c(e.components);return a.createElement(l.Provider,{value:t},e.children)},d="mdxType",p={inlineCode:"code",wrapper:function(e){var t=e.children;return a.createElement(a.Fragment,{},t)}},h=a.forwardRef((function(e,t){var n=e.components,i=e.mdxType,r=e.originalType,l=e.parentName,u=o(e,["components","mdxType","originalType","parentName"]),d=c(n),h=i,m=d["".concat(l,".").concat(h)]||d[h]||p[h]||r;return n?a.createElement(m,s(s({ref:t},u),{},{components:n})):a.createElement(m,s({ref:t},u))}));function m(e,t){var n=arguments,i=t&&t.mdxType;if("string"==typeof e||i){var r=n.length,s=new Array(r);s[0]=h;var o={};for(var l in t)hasOwnProperty.call(t,l)&&(o[l]=t[l]);o.originalType=e,o[d]="string"==typeof e?e:i,s[1]=o;for(var c=2;c<r;c++)s[c]=n[c];return a.createElement.apply(null,s)}return a.createElement.apply(null,n)}h.displayName="MDXCreateElement"},23090:(e,t,n)=>{n.r(t),n.d(t,{assets:()=>l,contentTitle:()=>s,default:()=>p,frontMatter:()=>r,metadata:()=>o,toc:()=>c});var a=n(87462),i=(n(67294),n(3905));const r={title:"Full-text search on ClickHouse",description:"Add full-text search to ClickHouse, using the Quickwit search streaming feature.",tags:["clickhouse","integration"],icon_url:"/img/tutorials/clickhouse.svg",sidebar_position:10},s=void 0,o={unversionedId:"guides/add-full-text-search-to-your-olap-db",id:"version-0.7.1/guides/add-full-text-search-to-your-olap-db",title:"Full-text search on ClickHouse",description:"Add full-text search to ClickHouse, using the Quickwit search streaming feature.",source:"@site/versioned_docs/version-0.7.1/guides/add-full-text-search-to-your-olap-db.md",sourceDirName:"guides",slug:"/guides/add-full-text-search-to-your-olap-db",permalink:"/docs/0.7.1/guides/add-full-text-search-to-your-olap-db",draft:!1,tags:[{label:"clickhouse",permalink:"/docs/0.7.1/tags/clickhouse"},{label:"integration",permalink:"/docs/0.7.1/tags/integration"}],version:"0.7.1",sidebarPosition:10,frontMatter:{title:"Full-text search on ClickHouse",description:"Add full-text search to ClickHouse, using the Quickwit search streaming feature.",tags:["clickhouse","integration"],icon_url:"/img/tutorials/clickhouse.svg",sidebar_position:10},sidebar:"tutorialSidebar",previous:{title:"AWS cluster setup",permalink:"/docs/0.7.1/guides/aws-setup"},next:{title:"REST API",permalink:"/docs/0.7.1/reference/rest-api"}},l={},c=[{value:"Install",id:"install",level:2},{value:"Start a Quickwit server",id:"start-a-quickwit-server",level:2},{value:"Create a Quickwit index",id:"create-a-quickwit-index",level:2},{value:"Indexing events",id:"indexing-events",level:2},{value:"Streaming IDs",id:"streaming-ids",level:2},{value:"ClickHouse",id:"clickhouse",level:2},{value:"Create database and table",id:"create-database-and-table",level:3},{value:"Import events",id:"import-events",level:3},{value:"Use Quickwit search inside ClickHouse",id:"use-quickwit-search-inside-clickhouse",level:3},{value:"Wrapping up",id:"wrapping-up",level:2}],u={toc:c},d="wrapper";
1function p(e){let{components:t,...n}=e;return(0,i.kt)(d,(0,a.Z)({},u,n,{components:t,mdxType:"MDXLayout"}),(0,i.kt)("p",null,"This guide will help you add full-text search to a well-known OLAP database, ClickHouse, using the Quickwit search streaming feature. Indeed Quickwit exposes a REST endpoint that streams ids or whatever attributes matching a search query ",(0,i.kt)("strong",{parentName:"p"},"extremely fast")," (up to 50 million in 1 second), and ClickHouse can easily use them with joins queries."),(0,i.kt)("p",null,"We will take the ",(0,i.kt)("a",{parentName:"p",href:"https://www.gharchive.org/"},"GitHub archive dataset"),", which gathers more than 3 billion GitHub events: ",(0,i.kt)("inlineCode",{parentName:"p"},"PullRequestEvent"),", ",(0,i.kt)("inlineCode",{parentName:"p"},"IssuesEvent"),"... You can dive into this ",(0,i.kt)("a",{parentName:"p",href:"https://ghe.clickhouse.tech/"},"great analysis")," made by ClickHouse to have a good understanding of the dataset. We also took strong inspiration from this work, and we are very grateful to them for sharing this."),(0,i.kt)("h2",{id:"install"},"Install"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-bash"},"curl -L https://install.quickwit.io | sh\ncd quickwit-v*/\n")),(0,i.kt)("h2",{id:"start-a-quickwit-server"},"Start a Quickwit server"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-bash"},"./quickwit run\n")),(0,i.kt)("h2",{id:"create-a-quickwit-index"},"Create a Quickwit index"),(0,i.kt)("p",null,"After ","[starting Quickwit]",", we need to create an index configured to receive these events. Let's first look at the data to ingest. Here is an event example:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-JSON"},'{\n "id": 11410577343,\n "event_type": "PullRequestEvent",\n "actor_login": "renovate[bot]",\n "repo_name": "dmtrKovalenko/reason-date-fns",\n "created_at": 1580515200000,\n "action": "closed",\n "number": 44,\n "title": "Update dependency rollup to ^1.31.0",\n "labels": [],\n "ref": null,\n "additions": 5,\n "deletions": 5,\n "commit_id": null,\n "body":"This PR contains the following updates..."\n}\n')),(0,i.kt)("p",null,"We don't need to index all fields described above as ",(0,i.kt)("inlineCode",{parentName:"p"},"title")," and ",(0,i.kt)("inlineCode",{parentName:"p"},"body")," are the fields of interest for our full-text search tutorial.\nThe ",(0,i.kt)("inlineCode",{parentName:"p"},"id")," will be helpful for making the JOINs in ClickHouse, ",(0,i.kt)("inlineCode",{parentName:"p"},"created_at")," and ",(0,i.kt)("inlineCode",{parentName:"p"},"event_type")," may also be beneficial for timestamp pruning and filtering."),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-yaml",metastring:'title="gh-archive-index-config.yaml"',title:'"gh-archive-index-config.yaml"'},"version: 0.7\nindex_id: gh-archive\n# By default, the index will be stored in your data directory,\n# but you can store it on s3 or on a custom path as follows:\n# index_uri: s3://my-bucket/gh-archive\n# index_uri: file://my-big-ssd-harddrive/\ndoc_mapping:\n store_source: false\n field_mappings:\n - name: id\n type: u64\n fast: true\n - name: created_at\n type: datetime\n input_formats:\n - unix_timestamp\n output_format: unix_timestamp_secs\n fast_precision: seconds\n fast: true\n - name: event_type\n type: text\n tokenizer: raw\n - name: title\n type: text\n tokenizer: default\n record: position\n - name: body\n type: text\n tokenizer: default\n record: position\n timestamp_field: created_at\n\nsearch_settings:\n default_search_fields: [title, body]\n")),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-bash"},"curl -o gh-archive-index-config.yaml https://raw.githubusercontent.com/quickwit-oss/quickwit/main/config/tutorials/gh-archive/index-config-for-clickhouse.yaml\n./quickwit index create --index-config gh-archive-index-config.yaml\n")),(0,i.kt)("h2",{id:"indexing-events"},"Indexing events"),(0,i.kt)("p",null,"The dataset is a compressed ",(0,i.kt)("a",{parentName:"p",href:"https://quickwit-d
1atasets-public.s3.amazonaws.com/gh-archive/gh-archive-2021-12.json.gz"},"NDJSON file"),".\nLet's index it."),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-bash"},"wget https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2021-12-text-only.json.gz\ngunzip -c gh-archive-2021-12-text-only.json.gz | ./quickwit index ingest --index gh-archive\n")),(0,i.kt)("p",null,"You can check it's working by using the ",(0,i.kt)("inlineCode",{parentName:"p"},"search")," command and looking for ",(0,i.kt)("inlineCode",{parentName:"p"},"tantivy")," word:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-bash"},'./quickwit index search --index gh-archive --query "tantivy"\n')),(0,i.kt)("h2",{id:"streaming-ids"},"Streaming IDs"),(0,i.kt)("p",null,"We are now ready to fetch some ids with the search stream endpoint. Let's start by streaming them on a simple\nquery and with a ",(0,i.kt)("inlineCode",{parentName:"p"},"csv")," output format."),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-bash"},'curl "http://127.0.0.1:7280/api/v1/gh-archive/search/stream?query=tantivy&output_format=csv&fast_field=id"\n')),(0,i.kt)("p",null,"We will use the ",(0,i.kt)("inlineCode",{parentName:"p"},"click_house")," binary output format in the following sections to speed up queries."),(0,i.kt)("h2",{id:"clickhouse"},"ClickHouse"),(0,i.kt)("p",null,"Let's leave Quickwit for now and ",(0,i.kt)("a",{parentName:"p",href:"https://clickhouse.com/docs/en/install"},"install ClickHouse"),". Start a ClickHouse server."),(0,i.kt)("h3",{id:"create-database-and-table"},"Create database and table"),(0,i.kt)("p",null,"Once installed, just start a client and execute the following sql statements:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-SQL"},"CREATE DATABASE \"gh-archive\";\nUSE \"gh-archive\";\n\n\nCREATE TABLE github_events\n(\n id UInt64,\n event_type Enum('CommitCommentEvent' = 1, 'CreateEvent' = 2, 'DeleteEvent' = 3, 'ForkEvent' = 4,\n 'GollumEvent' = 5, 'IssueCommentEvent' = 6, 'IssuesEvent' = 7, 'MemberEvent' = 8,\n 'PublicEvent' = 9, 'PullRequestEvent' = 10, 'PullRequestReviewCommentEvent' = 11,\n 'PushEvent' = 12, 'ReleaseEvent' = 13, 'SponsorshipEvent' = 14, 'WatchEvent' = 15,\n 'GistEvent' = 16, 'FollowEvent' = 17, 'DownloadEvent' = 18, 'PullRequestReviewEvent' = 19,\n 'ForkApplyEvent' = 20, 'Event' = 21, 'TeamAddEvent' = 22),\n actor_login LowCardinality(String),\n repo_name LowCardinality(String),\n created_at Int64,\n action Enum('none' = 0, 'created' = 1, 'added' = 2, 'edited' = 3, 'deleted' = 4, 'opened' = 5, 'closed' = 6, 'reopened' = 7, 'assigned' = 8, 'unassigned' = 9,\n 'labeled' = 10, 'unlabeled' = 11, 'review_requested' = 12, 'review_request_removed' = 13, 'synchronize' = 14, 'started' = 15, 'published' = 16, 'update' = 17, 'create' = 18, 'fork' = 19, 'merged' = 20),\n comment_id UInt64,\n body String,\n ref LowCardinality(String),\n number UInt32,\n title String,\n labels Array(LowCardinality(String)),\n additions UInt32,\n deletions UInt32,\n commit_id String\n) ENGINE = MergeTree ORDER BY (event_type, repo_name, created_at);\n")),(0,i.kt)("h3",{id:"import-events"},"Import events"),(0,i.kt)("p",null,"We have created a second dataset, ",(0,i.kt)("inlineCode",{parentName:"p"},"gh-archive-2021-12.json.gz"),", which gathers all events, even ones with no\ntext. So it's better to insert it into ClickHouse, but if you don't have the time, you can use the dataset\n",(0,i.kt)("inlineCode",{parentName:"p"},"gh-archive-2021-12-text-only.json.gz")," used for Quickwit."),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-bash"},'wget https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2021-12.json.gz\ngunzip -c gh-archive-2021-12.json.gz | clickhouse-client -d gh-archive --query="INSERT INTO github_events FORMAT JSONEachRow"\n')),(0,i.kt)("p",null,"Let's check it's working:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-SQL"},"# Top repositories by stars\nSELECT repo_name, count() AS stars\nFROM github_events\nGROUP BY repo_name\nORDER BY stars DESC LIMIT 5\n\n\u250c\u2500repo_name\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\u252c\u2500stars\u2500\u2510\n\u2502 test-organization-kkjeer/app-test-2 \u2502 16697 \u2502\n\u2502 test-organization-kkjeer/bot-validation-2 \u2502 15326 \u2502\n\u2502 microsoft/winget-pkgs \u2502 14099 \u2502\n\u2502 conda-forge/releases \u2502 13332 \u2502\n\u2502 NixOS/nixpkgs \u2502 12860 \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\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n")),(0,i.kt)("h3",{id:"use-quickwit-search-inside-clickhouse"},"Use Quickwit search inside ClickHouse"),(0,i.kt)("p",null,"ClickHouse has an exciting feature called ",(0,i.kt)("a",{parentName:"p",href:"https://clickhouse.com/docs/en/engines/table-engines/special/url/"},"URL Table Engine")," that queries data from a remote HTTP/HTTPS server.\nThis is precisely what we need: by creating a table pointing to Quickwit search stream endpoint, we will fetch ids that match a query from ClickHouse."),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-SQL"},"SELECT count(*) FROM url('http://127.0.0.1:7280/api/v1/gh-archive/search/stream?query=log4j+OR+log4shell&fast_field=id&output_format=click_house_row_binary', RowBinary, 'id UInt64')\n\n\u250c\u2500count()\u2500\u2510\n\u2502 217469 \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\n1 row in set. Elapsed: 0.068 sec. Processed 217.47 thous
1and rows, 1.74 MB (3.19 million rows/s., 25.55 MB/s.)\n")),(0,i.kt)("p",null,"We are fetching 217 469 u64 ids in 0.068 seconds. That's 3.19 million rows per second, not bad. And it's possible to increase the throughput if fast field are already cached."),(0,i.kt)("p",null,"Let's do another example with a more exciting query that will match ",(0,i.kt)("inlineCode",{parentName:"p"},"log4j")," or ",(0,i.kt)("inlineCode",{parentName:"p"},"log4shell")," and count events per day:"),(0,i.kt)("pre",null,(0,i.kt)("code",{parentName:"pre",className:"language-SQL"},"SELECT\n count(*),\n toDate(fromUnixTimestamp64Milli(created_at)) AS date\nFROM github_events\nWHERE id IN (\n SELECT id\n FROM url('http://127.0.0.1:7280/api/v1/gh-archive/search/stream?query=log4j+OR+log4shell&fast_field=id&output_format=click_house_row_binary', RowBinary, 'id UInt64')\n)\nGROUP BY date\n\nQuery id: 10cb0d5a-7817-424e-8248-820fa2c425b8\n\n\u250c\u2500count()\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500date\u2500\u2510\n\u2502 96 \u2502 2021-12-01 \u2502\n\u2502 66 \u2502 2021-12-02 \u2502\n\u2502 70 \u2502 2021-12-03 \u2502\n\u2502 62 \u2502 2021-12-04 \u2502\n\u2502 67 \u2502 2021-12-05 \u2502\n\u2502 167 \u2502 2021-12-06 \u2502\n\u2502 140 \u2502 2021-12-07 \u2502\n\u2502 104 \u2502 2021-12-08 \u2502\n\u2502 157 \u2502 2021-12-09 \u2502\n\u2502 88110 \u2502 2021-12-10 \u2502\n\u2502 2937 \u2502 2021-12-11 \u2502\n\u2502 1533 \u2502 2021-12-12 \u2502\n\u2502 5935 \u2502 2021-12-13 \u2502\n\u2502 118025 \u2502 2021-12-14 \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\n14 rows in set. Elapsed: 0.124 sec. Processed 8.35 million rows, 123.10 MB (67.42 million rows/s., 993.55 MB/s.)\n\n")),(0,i.kt)("p",null,"We can see two spikes on the 2021-12-10 and 2021-12-14."),(0,i.kt)("h2",{id:"wrapping-up"},"Wrapping up"),(0,i.kt)("p",null,"We have just scratched the surface of full-text search from ClickHouse with this small subset of GitHub archive.\nYou can play with the complete dataset that you can download from our public S3 bucket.\nWe have made available monthly gzipped ndjson files from 2015 until 2021. Here are ",(0,i.kt)("inlineCode",{parentName:"p"},"2015-01")," links:"),(0,i.kt)("ul",null,(0,i.kt)("li",{parentName:"ul"},"full JSON dataset ",(0,i.kt)("a",{parentName:"li",href:"https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2015-01.json.gz"},"https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2015-01.json.gz")),(0,i.kt)("li",{parentName:"ul"},"text-only JSON dataset ",(0,i.kt)("a",{parentName:"li",href:"https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2015-01-text-only.json.gz"},"https://quickwit-datasets-public.s3.amazonaws.com/gh-archive/gh-archive-2015-01-text-only.json.gz"))),(0,i.kt)("p",null,"The search stream endpoint is powerful enough to stream 100 million ids to ClickHouse in less than 2 seconds on a multi TB dataset.\nAnd you should be comfortable playing with search stream on even bigger datasets."))}p.isMDXComponent=!0}}]);
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.