"use strict";(self.webpackChunkdatabend=self.webpackChunkdatabend||[]).push([[27738],{3905:(e,t,a)=>{a.d(t,{Zo:()=>p,kt:()=>h});var n=a(67294);function r(e,t,a){return t in e?Object.defineProperty(e,t,{value:a,enumerable:!0,configurable:!0,writable:!0}):e[t]=a,e}function l(e,t){var a=Object.keys(e);if(Object.getOwnPropertySymbols){var n=Object.getOwnPropertySymbols(e);t&&(n=n.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),a.push.apply(a,n)}return a}function i(e){for(var t=1;t<arguments.length;t++){var a=null!=arguments[t]?arguments[t]:{};t%2?l(Object(a),!0).forEach((function(t){r(e,t,a[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(a)):l(Object(a)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(a,t))}))}return e}function s(e,t){if(null==e)return{};var a,n,r=function(e,t){if(null==e)return{};var a,n,r={},l=Object.keys(e);for(n=0;n<l.length;n++)a=l[n],t.indexOf(a)>=0||(r[a]=e[a]);return r}(e,t);if(Object.getOwnPropertySymbols){var l=Object.getOwnPropertySymbols(e);for(n=0;n<l.length;n++)a=l[n],t.indexOf(a)>=0||Object.prototype.propertyIsEnumerable.call(e,a)&&(r[a]=e[a])}return r}var d=n.createContext({}),o=function(e){var t=n.useContext(d),a=t;return e&&(a="function"==typeof e?e(t):i(i({},t),e)),a},p=function(e){var t=o(e.components);return n.createElement(d.Provider,{value:t},e.children)},u="mdxType",c={inlineCode:"code",wrapper:function(e){var t=e.children;return n.createElement(n.Fragment,{},t)}},m=n.forwardRef((function(e,t){var a=e.components,r=e.mdxType,l=e.originalType,d=e.parentName,p=s(e,["components","mdxType","originalType","parentName"]),u=o(a),m=r,h=u["".concat(d,".").concat(m)]||u[m]||c[m]||l;return a?n.createElement(h,i(i({ref:t},p),{},{components:a})):n.createElement(h,i({ref:t},p))}));function h(e,t){var a=arguments,r=t&&t.mdxType;if("string"==typeof e||r){var l=a.length,i=new Array(l);i[0]=m;var s={};for(var d in t)hasOwnProperty.call(t,d)&&(s[d]=t[d]);s.originalType=e,s[u]="string"==typeof e?e:r,i[1]=s;for(var o=2;o<l;o++)i[o]=a[o];return n.createElement.apply(null,i)}return n.createElement.apply(null,a)}m.displayName="MDXCreateElement"},83886:(e,t,a)=>{a.r(t),a.d(t,{assets:()=>d,contentTitle:()=>i,default:()=>c,frontMatter:()=>l,metadata:()=>s,toc:()=>o});var n=a(87462),r=(a(67294),a(3905));const l={title:"Analyzing Hits Dataset with Databend",sidebar_label:"Analyzing Hits Dataset",description:"Analyzing Hits Dataset with Databend"},i=void 0,s={unversionedId:"use-cases/analyze-hits-dataset-with-databend",id:"use-cases/analyze-hits-dataset-with-databend",title:"Analyzing Hits Dataset with Databend",description:"Analyzing Hits Dataset with Databend",source:"@site/../docs/doc/21-use-cases/05-analyze-hits-dataset-with-databend.md",sourceDirName:"21-use-cases",slug:"/use-cases/analyze-hits-dataset-with-databend",permalink:"/doc/use-cases/analyze-hits-dataset-with-databend",draft:!1,editUrl:"https://github.com/datafuselabs/databend/edit/main/docs/doc/21-use-cases/05-analyze-hits-dataset-with-databend.md",tags:[],version:"current",sidebarPosition:5,frontMatter:{title:"Analyzing Hits Dataset with Databend",sidebar_label:"Analyzing Hits Dataset",description:"Analyzing Hits Dataset with Databend"},sidebar:"doc",previous:{title:"Analyzing Conversion Funnel",permalink:"/doc/use-cases/analyze-funnel-with-databend"},next:{title:"Release Notes",permalink:"/doc/releases"}},d={},o=[{value:"Step 1. Deploy Databend",id:"step-1-deploy-databend",level:2},{value:"Step 2. Load hits Datasets",id:"step-2-load-hits-datasets",level:2},{value:"2.1 Create a Databend User",id:"21-create-a-databend-user",level:3},{value:"2.2 Create hits Table",id:"22-create-hits-table",level:3},{value:"2.3 Load Data Into hits Table",id:"23-load-data-into-hits-table",level:3},{value:"Step 3. Queries",id:"step-3-queries",level:2}],p={toc:o},u="wrapper";function c(e){let{components:t,...a}=e;return(0,r.kt)(u,(0,n.Z)({},p,a,{components:t,mdxType:"MDXLayout"}),(0,r.kt)("p",null,"This usecase shows how to analyze the ",(0,r.kt)("a",{parentName:"p",href:"https://clickhouse.com/docs/en/getting-started/example-datasets/metrica"},"Hits")," dataset with Databend."),(0,r.kt)("h2",{id:"step-1-deploy-databend"},"Step 1. Deploy Databend"),(0,r.kt)("p",null,"Make sure you have installed Databend, if not please see:"),(0,r.kt)("ul",null,(0,r.kt)("li",{parentName:"ul"},(0,r.kt)("a",{parentName:"li",href:"/doc/guides/#deployment"},"How to Deploy Databend"))),(0,r.kt)("h2",{id:"step-2-load-hits-datasets"},"Step 2. Load hits Datasets"),(0,r.kt)("h3",{id:"21-create-a-databend-user"},"2.1 Create a Databend User"),(0,r.kt)("p",null,"Connect to Databend server with MySQL client:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-shell"},"mysql -h127.0.0.1 -uroot -P3307 \n")),(0,r.kt)("p",null,"Create a user:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"CREATE USER user1 IDENTIFIED BY 'abc123';\n")),(0,r.kt)("p",null,"Grant privileges for the user:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"GRANT ALL ON *.* TO user1;\n")),(0,r.kt)("p",null,"See also ",(0,r.kt)("a",{parentName:"p",href:"/doc/sql-commands/ddl/user/user-create-user"},"How To Create User"),"."),(0,r.kt)("h3",{id:"22-create-hits-table"},"2.2 Create hits Table"),(0,r.kt)("p",null,(0,r.kt)("a",{parentName:"p",href:"https://github.com/datafuselabs/databend/blob/main/tests/suites/1_stateful/ddl/hits.sql"},"Create SQL")),(0,r.kt)("h3",{id:"23-load-data-into-hits-table"},"2.3 Load Data Into hits Table"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-shell"},"mysql -h127.0.0.1 -P3307 -uroot\n")),(0,r.kt)("p",null,(0,r.kt)("inlineCode",{parentName:"p"},"COPY")," data into ",(0,r.kt)("inlineCode",{parentName:"p"},"hits")," table:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-shell",metastring:"title='Load CSV files into Databend'",title:"'Load",CSV:!0,files:!0,into:!0,"Databend'":!0},"COPY INTO hits FROM 'https://repo.databend.rs/hits/hits_1m.tsv.gz' FILE_FORMAT=(type=TSV compression=AUTO);\n")),(0,r.kt)("h2",{id:"step-3-queries"},"Step 3. Queries"),(0,r.kt)("p",null,"Execute Queries:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-shell"},"mysql -h127.0.0.1 -P3307 -uroot\n")),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql"},"SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;\n")),(0,r.kt)("p",null,"Example Queries:"),(0,r.kt)("table",null,(0,r.kt)("thead",{parentName:"table"},(0,r.kt)("tr",{parentName:"thead"},(0,r.kt)("th",{parentName:"tr",align:null},"Number"),(0,r.kt)("th",{parentName:"tr",align:null},"Query"))),(0,r.kt)("tbody",{parentName:"table"},(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q1"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT COUNT(*) FROM hits;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q2"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q3"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q4"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT AVG(UserID) FROM hits;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q5"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT COUNT(DISTINCT UserID) FROM hits;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q6"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT COUNT(DISTINCT SearchPhrase) FROM hits;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q7"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT MIN(EventDate), MAX(EventDate) FROM hits;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q8"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT AdvEngineID, COUNT(*) FROM hits WHERE AdvEngineID <> 0 GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q9"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT RegionID, COUNT(DISTINCT UserID) AS u FROM hits GROUP BY RegionID ORDER BY u DESC LIMIT 10;"))),(0,r.kt)("tr",{parentName:"tbody"},(0,r.kt)("td",{parentName:"tr",align:null},"Q10"),(0,r.kt)("td",{parentName:"tr",align:null},(0,r.kt)("inlineCode",{parentName:"td"},"SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c, AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM hits GROUP BY RegionID ORDER BY c DESC LIMIT 10;"))))))}c.isMDXComponent=!0}}]);