CODEWITHSUNDEEP
azureazure-synapse
Rob M
Rob M

Reputation: 1

Azure SQL Synapse incorrect null values from external table

EDIT: The problem was that when I created the external cable, the column names I used were not the same column names as in the file. When I use the same column names as in the file to create the external table, I no longer see null values.

I am trying to select from an external table I created in Azure Synapse. When I do so, some columns are full of null values instead of the correct data. However, if I select the same data using openrowset, the data is not null.

The table is defined as per the schema below.

create external table my_table (
    date datetime2,
    product_id varchar(32),
    location_id varchar(32),
    visits int
) with (
    location = 'file.parquet',
    data_source = my_data_source,
    file_format = my_parquet_format
);

If I use openrowset and view the data in the parquet table directly, I use the following command.

select top 100 * from openrowset(
    bulk 'file.parquet',
    data_source = 'my_data_source',
    format='PARQUET'
) as [result];

This shows me the correct data. Selecting from the external table shows me null values for the product_id and location_id columns.

Upvotes: 0

Views: 800

Answers (1)

BhargavaGunnam-MSFT
BhargavaGunnam-MSFT

Reputation: 313

A similar issue has been resolved after creating the table with lowercase columns, as shown on the parquet files.

If your parquet file has the upper case characters, please create the external table with the upper case columns.

Upvotes: 1

Related Questions