Error while creating delta table with partitions

[Faisal]

Hi All,

I am unable to create delta table with partitioning option, can someone please correct me what I am missing and help me with updated query

CREATE OR REPLACE TABLE invoice 
USING DELTA 
PARTITION BY (year(shp_dt), month(shp_dt)) 
LOCATION '/tables/invoice' 
AS
SELECT
  *,
  current_timestamp() as adls_load_timestamp
FROM
  hive_metastore.invoice_edw 
WHERE 
  shp_dt between '2023-01-01' and '2023-12-31';
​

Error in SQL statement: ParseException: 
[PARSE_SYNTAX_ERROR] Syntax error at or near 'PARTITION'.(line 2, pos 55)

[Emil_Kaminski]

Hi,

PARTITION -> PARTITIONED

Good Luck

[Kaniz_Fatma · Accepted solution]

Hi @Faisal ,  Use the PARTITIONED BY clause instead of the PARTITION BY clause.

[Emil_Kaminski]

@Kaniz_Fatma Hi. Is that not exactly what I suggested before? Sorry for stupid questions, but I am learning rules or earning kudos and getting solutions approved, therefore suggestions from your end would be appreciated. Thank you.