The COPY command supports wild cards!

[knicely87]

The documentation doesn't mention this, but the COPY command supports wild cards...

Example:

Code: Select all

COPY some_table FROM '/path/to/data/ClickStreamData_201?/*' DIRECT...

When would this be useful? For example, you have many tables storing history including folders for the years 2010, 2011, 2012, etc. Each "year" folder contains tables for each month. You can load in separate streams each year or month and they will load in serial - one after other.

PS. Thanks to vertica-forums user sKwa for this handy tip!

[sKwa]

Example:
[*] Create data for example:

Code: Select all

test_db=> \! mkdir -p /tmp/vertica
test_db=> \! echo -e "one\ntwo\nthree" > /tmp/vertica/data_01.txt
test_db=> \! echo -e "one\ntwo\nthree" > /tmp/vertica/data_02.txt
test_db=> \! echo -e "one\ntwo\nthree" > /tmp/vertica/data_03.txt
test_db=> \! echo -e "one\ntwo\nthree" > /tmp/vertica/data_04.txt


[*]Show data

Code: Select all

test_db=> \! ls /tmp/vertica
data_01.txt  data_02.txt  data_03.txt  data_04.txt
test_db=> \! cat /tmp/vertica/data_01.txt
one
two
three


[*]Copy data:

Code: Select all

test_db=> create table tbl (var varchar(5));
CREATE TABLE
test_db=> copy tbl from '/tmp/vertica/data_0?.txt';
 Rows Loaded
-------------
          12
(1 row)

test_db=> copy tbl from '/tmp/vertica/data*';
 Rows Loaded
-------------
          12
(1 row)


[DataWhisperer]

Note: the WILDCARD (*) will only allow up to 50 files in a single directory to prevent overloading. It doesn't say this in the documentation, but when you try to COPY several thousand files with a wildcard, it complains.

Though, it is possible to loop through a directory (even recursively) with a bash script and use vsql to and a parameterized query and to load the files.