The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

Datatype mismatch on COPY from ORC

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?

Comments

  • Go like so:

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

    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.

  • 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?

  • Bryan_HBryan_H Employee

    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)

  • ChrisChris
    edited August 7

    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_HBryan_H Employee

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

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.