Generating Recursive COPY Commands

Posted June 13, 2019 by Jim Knicely, Vertica Principal Solution Architect

Copy data
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 directory /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/latest/HTML/Content/Authoring/SQLReferenceManual/Statements/COPY/COPY.htm

Have fun!