Generating Recursive COPY Commands

Jim_Knicely

Recently a friend mentioned she wanted to load all of the files using the COPY command where all the data files were located in many, many sub-directories..

You can load all of the files in a single command by looping through each of the sub-directories.

Example:

I have the following files in sub-directories under the parent director /home/dbadmin/base:

dbadmin=> \! find /home/dbadmin/base -mindepth 1 -type f
/home/dbadmin/base/dir1/dir1.txt
/home/dbadmin/base/dir1/dir2_b/dir2_b.txt
/home/dbadmin/base/dir1/dir2/dir3_a/dir3_aa/dir3_aaa/dir3_aaa.txt
/home/dbadmin/base/dir1/dir2/dir3_a/dir3_aa/dir3_aaa/dir3_aaa2.txt
/home/dbadmin/base/dir1/dir2/dir3_a/dir3_aa/dir3_aa.txt
/home/dbadmin/base/dir1/dir2/dir3_a/dir3_a.txt
/home/dbadmin/base/dir1/dir2/dir3/dir3.txt
/home/dbadmin/base/dir1/dir2/dir2.txt
/home/dbadmin/base/dir1/dir2_a/dir2_a.txt

I can load them all by doing the following:

dbadmin=> \! for d in `find /home/dbadmin/base -mindepth 1 -type d`; do vsql -ec "COPY t FROM '$d/*.txt';"; done
COPY t FROM '/home/dbadmin/base/dir1/*.txt';
Rows Loaded
-------------
           1
(1 row)

COPY t FROM '/home/dbadmin/base/dir1/dir2_b/*.txt';
Rows Loaded
-------------
           1
(1 row)

COPY t FROM '/home/dbadmin/base/dir1/dir2/*.txt';
Rows Loaded
-------------
           1
(1 row)

COPY t FROM '/home/dbadmin/base/dir1/dir2/dir3_a/*.txt';
Rows Loaded
-------------
           1
(1 row)

COPY t FROM '/home/dbadmin/base/dir1/dir2/dir3_a/dir3_aa/*.txt';
Rows Loaded
-------------
           1
(1 row)

COPY t FROM '/home/dbadmin/base/dir1/dir2/dir3_a/dir3_aa/dir3_aaa/*.txt';
Rows Loaded
-------------
           4
(1 row)

COPY t FROM '/home/dbadmin/base/dir1/dir2/dir3/*.txt';
Rows Loaded
-------------
           1
(1 row)

COPY t FROM '/home/dbadmin/base/dir1/dir2_a/*.txt';
Rows Loaded
-------------
           1
(1 row)

Helpful Link:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Statements/COPY/COPY.htm

Have fun!