1

I'm trying to add a column to a table (ideally without using a dataframe) with a default value of 'MONTHLY'

ALTER TABLE aa_monthly ADD COLUMNS (Monthly_or_Weekly_Indicator string NOT NULL FIRST DEFAULT ('MONTHLY'))

This gives me an error of:

Error in SQL statement: ParseException: mismatched input 'DEFAULT' expecting ')'(line 1, pos 88)

I get the same error regardless of whether or not I have FIRST in the sql statement.

I've looked here and here

Mariah Akinbi
  • 386
  • 1
  • 5
  • 19

3 Answers3

2

In Hive you cannot add a default value for a column, sadly.

The column change command will only modify Hive's metadata, and will not modify data

Liam Clarke
  • 375
  • 1
  • 6
0

Hope this helps

ALTER TABLE aa_monthly 
    ADD Monthly_or_Weekly_Indicator string NOT NULL DEFAULT 'MONTHLY'
gabrieljayyyy
  • 66
  • 5
  • Ah, I'm getting this error: Error in SQL statement: ParseException: no viable alternative at input 'ALTER TABLE aa_monthly ADD Monthly_or_Weekly_Indicator'(line 1, pos 35). It's showing the error after 'ADD' – Mariah Akinbi May 21 '19 at 03:27
0

There are multiple ways to that 

1)  ALTER TABLE aa_monthly 
    ADD Monthly_or_Weekly_Indicator string  not null
    CONSTRAINT Monthly_or_Weekly_Indicator DEFAULT 'MONTHLY'
    WITH VALUES

2)  ALTER TABLE aa_monthly 
    ADD Monthly_or_Weekly_Indicator string NOT NULL DEFAULT 'MONTHLY'
MohammadTausif Shaikh
  • 513
  • 3
  • 9
  • Unfortunately, neither of these work. They both get hung up right after 'ADD'. I get the same error: com.databricks.backend.common.rpc.DatabricksExceptions$SQLExecutionException: org.apache.spark.sql.catalyst.parser.ParseException: no viable alternative at input 'ALTER TABLE aa_monthly \n ADD Monthly_or_Weekly_Indicator'(line 2, pos 8) – Mariah Akinbi May 21 '19 at 17:58
  • Are you hitting this query directly or through any program ?? – MohammadTausif Shaikh May 23 '19 at 04:21
  • directly. I'm using sql in databricks – Mariah Akinbi May 24 '19 at 16:03