vsql/vertica，如何将文本输入文件名复制到目标表中

Question

我必须将一个输入文本文件(text_file.txt)复制到一个表(table_a)。我还需要将输入文件的名称包括到表中。

我的代码是：

\set t_pwd `pwd`
\set input_file '\'':t_pwd'/text_file.txt\'' 

copy table_a
( column1
 ,column2
 ,column3
 ,FileName :input_file
)
from :input_file

最后一行不复制表中的输入文本文件名。如何将输入文本文件的名称复制到表中？(无需手动键入文件名)

Answer 1

解决方案1

对于你的工作来说，这可能不是一个完美的解决方案，但我认为可以完成这项工作：

您可以获取表名并将其存储在一个TBL变量中，然后将此变量添加到将要加载到Vertica的CSV文件中的每一行的末尾。

现在，根据您的CSV文件大小，这可能会消耗相当多的时间和CPU。

export TBL=`ls -1 | grep *.txt` | sed -e 's/$/,'$TBL'/' -i $TBL

示例：

[dbadmin@bih001 ~]$ cat load_data1
1|2|3|4|5|6|7|8|9|10

[dbadmin@bih001 ~]$ export TBL=`ls -1 | grep load*` | sed -e 's/$/|'$TBL'/' -i $TBL

[dbadmin@bih001 ~]$ cat load_data1
1|2|3|4|5|6|7|8|9|10||load_data1

解决方案2

您可以使用DEFAULT CONSTRAINT__，请参阅示例：

1.使用默认约束创建表

[dbadmin@bih001 ~]$ vsql
Password:
Welcome to vsql, the Vertica Analytic Database interactive terminal.

Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit

dbadmin=> create table TBL (id int ,CSV_FILE_NAME varchar(200) default 'TBL');
CREATE TABLE
dbadmin=> \dt
              List of tables
 Schema | Name | Kind  |  Owner  | Comment
--------+------+-------+---------+---------
 public | TBL  | table | dbadmin |
(1 row)

查看默认约束，它具有'TBL‘缺省值

dbadmin=> \d TBL
                                       List of Fields by Tables
 Schema | Table |    Column     |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+---------------+--------------+------+---------+----------+-------------+-------------
 public | TBL   | id            | int          |    8 |         | f        | f           |
 public | TBL   | CSV_FILE_NAME | varchar(200) |  200 | 'TBL'   | f        | f           |
(2 rows)

variables 2.现在设置 COPY variables

• 插入一些数据并将DEFAULT CONSTRAINT值更改为当前的:input_file值。

dbadmin=> \set t_pwd `pwd`
dbadmin=> \set CSV_FILE `ls -1 | grep load*`
dbadmin=> \set input_file '\'':t_pwd'/':CSV_FILE'\''
dbadmin=>
dbadmin=>
dbadmin=> insert into TBL values(1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from TBL;
 id | CSV_FILE_NAME
----+---------------
  1 | TBL
(1 row)

dbadmin=> ALTER TABLE TBL  ALTER COLUMN CSV_FILE_NAME SET DEFAULT :input_file;
ALTER TABLE
dbadmin=> \dt TBL;
              List of tables
 Schema | Name | Kind  |  Owner  | Comment
--------+------+-------+---------+---------
 public | TBL  | table | dbadmin |
(1 row)

dbadmin=> \d TBL;
                                                 List of Fields by Tables
 Schema | Table |    Column     |     Type     | Size |          Default           | Not Null | Primary Key | Foreign Key
--------+-------+---------------+--------------+------+----------------------------+----------+-------------+-------------
 public | TBL   | id            | int          |    8 |                            | f        | f           |
 public | TBL   | CSV_FILE_NAME | varchar(200) |  200 | '/home/dbadmin/load_data1' | f        | f           |
(2 rows)

dbadmin=> insert into TBL values(2);
 OUTPUT
--------
      1
(1 row)

dbadmin=> select * from TBL;
 id |      CSV_FILE_NAME
----+--------------------------
  1 | TBL
  2 | /home/dbadmin/load_data1
(2 rows)

现在，您可以在copy脚本中实现此功能。

示例：

\set t_pwd `pwd`
\set CSV_FILE `ls -1 | grep load*`
\set input_file '\'':t_pwd'/':CSV_FILE'\''
ALTER TABLE TBL  ALTER COLUMN CSV_FILE_NAME SET DEFAULT :input_file;
copy TBL from :input_file DELIMITER '|' DIRECT;

解决方案3

使用LOAD_STREAMS表

示例：

当加载一个表时，给它一个stream name -这样你就可以识别文件名/流名称：

COPY mytable FROM myfile DELIMITER '|' DIRECT STREAM NAME 'My stream name';

*查询load_streams表的方法如下:*

=> SELECT stream_name, table_name, load_start, accepted_row_count, 
   rejected_row_count, read_bytes, unsorted_row_count, sorted_row_count,
   sort_complete_percent FROM load_streams;
-[ RECORD 1 ]----------+---------------------------
stream_name            | fact-13
table_name             | fact
load_start             | 2010-12-28 15:07:41.132053
accepted_row_count     | 900
rejected_row_count     | 100
read_bytes             | 11975
input_file_size_bytes  | 0
parse_complete_percent | 0
unsorted_row_count     | 3600
sorted_row_count       | 3600
sort_complete_percent  | 100

说得通吗？希望这能有所帮助！

Answer 2

如果您不需要完全从vsql内部完成，那么可以稍微作弊一下，然后将逻辑导出到Vertica之外，例如bash：

    FILE=text_file.txt
    (
      while read LINE; do
        echo "$LINE|$FILE"
      done < "$FILE"
    ) | vsql -c 'copy table_a (...) FROM STDIN'

这样你基本上就是COPY FROM STDIN了，在它到达Vertica之前就把文件名添加到每一行。