Im new to polybase. I have linked my SQL 2019 server to a third parties Azure cosmos and i am able to query data out of my collection. I am getting an error out when i try to query date fields though. In the documents the dates are defined as:
"created" : {
"$date" : 1579540834768
},
In my external table i have the column defined as
[created] DATE,
I have tried to create the column as int and nvarchar(128) but the schema detection rejects it each time. (i have tried to create a field created_date but the schema detection also disagree's that this is correct.
When i try a query that returns any of the date fields i get this error:
Msg 105082, Level 16, State 1, Line 8
105082;Generic ODBC error: [Microsoft][Support] (40460) Fractional data truncated while performing conversion. .
OLE DB provider "MSOLEDBSQL" for linked server "(null)" returned message "Unspecified error".
Msg 7421, Level 16, State 2, Line 8
Cannot fetch the rowset from OLE DB provider "MSOLEDBSQL" for linked server "(null)". .
This happens if i try and exclude null values in my query - even when filtering to specific records where the date is populated (validated using the Azure portal interface)
Is there something i should be doing to handle the integer date from the json records; or another type i can use to get my external table to work?
Found a solution. SQL Server recommends the wrong type for mongodb dates in the schema. Using DateTime2 resolved the issue. Found this on a polybase type mapping page in msdn.