"use strict";(self.webpackChunk=self.webpackChunk||[]).push([[8036],{3905:(e,t,r)=>{r.d(t,{Zo:()=>p,kt:()=>y});var n=r(67294);function i(e,t,r){return t in e?Object.defineProperty(e,t,{value:r,enumerable:!0,configurable:!0,writable:!0}):e[t]=r,e}function a(e,t){var r=Object.keys(e);if(Object.getOwnPropertySymbols){var n=Object.getOwnPropertySymbols(e);t&&(n=n.filter((function(t){return Object.getOwnPropertyDescriptor(e,t).enumerable}))),r.push.apply(r,n)}return r}function o(e){for(var t=1;t<arguments.length;t++){var r=null!=arguments[t]?arguments[t]:{};t%2?a(Object(r),!0).forEach((function(t){i(e,t,r[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(r)):a(Object(r)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(r,t))}))}return e}function u(e,t){if(null==e)return{};var r,n,i=function(e,t){if(null==e)return{};var r,n,i={},a=Object.keys(e);for(n=0;n<a.length;n++)r=a[n],t.indexOf(r)>=0||(i[r]=e[r]);return i}(e,t);if(Object.getOwnPropertySymbols){var a=Object.getOwnPropertySymbols(e);for(n=0;n<a.length;n++)r=a[n],t.indexOf(r)>=0||Object.prototype.propertyIsEnumerable.call(e,r)&&(i[r]=e[r])}return i}var l=n.createContext({}),s=function(e){var t=n.useContext(l),r=t;return e&&(r="function"==typeof e?e(t):o(o({},t),e)),r},p=function(e){var t=s(e.components);return n.createElement(l.Provider,{value:t},e.children)},c="mdxType",d={inlineCode:"code",wrapper:function(e){var t=e.children;return n.createElement(n.Fragment,{},t)}},m=n.forwardRef((function(e,t){var r=e.components,i=e.mdxType,a=e.originalType,l=e.parentName,p=u(e,["components","mdxType","originalType","parentName"]),c=s(r),m=i,y=c["".concat(l,".").concat(m)]||c[m]||d[m]||a;return r?n.createElement(y,o(o({ref:t},p),{},{components:r})):n.createElement(y,o({ref:t},p))}));function y(e,t){var r=arguments,i=t&&t.mdxType;if("string"==typeof e||i){var a=r.length,o=new Array(a);o[0]=m;var u={};for(var l in t)hasOwnProperty.call(t,l)&&(u[l]=t[l]);u.originalType=e,u[c]="string"==typeof e?e:i,o[1]=u;for(var s=2;s<a;s++)o[s]=r[s];return n.createElement.apply(null,o)}return n.createElement.apply(null,r)}m.displayName="MDXCreateElement"},34636:(e,t,r)=>{r.r(t),r.d(t,{assets:()=>p,contentTitle:()=>l,default:()=>y,frontMatter:()=>u,metadata:()=>s,toc:()=>c});var n=r(87462),i=r(63366),a=(r(67294),r(3905)),o=["components"],u={id:"tips-good-queries",title:"Tips for writing good queries in Druid",sidebar_label:"Tips for writing good queries"},l=void 0,s={unversionedId:"querying/tips-good-queries",id:"querying/tips-good-queries",title:"Tips for writing good queries in Druid",description:"\x3c!--",source:"@site/docs/29.0.1/querying/tips-good-queries.md",sourceDirName:"querying",slug:"/querying/tips-good-queries",permalink:"/docs/29.0.1/querying/tips-good-queries",draft:!1,tags:[],version:"current",frontMatter:{id:"tips-good-queries",title:"Tips for writing good queries in Druid",sidebar_label:"Tips for writing good queries"},sidebar:"docs",previous:{title:"Overview and syntax",permalink:"/docs/29.0.1/querying/sql"},next:{title:"Query from deep storage",permalink:"/docs/29.0.1/querying/query-deep-storage"}},p={},c=[{value:"Investigate query performance",id:"investigate-query-performance",level:2},{value:"Analyze query metrics",id:"analyze-query-metrics",level:3},{value:"Generate an explain plan",id:"generate-an-explain-plan",level:3},{value:"Improve query performance",id:"improve-query-performance",level:2},{value:"Adjust Druid settings",id:"adjust-druid-settings",level:3},{value:"Turn on query caching",id:"turn-on-query-caching",level:4},{value:"Use approximation",id:"use-approximation",level:4},{value:"Manually tune your queries",id:"manually-tune-your-queries",level:3},{value:"Query one table at a time",id:"query-one-table-at-a-time",level:4},{value:"Select specific columns",id:"select-specific-columns",level:4},{value:"Use filters",id:"use-filters",level:4},{value:"Shorten your queries",id:"shorten-your-queries",level:4},{value:"Minimize or remove subqueries",id:"minimize-or-remove-subqueries",level:4},{value:"Consider alternatives to GroupBy",id:"consider-alternatives-to-groupby",level:4},{value:"Query over smaller intervals",id:"query-over-smaller-intervals",level:4},{value:"Reduce the computation in your queries",id:"reduce-the-computation-in-your-queries",level:4},{value:"Druid SQL query example",id:"druid-sql-query-example",level:2}],d={toc:c},m="wrapper";function y(e){var t=e.components,r=(0,i.Z)(e,o);return(0,a.kt)(m,(0,n.Z)({},d,r,{components:t,mdxType:"MDXLayout"}),(0,a.kt)("p",null,"This topic includes tips and examples that can help you investigate and improve query performance and accuracy using ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/sql"},"Apache Druid SQL"),"."),(0,a.kt)("p",null,"For an interactive tutorial on Druid SQL, see ",(0,a.kt)("a",{parentName:"p",href:"https://github.com/implydata/learn-druid/tree/main/notebooks"},"Learn the basics of Druid SQL")," within the ",(0,a.kt)("a",{parentName:"p",href:"https://github.com/implydata/learn-druid"},"Learn Druid repo"),"."),(0,a.kt)("p",null,"Your ability to effectively query your data depends in large part on the way you've ingested and stored the data in Apache Druid. This document assumes that you've followed the best practices described in ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/ingestion/schema-design#general-tips-and-best-practices"},"Schema design tips and best practices")," when modeling your data. "),(0,a.kt)("h2",{id:"investigate-query-performance"},"Investigate query performance"),(0,a.kt)("p",null,"If your queries run slower than anticipated, you can use the following tools to investigate query performance issues."),(0,a.kt)("h3",{id:"analyze-query-metrics"},"Analyze query metrics"),(0,a.kt)("p",null,"You can configure Druid processes to emit metrics that are essential for monitoring query execution. See ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/operations/metrics#query-metrics"},"Query metrics")," for more information. "),(0,a.kt)("h3",{id:"generate-an-explain-plan"},"Generate an explain plan"),(0,a.kt)("p",null,"An explain plan shows the full query details and all of the operations Druid performs to execute it. You can use the information in the plan to identify possible areas of query improvement."),(0,a.kt)("p",null,"See ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/sql#explain-plan"},"Explain plan")," and ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/sql-translation#interpreting-explain-plan-output"},"Interpreting explain plan output")," for more information."),(0,a.kt)("p",null,"You can follow the ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/tutorials/tutorial-sql-query-view"},"Get to know Query view tutorial")," to create an example explain plan in the Druid console."),(0,a.kt)("h2",{id:"improve-query-performance"},"Improve query performance"),(0,a.kt)("p",null,"In most cases, you can improve query performance by adjusting Druid settings and by manually tuning your queries."),(0,a.kt)("h3",{id:"adjust-druid-settings"},"Adjust Druid settings"),(0,a.kt)("p",null,"This section outlines Druid settings that can help to improve query performance."),(0,a.kt)("h4",{id:"turn-on-query-caching"},"Turn on query caching"),(0,a.kt)("p",null,"You can enable caching in Druid to improve query times for frequently accessed data. Caching enables increased concurrency on the same system, leading to noticeable performance improvements for queries handling throughput for concurrent, mixed workloads."),(0,a.kt)("p",null,"The largest performance gains from caching tend to apply to TopN and timeseries queries. For GroupBy queries, if the bottleneck is in the merging phase on the Broker, enabling caching results in little noticeable query improvement. See ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/caching#performance-considerations-for-caching"},"Performance considerations for caching")," for more information."),(0,a.kt)("h4",{id:"use-approximation"},"Use approximation"),(0,a.kt)("p",null,"When possible, design your SQL queries in such a way that they match the rules for TopN approximation, so that Druid enables TopN by default. For Druid to automatically optimize for TopN, your SQL query must include the following:"),(0,a.kt)("ul",null,(0,a.kt)("li",{parentName:"ul"},(0,a.kt)("p",{parentName:"li"},"GROUP BY on one dimension, and")),(0,a.kt)("li",{parentName:"ul"},(0,a.kt)("p",{parentName:"li"},"ORDER BY on one aggregate."),(0,a.kt)("p",{parentName:"li"},"See ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/topnquery"},"TopN queries")," for more information."))),(0,a.kt)("p",null,"Note that TopN queries are approximate in that each data process ranks its top K results and only returns those top K results to the Broker."),(0,a.kt)("p",null,"You can follow the tutorial ",(0,a.kt)("a",{parentName:"p",href:"https://github.com/implydata/learn-druid/tree/main/notebooks"},"Using TopN approximation in Druid queries")," within the ",(0,a.kt)("a",{parentName:"p",href:"https://github.com/implydata/learn-druid"},"Learn Druid repo")," to work through some examples with approximation turned on and off.\nThe tutorial ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/tutorials/tutorial-sql-query-view"},"Get to know Query view")," demonstrates running aggregate queries in the Druid console."),(0,a.kt)("h3",{id:"manually-tune-your-queries"},"Manually tune your queries"),(0,a.kt)("p",null,"This section outlines techniques you can use to improve your query accuracy and performance."),(0,a.kt)("h4",{id:"query-one-table-at-a-time"},"Query one table at a time"),(0,a.kt)("p",null,"Query a single table at a time to minimize the load on the Druid processor."),(0,a.kt)("h4",{id:"select-specific-columns"},"Select specific columns"),(0,a.kt)("p",null,"Only select the columns needed for the query instead of retrieving all columns from the table. This reduces the amount of data retrieved from the database, which improves query performance."),(0,a.kt)("h4",{id:"use-filters"},"Use filters"),(0,a.kt)("p",null,"Use filters, for example the WHERE clause, and filter on time. Try to minimize the use of inequality filters, because they're very resource-intensive."),(0,a.kt)("p",null,"The following example query filters on ",(0,a.kt)("inlineCode",{parentName:"p"},"__time")," and ",(0,a.kt)("inlineCode",{parentName:"p"},"product"),":"),(0,a.kt)("pre",null,(0,a.kt)("code",{parentName:"pre"},"SELECT\n  FLOOR(__time to day),\n  product,\n  sum(quantity * price) as revenue\nFROM \"orders\"\nWHERE\n  __time > '2023-08-20' and product = 'product 1'\nGROUP BY 1, 2\n")),(0,a.kt)("p",null,"The following example uses a wildcard filter on the ",(0,a.kt)("inlineCode",{parentName:"p"},"diffUrl")," column:"),(0,a.kt)("pre",null,(0,a.kt)("code",{parentName:"pre"},"SELECT * from Wikipedia\nWHERE diffUrl LIKE 'https://en.wikipedia%'\nAND TIME_IN_INTERVAL(__time, '2016-06-27T01:00:00/2016-06-27T02:00:00')\n")),(0,a.kt)("h4",{id:"shorten-your-queries"},"Shorten your queries"),(0,a.kt)("p",null,"Make your queries shorter where possible","\u2014","Druid processes shorter queries faster. You might also be able to divide a single query into multiple queries."),(0,a.kt)("p",null,"For example, the following query aggregates over multiple datasources using UNION ALL:"),(0,a.kt)("pre",null,(0,a.kt)("code",{parentName:"pre"},"SELECT id, SUM(revenue) FROM\n   (SELECT id, revenue from datasource_1\nUNION ALL\n  SELECT id, revenue FROM datasource_2)\n...\nUNION ALL\n   SELECT id, revenue FROM datasource_n)\nGROUP BY id\n")),(0,a.kt)("p",null,"To simplify this query, you could split it into several queries, for example:"),(0,a.kt)("pre",null,(0,a.kt)("code",{parentName:"pre"},"SELECT id, SUM(revenue) FROM datasource_1\n\nSELECT id, SUM(revenue) FROM datasource_2\n...\nSELECT id, SUM(revenue) FROM datasource_n\n")),(0,a.kt)("p",null,"You could then manually aggregate the results of the individual queries."),(0,a.kt)("h4",{id:"minimize-or-remove-subqueries"},"Minimize or remove subqueries"),(0,a.kt)("p",null,"Consider whether you can pre-compute a subquery task and store it as a join or make it a part of the datasource. See ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/datasource#join"},"Datasources: join")," and ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/sql-translation#joins"},"SQL query translation: Joins")," for more information and examples."),(0,a.kt)("h4",{id:"consider-alternatives-to-groupby"},"Consider alternatives to GroupBy"),(0,a.kt)("p",null,"Consider using Timeseries and TopN as alternatives to GroupBy. See ",(0,a.kt)("a",{parentName:"p",href:"/docs/29.0.1/querying/groupbyquery#alternatives"},"GroupBy queries: alternatives")," for more information."),(0,a.kt)("p",null,"Avoid grouping on high cardinality columns, for example user ID. Investigate whether you can apply a filter first, to reduce the number of results for grouping. "),(0,a.kt)("h4",{id:"query-over-smaller-intervals"},"Query over smaller intervals"),(0,a.kt)("p",null,"Consider whether you can query a smaller time interval to return a smaller results set."),(0,a.kt)("p",null,"For example, the following query doesn't limit on time and could be resource-intensive:"),(0,a.kt)("pre",null,(0,a.kt)("code",{parentName:"pre"},"SELECT cust_id, sum(revenue) FROM myDatasource\nGROUP BY cust_id\n")),(0,a.kt)("p",null,"This query could be split into multiple queries over smaller time spans, with the results combined client-side. For example:"),(0,a.kt)("pre",null,(0,a.kt)("code",{parentName:"pre"},"SELECT cust_id, sum(revenue) FROM myDatasource\nGROUP BY cust_id\nWHERE __time BETWEEN '2023-07-01' AND '2023-07-31'\n\nSELECT cust_id, sum(revenue) FROM myDatasource\nGROUP BY cust_id\nWHERE __time BETWEEN '2023-08-01' AND '2023-08-31'\n")),(0,a.kt)("h4",{id:"reduce-the-computation-in-your-queries"},"Reduce the computation in your queries"),(0,a.kt)("p",null,"Examine your query to see if it uses a lot of transformations, functions, and expressions. Consider whether you could rewrite the query to reduce the level of computation."),(0,a.kt)("h2",{id:"druid-sql-query-example"},"Druid SQL query example"),(0,a.kt)("p",null,"The following example query demonstrates many of the tips outlined in this topic.\nThe query:"),(0,a.kt)("ul",null,(0,a.kt)("li",{parentName:"ul"},"selects specific dimensions and metrics"),(0,a.kt)("li",{parentName:"ul"},"uses approximation"),(0,a.kt)("li",{parentName:"ul"},"selects from a single table"),(0,a.kt)("li",{parentName:"ul"},"groups by low cardinality columns"),(0,a.kt)("li",{parentName:"ul"},"filters on both dimensions and time"),(0,a.kt)("li",{parentName:"ul"},"orders by a dimension and a measure"),(0,a.kt)("li",{parentName:"ul"},"includes a limit")),(0,a.kt)("pre",null,(0,a.kt)("code",{parentName:"pre"},"SELECT\n   FLOOR() AS month,\n   country,\n   SUM(price),\n   APPROX_COUNT_DISTINCT_DS_HLL(userid)\nFROM sales\nGROUP BY month, country\nWHERE artist = 'Madonna' AND TIME_IN_INTERVAL(__time, '2023-08-01/P1M')\nORDER BY country, SUM(price) DESC\nLIMIT 100\n")))}y.isMDXComponent=!0}}]);