Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to Copy text to ARRAY type column

i saw the follwing article

https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/DataLoad/LoadingUTF-8FormatData.htm

In this article, we have confirmed that COPY is performed using the query below.

CREATE TABLE researchers (id INT, name VARCHAR, grants ARRAY[VARCHAR], values ARRAY[INT]);

COPY researchers FROM STDIN;
--- Enter data to be copied followed by a newline.
--- End with a backslash and a period on a line by itself.
--- 42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
--- 17|Howard Wolowitz|[NASA-1683,NASA-7867,SPX-76]|[16700,85000,45000]
--- \.

So I created the following text file based on the query above.

42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]

but, i saw below error

[0A000][9222] [Vertica]VJDBC ERROR: Default COPY parsers do not support arrays yet

using query 'COPY researchers FROM LOCAL '/Users/liquid.bear/Desktop/test.txt';

Looking at the message, it seems that the ARRAY type cannot be specified in the basic COPY statement, so how should I modify it?

i use vertica v10.0.0-2

Answers

  • marcothesanemarcothesane Administrator

    You can always use expressions in the COPY statement:

    CREATE TABLE public.researchers (
      id INT
    , name VARCHAR
    , grants ARRAY[VARCHAR]
    , VALUES ARRAY[INT]
    );
    -- out CREATE TABLE
    -- out Time: First fetch (0 rows): 339.992 ms. All rows formatted: 340.329 ms
    COPY public.researchers (
      id
    , name
    , grants_in FILLER VARCHAR, grants AS string_to_array(grants_in)
    , values_in FILLER VARCHAR, values AS string_to_array(values_in)::ARRAY[INT]
    )
    FROM STDIN
    REJECTED DATA '/dev/tty' EXCEPTIONS 'public.researchers.log'
    ;
    42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
    \.
    -- out NOTICE 7850:  In a multi-threaded load, rejected record data may be written to additional files
    -- out HINT:  Exceptions may be written to files [public.researchers.log], [public.researchers.log.1], etc
    -- out Time: First fetch (0 rows): 405.654 ms. All rows formatted: 405.788 ms
    SELECT * FROM public.researchers;
    -- out  id |      name      |          grants          |     VALUES     
    -- out ----+----------------+--------------------------+----------------
    -- out  42 | Sheldon Cooper | ["US-7376","DARPA-1567"] | [65000,135000]
    
    
  • marcothesanemarcothesane Administrator

    You can always use expressions in the COPY statement:

    CREATE TABLE public.researchers (
      id INT
    , name VARCHAR
    , grants ARRAY[VARCHAR]
    , VALUES ARRAY[INT]
    );
    -- out CREATE TABLE
    -- out Time: First fetch (0 rows): 339.992 ms. All rows formatted: 340.329 ms
    COPY public.researchers (
      id
    , name
    , grants_in FILLER VARCHAR, grants AS string_to_array(grants_in)
    , values_in FILLER VARCHAR, values AS string_to_array(values_in)::ARRAY[INT]
    )
    FROM STDIN
    REJECTED DATA '/dev/tty' EXCEPTIONS 'public.researchers.log'
    ;
    42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
    \.
    -- out NOTICE 7850:  In a multi-threaded load, rejected record data may be written to additional files
    -- out HINT:  Exceptions may be written to files [public.researchers.log], [public.researchers.log.1], etc
    -- out Time: First fetch (0 rows): 405.654 ms. All rows formatted: 405.788 ms
    SELECT * FROM public.researchers;
    -- out  id |      name      |          grants          |     VALUES     
    -- out ----+----------------+--------------------------+----------------
    -- out  42 | Sheldon Cooper | ["US-7376","DARPA-1567"] | [65000,135000]
    
    
  • moshegmosheg Administrator
    edited November 7

    In addition, I've tried the following on v12.0.1 and it worked.

    cat test.txt
    42|Sheldon Cooper|[US-7376,DARPA-1567]|[65000,135000]
    17|Howard Wolowitz|[NASA-1683,NASA-7867,SPX-76]|[16700,85000,45000]
    
    CREATE TABLE researchers (id INT, name VARCHAR, grants ARRAY[VARCHAR], values ARRAY[INT]);
    COPY researchers FROM LOCAL '/mnt/c/DIR4VM/TMP/test.txt';
    SELECT * FROM researchers;
     id |      name       |               grants               |       values
    ----+-----------------+------------------------------------+---------------------
     17 | Howard Wolowitz | ["NASA-1683","NASA-7867","SPX-76"] | [16700,85000,45000]
     42 | Sheldon Cooper  | ["US-7376","DARPA-1567"]           | [65000,135000]
    (2 rows)
    

    I think it will also work on v11.0 because the following error message do not exist in v11.0 documentation:
    "Default COPY parsers do not support arrays yet"

  • SergeBSergeB Employee

    Support for array with the default DELIMITED parser was added in 10.0.1 (see https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/NewFeatures/10.0/10.0.1/LoadingData)

  • edited November 14

    I have a table with a column of type ARRAY. The column is called "name" and it's a varchar(255).

    I want to insert a new row into the table, and I want to copy the contents of another column called "full_name" into the "name" column.

  • SruthiASruthiA Employee

    @ryankim1726 : Please find the sample on how to insert the data into array column..

    CREATE TABLE transactions (tid INT, name ARRAY[VARCHAR,255], quantities ARRAY[INT,100]);
    CREATE TABLE

    => INSERT INTO transactions VALUES (12345, ARRAY['name1', 'name2'], ARRAY[15,2]);

    OUTPUT

      1
    

    is full_name column present in another table? if so you can use insert select.. Please share sample example from environment and error you are receiving.

  • marcothesanemarcothesane Administrator

    The question is confusing to me. Is your "name" column now a VARCHAR(255), an ARRAY[INT] or an ARRAY[VARCHAR(255)]?

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.