Datatype mismatch on COPY from ORC

Chris

Hello,
I try to import am ORC file in Vertica.

copy mytable(
myTimestampTzCol,
myIntegerCol,
myStringCol)
from '/home/user/my.orc' ORC DIRECT;

I get
ERROR 6726: Datatype mismatch: column 2 in the orc source [/home/user/my.orc] has type STRING, expected int

So tried to convert string to int using to_number.

copy mytable(
myTimestampTzCol,
myIntegerCol as TO_NUMBER(myIntegerCol),
myStringCol)
from '/home/user/my.orc' ORC DIRECT;

I get
ERROR 3457: Function TO_NUMBER(int) does not exist, or permission is denied for TO_NUMBER(int)
HINT: No function matches the given name and argument types. You may need to add explicit type casts

Which is weird, as it means imho that it has already the correct required type (int).
Any idea what is going on here?

marcothesane

Go like so:

copy mytable(
    myTimestampTzCol,
    myintAsString FILLER VARCHAR(64)
    myIntegerCol as myintAsString::INTEGER,
    myStringCol)
from '/home/user/my.orc' ORC DIRECT;

Chris

Wow, thanks a lot, did not expect that FILLER needs to be used to get this done, a bit counterintuitive..however performance is amazing (as your reply time )..100k rows in 1sec.

marcothesane

Well - once you know how it works, it seems logical - at least to me. You need to get the data in as-is, and if you can't put it into the target as is, you need to park it (FILLER), and then use the parked data for a transformation (our cast operator :: ). Once you simply expect it to be so, it's pretty intuitive ....

Chris

OK, I did expect and tried a direct cast like
myIntegerCol::INTEGER
expecting necessary intermediate steps are done in the background if necessary.
But no problem, as it works.

Just one thing.
I want to pass a static value using a variable like this
vsql ....-v myVariable='123'
where the belonging placeholder in the sql script looks like this:

myStaticStringCol as :myVariable

Problem is, that the value must be a quoted string.
So I get an error
vsql:copy.sql:24: ERROR 2624: Column "123" does not exist

Also tried
myStaticStringCol as select concat('''', :myVariable, '''')
but it fails.

Any advice on how passing a variable as quoted string might work?

Bryan_H

Though it might depend on exactly what you need to do, and this might not be what you meant, but the following worked for me, note absence of quotes in the variable:
$ vsql -U dbadmin -v myVar=abc123
dbadmin=> select 1 as :myVar;
abc123
--------
1
(1 row)

Chris

Yes, when it's not quoted, myVar is interpreted as column.

So when I remove the quotes

in the call:
vsql -f copy.sql ....-v myVar=123

and in the copy command (in copy.sql):
myStaticStringCol as :myVar

I get (as expected)
ERROR 2624: Column "123" does not exist

However, I want to set the myStaticStringCol to '123' for all records.

Any advice is very welcome.

Bryan_H

I had it backwards. You can try a shell script like so:
$ cat file.sql
SELECT STATIC_VALUE as myStaticStringCol;
$ cat file.sh
#!/bin/bash
firstString=`cat file.sql`
secondString="'123'"
vsqlCmd="vsql -c \"${firstString/STATIC_VALUE/$secondString}\""
echo $vsqlCmd
$ ./file.sh
vsql -c "SELECT '123' as myStaticStringCol;"