NULL ISSUE IN EXTERNAL TABLE

Rohit Kulkarni 686

Hello Team,

I have created a table and pass the null for all the columns .But still i am getting error .

CREATE EXTERNAL TABLE [Sh].[Dim]

( [col1] nvarchar NULL,

[col2] [int] NULL,

[col3] [int] NULL,

[col4] [int] NULL,

[col5] [int] NULL )

WITH (DATA_SOURCE = [RawAccessTypeAugmented], LOCATION = '/Dim/*.parquet', FILE_FORMAT = [Parque], REJECT_TYPE = VALUE, REJECT_VALUE = 0)

Please refer below the error :

Cannot insert the value NULL into column 'col', table 'QTable_11bb46bd660b479789ab1f65f3c80461_50'; column does not allow nulls. INSERT fails.

I am not able to understand, when i am passing the null value to all the column then why this issue is coming please let me know

Regards

Smaran Thoomu 10,880 Reputation points Microsoft Vendor

2024-04-12T09:35:08.1633333+00:00

@Rohit Kulkarni Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Smaran Thoomu 10,880 Reputation points Microsoft Vendor

2024-04-11T05:17:57.13+00:00
Hi @Rohit Kulkarni

Thanks for the question and using MS Q&A platform.

Based on the error message you provided, it seems that the issue is with the table you created. The error message indicates that the column 'col' does not allow null values, which means that you cannot insert a null value into that column.

To resolve this issue, you need to ensure that the column 'col' allows null values. You can modify the table schema to allow null values for this column by running the following command:

ALTER TABLE [Sh].[Dim] ALTER COLUMN [col] nvarchar NULL;

This command will modify the table schema to allow null values for the column 'col'. Once you have made this change, you should be able to insert null values into this column without any issues.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
Rohit Kulkarni 686 Reputation points

2024-04-12T11:33:21.89+00:00

If you refer my above question for all the column i have mentioned Null value

Smaran Thoomu 10,880 Reputation points Microsoft Vendor

2024-04-15T09:56:56.54+00:00

Hi Rohit Kulkarni

Thank you for your response. My apologies for the confusion. I see that you have mentioned NULL as the default value for all the columns in your table definition. In that case, the error message you are seeing could be due to the rejection of the null values by the external table.

One possible reason for this could be that the external table is not able to recognize the null values in the data source. You can try to modify the table definition to specify the null value explicitly. For example, you can modify the table definition as follows:

CREATE EXTERNAL TABLE [Sh].[Dim] ( [col1] nvarchar NULL DEFAULT NULL, [col2] [int] NULL DEFAULT NULL, [col3] [int] NULL DEFAULT NULL, [col4] [int] NULL DEFAULT NULL, [col5] [int] NULL DEFAULT NULL ) WITH (DATA_SOURCE = [RawAccessTypeAugmented], LOCATION = '/Dim/*.parquet', FILE_FORMAT = [Parque], REJECT_TYPE = VALUE, REJECT_VALUE = 0) Here, we have specified the default value for each column as NULL. This should help the external table to recognize the null values in the data source.

If this does not resolve the issue, it is possible that there is some other issue with the data source or the external table configuration. In that case, you may need to provide more information about the data source and the external table configuration to help us identify the issue.

Hope this helps. Let me know if you have any further questions.

Martin B 21 Reputation points

2024-05-13T16:13:29.6833333+00:00

We are facing the same issue.
Running SELECT COUNT(*) on our external tables fails with:

Cannot insert the value NULL into column 'col', table 'QTable_18b29637192346299ada09f54c1e17b7_2'; column does not allow nulls. INSERT fails.

However, when looking at the SQL requests in Synapse Studio, you can see that under the hood the SELECT (*) is translated in several other queries:

TEMP_ID_103 CREATE TABLE [qtabledb].[dbo].[TEMP_ID_103] ([col] TINYINT NOT NULL ) WITH(DISTRIBUTED_MOVE_FILE=''); SELECT CONVERT(TINYINT, NULL) FROM https://«storage account».dfs.core.windows.net/.../** CREATE TABLE [tempdb].[QTables].[QTable_a651b79031554d959ccbab4bf4da3fbd] ([col] BIGINT ) WITH(DISTRIBUTED_MOVE_FILE=''); SELECT [T1_1].[col] AS [col] FROM (SELECT COUNT_BIG(CAST ((0) AS INT)) AS [col] FROM (SELECT 0 AS [col] FROM [qtabledb].[dbo].[TEMP_ID_103] AS T3_1) AS T2_1 GROUP BY [T2_1].[col]) AS T1_1 OPTION (MAXDOP 3, MIN_GRANT_PERCENT = [MIN_GRANT], DISTRIBUTED_MOVE(N''), MAX_GRANT_PERCENT = [MAX_GRANT])

the last internally generated query fails, and in the request monitor a more detailed error message is displayed:

Microsoft.SqlServer.DataWarehouse.Common.ErrorHandling.MppSqlException[515:6]: Cannot insert the value NULL into column 'col', table 'QTable_18b29637192346299ada09f54c1e17b7_2'; column does not allow nulls. INSERT fails. ---> System.Data.SqlClient.SqlException[515:6]: Cannot insert the value NULL into column 'col', table 'QTable_18b29637192346299ada09f54c1e17b7_2'; column does not allow nulls. INSERT fails. at Microsoft.SqlServer.DataWarehouse.Parallelizer.ParallelizerTask.Wait() at Microsoft.SqlServer.DataWarehouse.Distributor.SqlDistributorTask.Wait() --- End of inner exception stack trace --- at Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.Execute(IQueryContext queryCtx, Boolean isSubBatch, Boolean isBatchInProgress) at Microsoft.SqlServer.DataWarehouse.Engine.Server.ServerInterface.OnExecuteRequest(IClientInterface client, String query, Boolean isInBatch, StatementExecutionContext executionContext, IDictionary`2 replacementInfos, String database, Statement pregeneratedStatement, SqlFrontEndRequest request)

Martin B 21 Reputation points

2024-05-14T06:52:19.53+00:00

After restarting (pause and start) the Dedicated SQL Pool, the issue does not show any more.
This is very worrying...
Sign in to comment

Share via

NULL ISSUE IN EXTERNAL TABLE

1 answer