Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

[Analytic Block] Redshift Admin

  • 28 January 2016
  • 17 replies
  • 449 views
A
  • 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

D
Rank
Userlevel 4

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

D
Rank
Userlevel 4

Thanks @Michael_Erasmus, this is awesome!

D
Rank
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!

D
Rank
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!

D
Rank
Userlevel 4

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

D
Rank
Userlevel 4

Found some additional helpful queries in this repo:







Michael_Erasmus

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







S
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.

S
Userlevel 2

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




Thanks!

S

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.



S

Thanks @scott.hoover. works charm.

A
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.

A
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.

R

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.

R

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

R

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?

R

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.

Reply


Terms of Service