1"use strict";(globalThis.webpackChunkstackql_io||=[]).push([[18978],{60874(e,i,n){n.r(i),n.d(i,{default:()=>c,metadata:()=>t});var t=n(70610),a=n(74848),r=n(28453);const s={authorsImageUrls:[void 0]};function l(e){const i={a:"a",code:"code",h2:"h2",li:"li",ol:"ol",p:"p",pre:"pre",strong:"strong",...(0,r.R)(),...e.components};return(0,a.jsxs)(a.Fragment,{children:[(0,a.jsx)(i.p,{children:"Materialized Views are now available in StackQL. Materialized Views can be used to improve performance for dependent or repetetive queries within StackQL provisioning or analytics routines."}),"\n",(0,a.jsx)(i.h2,{id:"refresher-on-materialized-views",children:"Refresher on Materialized Views"}),"\n",(0,a.jsx)(i.p,{children:"Unlike standard views that provide a virtual representation of data, a Materialized View physically stores the result set of a query. This implies that the data is pre-computed and stored, which can lead to performance gains as the data doesn't need to be fetched from the underlying resource(s) every time it is queried."}
1),"\n",(0,a.jsx)(i.h2,{id:"benefits-of-materialized-views-in-stackql",children:"Benefits of Materialized Views in StackQL"}),"\n",(0,a.jsxs)(i.ol,{children:["\n",(0,a.jsxs)(i.li,{children:["\n",(0,a.jsxs)(i.p,{children:[(0,a.jsx)(i.strong,{children:"Performance Boost"}),": With data already stored and readily available, Materialized Views can substantially reduce StackQL query execution time, especially for complex and frequently-run queries."]}),"\n"]}),"\n",(0,a.jsxs)(i.li,{children:["\n",(0,a.jsxs)(i.p,{children:[(0,a.jsx)(i.strong,{children:"Data Consistency"}),": Since Materialized Views provide a snapshot of the data at a specific point in time, it ensures consistent data is returned every time it is accessed until it is refreshed."]}),"\n"]}),"\n",(0,a.jsxs)(i.li,{children:["\n",(0,a.jsxs)(i.p,{children:[(0,a.jsx)(i.strong,{children:"Flexibility"}),": You have the flexibility to refresh the Materialized View as needed usign the ",(0,a.jsx)(i.code,{children:"REFRESH MATERIALIZED VIEW"})," lifecycle operation in StackQL. This is particularly useful when working with rapidly changing data."]}),"\n"]}),"\n"]}),"\n",(0,a.jsx)(i.h2,{id:"using-materialized-views-in-stackql",children:"Using Materialized Views in StackQL"}),"\n",(0,a.jsx)(i.p,{children:"Here's a step-by-step guide on how you to use this new feature in StackQL:"}),"\n",(0,a.jsxs)(i.ol,{children:["\n",(0,a.jsxs)(i.li,{children:[(0,a.jsx)(i.strong,{children:"Create a Materialized View"}),":"]}),"\n"]}),"\n",(0,a.jsx)(i.pre,{children:(0,a.jsx)(i.code,{className:"language-sql",children:"CREATE MATERIALIZED VIEW vw_ec2_instance_types AS \r\nSELECT \r\n memoryInfo, \r\n hypervisor, \r\n autoRecoverySupported, \r\n instanceType, \r\n SPLIT_PART(processorInfo, '\\n', 3) as processorArch, \r\n currentGeneration, \r\n freeTierEligible, \r\n hibernationSupported,\r\n SPLIT_PART(vCpuInfo, '\\n', 2) as vCPUs, \r\n bareMetal, \r\n burstablePerformanceSupported, \r\n dedicatedHostsSupported \r\nFROM aws.ec2.instance_types \r\nWHERE region = 'us-east-1';\n"})}),"\n",(0,a.jsxs)(i.ol,{start:"2",children:["\n",(0,a.jsxs)(i.li,{children:[(0,a.jsx)(i.strong,{children:"Refresh the Materialized View"}),":"]}),"\n"]}),"\n",(0,a.jsx)(i.pre,{children:(0,a.jsx)(i.code,{className:"language-sql",children:"REFRESH MATERIALIZED VIEW vw_ec2_instance_types;\n"})}),"\n",(0,a.jsxs)(i.ol,{start:"3",children:["\n",(0,a.jsxs)(i.li,{children:[(0,a.jsx)(i.strong,{children:"Use the Materialized View in a StackQL Query"}),":"]}),"\n"]}),"\n",(0,a.jsx)(i.pre,{children:(0,a.jsx)(i.code,{className:"language-sql",children:"SELECT \r\n i.instanceId, \r\n i.instanceType, \r\n it.vCPUs, \r\n it.memoryInfo \r\nFROM aws.ec2.instances i \r\n INNER JOIN vw_ec2_instance_types it \r\n ON i.instanceType = it.instanceType \r\nWHERE i.region = 'us-east-1';\n"})}),"\n",(0,a.jsxs)(i.ol,{start:"3",children:["\n",(0,a.jsxs)(i.li,{children:[(0,a.jsx)(i.strong,{children:"Drop the Materialized View"}),":"]}),"\n"]}),"\n",(0,a.jsx)(i.pre,{children:(0,a.jsx)(i.code,{className:"language-sql",children:"DROP MATERIALIZED VIEW vw_ec2_instance_types;\n"})}),"\n",(0,a.jsxs)(i.p,{children:["More information on Materialized Views in StackQL can be found ",(0,a.jsx)(i.a,{href:"/language-spec/createview",children:"here"}),"."]})]})}function c(e={}){const{wrapper:i}={...(0,r.R)(),...e.components};return i?(0,a.jsx)(i,{...e,children:(0,a.jsx)(l,{...e})}):l(e)}n.d(i,["assets",0,s,"contentTitle",0,void 0,"frontMatter",0,{slug:"introducing-materialized-views-with-stackql",title:"Introducing Materialized Views with StackQL",hide_table_of_contents:!1,authors:["kieranrimmer"],image:"/img/blog/stackql-featured-image.png",keywords:["stackql","analytics"],tags:["stackql","analytics"]},"toc",0,[{value:"Refresher on Materialized Views",id:"refresher-on-materialized-views",level:2},{value:"Benefits of Materialized Views in StackQL",id:"benefits-of-materialized-views-in-stackql",level:2},{value:"Using Materialized Views in StackQL",id:"using-materialized-views-in-stackql",level:2}]])},28453(e,i,n){n.d(i,{R:()=>s,x:()=>l});var t=n(96540);const a={},r=t.createContext(a);function s(e){const i=t.useContext(r);return t.useMemo(function(){return"function"==typeof e?e(i):{...i,...e}},[i,e])}function l(e){let i;return i=e.disableParentContext?"function"==typeof e.components?e.components(a):e.components||a:s(e.components),t.createElement(r.Provider,{value:i},e.children)}},70610(e){e.exports=JSON.parse('{"permalink":"/blog/product/introducing-materialized-views-with-stackql","editUrl":"https://github.com/stackql/stackql.io/edit/main/blog/product/2023-09-29-introducing-materialized-views-with-stackql.md","source":"@site/blog/product/2023-09-29-introducing-materialized-views-with-stackql.md","title":"Introducing Materialized Views with StackQL","description":"Materialized Views are now available in StackQL. Materialized Views can be used to improve performance for dependent or repetetive queries within StackQL provisioning or analytics routines.","date":"2023-09-29T00:00:00.000Z","tags":[{"inline":true,"label":"stackql","permalink":"/blog/product/tags/stackql"},{"inline":true,"label":"analytics","permalink":"/blog/product/tags/analytics"}],"readingTime":1.55,"hasTruncateMarker":false,"authors":[{"name":"Kieran Rimmer","title":"Technologist and Cloud Consultant","url":"https://www.linkedin.com/in/kieranrimmer/","key":"kieranrimmer","page":null}],"frontMatter":{"slug":"introducing-materialized-views-with-stackql","title":"Introducing Materialized Views with StackQL","hide_table_of_contents":false,"authors":["kieranrimmer"],"image":"/img/blog/stackql-featured-image.png","keywords":["stackql","analytics"],"tags":["stackql","analytics"]},"unlisted":false,"prevItem":{"title":"Builtin Parallel Query Execution in StackQL","permalink":"/blog/product/builtin-parallel-query-execution-in-stackql"},"nextItem":{"title":"Introducing Table Valued Functions in StackQL","permalink":"/blog/product/introducing-table-valued-functions-in-stackql"}}')}}]);
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.