We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Generating Recursive COPY Commands — Vertica Forum

Generating Recursive COPY Commands

Jim_KnicelyJim_Knicely - Select Field - Administrator

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!

Sign In or Register to comment.