Last active
November 21, 2020 11:20
-
-
Save sudopower/599be3ba677f527fe809fcac0d617a55 to your computer and use it in GitHub Desktop.
Install and run vertica database on local and some helpful commands
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment