https://vulcansql.com/assets/js/91acf19d.cc7787c3.js

1"use strict";(self.webpackChunkdoc=self.webpackChunkdoc||[]).push([[129],{3905:(e,t,a)=>{a.d(t,{Zo:()=>u,kt:()=>d});var n=a(7294);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 i(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 o(e){for(var t=1;t<arguments.length;t++){var a=null!=arguments[t]?arguments[t]:{};t%2?i(Object(a),!0).forEach((function(t){r(e,t,a[t])})):Object.getOwnPropertyDescriptors?Object.defineProperties(e,Object.getOwnPropertyDescriptors(a)):i(Object(a)).forEach((function(t){Object.defineProperty(e,t,Object.getOwnPropertyDescriptor(a,t))}))}return e}function c(e,t){if(null==e)return{};var a,n,r=function(e,t){if(null==e)return{};var a,n,r={},i=Object.keys(e);for(n=0;n<i.length;n++)a=i[n],t.indexOf(a)>=0||(r[a]=e[a]);return r}(e,t);if(Object.getOwnPropertySymbols){var i=Object.getOwnPropertySymbols(e);for(n=0;n<i.length;n++)a=i[n],t.indexOf(a)>=0||Object.prototype.propertyIsEnumerable.call(e,a)&&(r[a]=e[a])}return r}var l=n.createContext({}),s=function(e){var t=n.useContext(l),a=t;return e&&(a="function"==typeof e?e(t):o(o({},t),e)),a},u=function(e){var t=s(e.components);return n.createElement(l.Provider,{value:t},e.children)},p={inlineCode:"code",wrapper:function(e){var t=e.children;return n.createElement(n.Fragment,{},t)}},h=n.forwardRef((function(e,t){var a=e.components,r=e.mdxType,i=e.originalType,l=e.parentName,u=c(e,["components","mdxType","originalType","parentName"]),h=s(a),d=r,m=h["".concat(l,".").concat(d)]||h[d]||p[d]||i;return a?n.createElement(m,o(o({ref:t},u),{},{components:a})):n.createElement(m,o({ref:t},u))}));function d(e,t){var a=arguments,r=t&&t.mdxType;if("string"==typeof e||r){var i=a.length,o=new Array(i);o[0]=h;var c={};for(var l in t)hasOwnProperty.call(t,l)&&(c[l]=t[l]);c.originalType=e,c.mdxType="string"==typeof e?e:r,o[1]=c;for(var s=2;s<i;s++)o[s]=a[s];return n.createElement.apply(null,o)}return n.createElement.apply(null,a)}h.displayName="MDXCreateElement"},1351:(e,t,a)=>{a.r(t),a.d(t,{assets:()=>l,contentTitle:()=>o,default:()=>p,frontMatter:()=>i,metadata:()=>c,toc:()=>s});var n=a(7462),r=(a(7294),a(3905));const i={},o="Caching Datasets",c={unversionedId:"develop/cache",id:"develop/cache",title:"Caching Datasets",description:"The caching layer feature in VulcanSQL allows you to leverage caching to improve the performance and efficiency of your API queries. With the {% cache %} tag, you can query results directly from the cache layer storage, reducing the need for repeated queries to the data source. Let's explore how to use this feature effectively.",source:"@site/docs/develop/cache.mdx",sourceDirName:"develop",slug:"/develop/cache",permalink:"/docs/develop/cache",draft:!1,editUrl:"https://github.com/Canner/vulcan-sql/tree/main/packages/doc/docs/develop/cache.mdx",tags:[],version:"current",frontMatter:{},sidebar:"tutorialSidebar",previous:{title:"Advanced Techniques",permalink:"/docs/develop/advanced"},next:{title:"Handling Errors",permalink:"/docs/develop/error"}},l={},s=[{value:"Caching with DuckDB",id:"caching-with-duckdb",level:2},{value:"Caching Query Results",id:"caching-query-results",level:2},{value:"Reusing Cached Results",id:"reusing-cached-results",level:2}],u={toc:s};function p(e){let{components:t,...a}=e;return(0,r.kt)("wrapper",(0,n.Z)({},u,a,{components:t,mdxType:"MDXLayout"}),(0,r.kt)("h1",{id:"caching-datasets"},"Caching Datasets"),(0,r.kt)("p",null,"The caching layer feature in VulcanSQL allows you to leverage caching to improve the performance and efficiency of your API queries. With the ",(0,r.kt)("inlineCode",{parentName:"p"},"{% cache %}")," tag, you can query results directly from the cache layer storage, reducing the need for repeated queries to the data source. Let's explore how to use this feature effectively."),(0,r.kt)("h2",{id:"caching-with-duckdb"},"Caching with DuckDB"),(0,r.kt)("p",null,"To provide efficient caching functionality, VulcanSQL utilizes ",(0,r.kt)("a",{parentName:"p",href:"https://duckdb.org/"},"DuckDB")," as the underlying storage engine. This ensures high-performance caching and improves the overall query execution speed."),(0,r.kt)("admonition",{type:"info"},(0,r.kt)("p",{parentName:"admonition"},"Please note that in order to use the caching layer feature, you need to have the ",(0,r.kt)("a",{parentName:"p",href:"../connectors/duckdb"},"DuckDB extension")," installed. If you are using VulcanSQL CLI binary, the DuckDB extension is already pre-bundled, so no additional installation is required. However, if you are using VulcanSQL in a NodeJS project, make sure to install the DuckDB extension separately to enable the caching functionality.")),(0,r.kt)("admonition",{type:"caution"},(0,r.kt)("p",{parentName:"admonition"},"At the moment, if you need to use the caching layer, only these data sources are supported: BigQuery, Snowflake and DuckDB.\nIf you need to have the caching layer enabled for other data sources, please ",(0,r.kt)("a",{parentName:"p",href:"https://github.com/Canner/vulcan-sql/issues/new?assignees=&labels=type%2Fenhancement%2C+needs-triage&projects=&template=feature-request.md&title="}

1,"submit a feature request on GitHub"),". Thank you!")),(0,r.kt)("p",null,"First, you need to add the following configuration to vulcan.yaml."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-yaml",metastring:'title="vulcan.yaml"',title:'"vulcan.yaml"'},"cache:\n  type: parquet\n  folderPath: tmp\n  loader: duckdb\nextensions:\n  duckdb: @vulcan-sql/extension-driver-duckdb\n")),(0,r.kt)("p",null,"In the configuration, ",(0,r.kt)("inlineCode",{parentName:"p"},"tmp")," means there will be a new folder called ",(0,r.kt)("inlineCode",{parentName:"p"},"tmp")," in the root directory of the project you created."),(0,r.kt)("p",null,"If your data source is BigQuery, you need to update your ",(0,r.kt)("inlineCode",{parentName:"p"},"profiles.yaml")," as following:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-yaml"},"- name: bq # profile name\n  type: bq\n  ...\n  cache:\n    # You should make sure there is a storage object administrator role in your service account\n    bucketName: 'your-bucket-name'\n")),(0,r.kt)("h2",{id:"caching-query-results"},"Caching Query Results"),(0,r.kt)("p",null,"To utilize the caching layer, you can enclose your SQL query inside the ",(0,r.kt)("inlineCode",{parentName:"p"},"{% cache %}")," and ",(0,r.kt)("inlineCode",{parentName:"p"},"{% endcache %}")," tags. For example:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql",metastring:'title="dept.sql"',title:'"dept.sql"'},'{% cache %}\nSELECT * FROM cache_departments\nWHERE "name" = {{ context.params.name }};\n{% endcache %}\n')),(0,r.kt)("p",null,"This tag enables the query to fetch the result from the cache layer storage. You can define which queries are preloaded into the cache layer by specifying cache settings in the configuration file. Here's an example configuration:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-yaml",metastring:'title="dept.yaml"',title:'"dept.yaml"'},"urlPath: /departments\nprofile: pg\n// highlight-start\ncache:\n  - cacheTableName: 'cache_departments' # The name of the table in the cache layer storage\n    sql: 'SELECT * FROM departments' # The SQL query to preload into the cache layer\n    profile: pg # The data source profile to use for the SQL query\n// highlight-end\n")),(0,r.kt)("p",null,"In this configuration, the ",(0,r.kt)("inlineCode",{parentName:"p"},"cache_departments")," table will be utilized within the ",(0,r.kt)("inlineCode",{parentName:"p"},"{% cache %}")," tag."),(0,r.kt)("p",null,"Also, you can add the refresh interval configuration in the yaml file in the cache section using the ",(0,r.kt)("inlineCode",{parentName:"p"},"refreshTime")," keyword."),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-yaml"},"cache:\n  - cacheTableName: 'cache_departments' # The name of the table in the cache layer storage\n    ...\n    refreshTime: { every: '5m' }\n")),(0,r.kt)("admonition",{type:"info"},(0,r.kt)("p",{parentName:"admonition"},"The time format used in ",(0,r.kt)("inlineCode",{parentName:"p"},"refreshTime")," should be compliant with the ",(0,r.kt)("a",{parentName:"p",href:"https://www.npmjs.com/package/ms"},(0,r.kt)("inlineCode",{parentName:"a"},"ms"))," package.")),(0,r.kt)("h2",{id:"reusing-cached-results"},"Reusing Cached Results"),(0,r.kt)("p",null,"VulcanSQL provides the ability to keep the query result from the cache layer in a variable, which can be reused in subsequent queries. For example:"),(0,r.kt)("pre",null,(0,r.kt)("code",{parentName:"pre",className:"language-sql",metastring:'title="dept.sql"',title:'"dept.sql"'},'-- The cached result is stored in the variable named "employee"\n{% cache employee %}\nSELECT * FROM cache_employees WHERE "id" = {{ context.params.id }};\n{% endcache %}\n\n-- The cached result can be reused in subsequent queries by referencing the variable name "employee"\nSELECT * FROM departments WHERE "employee_id" = {{ employee.value()[0].id }};\n')),(0,r.kt)("p",null,"By assigning the result of the ",(0,r.kt)("inlineCode",{parentName:"p"},"{% cache %}")," tag to the ",(0,r.kt)("inlineCode",{parentName:"p"},"employee")," variable, you can access its value in subsequent queries. This allows you to build complex queries by utilizing the cached results. Note that when the ",(0,r.kt)("inlineCode",{parentName:"p"},"{% cache %}")," tag does not have a variable assigned, it retrieves the result directly from the cache builder."))}p.isMDXComponent=!0}}]);