How to convert Hive array type to Vertica varchar on exporting data

Question

I have a table in Hive db with array type column which I want to copy it with content to Vertica db. The Version of Vertica db is v9.0.1-0 and I cant create table with array type. To copy table I tried to use

COPY vertica_schema.destination FROM hive_table_path ON ANY NODE ORC;

but got an empty table. Could you help me copy table and covert Hive array int type to Vertica varchar type? For example Vertica varchar values can be look as: [1, 1, 1] or {1, 1, 1}.

Vertica destination table init:

CREATE TABLE vertica_schema.destination (
    col_a     INTEGER NOT NULL,
    col_array VARCHAR(200), -- ARRAY[INTEGER] not working
) ORDER BY id
UNSEGMENTED ALL NODES;

Hive source table init:

create table source
(
    col_a int,
    col_array array<int>
) stored as orc;

insert into source  select 1, array(1, 2, 3);
insert into source select 2, array(2, 2, 2);
insert into source select 3, array(3, 3, 3);

Chema · Answer 1 · 2020-07-21T21:26:49.947

You could try a different approach.

First create a stage table in Hive like

CREATE TABLE dest_stage(
    col_a INT,
    col_array array<INT> 
) STORED AS ORC;

Second load dest_stage with source table

INSERT INTO TABLE dest_stage SELECT col_a,col_array FROM source;

Third step would be convert col_array of type INT into col_array of type STRING, you can follow this link for that task

How to concatenate the elements of int array to string in Hive

And the final step would be load vertica_schema.destination from dest_stage.

score 2 · Answer 2 · answered Apr 17 '20 at 09:58

Explode array, cast elements and collect into array of string (load into staging table if necessary):

select s.col_a,
       collect_list(cast(e.element as string)) as string_array
  from source s
       lateral view outer explode(s.col_array) e as element

How to convert Hive array type to Vertica varchar on exporting data

2 Answers2