How to add ARRAY column to Spark table (using ALTER TABLE)?

Question

I am trying to add a new column of Array Type to the table with default value.

%sql
ALTER TABLE testdb.tabname ADD COLUMN new_arr_col ARRAY DEFAULT ['A','B','C'];

But it says that the data type in not supported

Error in SQL statement: ParseException: 
DataType array is not supported.(line 1, pos 54)

== SQL ==
ALTER TABLE testdb.dim_category ADD COLUMN c_cat_area ARRAY

So, there is no way we can add an array column directly to the table? Kindly assist me on this. Thanks in advance!

This DEFAULT keyword makes me think you use Databricks (not a vanilla Delta Lake on Spark 3.2)? — Jacek Laskowski
– Jacek Laskowski, Commented Aug 20, 2022 at 14:33
This is Delta lake spark. Spark v3.2.1. Is this way of applying default value wrong? — Dev Anand
– Dev Anand, Commented Aug 21, 2022 at 3:24
I don't think DEFAULT is supported in Delta Lake OSS. It will only work in Databricks. — Jacek Laskowski
– Jacek Laskowski, Commented Aug 21, 2022 at 11:54

Jacek Laskowski · Accepted Answer · 2022-08-20 14:37:05Z

7

The reason for this error is that complex types (e.g. ARRAY) require another type to be specified (cf. SqlBase.g4):

dataType
    : complex=ARRAY '<' dataType '>'                            #complexDataType
    | complex=MAP '<' dataType ',' dataType '>'                 #complexDataType
    | complex=STRUCT ('<' complexColTypeList? '>' | NEQ)        #complexDataType
    | INTERVAL from=(YEAR | MONTH) (TO to=MONTH)?               #yearMonthIntervalDataType
    | INTERVAL from=(DAY | HOUR | MINUTE | SECOND)
      (TO to=(HOUR | MINUTE | SECOND))?                         #dayTimeIntervalDataType
    | identifier ('(' INTEGER_VALUE (',' INTEGER_VALUE)* ')')?  #primitiveDataType
    ;

In your case, it'd be as follows:

ARRAY<CHAR(3)>

answered Aug 20, 2022 at 14:37

Jacek Laskowski

75k28 gold badges253 silver badges440 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to add ARRAY column to Spark table (using ALTER TABLE)?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related