topic Re: Facing issue while creating Delta Live Table on top of csv file in Data Engineering

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

Manasi_Sarang — Wed, 03 Aug 2022 12:09:48 GMT

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?

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

artsheiko — Wed, 03 Aug 2022 16:06:14 GMT

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;

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

Manasi_Sarang — Mon, 22 Aug 2022 11:40:31 GMT

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.

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

AbhiJ — Fri, 30 Jun 2023 05:48:26 GMT

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.

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

pvignesh92 — Fri, 30 Jun 2023 08:08:17 GMT

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")