はじめに

catコマンドやgrepコマンドなどの標準出力の結果を、Verticaのテーブルにロードする方法をご紹介します。

用途

この方法は以下の様な場面で役に立ちます。

・一時テーブルや中間テーブル、集計テーブルを作成する場合
・テーブルを再構築したい場合
・他のデータソースからデータを取り込みたい場合

構文と実行例

下記構文の「SELECT文」内に元テーブルを指定し、「テーブル名」に別テーブルを指定します。

＜標準出力のコマンド＞ | vsql -w ＜パスワード＞ -c “copy ＜テーブル名＞ from STDIN ＜各種オプション＞”

サンプルデータの確認

$ cat test.csv
"1","A","あ"
"2","B","い"
"3","C","う"
"4","D","え"
"5","E","お"

テスト用テーブルの作成

dbadmin=> create table tab1 (col1 varchar(1),col2 varchar(1),col3 varchar(3));
CREATE TABLE

dbadmin=> \d tab1
                                   List of Fields by Tables
 Schema | Table | Column |    Type    | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+------------+------+---------+----------+-------------+-------------
 public | tab1  | col1   | varchar(1) |    1 |         | f        | f           |
 public | tab1  | col2   | varchar(1) |    1 |         | f        | f           |
 public | tab1  | col3   | varchar(3) |    3 |         | f        | f           |
(3 rows)

標準出力の結果をテーブルにロード

テスト用CSVをcatしてロード
$ cat test.csv |vsql -w ssbm -c "copy tab1 from STDIN delimiter ',' enclosed by '\"' DIRECT"

ロードした結果を検索
$ vsql -w ssbm -c 'select * from tab1'
 col1 | col2 | col3
------+------+------
 1    | A    | あ
 2    | B    | い
 3    | C    | う
 4    | D    | え
 5    | E    | お
(5 rows)

作成したテーブルにテスト用のデータが正常にロードされました。

応用例

このやり方を応用すると、特定列のデータを任意の列にロードすることもできます。

特定列のデータを標準出力させてロード

1列目と3列目のデータを抽出してロード
$ cat test.csv |awk -F, '{print $1","$3}' |vsql -w ssbm -c "copy tab1(col1,col3) from STDIN delimiter ',' enclosed by '\"' DIRECT"

$ vsql -w ssbm -c 'select * from tab1'
 col1 | col2 | col3
------+------+------
 1    |      | あ
 2    |      | い
 3    |      | う
 4    |      | え
 5    |      | お
(5 rows)

1列目のデータと3列目のデータがそれぞれ1列目と3列目に正常にロードされました。

特定行＋特定列のデータを標準出力させてロード

「5」のデータを含む行を抽出し、1列目と3列目のデータをロード
$ grep "5" test.csv |awk -F, '{print $1","$3}' |vsql -w ssbm -c "copy tab1(col1,col3) from STDIN delimiter ',' enclosed by '\"' DIRECT"

$ vsql -w ssbm -c 'select * from tab1'
 col1 | col2 | col3
------+------+------
 5    |      | お
(1 rows)

「5」のデータが含まれる行を抽出し、1列目と3列目のデータが正常にロードされました。

補足

今回はCSVファイルの標準出力結果をロードする例でしたが、さらに応用すると、他のノードで稼働している他社製データベースの標準出力の結果をVerticaに取り込むといったことも可能です。

コマンド例＜他社データベースからの標準出力の結果＞ | vsql -h ＜Vertica稼働ノード＞ -U ＜ユーザ名＞ -w ＜パスワード＞ -c “COPY ＜テーブル名＞ FROM STDIN ＜各種オプション＞”

ちょっとしたテストなどを行う際にも利用できますので、是非お試しください。

参考情報

データロードに関するTipsはこちらの記事に纏めています。

データロードのまとめ
https://www.ashisuto.co.jp/cm/analytics-database/load-summary.html

検証バージョンについて

この記事の内容はVertica 9.1で確認しています。