Introduction
Materializing a query stores the results from any of your queries to your Latitude server. Then, you can create a new query that uses the materialized data instead of the original database. Materializing queries can be useful in a variety of scenarios:- Optimize queries. Store frequently used queries in your Latitude server, and use them as a source for other queries.
- Scale your queries. Store queries that are too large or expensive to run on your database, and use them as a source for other queries.
- Share data between sources. Store tables from different sources, and use them together in a single query, even if they are in different databases!
At the moment materializing only work with Potsgresql connector.
Materializing a query
Almost any query can be materialized.RequirementsQuery materialization is done on demand, so it is not possible to materialize a query that depends on user input or other dynamic content. This means that materialized queries cannot contain any
param
or runQuery
function calls.materialize
config tag to the query. This tag tells the Latitude server to store the query’s results in a file.
queries/mysql/users.sql
latitude materialize
to initiate the materialization process. Every query marked for materialization will be run, and the results will be stored in your Latitude server.
Using materialized queries
Running queries with thematerialize
config will still run the query as usual. In order to use the materialized results, you must use the materialized
function, which is a special function that allows you to reference materialized data, only available in DuckDB
sources.
Creating a duckdb source
If you do not already have any DuckDB source in your project, you first need to install the@latitude-data/duckdb-connector
package in order to create a new one.
queries
folder. To do so, create a new folder for this source and add a source.yml
with the following configuration:
queries/materialized/source.yaml
details
section to your source.yml
file to provide additional information about your source. Read more about DuckDB sources for more information.
Querying materialized data
Once you have created a DuckDB source, you can now create queries and reference any materialized data. To select a materialized query, you can use the special{materialized(...)}
function,
which takes the path to the materialized query as an argument, and lets you use the stored results as a regular SQL table.
queries/materialized/users.sql
queries/materialized/users.sql
Trying to reference a materialized query without having performed the materialization process will result in an error, as the data has not been stored in your server yet.
Deploying materialized queries
When deploying your Latitude project, you will need to perform the materialization process in your server, otherwise the queries will not be able to access the materialized data. The most simple approach is to add a command to the end of yourDockerfile
that will run the materialization process when the container is built.
When available, Latitude Cloud will materialize and update your queries automatically on a regular basis.