Rows Processed not output when using COPY STDIN

bmurrell

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.

s_crossman

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,

Bryan_H

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

bmurrell

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.

SruthiA

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

bmurrell

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;
"

SruthiA

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_H

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.

bmurrell

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