Materialized views

For larger, computationally expensive queries, you can store the results as materialized views in Lens. A materialized view stores the precomputed results of a query, allowing you to query the stored data instead of running the complex query each time.

Benefits of materialized views

Materialized views help you:

• Reduce query costs: Avoid repeatedly fetching the same data from external data sources, which can incur costs based on data scanned or transferred.
• Improve performance: Query precomputed results instead of running complex joins across multiple databases each time.
• Simplify dashboards: Store results from multi-database queries and reference them simply in dashboard widgets.

Prerequisites

Before you start:

• Verify you have the necessary permissions to create and manage materialized views.
• If you need to query external data sources, ensure the connectors are set up.

Create a materialized view

To create a materialized view:

1. Go to one.newrelic.com > Administration > Materialized views.
2. Click Create materialized view.
3. Enter a unique name for the materialized view.
4. To query NRDB data, select an account from the account selector. You can join NRDB data from this account with external data sources.
5. Write your SQL query using the table reference format: connection_name.schema.table
6. Click Create.

The materialized view appears in the list. Storing results may take some time depending on the complexity of your query.

Refresh a materialized view

Materialized views contain a snapshot of data from when they were created or last refreshed. To get fresh data:

1. Go to one.newrelic.com > Administration > Materialized views.
2. Find the materialized view you want to refresh.
3. Click the menu.
4. Select Refresh.

The view runs the original query again and updates the stored results with the latest data.

Query a materialized view

You can query a materialized view like any other table in Lens:

1. Go to one.newrelic.com > Query your data.

2. Enable the toggle beside Notebooks and click + SQL to add a SQL block.

3. Reference your materialized view using the mv. prefix followed by the view name:

   SELECT * FROM mv.your_materialized_view_name

   Copy

4. Run your query.

You can apply additional filters, joins, or aggregations on the materialized view:

SELECT store_id, transaction_count
FROM mv.top_stores_mv
WHERE transaction_count > 1000
ORDER BY transaction_count DESC

The materialized view acts as a table containing the precomputed results, allowing you to perform further analysis without running the original complex query again.

Delete a materialized view

To delete a materialized view you no longer need:

1. Go to one.newrelic.com > Administration > Materialized views.
2. Find the materialized view you want to delete.
3. Click the menu.
4. Select Delete.
5. Confirm the deletion.

For query examples including cross-database joins, refer to Query materialized views.

Related topics