Question 103160 in Azure Question posted in
The official documentation to get you started can be found here.

Azure ADX ingest data across databases on the same cluster

I want to find a way to ingest data from one database to another (same cluster), based on when the source table gets updated. We have an update query that is able to see across databases , however when I try to update the update policy with:

.alter table DataBase2.ChildTable policy update
'''
[
    {
        "IsEnabled": true,
        "Source": "DataBase1.SourceTable",
        "Query": "QueryFunction()"
    }
]
'''

This produces the following error:

 Error during execution of a policy operation: Source table does not exist for policy: 'IsEnabled = 'True', Source = 'DataBase1.SourceTable', Query = 'QueryFunction()', IsTransactional = 'False', PropagateIngestionProperties = 'False''

Anyone got any ideas?

-Thanks!

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    So we found a solution:

    Create a source table that is ingestion with a 0 time retention policy The command:

    .alter table SourceTableName policy retention 
'''
{
  "SoftDeletePeriod": "0", "Recoverability":"Disabled"
}
'''

    This will make the table never commit any rows.

    Next, for each of your update policies, you need to structure them as follows:

    .alter table Childtable policy update @'[{"Source": "SourceTableName", "Query": "UpdateQuery()", "IsEnabled": "True", "IsTransactional": "True"}]'

    This will make the child tables pull the rows as and when they are ingested, but the rows will NEVER be committed to the source table. Think of it as a stepping stone. I recommend having another DB that also ingests from the same source as a backup, allowing you to do .appends as needed (if ingestion ever fails) From here, you can add RLS to the child tables as wanted!

    Retention reference

    RLS Reference


  2. 0

    As of this writing**, and as the documentation explicitly mentions – it’s not possible to have cross-database queries as part of an update policy.

    One thing you could do is periodically run .set-or-append](https://learn.microsoft.com/en-us/azure/data-explorer/kusto/management/data-ingestion/ingest-from-query) commands to do cross-database ingestion, while leveraging the database cursor to guarantee that data gets processed exactly once.

    [ ** Cross-database update policies may be supported in the future ]

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search