Datatype mismatch on COPY from ORC

I try to import am ORC file in Vertica.

copy mytable(
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(
myIntegerCol as TO_NUMBER(myIntegerCol),
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?


  • Options
    marcothesanemarcothesane - Select Field - Administrator

    Go like so:

    copy mytable(
        myintAsString FILLER VARCHAR(64)
        myIntegerCol as myintAsString::INTEGER,
    from '/home/user/my.orc' ORC DIRECT;
  • Options
    edited August 2019

    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 :smile: )..100k rows in 1sec.

  • Options
    marcothesanemarcothesane - Select Field - Administrator

    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 ....

  • Options

    OK, I did expect and tried a direct cast like
    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?

  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    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;
    (1 row)

  • Options
    edited August 2019

    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. :)

  • Options
    Bryan_HBryan_H Vertica Employee Administrator

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

Leave a Comment

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