locked
How to insert data into two tables when there is a foreign key? RRS feed

  • Question

  • Is it possible or even a good idea to use stream analytics to write into two tables when there is a relation between the tables? Simple example could be Order - Orderlines. Each Orderline belongs to some order. My initial guess is that this is not possible because job can only write into single table. I could use two jobs but there is no guarantee that when my second job is trying write Orderline the Order exists.

    In my case I'm receicving some data and I have to pre process it into this type of model where there is a parent record (e.g. Order) and child records (e.g. Orderlines) and I'm wondering can I use stream analytics or do I have to manually insert the data into the database.

    Thursday, February 25, 2016 5:56 AM

Answers

  • Hi,

    Hi,

    You're correct, Azure Stream Analytics jobs will only send data to one table per output. One solution you can try is to insert into the OrderLine table where you have the foreign key and use an insert trigger (e.g. CREATE TRIGGER update_primary_order on OrderLine INSTEAD OF ...) on that table to check and create the primary key row if needed in the Order table.

    However this will have an impact on your streaming job performance because that trigger will be called for each new row inserted. Another solution is to write the data in a temporary table and have Azure Data Factory stored procedure activity run on a schedule and pick up the new rows from the temporary table and add them to both the Order and OrderLine table in the right sequence. See https://azure.microsoft.com/en-us/documentation/articles/data-factory-stored-proc-activity/ for more information.

    Hope it helps,


    This posting is provided "AS IS" with no warranties, and confers no rights

    Thursday, March 10, 2016 9:25 PM

All replies

  • Sorry. I don't understand how the EXTRACT command can help me. I'm sending JSON to azure stream analytics which looks like this:

    {
        "orderNumber": 123,
        "lines": [
            {
                "lineOrder": 1,
    			"product": "foo",
    			"price": 10
            },
            {
                "lineOrder": 2,
    			"product": "bar",
    			"price": 20
            }
    	]
    }
    

    Is there a way to fill the Order and OrderLine tables from this JSON so that the OrderLine has a foreign key to Order table? Of is this a bad way to use stream analytics since all I'm doing is inserting data into the database?

    Monday, February 29, 2016 6:02 AM
  • Hi,

    Hi,

    You're correct, Azure Stream Analytics jobs will only send data to one table per output. One solution you can try is to insert into the OrderLine table where you have the foreign key and use an insert trigger (e.g. CREATE TRIGGER update_primary_order on OrderLine INSTEAD OF ...) on that table to check and create the primary key row if needed in the Order table.

    However this will have an impact on your streaming job performance because that trigger will be called for each new row inserted. Another solution is to write the data in a temporary table and have Azure Data Factory stored procedure activity run on a schedule and pick up the new rows from the temporary table and add them to both the Order and OrderLine table in the right sequence. See https://azure.microsoft.com/en-us/documentation/articles/data-factory-stored-proc-activity/ for more information.

    Hope it helps,


    This posting is provided "AS IS" with no warranties, and confers no rights

    Thursday, March 10, 2016 9:25 PM