[Analytic Block] Redshift Admin

  • 28 January 2016
  • 17 replies
  • 425 views

  • Anonymous
  • 0 replies

Scott,


This is great!

A lot of companies think of using Looker to report on the usual suspects: revenue, user growth etc. and only for only a couple departments within a company. However, this really shows how you can use Looker beyond the usual use cases. Go setup a Looker alert for ETL load errors.


One note: On row 284 of the view, you have value_format_name which is not something I am seeing in any of the openly available Looker versions. Looks like it is in learn.looker.com so I am sure the general public will have access to it soon.


Thanks,

Lucas


17 replies

When i try to install redshift.admin.blocks i get “Model Not Found” Dashboard error ? please help



My Connection having necessary permission to reach out alll redshift tables.


Userlevel 2

@Sadhasivam_Jayabalag I noticed that, in the block, there’s a model hardcoded into the dashboard LookML—namely, model: bigfdata. Make sure to update all of that code with whatever you named your model.

Thanks @scott.hoover. works charm.

Userlevel 1

Thanks for this block, it’s very helpful!



A minor note - the “- scoping: true” line in the model file is obsolete in current LookML.

Userlevel 2

Good find, @apsmith. I’ve got a pull request in.



Thanks!

Userlevel 4

Thanks @scott.hoover! Just pushed the changes through.

Userlevel 1

One other note here - I was puzzled why I wasn’t seeing all the load info, and then discovered something - some of these redshift system tables are not completely visible except to users with superuser status (“create user” privilege). So the account here needs to be a superuser account to see all the load data.

These are great! We’ve also created our own, very similar model and views to monitor Redshift, but I definitely might steal some ideas from this (love the color coded columns)



Thought I might share two more related views that I find to be very useful:



Table Schemas


Query Alerts





Userlevel 4

Thanks @Michael_Erasmus, this is awesome!

Thanks. I ran into this too. I tried creating a new user called looker_admin and granted it privileges to all those system tables, yet in the SQL Runner, doing things like SELECT COUNT(*) FROM stl_load_commits would return the value 0 (rather than saying it doesn’t have permission, even though select has_schema_privilege('looker_admin', 'public', 'usage'); said 't').



This might be because these system tables aren’t part of the public (or any) schema that looker expects? None the less, using a real admin user resolves it, but isn’t ideal because of how any user with SQL Runner access can run ad hoc queries against everything.

Userlevel 4

Hmmm @rkulla not immediately clear without being able to look under the hood in your instance / database. Did you try using Chat Support so our analysts can see a bit more detail?



Thanks!

@Dillon_Morrison I did, yes. This was what chat support figured as well.

Userlevel 4

Okay thanks, just wanted to be sure. Maybe there’s a workaround we can figure out. Just to be clear, your goal is for you (and a certain subset of other users) to be able to query the systems tables, but restrict access for another subset of users (all of whom still have developer privileges within Looker)? Thanks!

Actually this behavior isn’t because of Looker. Upon further investigation:



production=# select current_user;

current_user

--------------

my_admin_user



production=# select count(*) from stl_load_commits;

count

-------

62966



production=# SET SESSION AUTHORIZATION 'looker_admin';

SET



production=> select count(*) from stl_load_commits;

count

------

0



production=# revoke select on table stl_load_commits from looker_admin;

REVOKE

production=# select has_table_privilege('looker_admin', 'stl_load_commits', 'select');

has_table_privilege

---------------------

t



Makes no sense to me. Maybe there’s a bug with redshift?

Userlevel 4

Hmmmm, yeah that is weird. Not sure what would be causing that but looks like a bug to me on first impression…

This seems to be why https://docs.aws.amazon.com/redshift/latest/dg/c_visibility-of-data.html



These are virtual tables where Only users with superuser privileges can see the data in those tables that are in the superuser visible category. Regular users can see data in the user visible tables. In most cases, rows generated by another user are invisible to a regular user.

Userlevel 4

Found some additional helpful queries in this repo:





Reply