topic Re: PARSE_SYNTAX_ERROR creating view from CSV in Data Engineering

PARSE_SYNTAX_ERROR creating view from CSV

DaniW — Tue, 01 Aug 2023 08:26:11 GMT

Hello, if i run this code:

%sql

CREATE OR REPLACE VIEW esprosilver.xxx.encuestas_talleres

SELECT * FROM CSV.`abfss://landing@esproanalyticscenterdl.dfs.core.windows.net/oracle-dwh/encuestas_talleres/encuestas_talleres.csv`

It creates the view in unity catalog, but places all data in a column _c0. so I have to tell it that it has headers. But if I run this query:

%sql

CREATE OR REPLACE VIEW esprosilver.xxxx.encuestas_talleres

USING csv

OPTIONS (

path "abfss://landing@esproanalyticscenterdl.dfs.core.windows.net/oracle-dwh/encuestas_talleres/encuestas_talleres.csv",

header = "true",

inferSchema "true",

mode "FAILFAST")

it returns this error:

ParseException: [PARSE_SYNTAX_ERROR] Syntax error at or near 'USING'(line 2, pos 0) == SQL == CREATE OR REPLACE VIEW esprosilver.realeseguros_dwh.encuestas_talleres
USING csv
^^^
OPTIONS ( path "abfss:REDACTED_LOCAL_PART@esproanalyticscenterdl.dfs.core.windows.net/oracle-dwh/encuestas_talleres/encuestas_talleres.csv", header = "true", inferSchema "true", mode "FAILFAST")

Can someone help me fix it? Thank you in advance

Re: PARSE_SYNTAX_ERROR creating view from CSV

DaniW — Tue, 01 Aug 2023 08:33:18 GMT

I forgot to mention that the csv delimiter is ';'

Re: PARSE_SYNTAX_ERROR creating view from CSV

DaniW — Tue, 01 Aug 2023 09:47:04 GMT

Hello Mo,

Thank you for the prompt reply. I require the data and schema to be accessible to all users at all times. Considering this, I'm thinking of creating a managed table that would function similarly to a view. If I create a table first and then a view based on it, any changes to the CSV file would necessitate re-creating the table. Right?

Re: PARSE_SYNTAX_ERROR creating view from CSV

DaniW — Tue, 01 Aug 2023 11:30:08 GMT

I finally used the managed table creation and works perfectly. So I don't need to schedule. I leave the code here in case is of any use for someone:

%sql

CREATE TABLE IF NOT EXISTS esprosilver.XXXX.encuestas_talleres

USING CSV

OPTIONS (

path "abfss://landing@esproanalyticscenterdl.dfs.core.windows.net/oracle-dwh/encuestas_talleres/encuestas_talleres.csv",

header "true",

inferSchema "true",

delimiter ";",

format "csv"

)

Thank you very much Mo for your time and giving me the idea of using tables. Have a great day!