topic Re: auto incremented, unique identifier in DB in Streaming Analytics

auto incremented, unique identifier in DB

Ebele — Mon, 23 Jul 2018 10:06:35 GMT

Hi,
From an ESP model we want to update a db table which has column defined as serial key (SERIAL PRIMARY KEY).
In this way postgres creates a auto incremented, unique identifier.

How do we define this column in the ESP schema?
We've tried several options: to insert data into a DB table, ESP expects a field for each column in the schema.

The functional window which writes the values to db looks like this:

<window-functional pubsub="true" name="B03_Write_raw_sensor_data">
<schema>
<fields>
<field name="raw_sensor_data_id" type="string"/>
<field name="esp_id" type="int64" key="true"/>
<field name="sensor_id" type="int64" key="true"/>
<field name="sensor_status" type="string"/>
<field name="sensor_status_description" type="string"/>
<field name="phenomenom_time" type="date" key="true"/>
<field name="value" type="double"/>
<field name="unit" type="string"/>
<field name="raw_sensor_output" type="string"/>
<field name="load_time" type="date"/>
<field name="sensor_metadata_id_from_sensor_metadata" type="int64"/>
<field name="access_license_id_from_access_license" type="int64"/>
<field name="quantity" type="string" key="true"/>
</fields>
</schema>
<function-context>
<functions>
<function name="load_time"><![CDATA[timeCurrent()]]></function>
<function name="access_license_id_from_access_license"><![CDATA[1]]></function>
<function name="raw_sensor_data_id"><![CDATA[concat(esp_id,sensor_id,phenomenom_time,quantity)]]></function>
<function name="sensor_metadata_id_from_sensor_metadata"><![CDATA[sensor_metadata_id]]></function>
</functions>
</function-context>
<connectors>
<connector class="db" name="raw_sensor_data">
<properties>
<property name="type"><![CDATA[sub]]></property>
<property name="snapshot"><![CDATA[false]]></property>
<property name="connectstring"><![CDATA[DSN=lmw_tmp;uid=??;pwd=??;]]></property>
<property name="desttablename"><![CDATA[@schema@.raw_sensor_data]]></property>
</properties>
</connector>
</connectors>
</window-functional>

As a workaround now, we use a concatenation of several fields (concat(esp_id,sensor_id,phenomenom_time,quantity).

Postgres is the database, working with ESP 5.1.

Ebele Veenstra
RWS

Re: auto incremented, unique identifier in DB

Patrick — Tue, 24 Jul 2018 02:10:41 GMT

@Ebele

I don't know much about ESP so can't answer you on this level.

As for loading: You obviously can't load into an auto-increment column so you need to find a way to not have this column present for any inserts. If you can't manage this from the ESP side then one way to go would be loading via a view which doesn't have the column in it (or doesn't pass the value to the table for inserts).

Another (less preferred) way would be to change the column type in the DB and go for an after insert trigger which auto increments the column.

But may be it's just a "how to... ESP) challenge and there is a solution. Given that ESP is rather new I'd suggest you contact SAS TechSupport directly with this question.

I'd be very interested to learn what the solution has been in the end.

Re: auto incremented, unique identifier in DB

Ebele — Tue, 24 Jul 2018 11:19:47 GMT

Patrick, thanks for your suggestions. The view tip might be the best workaround for this situation.
But I'm still interested if it is possible in the ESP model.

In an plain insert statement, you specify the DEFAULT (or NULL) keyword for the serial column, for example:
INSERT INTO test (id, NAME)
VALUES(DEFAULT, 'http://www.postgresql.org')

We tried to define the column in ESP with a value "DEFAULT", but that is not working.

I will check with technical support if there is a way to accomplish this. I will let you know, if there is a way.

Re: auto incremented, unique identifier in DB

FredCombaneyre — Tue, 24 Jul 2018 12:31:53 GMT

When the connector requests the table field's attributes at initialisation, if a field is defined as read only ( SQL_ATTR_READONLY) then it automatically replace the value by "DEFAULT" in the INSERT statement.

Now it would be interesting to look at the ODBC attributes of your table, to see if this attribute is set on this field or if there is another attribute to consider and then enhance the connector.

Hope this helps

Fred