Geek Answers Handbook

Processing inline new rows when creating / selecting external tables in SQL data warehouse

In SQL Data Warehouse (editors, please do not change this, this is the actual name: here ) I have an JobCandidate_extexternal table that looks like this.

CREATE EXTERNAL TABLE [HumanResources].[JobCandidate_ext](
    [JobCandidateID] int,
    [BusinessEntityID] int,
    [Resume] Varchar(8000),
    [ModifiedDate] Datetime
)
WITH (
    LOCATION='/[HumanResources].[JobCandidate]/data.txt',
    DATA_SOURCE=AzureStorage,
    FILE_FORMAT=TextFile)
GO

The column [Resume] was a XMLin SQL Server, but in SQL Data Warehouses XML must be converted to varchar(8000)as described.

I use a flat file data.txtto export data to blob and then create an external table from it.

The column [Resume]has carriages in it (as expected from an XML file), and so when SELECT * FROM [HumanResources].[JobCandidate_ext]you start, you get an error message. In this case:

- (0 ) : 1 2 .
(/[HumanResources].[JobCandidate]/data.txt )Column ordinal: 0, : INT, : .... ( ), : NVARCHAR INT.

, , .

UTF-8 Hadoops LineRecordReader. "\ r", "\n", "\ r\n". .

, : No closing string delimiter.

- (0 ) : 1 1 .
(/[HumanResources].[JobCandidate]/data.txt) : 2, : VARCHAR (8000) SQL_Latin1_General_CP1_CI_AS, : 'ShaiBassli ( Tokenization), : .

?

+4

More articles:


All Articles