Facing issue while creating Delta Live Table on top of csv file

[Manasi_Sarang]

Hello Everyone,

I am trying to create Delta Live Table on top of csv file using below syntax:

CREATE OR REFRESH LIVE TABLE employee_bronze_dlt

COMMENT "The bronze employee dataset, ingested from /mnt/lakehouse/PoC/DLT/Source/."

AS SELECT * FROM csv.`/mnt/lakehouse/PoC/DLT/Source/Employee.csv`;

Table is getting created but headers are appearing as part of data in table as shown below:

Can you please let me know what needs to be added in table creation definition to identify first row in data as header?

[artsheiko]

Hi, let's try the next :

CREATE TEMPORARY VIEW vw_employee
USING CSV
OPTIONS (path "/mnt/lakehouse/PoC/DLT/Source/Employee.csv", header "true");
 
CREATE OR REFRESH LIVE TABLE employee_bronze_dlt
COMMENT "The bronze employee dataset, ingested from /mnt/lakehouse/PoC/DLT/Source/."
AS SELECT * FROM vw_employee;

[Manasi_Sarang]

Hi,

I tried the above approach but getting below error:

org.apache.spark.sql.AnalysisException: Unable to process statement of type: 'CreateTempViewUsing'. DLT currently only accepts 'CREATE TEMPORARY LIVE VIEW', 'CREATE OR REFRESH LIVE TABLE', 'APPLY CHANGES INTO', and 'SET' statements.

Please find attached image file for error message.

[AbhiJ]

Facing similar issue

"message": "Unable to process top-level query. DLT currently only accepts 'CREATE TEMPORARY LIVE VIEW', 'CREATE OR REFRESH LIVE TABLE', 'APPLY CHANGES INTO', and 'SET' statements.".

Please let me know if you have got any luck.

[pvignesh92]

Hi @Manasi_Sarang ,

I believe the Delta is unable to infer the schema as you are using select statement to read entire content from csv file and I think the inferschema won't work here.  Instead you can try to create a temp live table or live view with the below options and read from that table or view. 

USING csv OPTIONS (path "./file.csv", header "true", inferSchema "true")