CODEWITHSUNDEEP
azure-synapsedynamics-365
Jānis
Jānis

Reputation: 63

How to create Synapse External table for Dynamics365 Finance and Operations table

I have a Data Lake in Azure Synapse, where is made table from Dynamics365 Finance and Operations - CustTable.

Data Lake structure:

xxx
- Tables
-- Common
--- Customer
---- Main
     CustTable.cdm.json
     config.json
     Main.manifest.cdm.json
----- CustTable
      CUSTTABLE_00001.csv
      index.json

Data file CUSTTABLE_00001.csv does not contain header.

How to create External Table CustTable, with correct header and column format configuration? Is the only way to do it manually?

It looks like file CustTable.cdm.json contains header configuration.

I made External Table in Synapse New SQL Script > Create external table, but it doesn't make script with correct header and column configuration.

Example:

CREATE EXTERNAL TABLE [dbo].[test] (
    [C1] bigint,
    [C2] nvarchar(100),
    [C3] nvarchar(100),

Is it possible to make External Table conveniently?

Upvotes: 0

Views: 335

Answers (1)

Aswin
Aswin

Reputation: 7156

Since your data file does not contain a header, you need to define the column names in the External Table script manually only in Synapse SQL pools. There is no option to infer the column names and type from CustTable.cdm.json file in the external table script.

CREATE EXTERNAL TABLE [dbo].[CustTable]
(
    [Field1] [datatype],
    [Field2] [datatype],
    [Field3] [datatype],
    ...
)
WITH
(
    LOCATION = 'CustTable_00001.csv',
    DATA_SOURCE = <your_data_lake_data_source>,
    FILE_FORMAT = <your_file_format>
)

Here replace <field1> and <datatype> with the required values from config file.

Upvotes: 1

Related Questions