SQL Alter table command not working for me

TimothyClotwort — Tue, 17 Aug 2021 18:32:29 GMT

I am a novice with databricks. I am performing some independent learning. I am trying to add a column to an existing table. Here is my syntax:

%sql

ALTER TABLE car_parts ADD COLUMNS (engine_present boolean)

which returns the error:

SyntaxError: invalid syntax File "<command-3097619422049343>", line 4 ALTER TABLE car_parts ADD COLUMNS (engine_present boolean)

In general I am having issues executing raw sql commands from these execution windows (cells) in my databricks community edition tool. I have also tried: sqlContext.sql("ALTER TABLE car_parts ADD engine_present boolean")

, which returns the error: ParseException: no viable alternative at input 'ALTER TABLE car_parts ADD engine_present' (line 1, pos 31)

I am certain the table is present as:

sqlContext.sql("SELECT * FROM car_parts")

works fine. I would grateful for any insights. Thank you.

Re: SQL Alter table command not working for me

Ryan_Chynoweth — Wed, 01 Sep 2021 21:07:06 GMT

Is the table you are working with in the Delta format? The table commands (i.e. Alter) do not work for all storage formats.

For example if I run the following commands then I can alter a table. Note - there is no data in the table but the table exists and I can alter the table.

create table if not exists add_col_test  (
col_one integer, 
col_two integer
)
USING DELTA 
; 
 
describe add_col_test; 
 
alter table add_col_test ADD columns (col_three boolean);
 
describe add_col_test;

topic SQL Alter table command not working for me in Data Engineering

SQL Alter table command not working for me

Re: SQL Alter table command not working for me