Skip to main content
Question

Equivalent of varchar2(N CHAR) in Vertica

  • July 16, 2019
  • 4 replies
  • 12 views
mkheir
Forum|alt.badge.img+1
  • mkheir
  • Participating Frequently

Hi Experts,

IHAC moving data from Oracle to Vertica (using Dataiku DSS).

What is the best rule to convert varchar2(N CHAR) to a varchar(n bytes) in vertica.

From documentation :

When using multibyte UTF-8 characters, the fields must be sized to accommodate from 1 to 4 octets per character, depending on the data. If the data loaded into a VARCHAR/CHAR column exceeds the specified maximum size for that column, data is truncated on UTF-8 character boundaries to fit within the specified size.
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/DataTypes/CharacterDataTypes.htm?TocPath=SQL Reference Manual|SQL%20Data%20Types|_____3

Is it a good solution to convert varchar2(N CHAR) in Oracle to varchar(4*N) in Vertica? any better way?

Example : the following Thai text that is 25 CHAR but 75 bytes "ผลิตและจำหน่ายระเบิดเพื่อ"

dbadmin=> create table test(a char(25));
CREATE TABLE
dbadmin=> insert into test values('ผลิตและจำหน่ายระเบิดเพื่อ');
ERROR 8682:  String of 75 octets is too long for type Char(25) for column a
dbadmin=>

Many thanks.
mkheir

    4 replies

    Bryan_H
    Forum|alt.badge.img+2
    • Bryan_H
    • Participating Frequently
    • July 16, 2019

    Hi, there is a reference for Oracle type conversion:
    https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Compatibility/Oracle-bill/DataTypeMappingsBetweenVerticaAndOracle.htm
    In this case, NVARCHAR2 (n) converts to VARCHAR(n*3)

    mkheir
    Forum|alt.badge.img+1
    • mkheir
    • Author
    • Participating Frequently
    • July 16, 2019

    Many Thanks Bryan_H.

    marcothesane
    Forum|alt.badge.img+1
    • marcothesane
    • Participating Frequently
    • July 16, 2019

    What I usually do is the following:

    1. create the target table in Vertica with strings 4 times as long as their NCHAR(n) counterparts. I actually wrote a tool that generates a DDL which can keep the same, double, triple or quadruple, individually, the lengths of NCHAR[ VARYING] types on one hand and the [VAR]CHAR types on the other hand.
    2. As certain strings resulting out of that could, in the worst case, turn out to be up to four times too big (which leads to spills to disk of GROUP BY hash tables and JOIN hash tables), profile the data, once they are in Vertica, for their maximum length:

    For each string column, you must:

    SELECT
  MAX(OCTET_LENGTH(RTRIM(<each_string_column>))) AS max_ln_<col>
FROM <table_with_too_long_strings> ;

    (you can and should, of course, group all string columns of a table into one query.)

    1. Finally, create another table with the shorter strings you have profiled, and INSERT ... SELECT .
    mkheir
    Forum|alt.badge.img+1
    • mkheir
    • Author
    • Participating Frequently
    • July 16, 2019

    Many thanks Marco.

    Still scrolling?
    Ask a question
    Terms & ConditionsAccessibility statement