Search code examples
azure-data-explorer

Untyped external table result

I'm running a simple query on an external table. Even that the external table has typed columns, the results is always kinda untyped.

enter image description here

This makes joins on this table not working correct.

enter image description here

Even if I try to force a type column, it is untyped after.

enter image description here

I would expect a result like this:

"DeviceID": 1702843,
"DeviceUniqueIdentifier": "inverter::92092SL40002M",
"DeviceType": "Inverter",
"DeviceName": "scb",
"DataSourceID": 80739,
"DataSourceName": "scb",
"SiteID": 57492,
"SiteName": "something",
"Test": "test"

===== Update related to comment from yifats ==================

Here the related join query:

external_table('DeviceMapping')
| where SiteID == 57492
| extend DeviceIdLeft = tostring(DeviceID)
| join kind= fullouter    
( 
    DeviceData
    | extend DeviceIdRight = DeviceId
 ) on $left.DeviceIdLeft == $right.DeviceIdRight
Solution

  • The data that was insgested was using from string format.

    Wrong syntax:

    .ingest inline into table [DeviceDataIngestion] <|
1702848,2023-01-01 10:00:00,'ac_current',10
2211260,2023-01-01 11:00:00,'ac_current',10

    Correct syntax:

    .ingest inline into table [DeviceDataIngestion] <|
1702848,2023-01-01 10:00:00,"ac_current",10
2211260,2023-01-01 11:00:00,"ac_current",10

    String needs to be escaped with " not '.