How can I ingest CDM folder - Synapse Link export data as an External Table in Synapse Analytics

Question

How can I ingest CDM folder - Synapse Link export data as an External Table in Synapse Analytics

Mehmet BAŞERDEM 41

Hello all,

We are using Synapse Link to export Dynamics CRM data to ADLS Gen2. The CRM entities are exported in CSV format in CDM folders. They don't have headers. The records are splitted into multiple CSV files. I am not sure if all of the CSVs for the same entity follow same table structure & they have all the columns given in the model.json file.

There is also a subfolder called "Snapshot" . I am not sure whether we should include it into our external table location definition. If we shouldn't, I am not sure how to exclude it.

When I try to query , I receive below error. I need to confirm whether my external fileformat is appropriate for reading data from CDM folders

Msg 107090, Level 16, State 1, Line 1
HdfsBridge::recordReaderFillBuffer - Unexpected error encountered filling record reader buffer: HadoopExecutionException: No closing string delimiter.

Completion time: 2022-07-17T13:33:46.6246351+03:00

CREATE EXTERNAL FILE FORMAT [CDMCSVFileFormat] WITH

(FORMAT_TYPE = DELIMITEDTEXT, FORMAT_OPTIONS (
FIELD_TERMINATOR = N',',
STRING_DELIMITER = N'"',
FIRST_ROW = 1,
USE_TYPE_DEFAULT = False))

Thanks Mehmet Başerdem

1 answer

Your answer

Answer 1

Mehmet BAŞERDEM 41

We are checking source data. We found double quote characters inside text fields. It looks like they are failing the query.

I think SynapseLink is not following the CSV format 100%. It is breaking the rule number #7 under
https://datatracker.ietf.org/doc/html/rfc4180#section-2

MartinJaffer-MSFT 26,236 Reputation points

2022-07-18T22:05:12.81+00:00
Thank you for linking the specifications for CSV @Mehmet BAŞERDEM . I wasn't aware of the existence of these specs, but I never really thought to look either.

Violation of rule #7 does explain some behavior I have seen in other features before.

So you are saying you have data like

1, "abc" 2, "def""ghi" 3, "xyz"

and not like

4, qf"hi 5, "jkl 6, wef"" 7, po""gr
Mehmet BAŞERDEM 41 Reputation points

2022-07-19T08:43:12.347+00:00
I have data coming from SynapseLink. Unfortunately it has data like

4, qf"hi

5, "jkl

6, wef""

7, po""gr

I started to think that the root cause of the problem is not Synapse Dedicated SQL Pool itself. It should be addressed by SynapseLink
MartinJaffer-MSFT 26,236 Reputation points

2022-07-19T17:31:34.097+00:00

In that case you should remove / change the STRING_DELIMITER . If the double-quotes (") are only as part of the data, and not enclosing the data, then it isn't filling the role as string delimiter.

If String Delimiter is not set to " , then these double quotes will be read as part of data.

Does this make sense @Mehmet BAŞERDEM ?
Mehmet BAŞERDEM 41 Reputation points

2022-07-19T18:14:57.67+00:00

some records has comma character within these text fields. If I remove string delimiter, then these records will fail to load.
MartinJaffer-MSFT 26,236 Reputation points

2022-07-20T16:22:51.82+00:00
So some rows are quoted and some are not, like

1, abc 2, "d,e"

and not like

3 fg","h
Mehmet BAŞERDEM 41 Reputation points

2022-07-20T16:26:38.307+00:00

Yes correct

Share via

How can I ingest CDM folder - Synapse Link export data as an External Table in Synapse Analytics

1 answer

Your answer