Vertica之vsql的用法

转载请注明出处，谢谢~
2022-11-20更新：由于图片中涉及到的主机IP、名称、目录等忘了打码，故删减图片

一、连接数据库

命令如下（需切换到vsql所在目录，为了方便，可以把vsql的目录加入环境变量）：

vsql -h 主机IP -d 数据库 -U 用户名 -w 密码 -e -i -C（注意U是大写）

输入\q按回车，退出连接。

二、 执行SQL脚本文件

注意Vertica目前只支持utf8编码，以防乱码，务必将脚本文件编码改为utf8
Windows环境（需切换到vsql所在目录，为了方便，可以把vsql的目录加入环境变量）

vsql -h 主机IP -d 数据库 -U 用户名 -w 密码 -f "D:\tmp\script.sql"

Linux环境（需切换到vsql所在目录，为了方便，可以把vsql的目录加入环境变量）

./vsql -h vaedb -d vae -U username -w password -f "/xxx/ceshi.sql"
此处的vaedb即IP地址，为hosts文件内的映射。Windows的host文件是在C:\Windows\System32\drivers\etc，Linux就是/etc

sql脚本内有变量的情况：

./vsql -h vaedb -d vae -U username -w password -f "vaedb_ceshi.sql" -v acct_month=$month -v file_name="'$file'"

对应的vaedb_ceshi.sql的内容如下：

COPY vae.ceshi_:acct_month FROM LOCAL :file_name DELIMITER E'\007' REJECTED DATA '/log/batch/vaedb_ceshi_err.log' EXCEPTIONS '/log/batch/vaedb_ceshi_exception.log' ENFORCELENGTH NO ESCAPE;
【解析】
$month和$file是shell内的变量，vsql获取变量的值必须先用“-v 变量名”声明变量，sql脚本内用“:变量名”引用。
比如month=202003，file=/bigdata/202003/test202003.txt
那么上面的语句执行时会变成
COPY vae.ceshi_202003 FROM LOCAL '/bigdata/202003/test202003.txt' DELIMITER E'\007' REJECTED DATA '/log/batch/vaedb_ceshi_err.log' EXCEPTIONS '/log/batch/vaedb_ceshi_exception.log' ENFORCELENGTH NO ESCAPE;

【注意】
vsql执行sql脚本会默认跳过错误全部执行，若想要出现错误立即停止，可以在sql脚本最前面加上\set ON_ERROR_STOP on;

每次delete后都要跟个commit太麻烦，若要自动提交，可在sql脚本最前面加上\set AUTOCOMMIT on;

三、数据导入导出

数据导入

特别注意，导入文件只支持UTF-8编码，以Linux环境举例：

1. 连上数据库后

COPY vae.ceshi_ftp FROM LOCAL '/data/ftp_data/ceshi_ftp_001.txt' DELIMITER '|' REJECTED DATA '/log/batch/ceshi_ftp_err.log' EXCEPTIONS '/log/batch/ceshi_ftp_exception.log' ENFORCELENGTH NO ESCAPE; 
【注意】
两个log文件一定要指定，导入出错的时候可以根据这两个log文件查找原因。若导入没报错，这两个log文件不会生成的。

2. 直接执行copy from

./vsql -h vaedb -d vae -U username -w password -c "COPY vae.ceshi_ftp FROM LOCAL '/data/ftp_data/ceshi_ftp_001.txt' DELIMITER '|' REJECTED DATA '/log/batch/ceshi_ftp_err.log' EXCEPTIONS '/log/batch/ceshi_ftp_exception.log' ENFORCELENGTH NO ESCAPE;"

copy from可以使用通配符*

COPY vae.ceshi_ftp FROM LOCAL '/data/ftp_data/ceshi_ftp*dat' DELIMITER ',' REJECTED DATA '/log/batch/ceshi_ftp_err.log' EXCEPTIONS '/log/batch/ceshi_ftp_exception.log' ENFORCELENGTH NO ESCAPE;

可用E’\007’的方式导入十六进制分隔符（0x07）的文件
十六进制分隔符要小于等于0x07，十六进制分隔符可百度ASCII表

./vsql -h vaedb -d vae -U username -w password -c "COPY vae.ceshi_202003 FROM LOCAL '/bigdata/202003/test202003.txt' DELIMITER E'\007' REJECTED DATA '/log/batch/vaedb_ceshi_err.log' EXCEPTIONS '/log/batch/vaedb_ceshi_exception.log' ENFORCELENGTH NO ESCAPE;"

3. 指定字段导入数据

./vsql -h vaedb -d vae -U username -w password -c "COPY vae.ceshi_202003(acctcode,cus_name) FROM LOCAL '/data/ftp_data/ceshi_202003_001.txt' DELIMITER ',' REJECTED DATA '/log/batch/ceshi_202003_err.log' EXCEPTIONS '/log/batch/ceshi_202003_exception.log' ENFORCELENGTH NO ESCAPE;"

4. 从压缩文件导入数据

支持的压缩格式有: BZIP | GZIP | LZO | UNCOMPRESSED
COPY vae.ceshi_compress FROM LOCAL '/data/ftp_data/compress/ceshi_compress_202003.gz' GZIP DELIMITER E'\007' REJECTED DATA '/log/batch/ceshi_compress_202003_err.log' EXCEPTIONS '/log/batch/ceshi_compress_202003_exception.log' ENFORCELENGTH NO ESCAPE;
--文件后缀为tar.gz（tgz）的也可以
COPY vae.ceshi_compress FROM LOCAL '/data/ftp_data/compress/ceshi_compress_202004.tar.gz' GZIP DELIMITER E'\007' REJECTED DATA '/log/batch/ceshi_compress_202004_err.log' EXCEPTIONS '/log/batch/ceshi_compress_202004_exception.log' ENFORCELENGTH NO ESCAPE;

数据导出

特别注意，导出文件只支持UTF-8编码，以Linux环境举例：

./vsql -h vaedb -d vae -U username -w Password -F "," -A -t -o "/data/ftp_data/export/xxxx.txt" -c "select * from …;"
还可以这样写：
echo `/data/vertica/bin/./vsql -h vaedb -d vae -U username -w Password -F "," -A -t -c "select * from …;" > /data/ftp_data/export/xxxx.txt`
此处/data/vertica/bin/为我的vsql所在路径

下面的可以指定十六进制分隔符：

./vsql -h vaedb -d vae -U username -w Password -F $'\a' -A -t -o "/data/ftp_data/export/xxxx.txt" -c "select * from …;"
或 ./vsql -h vaedb -d vae -U username -w Password -P fieldsep=$'\a' -A -t -o "/data/ftp_data/export/xxxx.txt" -c "select * from …;"  (\a用十六进制表示为0x07)

四、 命令行操作

执行sql脚本（以Linux环境举例）
vae=> \i /tmp/file.sql

列出所有表
vae=> \dt

显示表结构
vae=> \d tablename

查看所有表的权限
vae=> \dp 或者\z

查看某张表的权限
vae=> \dp vae.ceshi_ftp

退出数据库
vae=> \q

获取帮助
vae=> \h

切换路径
切换路径：\cd
vae=> \!pwd
/data/vertica/bin
vae=> \cd /data/vertica
vae=> \!pwd
/data/vertica

修改密码

连接数据库时会提示，如果出现The password will expire soon…，即密码快过期

按照它的提示，执行\password

vae=> \password

当密码已过期时，执行连接数据库的命令，会要求你改密码。