extraneous input 'timestamp' expecting {<EOF>, ';'}(line 1, pos 54)

Question

I'm trying to convert my column of string data type to timestamp in my Azure databricks. I'm using 10.4 LTS (includes Apache Spark 3.2.1, Scala 2.12) So I wrote the following query

Alter table convertToTimeStamp alter column FinalDate timestamp

My sample data looks like

|FinalDate            |
|---------------------|
|2/18/2021 7:20:12 PM | 
|2/22/2021 5:25:13 PM |
|4/23/2021 3:19:35 AM |

But I'm getting ParseException saying that

extraneous input 'timestamp' expecting {<EOF>, ';'}(line 1, pos 54)

Can you please guide me to resolve the issue?

Answer 1

Looking at the docs, it appears Databricks SQL doesn't support this operation.

You can see there is no option to alter the datatype in the documented syntax for ALTER TABLE ... ALTER COLUMN

{ { ALTER | CHANGE } [ COLUMN ] { column_identifier | field_name }
  { COMMENT comment |
    { FIRST | AFTER column_identifier } |
    { SET | DROP } NOT NULL |
    SET DEFAULT clause |
    DROP DEFAULT |
    SYNC IDENTITY |
    SET { MASK clause } |
    DROP MASK |
    SET TAGS clause |
    UNSET TAGS clause } }

This is consistent with the error message, which suggests it's expecting a semicolon at the 54th character (i.e. after alter column FinalDate).

Here's an example of an older SO answer that describes how it's done in pyspark.

Answer 2

I have created a table like below

%sql
CREATE TABLE dilip_02 (
     FinalDate STRING
   ) USING delta
   OPTIONS (
     path '/FileStore/tables/dilip_02_data.csv',
     header 'true',
     inferSchema 'true'
   )

I have tried the below approach in pyspark to convert FinalDate column to timestamp

from pyspark.sql.functions import col, to_timestamp
df = spark.read.format("delta").table("dilip_02")
df_transformed = df.withColumn("FinalDate", to_timestamp(col("FinalDate"), 'M/d/yyyy h:mm:ss a'))
df_transformed.write.format("delta").mode("overwrite").saveAsTable("dilip_02_transformed")
spark.sql("DESCRIBE dilip_02_transformed").show()

In the above reading the Delta table into a DataFrame Transforming the FinalDate column to timestamp

Results:

+---------+---------+-------+
| col_name|data_type|comment|
+---------+---------+-------+
|FinalDate|timestamp|   NULL|
+---------+---------+-------+

FinalDate
2021-02-18T19:20:12Z
2021-02-22T17:25:13Z
2021-04-23T03:19:35Z