Symptoms
Starting in SQL Server 2019 CU6, the Create External Table definition has been extended to include a Rejected_Row_Location parameter. This value represents the location in the External Data Source in which the Error Files and Rejected Rows will be written. This option can be used only with external tables using the DELIMITEDTEXT file format on Hadoop type external data sources.
CREATE EXTERNAL TABLE [dbo].[Reject_Example]
(
[Col_one] TINYINT NULL,
[Col_two] VARCHAR(100) NULL,
[Col_three] NUMERIC(2,2) NULL
)
WITH
(
DATA_SOURCE = EDS_Reject_Row
,LOCATION = '/Read_Directory'
,FILE_FORMAT = CSV
,REJECT_TYPE = VALUE
,REJECT_VALUE = 100
,REJECTED_ROW_LOCATION=‘/Reject_Directory'
)
What happens when data is loaded?
When a user runs a Create Table as Select (CTAS) on the previous table, PolyBase creates a directory on the External Data Source at the Rejected_Row_Location, if one doesn’t exist. A child directory is created that is named "_rejectedrows." The Underscore (_) character makes sure that the directory is escaped for other data processing, unless explicitly named in the location parameter. Within this directory, a folder is created based on the time of load submission in the format YearMonthDay-HourMinuteSecond (for example, 20180330-173205). In this folder, two types of files are written: The _reason file and the data file.
The reason file and the data file both have the queryID associated with the CTAS statement. Because the data and the reason are in separate files, corresponding files have a matching suffix.
Status
Microsoft has added new features that are listed in the "Applies to" section.
Resolution
This issue is fixed in the following cumulative update for SQL Server:
About cumulative updates for SQL Server:
Each new cumulative update for SQL Server contains all the hotfixes and all the security fixes that were included with the previous cumulative update. Check out the latest cumulative updates for SQL Server:
References
Learn about the terminology that Microsoft uses to describe software updates.
