Rows Processed not output when using COPY STDIN

bmurrellbmurrell Community Edition User

I usually use vsql with -i -e -c, but i'm doing a COPY from STDIN and it's not showing the output.
It works for regular commands if streaming the commands through a pipe, but I can't do that if I'm streaming in the data and commands are processed from -c option.
e.g. cat | vsql -i -e -c "COPY FROM STDIN PARSER FJSONPARSER();"
There is another issue where it only reports the last command processed, not all commands.

is not a file, and i don't have the space to create a temporary file to process, so I need to stream it in.

Tagged:

Best Answer

  • s_crossmans_crossman Vertica Employee Employee
    Answer ✓

    Hi,

    For what it's worth the copy from STDIN doesn't produce # rows output in it's most raw form as shown below. So this is unrelated as to whether it's being executed in a shell script or not.

    VMart=> COPY public.test FROM '/tmp/test.dat';

    Rows Loaded

           1
    

    (1 row)
    Time: First fetch (1 row): 17.485 ms. All rows formatted: 17.556 ms
    VMart=> INSERT INTO public.test (time) VALUES (CURRENT_TIMESTAMP);

    OUTPUT

      1
    

    (1 row)
    Time: First fetch (1 row): 10.660 ms. All rows formatted: 10.689 ms
    VMart=> COPY public.test FROM STDIN;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.

    test
    .

    Time: First fetch (0 rows): 5377.843 ms. All rows formatted: 5377.844 ms

    I found an old bug 29875 related to this. It's still open and there was some discussion about what the correct behaviour should be. There were argumnets for consistency across all copy commands, and arguments that in a shell script output is suppressed. So unresolved to date.

    It happened to note that copy .. LOCAL STDIN does output excepted rows.
    E.g. echo "$DT" | vsql -U dbadmin -w XXXXX -e -i -c "COPY public.test FROM LOCAL STDIN;"
    For this use case the "LOCAL" shouldn't change behaviour of the copy.

    It cited this as the use case, the STDIN being from a cat of a file
    $ cat mytable.csv | vsql -c "copy mytable from LOCAL STDIN DELIMITER ',';"

    Rows Loaded

           5
    

    (1 row)

    I tested in vsql and it doesn't seem to have the same success
    VMart=> COPY public.test FROM LOCAL STDIN;
    Enter data to be copied followed by a newline.
    End with a backslash and a period on a line by itself.

    test1
    test2
    .

    Rows Loaded

           0
    

    (1 row)

    So whether you could implement it or not may depend on you more complete use case and whether you are piping in your STDIN input.

    I hope it helps,

Answers

  • Bryan_HBryan_H Vertica Employee Administrator

    What version of Vertica are you running? Is there any redirect of stdout/stderr? In my test on 12.0.4-7, it appears to work with a single COPY LOCAL command:
    $ cat /tmp/localtest.txt | vsql -i -e -U abc -w 123 -c "copy localtestflex from local stdin parser fdelimitedparser();"
    copy localtestflex from local stdin parser fdelimitedparser();

    Rows Loaded

           3
    

    (1 row)

    Time: First fetch (1 row): 134.567 ms. All rows formatted: 134.656 ms

  • bmurrellbmurrell Community Edition User

    I'm running 12.0.3-2.

    Command is : -

    awk | vsql -h -U -w ${PWD} -e -i -c "COPY FROM STDIN PARSER FJSONPARSER() DIRECT;
    "
    I wasn't even getting the echo'd output unless I put line feeds into the -c command.

    Output is simply just the command, no rows processed.
    I did put a "select get_num_accepted_rows();" in the -c too, but it doesn't show that either.

    If I echo the commands in via a pipe it works, but not with -c. I remember seeing a forum entry about that, but can't find it.

  • SruthiASruthiA Vertica Employee Administrator

    I see that command just as awk piped to vsql command. how are you passing the data to COPY statement?

  • bmurrellbmurrell Community Edition User

    looks like my chevroned place-holders have removed keywords : -

    awk commands | vsql -h host -U username -w ${PWD} -e -i -c "COPY table_name FROM STDIN PARSER FJSONPARSER() DIRECT;
    "

  • SruthiASruthiA Vertica Employee Administrator

    I just tested and it works fine for me on 12.0.4-7

    awk '{print $0}' test.json | vsql -U dbadmin -e -i -c "COPY testjson FROM local STDIN PARSER FJSONPARSER() DIRECT rejected data as table rej;"
    COPY testjson FROM local STDIN PARSER FJSONPARSER() DIRECT rejected data as table rej;

    Rows Loaded

           3
    

    (1 row)

    Time: First fetch (1 row): 180.644 ms. All rows formatted: 180.933 ms

    cat test.json
    {"name":"John", "age":30, "car":null}
    {"name":"John", "age":30, "car":null}
    {"name":"John", "age":30, "car":null}

  • Bryan_HBryan_H Vertica Employee Administrator

    What shell are you running? bash is recommended for vsql. This seems like something that might happen if running another shell, or with non-standard options for bash affecting stdout/stderr redirection.

  • bmurrellbmurrell Community Edition User

    I'm running bash on RHEL 7.9.

    I've written a small test case, which shows that when using COPY with STDIN, the "Rows Loaded" are not shown. Would be good to if you could run in on your server : -

    #!/bin/bash
    
    DT=$(date "+%Y-%m-%d %H:%M:%S")
    
    echo "----------------------------------------------------------------------------------------------------"
    echo "Prep"
    echo "$DT" > /tmp/test.dat
    vsql -U dbadmin -w XXXXX -e -i -c "create table public.test (time timestamptz);"
    echo ""
    echo ""
    
    echo "----------------------------------------------------------------------------------------------------"
    echo "Test 1"
    vsql -U dbadmin -w XXXXX -e -i -c "COPY public.test FROM '/tmp/test.dat';"
    echo ""
    echo ""
    
    echo "----------------------------------------------------------------------------------------------------"
    echo "Test 2"
    DT=$(date "+%Y-%m-%d %H:%M:%S")
    echo "$DT" | vsql -U dbadmin -w XXXXX -e -i -c "COPY public.test FROM STDIN;"
    echo ""
    echo ""
    
    echo "----------------------------------------------------------------------------------------------------"
    echo "Test 3"
    cat << EOF | vsql -U dbadmin -w XXXXX -e -i
            INSERT INTO public.test (time) VALUES (CURRENT_TIMESTAMP);
            COMMIT;
    EOF
    
    exit 0
    

    My output is : -

    ----------------------------------------------------------------------------------------------------
    Prep
    create table public.test (time timestamptz);
    CREATE TABLE
    Time: First fetch (0 rows): 8.111 ms. All rows formatted: 8.155 ms
    
    
    ----------------------------------------------------------------------------------------------------
    Test 1
    COPY public.test FROM '/tmp/test.dat';
     Rows Loaded
    -------------
               1
    (1 row)
    
    Time: First fetch (1 row): 89.855 ms. All rows formatted: 89.897 ms
    
    
    ----------------------------------------------------------------------------------------------------
    Test 2
    COPY public.test FROM STDIN;
    Time: First fetch (0 rows): 14.106 ms. All rows formatted: 14.106 ms
    
    
    ----------------------------------------------------------------------------------------------------
    Test 3
    INSERT INTO public.test (time) VALUES (CURRENT_TIMESTAMP);
     OUTPUT
    --------
          1
    (1 row)
    
    Time: First fetch (1 row): 10.257 ms. All rows formatted: 10.284 ms
    COMMIT;
    COMMIT
    Time: First fetch (0 rows): 2.249 ms. All rows formatted: 2.259 ms
    

Leave a Comment

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