llooker / blocks_redshift_admin Goto Github PK

Something like this should work:

        dimension: looker_query_context {
          sql: REGEXP_SUBSTR(REGEXP_SUBSTR(${text},'(Query\\sContext\\s\'\\{)(.*)(\\}\')'),'(\\{)(.*)(\\})') ;;
        }
        dimension: looker_user_id {
          sql:json_extract_path_text(${looker_query_context},'user_id') ;;
        }
        dimension: looker_history_id {
          sql:json_extract_path_text(${looker_query_context},'history_id') ;;
        }
        dimension: looker_instance_slug {
          sql:json_extract_path_text(${looker_query_context},'instance_slug') ;;
        }

Since strings may contain sensitive data, e.g. within filter values, it would be good to use a regex to redact all strings by default when creating the block's PDTs

• Add dimension redshift_queries.is_pdt_build, based on the comment in sqltext: Possible values: "No", "Yes, prod", "Yes, dev"
• Apply is_pdt_build=No as a default filter on the performance dashboard

Use datagroups (for all PDTs, except Redshift slices since that's not in any dashboard, or ever joined)

The upcoming release will validate this previously uncaught reference error.... need to fix it.

What would be the recommended model to use here if your looker system connects to multiple redshift clusters??

Is there some preferred way to namespace things so that this could be used on one looker instance with multiple tenants (in essence).

See discussion and recommendation here: https://looker.slack.com/archives/C6VC7QNG4/p1523579288000057

Saw this company: http://intermix.io/

Leaving a note to review to put together potential features for the redshift admin block.

Could you please add a license to the project? In fact, most of the repos under this account don't have any license so I'm not sure if we can use it on our own* or in Looker.

*: Parsing the LookML and using it for something else.

• Identifiers
  • Database
  • Schema
  • Table (name)
  • Table ID
• Size
  • Rows
  • Megabytes
• Distribution
  • Distribution style
  • Skew rows
• Sort
  • Sortkey
  • Sortkey skew
  • Unsorted
• Other
  ...

The tables which you need to grant privileges for are not up to date with the most recent views.

To replace the hard-to-use table in the Redshift Query Inspection dashboard

https://blog.tanelpoder.com/posts/visualizing-sql-plan-execution-time-with-flamegraphs/

https://aws.amazon.com/about-aws/whats-new/2017/11/amazon-redshift-allows-regular-users-to-be-granted-access-to-all-rows-in-selected-system-tables/

11a63fa#commitcomment-27560587

And make it consistent between the table and in performance dashboard pie chart

DIST_NONE => vibrant green
DIST_ALL_NONE=> black/green
DIST_INNER => grey/green
BCAST_INNER => red
DIST_OUTER => orange
DIST_BOTH => orange/red

#20

PDT in this view is failing:
view: redshift_tables {
derived_table: {
# Insert into PDT because redshift won't allow joining certain system tables/views onto others (presumably because they are located only on the leader node)
persist_for: "8 hours"
sql: select
"database"::varchar,
"schema"::varchar,
"Table_id"::bigint,
"table"::varchar,
"encoded"::varchar,
"diststyle"::varchar,
"sortkey1"::varchar,
"max_varchar"::bigint,
"sortkey1_enc"::varchar,
"sortkey_num"::int,
"size"::bigint,
"pct_used"::numeric,
"unsorted"::numeric,
"stats_off"::numeric,
"tbl_rows"::bigint,
"skew_sortkey1"::numeric,
"skew_rows"::numeric
from svv_table_info
;;
# http://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLE_INFO.html
distribution_style: all
indexes: ["table_id","table"] # "indexes" translates to an interleaved sort key for Redshift
}

This is due to the "sortkey1" column, we see the error when we select just that column:
select "sortkey1"::varchar from svv_table_info
====>
The Amazon Redshift database encountered an error while running this query.
Invalid character data was found. This is most likely caused by stored data containing characters that are invalid for the character set the database was created in. The most common example of this is storing 8bit data in a SQL_ASCII database.

Quick (Looker) view to check that performance issues aren't due to Redshift views