\d 메타 명령어 (Meta-Commands)

00 개요

• vertica가 설치 된 서버에서 vertica를 vsql로 접속 시 \d 메타 명령어 사용 가능
•   
• # /opt/vertica/bin/vsql -U 사용자이름 -w 비밀번호 Welcome to vsql 사용자이름=>
• 예를 들면,

• # /opt/vertica/bin/vsql -U dbadmin -w si
  Welcome to vsql  
  dbadmin=>

• database를 생성하고 table을 생성한 후 /d 명령어를 사용하여 database에 관련된 정보(예, table 목록)들을 조회 가능

01 \d 메타 명령어

• Vertica는 여러 가지 \d 명령어들을 지원함
• 이 명령어들은 database 객체의 다양한 정보를 반환

0. 문법

\dCommand [[schema.]pattern]

1. Arguments (인자)

• 대부분의 \d 명령어에 추가적으로 string pattern arguments 함께 사용 가능 (사용 시 결과를 필터링하는 역할을 함)
• 패턴은 can optionally be qualified by a schema name
• 종류: schema, pattern

1) schema

• 대부분의 \d commands에 사용 가능
• 사용 시 해당 schema 안에 있는 database 객체만 반환
• 예)
  • the following \dp command obtains privileges info for all V_MONITOR tables that contain the string 'resource':

  • => \dp V_MONITOR.*resource*
                    Access privileges for database "dbadmin"
     Grantee | Grantor | Privileges |  Schema   |            Name
    ---------+---------+------------+-----------+----------------------------
     public  | dbadmin | SELECT     | v_monitor | resource_rejections
     public  | dbadmin | SELECT     | v_monitor | disk_resource_rejections
     public  | dbadmin | SELECT     | v_monitor | resource_usage
     public  | dbadmin | SELECT     | v_monitor | resource_acquisitions
     public  | dbadmin | SELECT     | v_monitor | resource_rejection_details
     public  | dbadmin | SELECT     | v_monitor | resource_pool_move
     public  | dbadmin | SELECT     | v_monitor | host_resources
     public  | dbadmin | SELECT     | v_monitor | node_resources
     public  | dbadmin | SELECT     | v_monitor | resource_queues
     public  | dbadmin | SELECT     | v_monitor | resource_pool_status
    (10 rows)

2) pattern

• 사용 시 해당 string 조건과 일치하는 database 객체만 반환
• pattern string은 1개 이상의 wildcard 포함 가능
• wildcard:

wildcard	설명
?	any single character
*	0개 이상 character(s)

• 예)
  • the following \dt command returns tables that start with the string 'store':

  • => \dt store*
                         List of tables
     Schema |       Name        | Kind  |  Owner  | Comment
    --------+-------------------+-------+---------+---------
     public | store_orders      | table | dbadmin |
     public | store_orders_2018 | table | dbadmin |
     public | store_overseas    | table | dbadmin |
     store  | store_dimension   | table | dbadmin |
     store  | store_orders_fact | table | dbadmin |
     store  | store_sales_fact  | table | dbadmin |
    (6 rows)

 

2. \d 명령어

명령어	반환
\d	- Unqualified by a pattern argument: all tables with their schema names, owners, comments - Qualified by a pattern argument: all matching tables, all columns in each table w/ details about each column (예: data type, size, default value 등)
\df	all function names, function return data type, function argument data type, procedure names, arguments for all procedures that are available to the user
\dj	all projections showing the schema, projection name, owner, node - returned rows include superprojections, live aggregate projections, Top-K projections, projections w/ expressions
\dn	schema names, schema owner
\dp	a summary of privileges on all objects in system table V_CATALOG.GRANTS: grantee, grantor, privileges, schema, object name (equivalent to \z)
\dS	- Unqualified by a pattern argument: all V_CATALOG, V_MONITOR system tables - To obtain system tables for just one schema, qualify the command with the schema name, as follows: \dS { V_CATALOG | V_MONITOR }.*
\ds	sequences, their parameters
\dT	all data types that Vertica supports - 주의: \dT returns no results if qualified with a pattern argument
\dt	- Unqualified by a pattern argument: unqualified \d 명령어와 같은 정보 - Qualified by a pattern argument: matching tables w/ the same level of detail as an unqualified \dt command
\dtv	tables, views
\du	database users, whether they are superusers
\dv	- Unqualified by a pattern argument: all views w/ their schema names, owners, comments - Qualified by a pattern argument: all matching views, columns in each view w/ each column's data type, size

3. 사용 예시

dbadmin=> \dt
                  List of tables
 Schema |     Name     | Kind  |  Owner  | Comment 
--------+--------------+-------+---------+---------
 public | test_table02 | table | dbadmin | 
 public | test_table03 | table | dbadmin | 
(2 rows)

참조

1. https://www.vertica.com/docs/9.3.x/HTML/Content/Authoring/ConnectingToVertica/vsql/Meta-Commands/DPATTERNMeta-commands.htm?tocpath=Connecting%20to%20Vertica%7CUsing%20vsql%7CMeta-Commands%7C_____3 
2.  
3.  
4.