Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Copy command using filter

Hi,

 

I have a requirement where i need to restrict the data in copy command. suppose i hve 1000 records in my csv file that i will be loadinh using copy command, so i want that with this copy command i can load the data like top 500 from bottom.

 

That is from 500 to 1000.

 

Now if my record got increased by 50 that is total 1050 record the 550 record to be populated.

 

Is their any solution for this????

Comments

  • If requirement is only to skip top 500 records everytime , You can use SKIP with copy command , it will  skip top 500 records always means 1 -500 .

     

     

    copy abc from /temp/abc.txt delimiter ';' SKIP 500  ;

    --> above command will skip top 500 records .

     

     

    copy abc from /temp/abc.txt delimiter ';' SKIP bytes 500  ;

    --> This command will skip 500 bytes from starting of file.

     

    Thanks :

    Abhi

     

  •  You can do this using pipes in linux and the stdin copy option.

     

    - just pipe the output of the tail or any linux command that can limit the numbers of rows, form head or tail to your copy command.

     - you can put more logic in linux scripting then in the copy command.

     

     See example:

    [[email protected]~]$ cat /tmp/file.csv
    1,1,2
    2,1,2
    3,1,2
    4,1,2
    [[email protected]~]$ tail -2 /tmp/file.csv
    3,1,2
    4,1,2
    [[email protected]~]$ tail -2 /tmp/file.csv |vsql -d db -U dbadmin -c "COPY test FROM LOCAL STDIN DELIMITER ',' ;"
    Password:
    Rows Loaded
    -------------
    2
    (1 row)

    [[email protected]~]$ vsql -d db -U dbadmin -c "select * from test;"
    Password:
    col1 | col2 | col3
    ------+------+------
    3 | 1 | 2
    4 | 1 | 2
    (2 rows)

     

  • Hi, My requirement is i have to keep last 500 record from current date. So, every time this record will change.I have to keep only top 600 record for each day.So using skip will skip top 600 record itself but i need that only top 600 record to keep using copy command.
  • Hi, Thanks for the help, this looks like now i can solve my problem. But if we use this command the every time the result of copy command is getting stored in some cache or some other place and when next time i am running the same copy command it is taking the earlier data also. Like first time when i am running the command and is populating 10 records then i deleted these records and re ran the copy cammand and this time it is populating 20 records, 10 from current record and 10 from last record. Can u help me over this as i am new to linux
  •  not sure i understand your requirment ! 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.