locked
Can columns be defined as optional in CREATE TABLE? RRS feed

  • Question

  • I have a Stream Analytics job that reads from an Event Hub that is populated with JSON data.  As a space saving optimization, properties that are null, are not included in the JSON message posted to the Event Hub.

    SA handles the missing properties without a problem, with just a SELECT.   But, when I add a CREATE TABLE, with all of the possible properties,  then messages with missing properties fail to be processed. 

    Question 1:  Is there a way to define columns (properties) as optional in the CREATE TABLE?

    Question 2:  If not, does CREATE TABLE provide a significant performance gain?  Is it large enough to offset the JSON message size increase (since all properties will have to be present, even if they are null).

    Thanks

    Friday, April 17, 2015 6:43 PM

Answers

  • Properties cannot be optional in CREATE TABLE. It does provide certain level of performance gain because all the properties not present in the CREATE TABLE schema are dropped right away at the beginning of the processing. Another benefit of using CREATE TABLE is you inform the system of the expected data types, so the properties are converted to the right type at the very beginning; in your query, you don't need to CAST them any more.

    • Marked as answer by JThompkins Monday, April 20, 2015 9:48 PM
    Sunday, April 19, 2015 11:24 PM