Generating Recursive COPY Commands
Jim_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!
0