Options
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?
0
Comments
Go like so:
copy mytable( myTimestampTzCol, myintAsString FILLER VARCHAR(64) myIntegerCol as myintAsString::INTEGER, myStringCol) from '/home/user/my.orc' ORC DIRECT;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.
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 ....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?
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)
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.
I had it backwards. You can try a shell script like so:
$ cat file.sqlSELECT 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.shvsql -c "SELECT '123' as myStaticStringCol;"