| "use strict";(self.webpackChunk=self.webpackChunk||[]).push([[7120],{25783:(e,n,t)=>{t.d(n,{A:()=>s});const s=t.p+"assets/images/tutorial-sql-count-distinct-help-4c7ab10ef4a68ccd07adc09287bbe3e5.png"},28453:(e,n,t)=>{t.d(n,{R:()=>l,x:()=>a});var s=t(96540);const r={},i=s.createContext(r);function l(e){const n=s.useContext(i);return s.useMemo((function(){return"function"==typeof e?e(n):{...n,...e}}),[n,e])}function a(e){let n;return n=e.disableParentContext?"function"==typeof e.components?e.components(r):e.components||r:l(e.components),s.createElement(i.Provider,{value:n},e.children)}},30493:(e,n,t)=>{t.r(n),t.d(n,{assets:()=>o,contentTitle:()=>a,default:()=>u,frontMatter:()=>l,metadata:()=>s,toc:()=>d});const s=JSON.parse('{"id":"tutorials/tutorial-sql-query-view","title":"Get to know Query view","description":"\x3c!--","source":"@site/docs/latest/tutorials/tutorial-sql-query-view.md","sourceDirName":"tutorials","slug":"/tutorials/tutorial-sql-query-view","permalink":"/docs/latest/tutorials/tutorial-sql-query-view","draft":false,"unlisted":false,"tags":[],"version":"current","frontMatter":{"id":"tutorial-sql-query-view","title":"Get to know Query view","sidebar_label":"Get to know Query view"},"sidebar":"docs","previous":{"title":"Query data","permalink":"/docs/latest/tutorials/tutorial-query"},"next":{"title":"Unnesting arrays","permalink":"/docs/latest/tutorials/tutorial-unnest-arrays"}}');var r=t(74848),i=t(28453);const l={id:"tutorial-sql-query-view",title:"Get to know Query view",sidebar_label:"Get to know Query view"},a=void 0,o={},d=[{value:"Prerequisites",id:"prerequisites",level:2},{value:"Run a demo query to ingest data",id:"run-a-demo-query-to-ingest-data",level:2},{value:"View and filter query results",id:"view-and-filter-query-results",level:2},{value:"Run aggregate queries",id:"run-aggregate-queries",level:2},{value:"Generate an explain plan",id:"generate-an-explain-plan",level:2},{value:"Try out a few more features",id:"try-out-a-few-more-features",level:2},{value:"Use calculator mode",id:"use-calculator-mode",level:3},{value:"Download query results",id:"download-query-results",level:3},{value:"View query history",id:"view-query-history",level:3},{value:"Further reading",id:"further-reading",level:2}];function c(e){const n={a:"a",code:"code",h2:"h2",h3:"h3",img:"img",li:"li",ol:"ol",p:"p",pre:"pre",strong:"strong",ul:"ul",...(0,i.R)(),...e.components};return(0,r.jsxs)(r.Fragment,{children:[(0,r.jsx)(n.p,{children:"This tutorial demonstrates some useful features built into Query view in Apache Druid."}),"\n",(0,r.jsxs)(n.p,{children:["Query view lets you run ",(0,r.jsx)(n.a,{href:"/docs/latest/querying/sql",children:"Druid SQL queries"})," and ",(0,r.jsx)(n.a,{href:"/docs/latest/querying/",children:"native (JSON-based) queries"})," against ingested data."]}),"\n",(0,r.jsxs)(n.p,{children:["You can use Query view to test and tune queries before you use them in API requests\u2014for example, to perform ",(0,r.jsx)(n.a,{href:"/docs/latest/api-reference/sql-ingestion-api",children:"SQL-based ingestion"}),". You can also ingest data directly in Query view."]}),"\n",(0,r.jsx)(n.p,{children:"The tutorial guides you through the steps to ingest sample data and query the ingested data using some Query view features."}),"\n",(0,r.jsx)(n.h2,{id:"prerequisites",children:"Prerequisites"}),"\n",(0,r.jsxs)(n.p,{children:["Before you follow the steps in this tutorial, download Druid as described in the ",(0,r.jsx)(n.a,{href:"/docs/latest/tutorials/",children:"quickstart"})," and have it running on your local machine. You don't need to have loaded any data."]}),"\n",(0,r.jsx)(n.h2,{id:"run-a-demo-query-to-ingest-data",children:"Run a demo query to ingest data"}),"\n",(0,r.jsx)(n.p,{children:"Druid includes demo queries that each demonstrate a different Druid feature\u2014for example transforming data during ingestion and sorting ingested data. Each query has detailed comments to help you learn more."}),"\n",(0,r.jsxs)(n.p,{children:["In this section you load the demo queries and run a SQL task to ingest sample data into a ",(0,r.jsx)(n.a,{href:"/docs/latest/querying/datasource#table",children:"table datasource"}),"."]}),"\n",(0,r.jsxs)(n.ol,{children:["\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Navigate to the Druid console at ",(0,r.jsx)(n.a,{href:"http://localhost:8888",children:"http://localhost:8888"})," and click ",(0,r.jsx)(n.strong,{children:"Query"}),"."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click the ellipsis at the bottom of the query window and select ",(0,r.jsx)(n.strong,{children:"Load demo queries"}),". Note that loading the demo queries replaces all of your current query tabs. The demo queries load in several tabs:"]}),"\n",(0,r.jsx)(n.p,{children:(0,r.jsx)(n.img,{alt:"demo queries",src:t(44412).A+"",width:"1250",height:"640"})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click the ",(0,r.jsx)(n.strong,{children:"Demo 1"})," tab. This query ingests sample data into a datasource called ",(0,r.jsx)(n.strong,{children:"kttm_simple"}),". Click the ",(0,r.jsx)(n.strong,{children:"Demo 1"})," tab heading again and note the options\u2014you can rename, copy, and duplicate tabs."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:"Run"})," to ingest the data."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["When ingestion is complete, Druid displays the time it took to complete the insert query, and the new datasource ",(0,r.jsx)(n.strong,{children:"kttm_simple"})," displays in the left pane."]}),"\n"]}),"\n"]}),"\n",(0,r.jsx)(n.h2,{id:"view-and-filter-query-results",children:"View and filter query results"}),"\n",(0,r.jsx)(n.p,{children:"In this section you run some queries against the new datasource and perform some operations on the query results."}),"\n",(0,r.jsxs)(n.ol,{children:["\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:"+"})," to the right of the existing tabs to open a new query tab."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click the name of the datasource ",(0,r.jsx)(n.strong,{children:"kttm_simple"})," in the left pane to display some automatically generated queries:"]}),"\n",(0,r.jsx)(n.p,{children:(0,r.jsx)(n.img,{alt:"auto queries",src:t(36055).A+"",width:"1250",height:"640"})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:"SELECT * FROM kttm_simple"})," and run the query."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["In the query results pane, click ",(0,r.jsx)(n.strong,{children:"Chrome"})," anywhere it appears in the ",(0,r.jsx)(n.strong,{children:"browser"})," column then click ",(0,r.jsx)(n.strong,{children:"Filter on: browser = 'Chrome'"})," to filter the results."]}),"\n"]}),"\n"]}),"\n",(0,r.jsx)(n.h2,{id:"run-aggregate-queries",children:"Run aggregate queries"}),"\n",(0,r.jsxs)(n.p,{children:[(0,r.jsx)(n.a,{href:"/docs/latest/querying/sql-aggregations",children:"Aggregate functions"})," allow you to perform a calculation on a set of values and return a single value."]}),"\n",(0,r.jsx)(n.p,{children:"In this section you run some queries using aggregate functions and perform some operations on the results, using shortcut features designed to help you build your query."}),"\n",(0,r.jsxs)(n.ol,{children:["\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"Open a new query tab."}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:"kttm_simple"})," in the left pane to display the generated queries."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:'SELECT COUNT(*) AS "Count" FROM kttm_simple'})," and run the query."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"After you run a query that contains an aggregate function, additional Query view options become available."}),"\n",(0,r.jsxs)(n.p,{children:["Click the arrow to the left of the ",(0,r.jsx)(n.strong,{children:"kttm_simple"})," datasource to display the columns, then click the ",(0,r.jsx)(n.strong,{children:"country"})," column. Several options appear to apply country-based filters and aggregate functions to the query:"]}),"\n",(0,r.jsx)(n.p,{children:(0,r.jsx)(n.img,{alt:"count distinct",src:t(33411).A+"",width:"1250",height:"640"})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:'Aggregate > COUNT(DISTINCT "country")'})," to add this clause to the query. The query now appears as follows:"]}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'SELECT COUNT(*) AS "Count",\n COUNT(DISTINCT "country") AS "dist_country"\nFROM "kttm_simple"\nGROUP BY ()\n'})}),"\n",(0,r.jsxs)(n.p,{children:["Note that you can use column names such as ",(0,r.jsx)(n.code,{children:"dist_country"})," in this example as shortcuts when building your query."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"Run the updated query:"}),"\n",(0,r.jsx)(n.p,{children:(0,r.jsx)(n.img,{alt:"aggregate-query",src:t(91799).A+"",width:"1250",height:"640"})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:"Engine: Auto (SQL native)"})," to display the engine options\u2014",(0,r.jsx)(n.strong,{children:"Native"})," for native (JSON-based) queries, ",(0,r.jsx)(n.strong,{children:"SQL native"})," for Druid SQL queries, and ",(0,r.jsx)(n.strong,{children:"SQL MSQ-task"})," for SQL-based ingestion."]}),"\n",(0,r.jsxs)(n.p,{children:["Select ",(0,r.jsx)(n.strong,{children:"Auto"})," to let Druid select the most efficient engine based on your query input."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"From the engine menu you can also edit the query context and turn off some query defaults."}),"\n",(0,r.jsxs)(n.p,{children:["Deselect ",(0,r.jsx)(n.strong,{children:"Use approximate COUNT(DISTINCT)"})," and rerun the query. The country count in the results decreases because the computation has become more exact. See ",(0,r.jsx)(n.a,{href:"/docs/latest/querying/sql-aggregations",children:"SQL aggregation functions"})," for more information."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"Query view can provide information about a function, in case you aren't sure exactly what it does."}),"\n",(0,r.jsxs)(n.p,{children:["Delete the contents of the query line ",(0,r.jsx)(n.code,{children:"COUNT(DISTINCT country) AS dist_country"})," and type ",(0,r.jsx)(n.code,{children:"COUNT(DISTINCT)"})," to replace it. A help dialog for the function displays:"]}),"\n",(0,r.jsx)(n.p,{children:(0,r.jsx)(n.img,{alt:"count distinct help",src:t(25783).A+"",width:"1250",height:"640"})}),"\n",(0,r.jsx)(n.p,{children:"Click outside the help window to close it."}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"You can perform actions on calculated columns in the results pane."}),"\n",(0,r.jsxs)(n.p,{children:["Click the results column heading ",(0,r.jsx)(n.strong,{children:'dist_country COUNT(DISTINCT "country")'})," to see the available options:"]}),"\n",(0,r.jsx)(n.p,{children:(0,r.jsx)(n.img,{alt:"result columns actions",src:t(51001).A+"",width:"1250",height:"640"})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Select ",(0,r.jsx)(n.strong,{children:"Edit column"})," and change the ",(0,r.jsx)(n.strong,{children:"Output name"})," to ",(0,r.jsx)(n.strong,{children:"Distinct countries"}),"."]}),"\n"]}),"\n"]}),"\n",(0,r.jsx)(n.h2,{id:"generate-an-explain-plan",children:"Generate an explain plan"}),"\n",(0,r.jsx)(n.p,{children:"In this section you generate an explain plan for a query. An explain plan shows the full query details and all of the operations Druid performs to execute it."}),"\n",(0,r.jsxs)(n.p,{children:["Druid optimizes queries of certain ",(0,r.jsx)(n.a,{href:"/docs/latest/querying/sql-translation#query-types",children:"types"}),"\u2014see ",(0,r.jsx)(n.a,{href:"/docs/latest/querying/sql-translation",children:"SQL query translation"})," for information on how to interpret an explain plan and use the details to improve query performance."]}),"\n",(0,r.jsxs)(n.ol,{children:["\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"Open a new query tab."}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:"kttm_simple"})," in the left pane to display the generated queries."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:"SELECT * FROM kttm_simple"})," and run the query."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click the ellipsis at the bottom of the query window and select ",(0,r.jsx)(n.strong,{children:"Explain SQL query"}),". The query plan opens in a new window:"]}),"\n",(0,r.jsx)(n.p,{children:(0,r.jsx)(n.img,{alt:"query plan",src:t(73541).A+"",width:"1250",height:"640"})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Click ",(0,r.jsx)(n.strong,{children:"Open in new tab"}),". You can review the query details and modify it as required."]}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"Change the limit from 1001 to 2001:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:'"Limit": 2001,\n'})}),"\n",(0,r.jsx)(n.p,{children:"and run the query to confirm that the updated query returns 2,001 results."}),"\n"]}),"\n"]}),"\n",(0,r.jsx)(n.h2,{id:"try-out-a-few-more-features",children:"Try out a few more features"}),"\n",(0,r.jsx)(n.p,{children:"In this section you try out a few more useful Query view features."}),"\n",(0,r.jsx)(n.h3,{id:"use-calculator-mode",children:"Use calculator mode"}),"\n",(0,r.jsxs)(n.p,{children:["Queries without a FROM clause run in calculator mode\u2014this can be useful to help you understand how functions work. See the ",(0,r.jsx)(n.a,{href:"/docs/latest/querying/sql-functions",children:"Druid SQL functions"})," reference for more information."]}),"\n",(0,r.jsxs)(n.ol,{children:["\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"Open a new query tab and enter the following:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"SELECT SQRT(49)\n"})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Run the query to produce the result ",(0,r.jsx)(n.code,{children:"7"}),"."]}),"\n"]}),"\n"]}),"\n",(0,r.jsx)(n.h3,{id:"download-query-results",children:"Download query results"}),"\n",(0,r.jsx)(n.p,{children:"You can download query results in CSV, TSV, or newline-delimited JSON format."}),"\n",(0,r.jsxs)(n.ol,{children:["\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsx)(n.p,{children:"Open a new query tab and run a query, for example:"}),"\n",(0,r.jsx)(n.pre,{children:(0,r.jsx)(n.code,{className:"language-sql",children:"SELECT DISTINCT platform\nFROM kttm_simple\n"})}),"\n"]}),"\n",(0,r.jsxs)(n.li,{children:["\n",(0,r.jsxs)(n.p,{children:["Above the results pane, click the down arrow and select ",(0,r.jsx)(n.strong,{children:"Download results as\u2026 CSV"}),"."]}),"\n"]}),"\n"]}),"\n",(0,r.jsx)(n.h3,{id:"view-query-history",children:"View query history"}),"\n",(0,r.jsxs)(n.p,{children:["In any query tab, click the ellipsis at the bottom of the query window and select ",(0,r.jsx)(n.strong,{children:"Query history"}),"."]}),"\n",(0,r.jsx)(n.p,{children:"You can click the links on the left to view queries run at a particular date and time, and open a previously run query in a new query tab."}),"\n",(0,r.jsx)(n.h2,{id:"further-reading",children:"Further reading"}),"\n",(0,r.jsx)(n.p,{children:"For more information on ingestion and querying data, see the following topics:"}),"\n",(0,r.jsxs)(n.ul,{children:["\n",(0,r.jsxs)(n.li,{children:[(0,r.jsx)(n.a,{href:"/docs/latest/tutorials/",children:"Quickstart"})," for information on getting started with Druid."]}),"\n",(0,r.jsxs)(n.li,{children:[(0,r.jsx)(n.a,{href:"/docs/latest/tutorials/tutorial-query",children:"Tutorial: Querying data"})," for example queries to run on Druid data."]}),"\n",(0,r.jsxs)(n.li,{children:[(0,r.jsx)(n.a,{href:"/docs/latest/ingestion/",children:"Ingestion"})," for an overview of ingestion and the ingestion methods available in Druid."]}),"\n",(0,r.jsxs)(n.li,{children:[(0,r.jsx)(n.a,{href:"/docs/latest/multi-stage-query/",children:"SQL-based ingestion"})," for an overview of SQL-based ingestion."]}),"\n",(0,r.jsxs)(n.li,{children:[(0,r.jsx)(n.a,{href:"/docs/latest/multi-stage-query/examples",children:"SQL-based ingestion query examples"})," for examples of SQL-based ingestion for various use cases."]}),"\n",(0,r.jsxs)(n.li,{children:[(0,r.jsx)(n.a,{href:"https://github.com/implydata/learn-druid/tree/main/notebooks",children:"Introduction to Druid SQL"})," to learn more about Druid SQL."]}),"\n"]})]})}function u(e={}){const{wrapper:n}={...(0,i.R)(),...e.components};return n?(0,r.jsx)(n,{...e,children:(0,r.jsx)(c,{...e})}):c(e)}},33411:(e,n,t)=>{t.d(n,{A:()=>s});const s=t.p+"assets/images/tutorial-sql-count-distinct-d6020a15c19fff26ed0570fb2a7c7a64.png"},36055:(e,n,t)=>{t.d(n,{A:()=>s});const s=t.p+"assets/images/tutorial-sql-auto-queries-eb62d0f4f2bb563fddf353a7afbd18a2.png"},44412:(e,n,t)=>{t.d(n,{A:()=>s});const s=t.p+"assets/images/tutorial-sql-demo-queries-18b129cf96aca4bf1f61903039a0d462.png"},51001:(e,n,t)=>{t.d(n,{A:()=>s});const s=t.p+"assets/images/tutorial-sql-result-column-actions-f40697022055f14315241ca973c0847d.png"},73541:(e,n,t)=>{t.d(n,{A:()=>s});const s=t.p+"assets/images/tutorial-sql-query-plan-f9aae6d8f66389766c16b59aadd1a2f0.png"},91799:(e,n,t)=>{t.d(n,{A:()=>s});const s=t.p+"assets/images/tutorial-sql-aggregate-query-cda2de57ff7e679c2ebb5d1373b3ff62.png"}}]); |