create external table with specific field order

Question

Hi,

I have a csv file in datalake. I have created

1. database scoped credential
2. external data source
3. external file format

The csv file has fields in the order

1. Segment
2. Product
3. Qty
4. Sales
5. Month
6. Year

my Create external table DML is as below

CREATE EXTERNAL TABLE testfile

(

Segment varchar(20),

Product varchar(20),

Sales varchar(20)

)

WITH

(

Location = "xxxxxx",

data_source=demods,

FILE_FORMAT=csvfiles

)

When I query the table, I am getting strange results

SELECT * FROM testfile

Result:

Segment Product Sales

---

SEG12 PRD123 154 -- Which is actually Qty not Sales

Is there field order is mandatory?

Answer 1

Hi rajanisqldev-42, Thank you for posting query in Microsoft Q&A Platform.

Yes, order should be same. But you can consider using CETAS also in your case.

-- use CETAS to export select statement with OPENROWSET result to  storage
CREATE EXTERNAL TABLE population_by_year_state
WITH (
    LOCATION = 'aggregated_data/',
    DATA_SOURCE = population_ds,  
    FILE_FORMAT = census_file_format
)  
AS
SELECT decennialTime, stateName, SUM(population) AS population
FROM
    OPENROWSET(BULK 'https://azureopendatastorage.dfs.core.windows.net/censusdatacontainer/release/us_population_county/year=*/*.parquet',
    FORMAT='PARQUET') AS [r]
GROUP BY decennialTime, stateName
GO

-- you can query the newly created external table
SELECT * FROM population_by_year_state

Please consider hitting Accept Answer button. Accepted Answers help community as well.