Column Schema in External table definition

Question

Hi Team,

Recently I have observed two things on Azure Synapse serverless, which I'd like to highlight here one by one:

1. Issue in External File Format: I have observed in the syntax of creating an external file format we do not have the option of Header_Row=True option, why is that?
2. Column Order in External Table: Can I not give any custom/random column order while defining the external table in Synapse serverless? Does the column names need to be in the specific order as in the file in order to create an external table.

Answer 1

For your first question, the option HEADER_ROW is not available in the syntax of creating an external file format in Azure Synapse serverless SQL pools.

Regarding your second question, the column names in the external table definition do not need to be in the same order as the columns in the file. You can specify the column order in the CREATE EXTERNAL TABLE statement using the COLUMN_ORDINAL option.

Here's an example of how to create an external table with custom column order:

CREATE EXTERNAL TABLE MyExternalTable (
    Column3 int,
    Column1 varchar(100),
    Column2 varchar(50)
)
WITH (
    LOCATION = 'my/location',
    DATA_SOURCE = MyDataSource,
    FILE_FORMAT = MyFileFormat,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0,
    COLUMN_ORDINAL = 'Column3, Column1, Column2'
);

In the example above, the columns in the external table are defined in a different order than the columns in the file. The COLUMN_ORDINAL option is used to specify the order of the columns in the file.

---

References:

• Use external tables with Synapse SQL
• Create and use native external tables using SQL pools in Azure Synapse Analytics

Answer 2

Hi Amandeep Bajaj,

Thank you for posting query in Microsoft Q&A Platform.

You are right, HEADER_ROW = True option will not be available in File Format. While we create External Tables, we will define the Headers and in File Format Option we can define First_Row option. Hence, Having HEADER_ROW is not needed.

You can give column order differently as well. It should work.

Hope this helps. Please let me know if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well. Thank you.