Home

SQL reference data in Azure Stream Analytics

%3CLINGO-SUB%20id%3D%22lingo-sub-719041%22%20slang%3D%22en-US%22%3ESQL%20reference%20data%20in%20Azure%20Stream%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-719041%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20with%20Time%20as%20Data%20type%20in%20my%20SQL%20Server%20table%20which%20is%20being%20used%20as%20a%20reference%20data%20in%20Azure%20Stream%20Analytics.%20What%20I'm%20doing%20is%20cast%20the%20values%20in%20the%20same%20data%20type%20which%20they%20belong%20to%20before%20using%20them%20as%20a%20look%20up%20data%20and%20pass%20on%20to%20Power%20BI%20datasets%2C%20but%20it%20seems%20SQL%20reference%20input%20does%20not%20support%20TimeSpan.%20For%20a%20work%20around%2C%20as%20of%20now%20I'm%20casting%20it%20to%20varchar%20but%20I%20believe%20reference%20input%20should%20support%20TimeSpan%20soon.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-732041%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20reference%20data%20in%20Azure%20Stream%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732041%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F366002%22%20target%3D%22_blank%22%3E%40anishXsharma%3C%2FA%3E%26nbsp%3BPlease%20take%20a%20look%20at%20the%20current%20supported%20types%20in%20ASA%3A%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fstream-analytics-query%2Fdata-types-azure-stream-analytics%3Ftoc%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fstream-analytics%252FTOC.json%26amp%3Bbc%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fbread%252Ftoc.json%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fstream-analytics-query%2Fdata-types-azure-stream-analytics%3Ftoc%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fstream-analytics%252FTOC.json%26amp%3Bbc%3Dhttps%253A%252F%252Fdocs.microsoft.com%252Fen-us%252Fazure%252Fbread%252Ftoc.json%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20'DateTime'%20be%20used%20as%20an%20alternative%20in%20your%20case%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-732043%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20reference%20data%20in%20Azure%20Stream%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-732043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F366002%22%20target%3D%22_blank%22%3E%40anishXsharma%3C%2FA%3E%26nbsp%3BIf%20I%20didn't%20understand%20your%20question%20correctly%20could%20you%20also%20paste%20a%20snippet%20of%20your%20ASA%20query%20section%20and%20sql%20reference%20query%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733611%22%20slang%3D%22en-US%22%3ERe%3A%20SQL%20reference%20data%20in%20Azure%20Stream%20Analytics%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369460%22%20target%3D%22_blank%22%3E%40AndyZ_ASA%3C%2FA%3E%26nbsp%3BI%20think%20the%20best%20solution%20should%20be%20to%20support%20TimeSpan%20data%20type%20in%20Azure%20Stream%20Analytics%2C%20so%20that%20the%20Power%20BI%20reports%20can%20be%20created%20without%20altering%20or%20converting%20the%20field%20to%20the%20desired%20output.%3C%2FP%3E%3CP%3EAs%20of%20now%20rather%20than%20converting%20it%20to%20a%20DateTime%2C%20I%20have%20converted%20it%20to%20a%20nvarchar(max).%20The%20fields%20are%20dynamic%20so%20I%20don't%20want%20any%20individual%20manipulations%20to%20happen.%3C%2FP%3E%3C%2FLINGO-BODY%3E
anishXsharma
New Contributor

I have a column with Time as Data type in my SQL Server table which is being used as a reference data in Azure Stream Analytics. What I'm doing is cast the values in the same data type which they belong to before using them as a look up data and pass on to Power BI datasets, but it seems SQL reference input does not support TimeSpan. For a work around, as of now I'm casting it to varchar but I believe reference input should support TimeSpan soon.

3 Replies

@anishXsharma Please take a look at the current supported types in ASA:

https://docs.microsoft.com/en-us/stream-analytics-query/data-types-azure-stream-analytics?toc=https%...

 

Could 'DateTime' be used as an alternative in your case?

 

@anishXsharma If I didn't understand your question correctly could you also paste a snippet of your ASA query section and sql reference query?

@AndyZ_ASA I think the best solution should be to support TimeSpan data type in Azure Stream Analytics, so that the Power BI reports can be created without altering or converting the field to the desired output.

As of now rather than converting it to a DateTime, I have converted it to a nvarchar(max). The fields are dynamic so I don't want any individual manipulations to happen.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
ChirmyRam in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies