Remove column header info. in out file

Moderator: NorbertKrupa

Post Reply
User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

Remove column header info. in out file

Post by Jbaskin » Mon Jun 11, 2012 12:52 pm

Hi all, I hope everyone had a nice weekend.

Can someone help me figure out how to not include header info. in an out file when running a select statement via the vsql command line?

For instance, in the following example I don't want the column header information to be included in the out file. I just want the two records returned from the select...

Code: Select all

vert1$ vsql -U dbadmin -c "select * from no_out" -o /usr/home/dbadmin/test.txt
vert1$ cat /usr/home/dbadmin/test.txt
 num |                               text
-----+-------------------------------------------------------------------
   1 | I just want this data
   2 | I just want this data too, not the column header in the out file!
(2 rows)
Thanks!

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Remove column header info. in out file

Post by id10t » Mon Jun 11, 2012 2:13 pm

Hi Jbaskin!


Meta-command: "-At"

  • -A --no-align switches to unaligned output mode. (The default output mode is aligned.)
  • -t --tuples-only disables printing of column names, result row count footers, and so on. This is equivalent to the \t command.
  • -F separator --field-separator separator specifies the field separator for unaligned output (default: "|") (-P fieldsep=). (See -A --no-align.) This is equivalent to \pset fieldsep or \f.

Code: Select all

$ vsql -c "select * from BackUpTable limit 2"; 
         id         | val 
--------------------+-----
 769262974800598314 | ebc
 943701414829961214 | abc
(2 rows)

Code: Select all

$ vsql -A -c "select * from BackUpTable limit 2"; 
id|val
769262974800598314|ebc
943701414829961214|abc
(2 rows)

Code: Select all

$ vsql -At -c "select * from BackUpTable limit 2"; 
769262974800598314|ebc
943701414829961214|abc

Code: Select all

$ vsql -At -F '===' -c "select * from BackUpTable limit 2";
769262974800598314===ebc
943701414829961214===abc

rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Re: Remove column header info. in out file

Post by rajasekhart » Tue Jun 12, 2012 8:46 am

Daniel,

Thats a very good explanation from you about file generation without headers.

Regarding this im having a small doubt.

Once if i have generated a file with such field separator ('===') , could i load the generated file into a table using copy command (placing delimiter as '===' ) ?

In copy command , we have to specify the delimiter as '===' in order to load the file which we have generated.

Eg: copy tablename from '/home/dbadmin/filename.txt' delimiter '===' exceptions '/home/dbadmin/filename.txt' rejected data '/home/dbadmin/filename.txt' ;

When i tried it, it is showing an error, that the delimiter should contain only a single character instead of three('===')

1.Do we have any chance to allow delimiter to have more than a single character?

2. And what is the meaning of "Escape As" in copy command? what is its significance? can u plz explain me with an example..

Thanks,
Raj
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Remove column header info. in out file

Post by id10t » Tue Jun 12, 2012 12:00 pm

Hi rajasekhart !
1.Do we have any chance to allow delimiter to have more than a single character?
Example for FILLER:
  • Table

    Code: Select all

    CREATE TABLE public.DelimiterFillerExample
    (
        col1 varchar(80),
        col2 varchar(80),
        col3 varchar(80)
    );
    
  • DATA:

    Code: Select all

    value1===value2===foo
    foo===bar===baz
    egg===spam===val3
    
  • Copy command

    Code: Select all

    test_db=> copy DelimiterFillerExample (f filler varchar(64000), col1 as SPLIT_PART(f,'===',1), col2 as SPLIT_PART(f,'===',2), col3 as SPLIT_PART(f,'===',3)) from stdin;

    Code: Select all

    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> value1===value2===foo
    >> foo===bar===baz
    >> egg===spam===val3
    >> \.
    
  • Output:

    Code: Select all

    test_db=> select * from DelimiterFillerExample;
      col1  |  col2  | col3 
    --------+--------+------
     value1 | value2 | foo
     foo    | bar    | baz
     egg    | spam   | val3
    (3 rows)
    
2. And what is the meaning of "Escape As" in copy command? what is its significance? can u plz explain me with an example..
If you have data that contains char sequences like '\n','\t' or '\r' and you want to load as is. How to do it?
Example:
  • Data:

    Code: Select all

    '1',foo 
    '2','bar'
    3,'baz' 
    4,egg   
    '5',\n\r
    6,\t\v
    
    ___
    data mixed - some values enclosed by `'` some not but ENCLOSED BY solves it like a charm
  • Table:

    Code: Select all

    CREATE TABLE public.EscapeExample
    (
        id int,
        val varchar(10)
    );
    
  • Copy command:

    Code: Select all

    test_db=> copy EscapeExample from stdin delimiter ',' direct ENCLOSED BY  E'\'' ESCAPE AS E'\001';
    

    Code: Select all

    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.
    >> '1',foo 
    >> '2','bar'
    >> 4,egg   
    >> 3,'baz' 
    >> '5',\n\r
    >> 6,\t\v
    >> \.
    
  • Output:

    Code: Select all

    test_db=> select * from EscapeExample;
     id |  val   
    ----+--------
      1 | foo 
      2 | bar
      4 | egg   
      5 | \n\r
      6 | \t\v
    (5 rows)
    
    ---
    compare data with output.

id10t
GURU
GURU
Posts: 732
Joined: Mon Apr 16, 2012 2:44 pm

Re: Remove column header info. in out file

Post by id10t » Tue Jun 12, 2012 12:03 pm

PS
Loading UTF-8 Format Data

Also you can use linux utilities and copy from STDIN or PIPE:
  • Table:

    Code: Select all

    CREATE TABLE public.PipeExample
    (
        col1 char(3),
        col2 char(3),
        col3 char(3)
    );
    
  • Data:

    Code: Select all

    dbadmin@suse:~> cat > pipe_example.data
    foo===bar===baz
    egg===qux===tux
    etc===bin===var
    dbadmin@suse:~> sed 's/===/|/g' pipe_example.data
    foo|bar|baz
    egg|qux|tux
    etc|bin|var
    
  • Load data

    Code: Select all

    dbadmin@suse:~> sed 's/===/|/g' pipe_example.data |   /opt/vertica/bin/vsql -c "copy PipeExample from STDIN direct delimiter '|'"
  • Output:

    Code: Select all

    dbadmin@suse:~> /opt/vertica/bin/vsql -c "select * from PipeExample"
     col1 | col2 | col3 
    ------+------+------
     egg  | qux  | tux
     etc  | bin  | var
     foo  | bar  | baz
    (3 rows)
    

User avatar
Jbaskin
Intermediate
Intermediate
Posts: 61
Joined: Sat Jan 28, 2012 1:21 pm

Re: Remove column header info. in out file

Post by Jbaskin » Sat Jun 16, 2012 12:33 pm

As always, thanks for the great help on this guys!

vissu220985
Newbie
Newbie
Posts: 2
Joined: Fri Jan 11, 2013 5:39 am

Re: Remove column header info. in out file

Post by vissu220985 » Thu Feb 07, 2013 5:22 pm

In Vertica 6 do this (I don't know if this is available in earlier versions):

1. \pset tuples_only
2. \o file_path/file_name.sql
3. select statement
4. \o
5. \i file_path/file_name.sql

Post Reply

Return to “vSQL”