vertica使用vsql导数据

71 阅读 0 评论 47 点赞

我是靠谱客的博主默默蓝天，最近开发中收集的这篇文章主要介绍vertica使用vsql导数据，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

vertica用vsql导出数据

要导出的表：
dbadmin=> select count(*) from test.t1;
count
-------
9998
(1 row)

dbadmin=> select * from test.t1;
id |   name   | b
------+----------+---
    1 | mingshuo |
    2 | mingshuo |
    3 | mingshuo |
    4 | mingshuo |
    5 | mingshuo |
    6 | mingshuo |
    7 | mingshuo |
    8 | mingshuo |
    9 | mingshuo |
   10 | mingshuo |
   11 | mingshuo |
   12 | mingshuo |
   13 | mingshuo |
   14 | mingshuo |
以上只显示部分数据。

目标：将test.t1表的id<2000的数据行导出
使用vsql导出：
vsql -Udbadmin -woracle -At -F'|' -c "select * from test.t1 where id<2000" -o /home/dbadmin/testt1.dat

-U指执行导出动作的用户；
-w指执行到处动作用户的密码；
-t只打印数据行
-c执行命令，然后退出
-o文件输出位置

查看部分输出文件：
[dbadmin@verticatest ~]$ more testt1.dat
1|mingshuo|
2|mingshuo|
3|mingshuo|
4|mingshuo|
5|mingshuo|
6|mingshuo|
7|mingshuo|
8|mingshuo|
9|mingshuo|

此外导数据还可以使用Parallel Export函数，这是一个用户自定义的函数（UDF）。这个函数可以同时在集群的所有的节点导出数据。它可以将数据导出到外部文件，也可以导出到外部命令里。导出到外部命令指的是vertica可以通过这个函数直接将文件推送到hadoop里面去，当然需要配置Hadoop客户端和网络。

附vsql参数：
[dbadmin@verticatest ~]$ 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)
-Q              turn on trailing record separator (-P trailingrecordsep)
-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:
-4              prefer IPv4 addresses when resolving hostnames
-6              prefer IPv6 addresses when resolving hostnames
-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")
-k KRB SERVICE kerberos service name (default: "vertica")
-K KRB HOST     kerberos host name
-m SSLMODE      SSL mode (require, prefer, allow or disable; default: prefer)
-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

For more information, type "?" (for vsql commands)
from within vsql, or consult the vsql section in
the Vertica Analytic Database documentation.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31480688/viewspace-2157048/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31480688/viewspace-2157048/