카탈로그 쿼리 예제

아래 쿼리들은 카탈로그 테이블에 대한 쿼리를 실행하여 Amazon Redshift 데이터베이스에 대한 유용한 정보를 가져올 수 있는 몇 가지 방법을 보여줍니다.

테이블 ID, 데이터베이스, 스키마 및 테이블 이름 보기

다음은 STV_TBL_PERM 시스템 테이블과 PG_CLASS, PG_NAMESPACE 및 PG_DATABASE 시스템 카탈로그 테이블을 조인하여 테이블 ID, 데이터베이스 이름, 스키마 이름 및 테이블 이름을 반환하는 뷰 정의입니다.

create view tables_vw as
select distinct(stv_tbl_perm.id) table_id
,trim(pg_database.datname)   db_name
,trim(pg_namespace.nspname)   schema_name
,trim(pg_class.relname)   table_name
from stv_tbl_perm
join pg_class on pg_class.oid = stv_tbl_perm.id
join pg_namespace on pg_namespace.oid = pg_class.relnamespace
join pg_database on pg_database.oid = stv_tbl_perm.db_id;

다음은 테이블 ID 117855의 정보를 반환하는 예입니다.

select * from tables_vw where table_id = 117855;

table_id | db_name   | schema_name | table_name
---------+-----------+-------------+-----------
  117855 |       dev | public      | customer  

Amazon Redshift 테이블당 열의 수 나열

다음 쿼리는 일부 카탈로그 테이블을 조인하여 각 Amazon Redshift 테이블에 포함된 열 수를 확인합니다. Amazon Redshift 테이블 이름은 PG_TABLES와 STV_TBL_PERM에 모두 저장됩니다. 가능한 경우 PG_TABLES를 사용하여 Amazon Redshift 테이블 이름을 반환합니다.

이번 쿼리에는 Amazon Redshift 테이블이 포함되지 않습니다.

select nspname, relname, max(attnum) as num_cols
from pg_attribute a, pg_namespace n, pg_class c
where n.oid = c.relnamespace and  a.attrelid = c.oid
and c.relname not like '%pkey'
and n.nspname not like 'pg%'
and n.nspname not like 'information%'
group by 1, 2
order by 1, 2;

nspname | relname  | num_cols
--------+----------+----------
public  | category |        4
public  | date     |        8
public  | event    |        6
public  | listing  |        8
public  | sales    |       10
public  | users    |       18
public  | venue    |        5
(7 rows)

데이터베이스의 스키마 및 테이블 나열

다음은 STV_TBL_PERM을 일부 PG 테이블에 조인하여 TICKIT 데이터베이스의 테이블 목록과 스키마 이름(NSPNAME 열)을 반환하는 쿼리입니다. 그 밖에 각 테이블의 전체 행 수도 반환합니다. 이 쿼리는 시스템에서 다수의 스키마가 똑같은 테이블 이름을 가지고 있을 때 유용합니다.

select datname, nspname, relname, sum(rows) as rows
from pg_class, pg_namespace, pg_database, stv_tbl_perm
where pg_namespace.oid = relnamespace
and pg_class.oid = stv_tbl_perm.id
and pg_database.oid = stv_tbl_perm.db_id
and datname ='tickit'
group by datname, nspname, relname
order by datname, nspname, relname;

datname | nspname | relname  |  rows
--------+---------+----------+--------
tickit  | public  | category |     11
tickit  | public  | date     |    365
tickit  | public  | event    |   8798
tickit  | public  | listing  | 192497
tickit  | public  | sales    | 172456
tickit  | public  | users    |  49990
tickit  | public  | venue    |    202
(7 rows)

테이블 ID, 데이터 형식, 열 이름 및 테이블 이름 나열

다음은 테이블 ID, 테이블 이름, 열 이름, 각 열의 데이터 형식 등 각 사용자 테이블과 테이블 열에 대한 정보를 나열하는 쿼리입니다.

select distinct attrelid, rtrim(name), attname, typname
from pg_attribute a, pg_type t, stv_tbl_perm p
where t.oid=a.atttypid and a.attrelid=p.id
and a.attrelid between 100100 and 110000
and typname not in('oid','xid','tid','cid')
order by a.attrelid asc, typname, attname;

attrelid |  rtrim   |    attname     |  typname
---------+----------+----------------+-----------
  100133 | users    | likebroadway   | bool
  100133 | users    | likeclassical  | bool
  100133 | users    | likeconcerts   | bool
...
  100137 | venue    | venuestate     | bpchar
  100137 | venue    | venueid        | int2
  100137 | venue    | venueseats     | int4
  100137 | venue    | venuecity      | varchar
...

테이블에 있는 각 열의 데이터 블록 수 계산

다음은 STV_BLOCKLIST 테이블을 PG_CLASS에 조인하여 SALES 테이블의 열에 대한 스토리지 정보를 반환하는 쿼리입니다.

select col, count(*)
from stv_blocklist s, pg_class p
where s.tbl=p.oid and relname='sales'
group by col
order by col;

col | count
----+-------
  0 |     4
  1 |     4
  2 |     4
  3 |     4
  4 |     4
  5 |     4
  6 |     4
  7 |     4
  8 |     4
  9 |     8
 10 |     4
 12 |     4
 13 |     8
(13 rows)