测试用例:导出test业务用户t_jingyu表中的数据。
一、初始化测试环境
业务用户test下创建t_jingyu测试表,并初始化一些数据.
参考《Vertica 业务用户指定资源池加载数据》构建初始化测试环境。
二、导出数据
2.1 vsql命令说明帮助
[dbadmin@vertica1 ~]$ vsql --help This is vsql, the Vertica Analytic Database interactive terminal. Usage: vsql [OPTIONS]... [DBNAME [USERNAME]] General options: -d DBNAME specify database name to connect to (default: "dbadmin") -c COMMAND run only single command (SQL or internal) and exit -f FILENAME execute commands from file, then exit -l list available databases, then exit -v NAME=VALUE set vsql variable NAME to VALUE -X do not read startup file (~/.vsqlrc) --help show this help, then exit --version output version information, then exit Input and output options: -a echo all input from script -e echo commands sent to server -E display queries that internal commands generate -q run quietly (no messages, only query output) -o FILENAME send query results to file (or |pipe) -n disable enhanced command line editing (readline) -s single-step mode (confirm each query) -S single-line mode (end of line terminates SQL command) Output format options: -A unaligned table output mode (-P format=unaligned) -b beep on command completion -H HTML table output mode (-P format=html) -t print rows only (-P tuples_only) -T TEXT set HTML table tag attributes (width, border) (-P tableattr=) -x turn on expanded table output (-P expanded) -P VAR[=ARG] set printing option VAR to ARG (see \pset command) -F STRING set field separator for unaligned output (default: "|") (-P fieldsep=) -R STRING set record separator (default: newline) (-P recordsep=) Connection options: -B SERVER:PORT set connection backup server/port (default: not set) -C enable connection load balancing (default: not enabled) -h HOSTNAME database server host or socket directory (default: "local socket") -p PORT database server port (default: "5433") -U NAME database user name (default: "dbadmin") -W prompt for password (should happen automatically) -w PASSWORD database user's password -k KRB SERVICE Kerberos service name (default: "vertica") -K KRB HOST Kerberos host name For more information, type "\?" (for vsql commands) from within vsql, or consult the vsql section in the Vertica Analytic Database documentation. |
2.2 导出全表数据
根据vsql参数说明,确定本次导出表数据的命令:
vsql -Utest -wtestpwd -At -F'|' -c "select * from t_jingyu" -o /tmp/t_jingyu_expdata.dat
最终实现将test用户下t_jingyu的数据导出到文件/tmp/t_jingyu_expdata.dat中。
2.3 导出符合指定条件的表数据
可以指定where条件,只导出符合条件的数据,例如:
vsql -Utest -wtestpwd -At -F'|' -c "select * from t_jingyu where col1=1" -o /tmp/t_jingyu_expdata_col1.dat
这样就只导出col1=1的t_jingyu表中数据到文件/tmp/t_jingyu_expdata_col1.dat中。
三、验证
查看表数据的导出文件/tmp/t_jingyu_expdata.dat内容:
[dbadmin@vertica1 tmp]$ more /tmp/t_jingyu_expdata.dat
4|martin|2014-06-20 00:00:00
1|jingyu|2014-06-19 00:00:00
5|靖宇|2014-06-19 00:00:00
3|alfred|2014-05-20 00:00:00
2|xiaobei|2014-06-20 00:00:00
查看表数据的导出文件/tmp/t_jingyu_expdata_col1.dat内容:
[dbadmin@vertica1 tmp]$ more /tmp/t_jingyu_expdata_col1.dat
1|jingyu|2014-06-19 00:00:00