| "use strict";(self.webpackChunkdoris_website=self.webpackChunkdoris_website||[]).push([[14037],{15680:(e,t,a)=>{a.d(t,{xA:()=>d,yg:()=>m});var n=a(296540);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 o(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 s=n.createContext({}),p=function(e){var t=n.useContext(s),a=t;return e&&(a="function"==typeof e?e(t):i(i({},t),e)),a},d=function(e){var t=p(e.components);return n.createElement(s.Provider,{value:t},e.children)},g="mdxType",N={inlineCode:"code",wrapper:function(e){var t=e.children;return n.createElement(n.Fragment,{},t)}},y=n.forwardRef((function(e,t){var a=e.components,r=e.mdxType,l=e.originalType,s=e.parentName,d=o(e,["components","mdxType","originalType","parentName"]),g=p(a),y=r,m=g["".concat(s,".").concat(y)]||g[y]||N[y]||l;return a?n.createElement(m,i(i({ref:t},d),{},{components:a})):n.createElement(m,i({ref:t},d))}));function m(e,t){var a=arguments,r=t&&t.mdxType;if("string"==typeof e||r){var l=a.length,i=new Array(l);i[0]=y;var o={};for(var s in t)hasOwnProperty.call(t,s)&&(o[s]=t[s]);o.originalType=e,o[g]="string"==typeof e?e:r,i[1]=o;for(var p=2;p<l;p++)i[p]=a[p];return n.createElement.apply(null,i)}return n.createElement.apply(null,a)}y.displayName="MDXCreateElement"},100557:(e,t,a)=>{a.r(t),a.d(t,{assets:()=>s,contentTitle:()=>i,default:()=>N,frontMatter:()=>l,metadata:()=>o,toc:()=>p});var n=a(58168),r=(a(296540),a(15680));const l={title:"Apache Doris 1.2 star-schema-benchmark performance test report",description:"On the SSB flat wide table, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 4 times compared with Apache Doris 1.1.3, and nearly 10 times compared with Apache Doris 0.15.0 RC04. On the SQL test with standard SSB, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 2 times compared with Apache Doris 1.1.3, and nearly 31 times compared with Apache Doris 0.15.0 RC04.",date:"2022-11-22",author:"Apache Doris",tags:["Tech Sharing"],image:"/images/tech-sharing.png"},i=void 0,o={permalink:"/zh-CN/blog/ssb",source:"@site/blog/ssb.md",title:"Apache Doris 1.2 star-schema-benchmark performance test report",description:"On the SSB flat wide table, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 4 times compared with Apache Doris 1.1.3, and nearly 10 times compared with Apache Doris 0.15.0 RC04. On the SQL test with standard SSB, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 2 times compared with Apache Doris 1.1.3, and nearly 31 times compared with Apache Doris 0.15.0 RC04.",date:"2022-11-22T00:00:00.000Z",formattedDate:"2022\u5e7411\u670822\u65e5",tags:[{label:"Tech Sharing",permalink:"/zh-CN/blog/tags/tech-sharing"}],hasTruncateMarker:!1,authors:[{name:"Apache Doris"}],frontMatter:{title:"Apache Doris 1.2 star-schema-benchmark performance test report",description:"On the SSB flat wide table, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 4 times compared with Apache Doris 1.1.3, and nearly 10 times compared with Apache Doris 0.15.0 RC04. On the SQL test with standard SSB, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 2 times compared with Apache Doris 1.1.3, and nearly 31 times compared with Apache Doris 0.15.0 RC04.",date:"2022-11-22",author:"Apache Doris",tags:["Tech Sharing"],image:"/images/tech-sharing.png"},prevItem:{title:"How does Apache Doris help AISPEECH build a data warehouse in AI chatbots scenario",permalink:"/zh-CN/blog/Use-Apache-Doris-with-AI-chatbots"},nextItem:{title:"Apache Doris 1.2 TPC-H performance test report",permalink:"/zh-CN/blog/tpch"}},s={authorsImageUrls:[void 0]},p=[{value:"1. Hardware Environment",id:"1-hardware-environment",level:2},{value:"2. Software Environment",id:"2-software-environment",level:2},{value:"3. Test Data Volume",id:"3-test-data-volume",level:2},{value:"4. Test Results",id:"4-test-results",level:2},{value:"5. Standard SSB Test Results",id:"5-standard-ssb-test-results",level:2},{value:"6. Environment Preparation",id:"6-environment-preparation",level:2},{value:"7. Data Preparation",id:"7-data-preparation",level:2},{value:"7.1 Download and Install the SSB Data Generation Tool.",id:"71-download-and-install-the-ssb-data-generation-tool",level:3},{value:"7.2 Generate SSB Test Set",id:"72-generate-ssb-test-set",level:3},{value:"7.3 Create Table",id:"73-create-table",level:3},{value:"7.3.1 Prepare the <code>doris-cluster.conf</code> File.",id:"731-prepare-the-doris-clusterconf-file",level:4},{value:"7.3.2 Execute the Following Script to Generate and Create the SSB Table:",id:"732-execute-the-following-script-to-generate-and-create-the-ssb-table",level:4},{value:"7.4 Import data",id:"74-import-data",level:3},{value:"7.5 Checking Imported data",id:"75-checking-imported-data",level:3},{value:"7.6 Query Test",id:"76-query-test",level:3},{value:"7.6.1 SSB FLAT Test for SQL",id:"761-ssb-flat-test-for-sql",level:4},{value:"7.6.2 SSB Standard Test for SQL",id:"762-ssb-standard-test-for-sql",level:4}],d={toc:p},g="wrapper";function N(e){let{components:t,...l}=e;return(0,r.yg)(g,(0,n.A)({},d,l,{components:t,mdxType:"MDXLayout"}),(0,r.yg)("h1",{id:"star-schema-benchmark"},"Star Schema Benchmark"),(0,r.yg)("p",null,(0,r.yg)("a",{parentName:"p",href:"https://www.cs.umb.edu/~poneil/StarSchemaB.PDF"},"Star Schema Benchmark(SSB)")," is a lightweight performance test set in the data warehouse scenario. SSB provides a simplified star schema data based on ",(0,r.yg)("a",{parentName:"p",href:"http://www.tpc.org/tpch/"},"TPC-H"),", which is mainly used to test the performance of multi-table JOIN query under star schema. In addition, the industry usually flattens SSB into a wide table model (Referred as: SSB flat) to test the performance of the query engine, refer to ",(0,r.yg)("a",{parentName:"p",href:"https://clickhouse.com/docs/zh/getting-started"},"Clickhouse"),"."),(0,r.yg)("p",null,"This document mainly introduces the performance of Doris on the SSB 100G test set."),(0,r.yg)("blockquote",null,(0,r.yg)("p",{parentName:"blockquote"},"Note 1: The standard test set including SSB usually has a large gap with the actual business scenario, and some tests will perform parameter tuning for the test set. Therefore, the test results of the standard test set can only reflect the performance of the database in a specific scenario. It is recommended that users use actual business data for further testing."),(0,r.yg)("p",{parentName:"blockquote"},"Note 2: The operations involved in this document are all performed in the Ubuntu Server 20.04 environment, and CentOS 7 as well.")),(0,r.yg)("p",null,"With 13 queries on the SSB standard test data set, we conducted a comparison test based on Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0 RC04 versions."),(0,r.yg)("p",null,"On the SSB flat wide table, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 4 times compared with Apache Doris 1.1.3, and nearly 10 times compared with Apache Doris 0.15.0 RC04."),(0,r.yg)("p",null,"On the SQL test with standard SSB, the overall performance of Apache Doris 1.2.0-rc01 has been improved by nearly 2 times compared with Apache Doris 1.1.3, and nearly 31 times compared with Apache Doris 0.15.0 RC04."),(0,r.yg)("h2",{id:"1-hardware-environment"},"1. Hardware Environment"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Number of machines"),(0,r.yg)("th",{parentName:"tr",align:null},"4 Tencent Cloud Hosts (1 FE, 3 BEs)"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"CPU"),(0,r.yg)("td",{parentName:"tr",align:null},"AMD EPYC\u2122 Milan (2.55GHz/3.5GHz) 16 Cores")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Memory"),(0,r.yg)("td",{parentName:"tr",align:null},"64G")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Network Bandwidth"),(0,r.yg)("td",{parentName:"tr",align:null},"7Gbps")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Disk"),(0,r.yg)("td",{parentName:"tr",align:null},"High-performance Cloud Disk")))),(0,r.yg)("h2",{id:"2-software-environment"},"2. Software Environment"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"Doris deployed 3BEs and 1FE;"),(0,r.yg)("li",{parentName:"ul"},"Kernel version: Linux version 5.4.0-96-generic (buildd@lgw01-amd64-051)"),(0,r.yg)("li",{parentName:"ul"},"OS version: Ubuntu Server 20.04 LTS 64-bit"),(0,r.yg)("li",{parentName:"ul"},"Doris software versions: Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0 RC04"),(0,r.yg)("li",{parentName:"ul"},'JDK: openjdk version "11.0.14" 2022-01-18')),(0,r.yg)("h2",{id:"3-test-data-volume"},"3. Test Data Volume"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:"left"},"SSB Table Name"),(0,r.yg)("th",{parentName:"tr",align:"left"},"Rows"),(0,r.yg)("th",{parentName:"tr",align:"left"},"Annotation"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:"left"},"lineorder"),(0,r.yg)("td",{parentName:"tr",align:"left"},"600,037,902"),(0,r.yg)("td",{parentName:"tr",align:"left"},"Commodity Order Details")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:"left"},"customer"),(0,r.yg)("td",{parentName:"tr",align:"left"},"3,000,000"),(0,r.yg)("td",{parentName:"tr",align:"left"},"Customer Information")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:"left"},"part"),(0,r.yg)("td",{parentName:"tr",align:"left"},"1,400,000"),(0,r.yg)("td",{parentName:"tr",align:"left"},"Parts Information")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:"left"},"supplier"),(0,r.yg)("td",{parentName:"tr",align:"left"},"200,000"),(0,r.yg)("td",{parentName:"tr",align:"left"},"Supplier Information")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:"left"},"date"),(0,r.yg)("td",{parentName:"tr",align:"left"},"2,556"),(0,r.yg)("td",{parentName:"tr",align:"left"},"Date")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:"left"},"lineorder_flat"),(0,r.yg)("td",{parentName:"tr",align:"left"},"600,037,902"),(0,r.yg)("td",{parentName:"tr",align:"left"},"Wide Table after Data Flattening")))),(0,r.yg)("h2",{id:"4-test-results"},"4. Test Results"),(0,r.yg)("p",null,"We use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0 RC04 for comparative testing. The test results are as follows:"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Query"),(0,r.yg)("th",{parentName:"tr",align:null},"Apache Doris 1.2.0-rc01(ms)"),(0,r.yg)("th",{parentName:"tr",align:null},"Apache Doris 1.1.3 (ms)"),(0,r.yg)("th",{parentName:"tr",align:null},"Doris 0.15.0 RC04 (ms)"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q1.1"),(0,r.yg)("td",{parentName:"tr",align:null},"20"),(0,r.yg)("td",{parentName:"tr",align:null},"90"),(0,r.yg)("td",{parentName:"tr",align:null},"250")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q1.2"),(0,r.yg)("td",{parentName:"tr",align:null},"10"),(0,r.yg)("td",{parentName:"tr",align:null},"10"),(0,r.yg)("td",{parentName:"tr",align:null},"30")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q1.3"),(0,r.yg)("td",{parentName:"tr",align:null},"30"),(0,r.yg)("td",{parentName:"tr",align:null},"70"),(0,r.yg)("td",{parentName:"tr",align:null},"120")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q2.1"),(0,r.yg)("td",{parentName:"tr",align:null},"90"),(0,r.yg)("td",{parentName:"tr",align:null},"360"),(0,r.yg)("td",{parentName:"tr",align:null},"900")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q2.2"),(0,r.yg)("td",{parentName:"tr",align:null},"90"),(0,r.yg)("td",{parentName:"tr",align:null},"340"),(0,r.yg)("td",{parentName:"tr",align:null},"1,020")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q2.3"),(0,r.yg)("td",{parentName:"tr",align:null},"60"),(0,r.yg)("td",{parentName:"tr",align:null},"260"),(0,r.yg)("td",{parentName:"tr",align:null},"770")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q3.1"),(0,r.yg)("td",{parentName:"tr",align:null},"160"),(0,r.yg)("td",{parentName:"tr",align:null},"550"),(0,r.yg)("td",{parentName:"tr",align:null},"1,710")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q3.2"),(0,r.yg)("td",{parentName:"tr",align:null},"80"),(0,r.yg)("td",{parentName:"tr",align:null},"290"),(0,r.yg)("td",{parentName:"tr",align:null},"670")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q3.3"),(0,r.yg)("td",{parentName:"tr",align:null},"90"),(0,r.yg)("td",{parentName:"tr",align:null},"240"),(0,r.yg)("td",{parentName:"tr",align:null},"550")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q3.4"),(0,r.yg)("td",{parentName:"tr",align:null},"20"),(0,r.yg)("td",{parentName:"tr",align:null},"20"),(0,r.yg)("td",{parentName:"tr",align:null},"30")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q4.1"),(0,r.yg)("td",{parentName:"tr",align:null},"140"),(0,r.yg)("td",{parentName:"tr",align:null},"480"),(0,r.yg)("td",{parentName:"tr",align:null},"1,250")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q4.2"),(0,r.yg)("td",{parentName:"tr",align:null},"50"),(0,r.yg)("td",{parentName:"tr",align:null},"240"),(0,r.yg)("td",{parentName:"tr",align:null},"400")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q4.3"),(0,r.yg)("td",{parentName:"tr",align:null},"30"),(0,r.yg)("td",{parentName:"tr",align:null},"200"),(0,r.yg)("td",{parentName:"tr",align:null},"330")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Total"),(0,r.yg)("td",{parentName:"tr",align:null},"880"),(0,r.yg)("td",{parentName:"tr",align:null},"3,150"),(0,r.yg)("td",{parentName:"tr",align:null},"8,030")))),(0,r.yg)("p",null,(0,r.yg)("img",{alt:"ssb_v11_v015_compare",src:a(800846).A,width:"1522",height:"674"})),(0,r.yg)("p",null,(0,r.yg)("strong",{parentName:"p"},"Interpretation of Results")),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"The data set corresponding to the test results is scale 100, about 600 million."),(0,r.yg)("li",{parentName:"ul"},"The test environment is configured as the user's common configuration, with 4 cloud servers, 16-core 64G SSD, and 1 FE, 3 BEs deployment."),(0,r.yg)("li",{parentName:"ul"},"We select the user's common configuration test to reduce the cost of user selection and evaluation, but the entire test process will not consume so many hardware resources.")),(0,r.yg)("h2",{id:"5-standard-ssb-test-results"},"5. Standard SSB Test Results"),(0,r.yg)("p",null,"Here we use Apache Doris 1.2.0-rc01, Apache Doris 1.1.3 and Apache Doris 0.15.0 RC04 for comparative testing. In the test, we use Query Time\uff08ms\uff09 as the main performance indicator. The test results are as follows:"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Query"),(0,r.yg)("th",{parentName:"tr",align:null},"Apache Doris 1.2.0-rc01 (ms)"),(0,r.yg)("th",{parentName:"tr",align:null},"Apache Doris 1.1.3 (ms)"),(0,r.yg)("th",{parentName:"tr",align:null},"Doris 0.15.0 RC04 (ms)"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q1.1"),(0,r.yg)("td",{parentName:"tr",align:null},"40"),(0,r.yg)("td",{parentName:"tr",align:null},"18"),(0,r.yg)("td",{parentName:"tr",align:null},"350")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q1.2"),(0,r.yg)("td",{parentName:"tr",align:null},"30"),(0,r.yg)("td",{parentName:"tr",align:null},"100"),(0,r.yg)("td",{parentName:"tr",align:null},"80")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q1.3"),(0,r.yg)("td",{parentName:"tr",align:null},"20"),(0,r.yg)("td",{parentName:"tr",align:null},"70"),(0,r.yg)("td",{parentName:"tr",align:null},"80")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q2.1"),(0,r.yg)("td",{parentName:"tr",align:null},"350"),(0,r.yg)("td",{parentName:"tr",align:null},"940"),(0,r.yg)("td",{parentName:"tr",align:null},"20,680")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q2.2"),(0,r.yg)("td",{parentName:"tr",align:null},"320"),(0,r.yg)("td",{parentName:"tr",align:null},"750"),(0,r.yg)("td",{parentName:"tr",align:null},"18,250")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q2.3"),(0,r.yg)("td",{parentName:"tr",align:null},"300"),(0,r.yg)("td",{parentName:"tr",align:null},"720"),(0,r.yg)("td",{parentName:"tr",align:null},"14,760")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q3.1"),(0,r.yg)("td",{parentName:"tr",align:null},"650"),(0,r.yg)("td",{parentName:"tr",align:null},"2,150"),(0,r.yg)("td",{parentName:"tr",align:null},"22,190")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q3.2"),(0,r.yg)("td",{parentName:"tr",align:null},"260"),(0,r.yg)("td",{parentName:"tr",align:null},"510"),(0,r.yg)("td",{parentName:"tr",align:null},"8,360")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q3.3"),(0,r.yg)("td",{parentName:"tr",align:null},"220"),(0,r.yg)("td",{parentName:"tr",align:null},"450"),(0,r.yg)("td",{parentName:"tr",align:null},"6,200")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q3.4"),(0,r.yg)("td",{parentName:"tr",align:null},"60"),(0,r.yg)("td",{parentName:"tr",align:null},"70"),(0,r.yg)("td",{parentName:"tr",align:null},"160")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q4.1"),(0,r.yg)("td",{parentName:"tr",align:null},"840"),(0,r.yg)("td",{parentName:"tr",align:null},"1,480"),(0,r.yg)("td",{parentName:"tr",align:null},"24,320")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q4.2"),(0,r.yg)("td",{parentName:"tr",align:null},"460"),(0,r.yg)("td",{parentName:"tr",align:null},"560"),(0,r.yg)("td",{parentName:"tr",align:null},"6,310")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Q4.3"),(0,r.yg)("td",{parentName:"tr",align:null},"610"),(0,r.yg)("td",{parentName:"tr",align:null},"660"),(0,r.yg)("td",{parentName:"tr",align:null},"10,170")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"Total"),(0,r.yg)("td",{parentName:"tr",align:null},"4,160"),(0,r.yg)("td",{parentName:"tr",align:null},"8,478"),(0,r.yg)("td",{parentName:"tr",align:null},"131,910")))),(0,r.yg)("p",null,(0,r.yg)("img",{alt:"ssb_12_11_015",src:a(373334).A,width:"1354",height:"728"})),(0,r.yg)("p",null,(0,r.yg)("strong",{parentName:"p"},"Interpretation of Results")),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"The data set corresponding to the test results is scale 100, about 600 million."),(0,r.yg)("li",{parentName:"ul"},"The test environment is configured as the user's common configuration, with 4 cloud servers, 16-core 64G SSD, and 1 FE 3 BEs deployment."),(0,r.yg)("li",{parentName:"ul"},"We select the user's common configuration test to reduce the cost of user selection and evaluation, but the entire test process will not consume so many hardware resources.")),(0,r.yg)("h2",{id:"6-environment-preparation"},"6. Environment Preparation"),(0,r.yg)("p",null,"Please first refer to the ","[official documentation]","(. /install/install-deploy.md) to install and deploy Apache Doris first to obtain a Doris cluster which is working well(including at least 1 FE 1 BE, 1 FE 3 BEs is recommended)."),(0,r.yg)("p",null,"The scripts mentioned in the following documents are stored in the Apache Doris codebase: ",(0,r.yg)("a",{parentName:"p",href:"https://github.com/apache/doris/tree/master/tools/ssb-tools"},"ssb-tools")),(0,r.yg)("h2",{id:"7-data-preparation"},"7. Data Preparation"),(0,r.yg)("h3",{id:"71-download-and-install-the-ssb-data-generation-tool"},"7.1 Download and Install the SSB Data Generation Tool."),(0,r.yg)("p",null,"Execute the following script to download and compile the ",(0,r.yg)("a",{parentName:"p",href:"https://github.com/electrum/ssb-dbgen.git"},"ssb-dbgen")," tool."),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-shell"},"sh build-ssb-dbgen.sh\n")),(0,r.yg)("p",null,"After successful installation, the ",(0,r.yg)("inlineCode",{parentName:"p"},"dbgen")," binary will be generated under the ",(0,r.yg)("inlineCode",{parentName:"p"},"ssb-dbgen/")," directory."),(0,r.yg)("h3",{id:"72-generate-ssb-test-set"},"7.2 Generate SSB Test Set"),(0,r.yg)("p",null,"Execute the following script to generate the SSB dataset:"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-shell"},"sh gen-ssb-data.sh -s 100 -c 100\n")),(0,r.yg)("blockquote",null,(0,r.yg)("p",{parentName:"blockquote"},"Note 1: Check the script help via ",(0,r.yg)("inlineCode",{parentName:"p"},"sh gen-ssb-data.sh -h"),"."),(0,r.yg)("p",{parentName:"blockquote"},"Note 2: The data will be generated under the ",(0,r.yg)("inlineCode",{parentName:"p"},"ssb-data/")," directory with the suffix ",(0,r.yg)("inlineCode",{parentName:"p"},".tbl"),". The total file size is about 60GB and may need a few minutes to an hour to generate."),(0,r.yg)("p",{parentName:"blockquote"},"Note 3: ",(0,r.yg)("inlineCode",{parentName:"p"},"-s 100")," indicates that the test set size factor is 100, ",(0,r.yg)("inlineCode",{parentName:"p"},"-c 100")," indicates that 100 concurrent threads generate the data of the lineorder table. The ",(0,r.yg)("inlineCode",{parentName:"p"},"-c")," parameter also determines the number of files in the final lineorder table. The larger the parameter, the larger the number of files and the smaller each file.")),(0,r.yg)("p",null,"With the ",(0,r.yg)("inlineCode",{parentName:"p"},"-s 100")," parameter, the resulting dataset size is:"),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Table"),(0,r.yg)("th",{parentName:"tr",align:null},"Rows"),(0,r.yg)("th",{parentName:"tr",align:null},"Size"),(0,r.yg)("th",{parentName:"tr",align:null},"File Number"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"lineorder"),(0,r.yg)("td",{parentName:"tr",align:null},"600,037,902"),(0,r.yg)("td",{parentName:"tr",align:null},"60GB"),(0,r.yg)("td",{parentName:"tr",align:null},"100")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"customer"),(0,r.yg)("td",{parentName:"tr",align:null},"3,000,000"),(0,r.yg)("td",{parentName:"tr",align:null},"277M"),(0,r.yg)("td",{parentName:"tr",align:null},"1")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"part"),(0,r.yg)("td",{parentName:"tr",align:null},"1,400,000"),(0,r.yg)("td",{parentName:"tr",align:null},"116M"),(0,r.yg)("td",{parentName:"tr",align:null},"1")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"supplier"),(0,r.yg)("td",{parentName:"tr",align:null},"200,000"),(0,r.yg)("td",{parentName:"tr",align:null},"17M"),(0,r.yg)("td",{parentName:"tr",align:null},"1")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"date"),(0,r.yg)("td",{parentName:"tr",align:null},"2,556"),(0,r.yg)("td",{parentName:"tr",align:null},"228K"),(0,r.yg)("td",{parentName:"tr",align:null},"1")))),(0,r.yg)("h3",{id:"73-create-table"},"7.3 Create Table"),(0,r.yg)("h4",{id:"731-prepare-the-doris-clusterconf-file"},"7.3.1 Prepare the ",(0,r.yg)("inlineCode",{parentName:"h4"},"doris-cluster.conf")," File."),(0,r.yg)("p",null,"Before import the script, you need to write the FE\u2019s ip port and other information in the ",(0,r.yg)("inlineCode",{parentName:"p"},"doris-cluster.conf")," file."),(0,r.yg)("p",null,"The file location is at the same level as ",(0,r.yg)("inlineCode",{parentName:"p"},"load-ssb-dimension-data.sh"),"."),(0,r.yg)("p",null,"The content of the file includes FE's ip, HTTP port, user name, password and the DB name of the data to be imported:"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-shell"},'export FE_HOST="xxx"\nexport FE_HTTP_PORT="8030"\nexport FE_QUERY_PORT="9030"\nexport USER="root"\nexport PASSWORD=\'xxx\'\nexport DB="ssb"\n')),(0,r.yg)("h4",{id:"732-execute-the-following-script-to-generate-and-create-the-ssb-table"},"7.3.2 Execute the Following Script to Generate and Create the SSB Table:"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-shell"},"sh create-ssb-tables.sh\n")),(0,r.yg)("p",null,"Or copy the table creation statements in ",(0,r.yg)("a",{parentName:"p",href:"https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-tables.sql"},"create-ssb-tables.sql")," and ",(0,r.yg)("a",{parentName:"p",href:"https://github.com/apache/incubator-doris/tree/master/tools/ssb-tools/ddl/create-ssb-flat-table.sql"}," create-ssb-flat-table.sql")," and then execute them in the MySQL client."),(0,r.yg)("p",null,"The following is the ",(0,r.yg)("inlineCode",{parentName:"p"},"lineorder_flat")," table build statement. Create the ",(0,r.yg)("inlineCode",{parentName:"p"},"lineorder_flat")," table in the above ",(0,r.yg)("inlineCode",{parentName:"p"},"create-ssb-flat-table.sh")," script, and perform the default number of buckets (48 buckets). You can delete this table and adjust the number of buckets according to your cluster scale node configuration, so as to obtain a better test result."),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},'CREATE TABLE `lineorder_flat` (\n `LO_ORDERDATE` date NOT NULL COMMENT "",\n `LO_ORDERKEY` int(11) NOT NULL COMMENT "",\n `LO_LINENUMBER` tinyint(4) NOT NULL COMMENT "",\n `LO_CUSTKEY` int(11) NOT NULL COMMENT "",\n `LO_PARTKEY` int(11) NOT NULL COMMENT "",\n `LO_SUPPKEY` int(11) NOT NULL COMMENT "",\n `LO_ORDERPRIORITY` varchar(100) NOT NULL COMMENT "",\n `LO_SHIPPRIORITY` tinyint(4) NOT NULL COMMENT "",\n `LO_QUANTITY` tinyint(4) NOT NULL COMMENT "",\n `LO_EXTENDEDPRICE` int(11) NOT NULL COMMENT "",\n `LO_ORDTOTALPRICE` int(11) NOT NULL COMMENT "",\n `LO_DISCOUNT` tinyint(4) NOT NULL COMMENT "",\n `LO_REVENUE` int(11) NOT NULL COMMENT "",\n `LO_SUPPLYCOST` int(11) NOT NULL COMMENT "",\n `LO_TAX` tinyint(4) NOT NULL COMMENT "",\n `LO_COMMITDATE` date NOT NULL COMMENT "",\n `LO_SHIPMODE` varchar(100) NOT NULL COMMENT "",\n `C_NAME` varchar(100) NOT NULL COMMENT "",\n `C_ADDRESS` varchar(100) NOT NULL COMMENT "",\n `C_CITY` varchar(100) NOT NULL COMMENT "",\n `C_NATION` varchar(100) NOT NULL COMMENT "",\n `C_REGION` varchar(100) NOT NULL COMMENT "",\n `C_PHONE` varchar(100) NOT NULL COMMENT "",\n `C_MKTSEGMENT` varchar(100) NOT NULL COMMENT "",\n `S_NAME` varchar(100) NOT NULL COMMENT "",\n `S_ADDRESS` varchar(100) NOT NULL COMMENT "",\n `S_CITY` varchar(100) NOT NULL COMMENT "",\n `S_NATION` varchar(100) NOT NULL COMMENT "",\n `S_REGION` varchar(100) NOT NULL COMMENT "",\n `S_PHONE` varchar(100) NOT NULL COMMENT "",\n `P_NAME` varchar(100) NOT NULL COMMENT "",\n `P_MFGR` varchar(100) NOT NULL COMMENT "",\n `P_CATEGORY` varchar(100) NOT NULL COMMENT "",\n `P_BRAND` varchar(100) NOT NULL COMMENT "",\n `P_COLOR` varchar(100) NOT NULL COMMENT "",\n `P_TYPE` varchar(100) NOT NULL COMMENT "",\n `P_SIZE` tinyint(4) NOT NULL COMMENT "",\n `P_CONTAINER` varchar(100) NOT NULL COMMENT ""\n) ENGINE=OLAP\nDUPLICATE KEY(`LO_ORDERDATE`, `LO_ORDERKEY`)\nCOMMENT "OLAP"\nPARTITION BY RANGE(`LO_ORDERDATE`)\n(PARTITION p1 VALUES [(\'0000-01-01\'), (\'1993-01-01\')),\nPARTITION p2 VALUES [(\'1993-01-01\'), (\'1994-01-01\')),\nPARTITION p3 VALUES [(\'1994-01-01\'), (\'1995-01-01\')),\nPARTITION p4 VALUES [(\'1995-01-01\'), (\'1996-01-01\')),\nPARTITION p5 VALUES [(\'1996-01-01\'), (\'1997-01-01\')),\nPARTITION p6 VALUES [(\'1997-01-01\'), (\'1998-01-01\')),\nPARTITION p7 VALUES [(\'1998-01-01\'), (\'1999-01-01\')))\nDISTRIBUTED BY HASH(`LO_ORDERKEY`) BUCKETS 48\nPROPERTIES (\n"replication_num" = "1",\n"colocate_with" = "groupxx1",\n"in_memory" = "false",\n"storage_format" = "DEFAULT"\n);\n')),(0,r.yg)("h3",{id:"74-import-data"},"7.4 Import data"),(0,r.yg)("p",null,"We use the following command to complete all data import of SSB test set and SSB FLAT wide table data synthesis and then import into the table."),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-shell"}," sh bin/load-ssb-data.sh -c 10\n")),(0,r.yg)("p",null,(0,r.yg)("inlineCode",{parentName:"p"},"-c 5")," means start 10 concurrent threads to import (5 by default). In the case of a single BE node, the lineorder data generated by ",(0,r.yg)("inlineCode",{parentName:"p"},"sh gen-ssb-data.sh -s 100 -c 100")," will also generate the data of the ssb-flat table in the end. If more threads are enabled, the import speed can be accelerated. But it will cost extra memory."),(0,r.yg)("blockquote",null,(0,r.yg)("p",{parentName:"blockquote"},"Notes."),(0,r.yg)("ol",{parentName:"blockquote"},(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("p",{parentName:"li"},"To get faster import speed, you can add ",(0,r.yg)("inlineCode",{parentName:"p"},"flush_thread_num_per_store=5")," in be.conf and then restart BE. This configuration indicates the number of disk writing threads for each data directory, 2 by default. Larger data can improve write data throughput, but may increase IO Util. (Reference value: 1 mechanical disk, with 2 by default, the IO Util during the import process is about 12%. When it is set to 5, the IO Util is about 26%. If it is an SSD disk, it is almost 0%) .")),(0,r.yg)("li",{parentName:"ol"},(0,r.yg)("p",{parentName:"li"},"The flat table data is imported by 'INSERT INTO ... SELECT ... '.")))),(0,r.yg)("h3",{id:"75-checking-imported-data"},"7.5 Checking Imported data"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},"select count(*) from part;\nselect count(*) from customer;\nselect count(*) from supplier;\nselect count(*) from date;\nselect count(*) from lineorder;\nselect count(*) from lineorder_flat;\n")),(0,r.yg)("p",null,"The amount of data should be consistent with the number of rows of generated data."),(0,r.yg)("table",null,(0,r.yg)("thead",{parentName:"table"},(0,r.yg)("tr",{parentName:"thead"},(0,r.yg)("th",{parentName:"tr",align:null},"Table"),(0,r.yg)("th",{parentName:"tr",align:null},"Rows"),(0,r.yg)("th",{parentName:"tr",align:null},"Origin Size"),(0,r.yg)("th",{parentName:"tr",align:null},"Compacted Size(1 Replica)"))),(0,r.yg)("tbody",{parentName:"table"},(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"lineorder_flat"),(0,r.yg)("td",{parentName:"tr",align:null},"600,037,902"),(0,r.yg)("td",{parentName:"tr",align:null}),(0,r.yg)("td",{parentName:"tr",align:null},"59.709 GB")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"lineorder"),(0,r.yg)("td",{parentName:"tr",align:null},"600,037,902"),(0,r.yg)("td",{parentName:"tr",align:null},"60 GB"),(0,r.yg)("td",{parentName:"tr",align:null},"14.514 GB")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"customer"),(0,r.yg)("td",{parentName:"tr",align:null},"3,000,000"),(0,r.yg)("td",{parentName:"tr",align:null},"277 MB"),(0,r.yg)("td",{parentName:"tr",align:null},"138.247 MB")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"part"),(0,r.yg)("td",{parentName:"tr",align:null},"1,400,000"),(0,r.yg)("td",{parentName:"tr",align:null},"116 MB"),(0,r.yg)("td",{parentName:"tr",align:null},"12.759 MB")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"supplier"),(0,r.yg)("td",{parentName:"tr",align:null},"200,000"),(0,r.yg)("td",{parentName:"tr",align:null},"17 MB"),(0,r.yg)("td",{parentName:"tr",align:null},"9.143 MB")),(0,r.yg)("tr",{parentName:"tbody"},(0,r.yg)("td",{parentName:"tr",align:null},"date"),(0,r.yg)("td",{parentName:"tr",align:null},"2,556"),(0,r.yg)("td",{parentName:"tr",align:null},"228 KB"),(0,r.yg)("td",{parentName:"tr",align:null},"34.276 KB")))),(0,r.yg)("h3",{id:"76-query-test"},"7.6 Query Test"),(0,r.yg)("ul",null,(0,r.yg)("li",{parentName:"ul"},"SSB-Flat Query Statement: ",(0,r.yg)("a",{parentName:"li",href:"https://github.com/apache/doris/tree/master/tools/ssb-tools/ssb-flat-queries"}," ssb-flat-queries")),(0,r.yg)("li",{parentName:"ul"},"Standard SSB Queries: ",(0,r.yg)("a",{parentName:"li",href:"https://github.com/apache/doris/tree/master/tools/ssb-tools/ssb-queries"}," ssb-queries"))),(0,r.yg)("h4",{id:"761-ssb-flat-test-for-sql"},"7.6.1 SSB FLAT Test for SQL"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-sql"},"--Q1.1\nSELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue\nFROM lineorder_flat\nWHERE LO_ORDERDATE >= 19930101 AND LO_ORDERDATE <= 19931231 AND LO_DISCOUNT BETWEEN 1 AND 3 AND LO_QUANTITY < 25;\n--Q1.2\nSELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue\nFROM lineorder_flat\nWHERE LO_ORDERDATE >= 19940101 AND LO_ORDERDATE <= 19940131 AND LO_DISCOUNT BETWEEN 4 AND 6 AND LO_QUANTITY BETWEEN 26 AND 35;\n\n--Q1.3\nSELECT SUM(LO_EXTENDEDPRICE * LO_DISCOUNT) AS revenue\nFROM lineorder_flat\nWHERE weekofyear(LO_ORDERDATE) = 6 AND LO_ORDERDATE >= 19940101 AND LO_ORDERDATE <= 19941231 AND LO_DISCOUNT BETWEEN 5 AND 7 AND LO_QUANTITY BETWEEN 26 AND 35;\n\n--Q2.1\nSELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND\nFROM lineorder_flat WHERE P_CATEGORY = 'MFGR#12' AND S_REGION = 'AMERICA'\nGROUP BY YEAR, P_BRAND\nORDER BY YEAR, P_BRAND;\n\n--Q2.2\nSELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND\nFROM lineorder_flat\nWHERE P_BRAND >= 'MFGR#2221' AND P_BRAND <= 'MFGR#2228' AND S_REGION = 'ASIA'\nGROUP BY YEAR, P_BRAND\nORDER BY YEAR, P_BRAND;\n\n--Q2.3\nSELECT SUM(LO_REVENUE), (LO_ORDERDATE DIV 10000) AS YEAR, P_BRAND\nFROM lineorder_flat\nWHERE P_BRAND = 'MFGR#2239' AND S_REGION = 'EUROPE'\nGROUP BY YEAR, P_BRAND\nORDER BY YEAR, P_BRAND;\n\n--Q3.1\nSELECT C_NATION, S_NATION, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS revenue\nFROM lineorder_flat\nWHERE C_REGION = 'ASIA' AND S_REGION = 'ASIA' AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231\nGROUP BY C_NATION, S_NATION, YEAR\nORDER BY YEAR ASC, revenue DESC;\n\n--Q3.2\nSELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS revenue\nFROM lineorder_flat\nWHERE C_NATION = 'UNITED STATES' AND S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231\nGROUP BY C_CITY, S_CITY, YEAR\nORDER BY YEAR ASC, revenue DESC;\n\n--Q3.3\nSELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS revenue\nFROM lineorder_flat\nWHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') AND S_CITY IN ('UNITED KI1', 'UNITED KI5') AND LO_ORDERDATE >= 19920101 AND LO_ORDERDATE <= 19971231\nGROUP BY C_CITY, S_CITY, YEAR\nORDER BY YEAR ASC, revenue DESC;\n\n--Q3.4\nSELECT C_CITY, S_CITY, (LO_ORDERDATE DIV 10000) AS YEAR, SUM(LO_REVENUE) AS revenue\nFROM lineorder_flat\nWHERE C_CITY IN ('UNITED KI1', 'UNITED KI5') AND S_CITY IN ('UNITED KI1', 'UNITED KI5') AND LO_ORDERDATE >= 19971201 AND LO_ORDERDATE <= 19971231\nGROUP BY C_CITY, S_CITY, YEAR\nORDER BY YEAR ASC, revenue DESC;\n\n--Q4.1\nSELECT (LO_ORDERDATE DIV 10000) AS YEAR, C_NATION, SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit\nFROM lineorder_flat\nWHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND P_MFGR IN ('MFGR#1', 'MFGR#2')\nGROUP BY YEAR, C_NATION\nORDER BY YEAR ASC, C_NATION ASC;\n\n--Q4.2\nSELECT (LO_ORDERDATE DIV 10000) AS YEAR,S_NATION, P_CATEGORY, SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit\nFROM lineorder_flat\nWHERE C_REGION = 'AMERICA' AND S_REGION = 'AMERICA' AND LO_ORDERDATE >= 19970101 AND LO_ORDERDATE <= 19981231 AND P_MFGR IN ('MFGR#1', 'MFGR#2')\nGROUP BY YEAR, S_NATION, P_CATEGORY\nORDER BY YEAR ASC, S_NATION ASC, P_CATEGORY ASC;\n\n--Q4.3\nSELECT (LO_ORDERDATE DIV 10000) AS YEAR, S_CITY, P_BRAND, SUM(LO_REVENUE - LO_SUPPLYCOST) AS profit\nFROM lineorder_flat\nWHERE S_NATION = 'UNITED STATES' AND LO_ORDERDATE >= 19970101 AND LO_ORDERDATE <= 19981231 AND P_CATEGORY = 'MFGR#14'\nGROUP BY YEAR, S_CITY, P_BRAND\nORDER BY YEAR ASC, S_CITY ASC, P_BRAND ASC;\n")),(0,r.yg)("h4",{id:"762-ssb-standard-test-for-sql"},"7.6.2 SSB Standard Test for SQL"),(0,r.yg)("pre",null,(0,r.yg)("code",{parentName:"pre",className:"language-SQL"},"--Q1.1\nSELECT SUM(lo_extendedprice * lo_discount) AS REVENUE\nFROM lineorder, dates\nWHERE\n lo_orderdate = d_datekey\n AND d_year = 1993\n AND lo_discount BETWEEN 1 AND 3\n AND lo_quantity < 25;\n--Q1.2\nSELECT SUM(lo_extendedprice * lo_discount) AS REVENUE\nFROM lineorder, dates\nWHERE\n lo_orderdate = d_datekey\n AND d_yearmonth = 'Jan1994'\n AND lo_discount BETWEEN 4 AND 6\n AND lo_quantity BETWEEN 26 AND 35;\n \n--Q1.3\nSELECT\n SUM(lo_extendedprice * lo_discount) AS REVENUE\nFROM lineorder, dates\nWHERE\n lo_orderdate = d_datekey\n AND d_weeknuminyear = 6\n AND d_year = 1994\n AND lo_discount BETWEEN 5 AND 7\n AND lo_quantity BETWEEN 26 AND 35;\n \n--Q2.1\nSELECT SUM(lo_revenue), d_year, p_brand\nFROM lineorder, dates, part, supplier\nWHERE\n lo_orderdate = d_datekey\n AND lo_partkey = p_partkey\n AND lo_suppkey = s_suppkey\n AND p_category = 'MFGR#12'\n AND s_region = 'AMERICA'\nGROUP BY d_year, p_brand\nORDER BY p_brand;\n\n--Q2.2\nSELECT SUM(lo_revenue), d_year, p_brand\nFROM lineorder, dates, part, supplier\nWHERE\n lo_orderdate = d_datekey\n AND lo_partkey = p_partkey\n AND lo_suppkey = s_suppkey\n AND p_brand BETWEEN 'MFGR#2221' AND 'MFGR#2228'\n AND s_region = 'ASIA'\nGROUP BY d_year, p_brand\nORDER BY d_year, p_brand;\n\n--Q2.3\nSELECT SUM(lo_revenue), d_year, p_brand\nFROM lineorder, dates, part, supplier\nWHERE\n lo_orderdate = d_datekey\n AND lo_partkey = p_partkey\n AND lo_suppkey = s_suppkey\n AND p_brand = 'MFGR#2239'\n AND s_region = 'EUROPE'\nGROUP BY d_year, p_brand\nORDER BY d_year, p_brand;\n\n--Q3.1\nSELECT\n c_nation,\n s_nation,\n d_year,\n SUM(lo_revenue) AS REVENUE\nFROM customer, lineorder, supplier, dates\nWHERE\n lo_custkey = c_custkey\n AND lo_suppkey = s_suppkey\n AND lo_orderdate = d_datekey\n AND c_region = 'ASIA'\n AND s_region = 'ASIA'\n AND d_year >= 1992\n AND d_year <= 1997\nGROUP BY c_nation, s_nation, d_year\nORDER BY d_year ASC, REVENUE DESC;\n\n--Q3.2\nSELECT\n c_city,\n s_city,\n d_year,\n SUM(lo_revenue) AS REVENUE\nFROM customer, lineorder, supplier, dates\nWHERE\n lo_custkey = c_custkey\n AND lo_suppkey = s_suppkey\n AND lo_orderdate = d_datekey\n AND c_nation = 'UNITED STATES'\n AND s_nation = 'UNITED STATES'\n AND d_year >= 1992\n AND d_year <= 1997\nGROUP BY c_city, s_city, d_year\nORDER BY d_year ASC, REVENUE DESC;\n\n--Q3.3\nSELECT\n c_city,\n s_city,\n d_year,\n SUM(lo_revenue) AS REVENUE\nFROM customer, lineorder, supplier, dates\nWHERE\n lo_custkey = c_custkey\n AND lo_suppkey = s_suppkey\n AND lo_orderdate = d_datekey\n AND (\n c_city = 'UNITED KI1'\n OR c_city = 'UNITED KI5'\n )\n AND (\n s_city = 'UNITED KI1'\n OR s_city = 'UNITED KI5'\n )\n AND d_year >= 1992\n AND d_year <= 1997\nGROUP BY c_city, s_city, d_year\nORDER BY d_year ASC, REVENUE DESC;\n\n--Q3.4\nSELECT\n c_city,\n s_city,\n d_year,\n SUM(lo_revenue) AS REVENUE\nFROM customer, lineorder, supplier, dates\nWHERE\n lo_custkey = c_custkey\n AND lo_suppkey = s_suppkey\n AND lo_orderdate = d_datekey\n AND (\n c_city = 'UNITED KI1'\n OR c_city = 'UNITED KI5'\n )\n AND (\n s_city = 'UNITED KI1'\n OR s_city = 'UNITED KI5'\n )\n AND d_yearmonth = 'Dec1997'\nGROUP BY c_city, s_city, d_year\nORDER BY d_year ASC, REVENUE DESC;\n\n--Q4.1\nSELECT /*+SET_VAR(parallel_fragment_exec_instance_num=4, enable_vectorized_engine=true, batch_size=4096, enable_cost_based_join_reorder=true, enable_projection=true) */\n d_year,\n c_nation,\n SUM(lo_revenue - lo_supplycost) AS PROFIT\nFROM dates, customer, supplier, part, lineorder\nWHERE\n lo_custkey = c_custkey\n AND lo_suppkey = s_suppkey\n AND lo_partkey = p_partkey\n AND lo_orderdate = d_datekey\n AND c_region = 'AMERICA'\n AND s_region = 'AMERICA'\n AND (\n p_mfgr = 'MFGR#1'\n OR p_mfgr = 'MFGR#2'\n )\nGROUP BY d_year, c_nation\nORDER BY d_year, c_nation;\n\n--Q4.2\nSELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, enable_cost_based_join_reorder=true, enable_projection=true) */ \n d_year,\n s_nation,\n p_category,\n SUM(lo_revenue - lo_supplycost) AS PROFIT\nFROM dates, customer, supplier, part, lineorder\nWHERE\n lo_custkey = c_custkey\n AND lo_suppkey = s_suppkey\n AND lo_partkey = p_partkey\n AND lo_orderdate = d_datekey\n AND c_region = 'AMERICA'\n AND s_region = 'AMERICA'\n AND (\n d_year = 1997\n OR d_year = 1998\n )\n AND (\n p_mfgr = 'MFGR#1'\n OR p_mfgr = 'MFGR#2'\n )\nGROUP BY d_year, s_nation, p_category\nORDER BY d_year, s_nation, p_category;\n\n--Q4.3\nSELECT /*+SET_VAR(parallel_fragment_exec_instance_num=2, enable_vectorized_engine=true, batch_size=4096, enable_cost_based_join_reorder=true, enable_projection=true) */\n d_year,\n s_city,\n p_brand,\n SUM(lo_revenue - lo_supplycost) AS PROFIT\nFROM dates, customer, supplier, part, lineorder\nWHERE\n lo_custkey = c_custkey\n AND lo_suppkey = s_suppkey\n AND lo_partkey = p_partkey\n AND lo_orderdate = d_datekey\n AND s_nation = 'UNITED STATES'\n AND (\n d_year = 1997\n OR d_year = 1998\n )\n AND p_category = 'MFGR#14'\nGROUP BY d_year, s_city, p_brand\nORDER BY d_year, s_city, p_brand;\n")))}N.isMDXComponent=!0},373334:(e,t,a)=>{a.d(t,{A:()=>n});const n=a.p+"assets/images/ssb-6f7fc8825356019f61622f6fcb9fa1d0.png"},800846:(e,t,a)=>{a.d(t,{A:()=>n});const n=a.p+"assets/images/ssb_flat-a8cfebbc53e6f2db116876e3d53e19c7.png"}}]); |