Page 1 of 1
The COPY command supports wild cards!
Posted: Tue May 01, 2012 8:34 pm
by JimKnicely
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!
Re: The COPY command supports wild cards!
Posted: Tue May 01, 2012 8:59 pm
by id10t
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)
Re: The COPY command supports wild cards!
Posted: Sat May 12, 2012 8:51 am
by 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.
Re: The COPY command supports wild cards!
Posted: Thu Jun 20, 2013 11:39 am
by ssrao
My directory containing more than 1000files.
which command I need to write in copy command
copy mdn.customer from '/home/dbadmin/customer/cust_?' delimiter '|' direct abort on error;
copy mdn.customer from '/home/dbadmin/customer/*.log' delimiter '|' direct abort on error;
Above commands are not working,what command I need to write to load all logs at one time.
Thanks in advance