This Looker Block provides a substitute for the AWS Console, helping users identify how tables are structured and if data flow is normal.
(1) ETL and Data Flow Management - review ETL history, tables, and capacity, and set up alerts to trigger whenever errors occur in ETL or data movement processes.
(2) Monitor Database Performance - provides a comprehensive view of query performance across all queries and users to understand how quickly your database is performing, allowing users to see exactly which queries are causing delays (if any), and why.
(3) Inspect Queries - gain full detail around any particular query, from the time to return to the bytes scanned to the specific execution steps. This Block helps highlight those execution steps that are particularly slow moving and complex.
(4) Understand User Behavior - see which users or user groups are issuing the most or least performant queries, and help architect your schema and data pipeline to empower the users that need it most.
- Redshift comes with System Tables, which provide metadata around database performance and operations. These tables come natively, with the same table structures, for each Redshift instance. More information on System Tables can be found in AWS documentation.
-
Redshift Views - This Block consists of several derived tables based on the System Tables, which we've consolidated into one single view file.
-
Dashboards - Due to the nature in which this data is displayed, this Block only exposes Dashboards as starting points for exploration, rather than using individual "Explore" pages. Start from a dashboard, then drill into any tile to start free-form exploration.
-
Where to deploy it
- Separate Project (Recommended) - Since this model has little to no need to share views with the primary model, keeping it as its own project makes for the most convenient isolation of concerns.
- Existing Project - Make sure that other models do not contain include: "*.dashboard.lkml", as these will attempt to include the Redshift dashboards into existing models in which they do not make sense and will cause errors.
-
Connection and Permission
- Grant the SELECT privilege on:
- By default, grants to the above tables will only allow the Redshift user to view their own activity. Since Looker normally connects as a single Redshift user, this usually means all Looker activity, which is normally fine. If you want the reports to include data from other users you can execute these grants with SYSLOG ACCESS
-
[Optional] Change daily PDT trigger - The default PDT trigger (00:00 UTC) is not selected for any particular timezone, so you may want to offset it so that it does not trigger during your peak hours.
-
[Optional] Unhide Explores - Explores are hidden by default.
Check our Looker Discourse article for an overview of common performance issues, and suggestions to resolve them.
Great! Blocks were designed for continuous improvement through the help of the entire Looker community, and we'd love your input. To log an error or improvement recommentation, simply create a "New Issue" in the corresponding Github repo for this Block. Please be as detailed as possible in your explanation, and we'll address it as quick as we can.
- Sometimes, drilling into a list of queries doesn't return any records. As far as we can tell, this is due to categorically wrong result sets from Redshift for certain where filters. As a workaround, remove some filters, such as redshift_tables.sortkey1_enc
- Sometimes, the query execution table has 0 distribution bytes, despite the query plan and table distributions both suggesting that there was distribution activity. This zeroing out is present in each of SVL_QUERY_SUMMARY, SVL_QUERY_REPORT, and STL_DIST. Always check query execution metrics to ensure they're in the right ballpark before relying on them.
- "Rows out" according to the query plan are estimates. The are often highly inflated. If anything, this is an indication that you should update table statistics in Redshift so it can generate better query plans.
- Need to think about metrics for scans (e.g. bytes, rows emitted, and emitted rows to table rows ratio). When a table has dist style='all', the measures are increased by a factor of the number of slices. This is unintuitive since, for example the ratio is then typically >100%, but this may be a good thing.
Requires deployment onto a Looker instance with access to database tables as referenced by the '*.lkml' files.
This project is in production and the GDX Analytics Team will continue to update and maintain the project as required.
This is the central repository for work by the GDX Analytics Team.
For any questions regarding this project, or for inquiries about starting a new analytics account, please contact the GDX Analytics Team.
This Looker Block originates from https://github.com/llooker/blocks_redshift_admin.
The GDX Analytics Team are the main contributors to this project and maintain the code.
If you would like to contribute, please see our CONTRIBUTING guideleines.
Please note that this project is released with a Contributor Code of Conduct. By participating in this project you agree to abide by its terms.
Copyright 2015 Province of British Columbia
Licensed under the Apache License, Version 2.0 (the "License"); you may not use this file except in compliance with the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software distributed under the License is distributed on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the License for the specific language governing permissions and limitations under the License.
![repo-mountie[bot] avatar](https://avatars.githubusercontent.com/in/19327?v=4 "repo-mountie[bot]")
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent