Hi,
I have to load data from a csv file to a table in vertica DB.
The mapping is like the 2nd field in the csv file should be loaded to the 3rd column in the table.
Is it possible to do this using COPY command or in any other way in Vertica?
Thanks in advance
Using the COPY command
Moderator: NorbertKrupa
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Using the COPY command
Hi,
Welcome to the forums! Check out the FILLER option of the COPY command. You can use it to skip columns.
For example:
See how the data is shifted one column to the right?
Welcome to the forums! Check out the FILLER option of the COPY command. You can use it to skip columns.
For example:
Code: Select all
dbadmin=> create table test_data (a varchar(1), b varchar(1), c varchar(1), d varchar(1), e varchar(1), f varchar(1));
CREATE TABLE
dbadmin=> \! cat /home/dbadmin/test_data.txt
a|b|c|d|e
dbadmin=> copy test_data (af filler varchar(1), bf filler varchar(1), cf filler varchar(1), df filler varchar(1), ef filler varchar(1), b as af, c as bf, d as cf, e as df, f as ef) from '/home/dbadmin/test_data.txt';
Rows Loaded
-------------
1
(1 row)
dbadmin=> select * from test_data;
a | b | c | d | e | f
---+---+---+---+---+---
| a | b | c | d | e
(1 row)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: Using the COPY command
Hello chithu21,
You can refer this link for loading a CSV file.
http://www.vertica-forums.com/viewtopic ... ller#p3110
Hope this helps..
You can refer this link for loading a CSV file.
http://www.vertica-forums.com/viewtopic ... ller#p3110
Hope this helps..
Re: Using the COPY command
Thank you all
I need a single row to be loaded with data from multiple files.
Is it possible?
I need a single row to be loaded with data from multiple files.
Is it possible?
Re: Using the COPY command
#chithu21
I don't see the requirement of a NOT NULL column in the table schema, if you don't want to populate it.
You can't skip the NOT NULL columns while loading data.
Instead you can try and follow this approach
1. You need to have two tables one as a stage and one as a target
2. Set the all the columns to NULL in stage and set the actual NOT NULL columns same in Target table
3. Then while loading use COPY and load into stage table
4. Then load target table according to your calculated values using INSERT INTO TABLE SELECT AS statement.
OR
if you have a empty string while loading the NOT NULL column, try populating an empty string with some other value using the NULL option in COPY command
Hope this helps..
I don't see the requirement of a NOT NULL column in the table schema, if you don't want to populate it.
You can't skip the NOT NULL columns while loading data.
Instead you can try and follow this approach
1. You need to have two tables one as a stage and one as a target
2. Set the all the columns to NULL in stage and set the actual NOT NULL columns same in Target table
3. Then while loading use COPY and load into stage table
4. Then load target table according to your calculated values using INSERT INTO TABLE SELECT AS statement.
OR
if you have a empty string while loading the NOT NULL column, try populating an empty string with some other value using the NULL option in COPY command
Hope this helps..