Install and run vertica database on local and some helpful commands

terminal

docker pull dataplatform/docker-vertica
docker build -t dataplatform/docker-vertica .
docker run -p 5433:5433 -d -v /data/vertica/vertica_data:/home/dbadmin/docker dataplatform/docker-vertica
vsql -hlocalhost -Udbadmin

#vertica: access via cli local
/opt/vertica/bin/vsql -Udbadmin -p5433 -hlocalhost

#vertica: check structure of table
select EXPORT_OBJECTS('','schema.table_name');

#vertica: grant all privileges on table
grant all privileges on table table_name to username;

#vertica: list all schemas
select schema_id, schema_name, u.user_name as owner, create_time, is_system_schema from v_catalog.schemata s join v_catalog.users u on s.schema_owner_id = u.user_id order by schema_name;

#vertica: list all tables in schema
select table_schema,table_name,create_time from v_catalog.tables where table_schema = 'schema_name' order by table_name;

#vertica: access via cli live
vsql -h vertica_host_name -U username -w password -p 5433

#vertica: export data to csv file 
/opt/vertica/bin/vsql -h hostname -U username -w password -F $',' -At -o /path/to/store/file -c "query"
example: /opt/vertica/bin/vsql -h hostname -Udbadmin -w password, -F $',' -At -o /mnt/data/tmp/result.csv -c "select * from schema.table_name"

#vertica: import data from csv with header to vertica table (if null values are \N in csv)
COPY table_name FROM LOCAL 'path/to/file' NULL AS '\N' DELIMITER E',' SKIP 1

#vertica: locked tables
SELECT object_name, lock_scope FROM LOCKS;

#vertica: search for table name with column name
SELECT table_schema, table_name, column_name FROM v_catalog.columns WHERE column_name = 'gaid';
example:SELECT table_schema, table_name, column_name FROM v_catalog.columns WHERE column_name = 'cpp_id';

#vertica: check running queries and sessions
SELECT user_name, session_id, current_statement, statement_start FROM v_monitor.sessions;

#vertica: kill session
SELECT CLOSE_SESSION('session_id)