Looker Redshift Management Model

custom_table_info.view.lookml

- view: custom_table_colmn_info
  derived_table:
    sql: |
      SELECT 
          a.schemaname
          , b.table_name
          , b.ordinal_position
          , b.column_name
          , REPLACE(b.udt_name, 'bpchar', 'char') data_type
          , character_maximum_length data_length
          , CASE a.encoding WHEN 'none' THEN 'raw' ELSE a.encoding END as encoding
          , REPLACE(
          ', ' 
          || column_name 
          || ' ' 
          || REPLACE(udt_name, 'bpchar', 'char')
          || '(' 
          || COALESCE(CAST(character_maximum_length AS VARCHAR),'') 
          || ')' 
          || CASE a.notnull WHEN 't' then ' NOT NULL ' else ' ' END
          || 'encoding '
          || CASE a.encoding WHEN 'none' THEN 'raw' ELSE a.encoding END
      
          ,'()','') AS column_definition
          , "distkey"
          , "sortkey"
          , "notnull"
      FROM information_schema.columns b
      LEFT OUTER JOIN PG_TABLE_DEF a ON b.table_name = a.tablename
          AND b.table_schema = a.schemaname
          AND b.column_name = a.column
      ORDER By ordinal_position

  fields:
  - measure: count
    type: count
    detail: detail*

  - dimension: schemaname
    sql: ${TABLE}.schemaname

  - dimension: table_name
    sql: ${TABLE}.table_name

  - dimension: column_name
    sql: ${TABLE}.column_name

  - dimension: data_type
    sql: ${TABLE}.data_type
  
  - dimension: data_length
    sql: ${TABLE}.data_length
    


  - dimension: ordinal_position
    sql: ${TABLE}.ordinal_position
    
  - dimension: encoding
    sql: ${TABLE}.encoding
  
    
  - dimension: column_definition
    sql: ${TABLE}.column_definition

  - dimension: distkey
    type: yesno
    sql: ${TABLE}.distkey

  - dimension: sortkey
    type: number
    sql: ${TABLE}.sortkey

  - dimension: notnull
    type: yesno
    sql: ${TABLE}.notnull

  sets:
    detail:
      - column
      - distkey
      - sortkey
      - notnull





- view: custom_table_summary_info
  derived_table:
    sql: |
      select trim(pgn.nspname) as Schema, trim(a.name) as Table, id as TableId, decode(pgc.reldiststyle,0, 'EVEN',1,det.distkey ,8,'ALL') as DistKey, dist_ratio.ratio::decimal(10,4) as Skew, det.head_sort as "SortKey", det.n_sortkeys as "num_SKs", b.mbytes, ((b.mbytes/part.total::decimal)*100)::decimal(5,2) as pct_of_total, decode(det.max_enc,0,'N','Y') as Enc, a.rows,
      decode( det.n_sortkeys, 0, null , a.unsorted_rows ) as unsorted_rows, decode( det.n_sortkeys, 0, null , a.rows, 0, null, (a.unsorted_rows::decimal(32)/a.rows)*100 )::decimal(5,2) as pct_unsorted
      from ( select db_id, id, name, sum(rows) as rows,
      sum(rows)-sum(sorted_rows) as unsorted_rows from stv_tbl_perm a group by db_id, id, name ) as a
      join pg_class as pgc on pgc.oid = a.id
      join pg_namespace as pgn on pgn.oid = pgc.relnamespace
      left outer join (select tbl, count(*) as mbytes
      from stv_blocklist group by tbl) b on a.id=b.tbl
      inner join ( SELECT attrelid, min(case attisdistkey when 't' then attname else null end) as "distkey",min(case attsortkeyord when 1 then attname else null end ) as head_sort , max(attsortkeyord) as n_sortkeys, max(attencodingtype) as max_enc FROM pg_attribute group by 1) as det
      on det.attrelid = a.id
      inner join ( select tbl, max(Mbytes)::decimal(32)/min(Mbytes) as ratio from
      (select tbl, trim(name) as name, slice, count(*) as Mbytes
      from svv_diskusage group by tbl, name, slice )
      group by tbl, name ) as dist_ratio on a.id = dist_ratio.tbl
      join ( select sum(capacity) as total
      from stv_partitions where part_begin=0 ) as part on 1=1
      where mbytes is not null

  fields:
  - measure: count
    type: count
    detail: detail*

  - dimension: schema
    sql: ${TABLE}.schema

  - dimension: table
    sql: ${TABLE}.table

  - dimension: tableid
    type: number
    sql: ${TABLE}.tableid

  - dimension: distkey
    sql: ${TABLE}.distkey

  - dimension: skew
    type: number
    decimals: 2
    sql: ${TABLE}.skew

  - dimension: sortkey
    sql: ${TABLE}.sortkey

  - dimension: num_sks
    type: number
    sql: ${TABLE}.num_sks

  - dimension: mbytes
    type: number
    sql: ${TABLE}.mbytes

  - dimension: pct_of_total
    type: number
    decimals: 2
    sql: ${TABLE}.pct_of_total

  - dimension: enc
    sql: ${TABLE}.enc

  - dimension: rows
    type: number
    sql: ${TABLE}.rows

  - dimension: unsorted_rows
    type: number
    sql: ${TABLE}.unsorted_rows

  - dimension: pct_unsorted
    sql: ${TABLE}.pct_unsorted

  sets:
    detail:
      - schema
      - table
      - tableid
      - distkey
      - skew
      - sortkey
      - num_sks
      - mbytes
      - pct_of_total
      - enc
      - rows
      - unsorted_rows
      - pct_unsorted

database_management_model.model.lookml

- connection: your_connection

- scoping: true                  # for backward compatibility
- include: "*.view.lookml"       # include all the views
- include: "*.dashboard.lookml"  # include all the dashboards

- explore: stl_load_errors
  persist_for: 0 minutes

# persist is 0 so there is no caching. That would be bad for a showing whats "right now"
# history is also available in this table
- explore: stv_recents
  persist_for: 0 minutes

# colums table  
- explore: tbl_col_summary
  from: custom_table_colmn_info
  persist_for: 0 minutes

# summary table
- explore: tbl_summary
  from: custom_table_summary_info
  persist_for: 0 minutes

stl_load_errors.view.lookml

- view: stl_load_errors
  sql_table_name: pg_catalog.stl_load_errors
  fields:

  - dimension: col_length
    sql: ${TABLE}.col_length

  - dimension: colname
    sql: ${TABLE}.colname

  - dimension: err_code
    type: int
    sql: ${TABLE}.err_code

  - dimension: err_reason
    sql: ${TABLE}.err_reason

  - dimension: filename
    sql: ${TABLE}.filename

  - dimension: line_number
    type: number
    sql: ${TABLE}.line_number

  - dimension: position
    type: int
    sql: ${TABLE}.position

  - dimension: query
    type: int
    sql: ${TABLE}.query

  - dimension: raw_field_value
    sql: ${TABLE}.raw_field_value

  - dimension: raw_line
    sql: ${TABLE}.raw_line

  - dimension: session
    type: int
    sql: ${TABLE}.session

  - dimension: slice
    type: int
    sql: ${TABLE}.slice

  - dimension_group: starttime
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.starttime

  - dimension: tbl
    type: int
    sql: ${TABLE}.tbl

  - dimension: type
    sql: ${TABLE}.type

  - dimension: userid
    type: int
    hidden: true
    sql: ${TABLE}.userid

  - measure: count
    type: count
    drill_fields: [colname, filename, users.id]
# test

stv_recents.view.lookml

- view: stv_recents
  sql_table_name: pg_catalog.stv_recents
  fields:

  - dimension: db_name
    sql: ${TABLE}.db_name

  - dimension: duration
    type: int
    sql: ${TABLE}.duration

  - dimension: pid
    type: int
    sql: ${TABLE}.pid

  - dimension: query
    sql: ${TABLE}.query

  - dimension_group: starttime
    type: time
    timeframes: [time, date, week, month]
    sql: ${TABLE}.starttime

  - dimension: status
    sql: ${TABLE}.status

  - dimension: user_name
    sql: ${TABLE}.user_name

  - dimension: userid
    type: int
    hidden: true
    sql: ${TABLE}.userid

  - measure: count
    type: count
    drill_fields: [db_name, user_name, users.id]