Forum Widgets
Latest Discussions
External Table in ADX
Hi, I'm trying to create an external table in ADX which uses a Synapse Analytics (SA) database view (called undelivered). The undelivered view itself is query data from a Cosmos analytical store I've create a user defined idenity Added the identiy to the ADX cluster, SA and Cosmos Updated the ADX database: .alter-merge cluster policy managed_identity[ { "ObjectId": "a3d7ddcd-d625-4715-be6f-c099c56e1567", "AllowedUsages": "ExternalTable" } ] Created the database users in SA -- Create a database user for the ADX Managed Identity CREATE USER [adx-synapse-identity] FROM EXTERNAL PROVIDER; -- Grant read permissions ALTER ROLE db_datareader ADD MEMBER [adx-synapse-identity]; GRANT SELECT ON OBJECT::undelivered TO [adx-synapse-identity]; From within SA I can "SELECT * FROM undelivered" and the correct information is returned But when I come to create the external table in ADX: .create-or-alter external table MyExternalTable ( Status: string ) kind=sql table=undelivered ( h@'Server=tcp:synapse-xxxxx.sql.azuresynapse.net,1433;Database="Registration";ManagedIdentityClientId=<key>;Authentication=Active Directory Managed Identity;' ) with ( managed_identity = "<key>" ) I get the error: Managed Identity 'system' is not allowed by the managed_identity policy for usage: ExternalTable So even with me specifying the managed identity I want to use it is still trying to use the system one. How can I get the external table created with the correct managed identity? Any questions please just ask Thanks
Issue with mysql_request Plugin on Dashboard
Today, while using the Azure Data Explorer dashboard, we noticed that the mysql_request plugin is no longer functioning as expected. Specifically, we are encountering the following error message: evaluate mysql_request(): the following error(s) occurred while evaluating the output schema: The 'mysql_request' plugin cannot be used as the request property request_readonly_hardline is set. Interestingly, the plugin works perfectly fine within the Query tab. However, when called from the dashboard, it fails to execute. This issue was not present yesterday, as it was working seamlessly at that time. Looking further, I realize that in Query Tab, the ADX UI send with "Options":{ "request_readonly_hardline": false } And the request from Dashboard such as Table, Chart. It send with request_readonly_hardline: true Is this on purpose? Thanks
Extending by a function output
datatable(ids: dynamic) [ dynamic(["value1", "value2"]) ] | function(ids) This above snippet works fine and returns a table datatable(date: datetime, ids: dynamic) [ datetime(2022-01-01), dynamic(["value1", "value2"]), datetime(2022-01-02), dynamic(["value3", "value4"]) ] | extend outputs = function(ids) This one however complains that extend expects a scalar and not table that the function returns datatable(date: datetime, ids: dynamic) [ datetime(2022-01-01), dynamic(["value1", "value2"]), datetime(2022-01-02), dynamic(["value3", "value4"]) ] | extend outputs = toscalar(function(ids)) When using toscalar, ids cannot be referenced. Is there a workaround? The function take in dynamic and returns a tubular expression of one row and two columns
SQL Server emulation layer; support for prepared statements
ADX supports very basic transformation of T-SQL to KQL, as it is limited to normal SQL statements. Currently support for prepared statements doesn't seem to be in-place. This would greatly improve security of the emulation layer by enabling prepared statements to be used. This would also allow semantic layers and customer facing analytics tools such as Vizzly to integrate in a secured manner.Disappearing files after ending session
Good day Any idea why my files on a Surface Hub 2s keep disappearing whenever I end session and log back in? Is there a way I can permanently keep them in my Documents folder or at the File Explorer folder and access them at anytime even after ending session? attached I uploaded two pictures. One with the created folder called JEOC Docs and in the other picture the files disappeared after ending session or login out. Like in the below picture.
Connect using SAS Token or APIKey
Hello, There is a lot of samples showing how to connect to ADX using an interactive user or a service principal but I would like to know if there is a way to connect to ADX within a non-interactive service using something like a SAS token or a kind of APIKey ? Regards
Materialized view arg_max based on 2 columns
Hey, if I have a table that has these fields (key. ,....., updated, ingestion_time), I need to build a materialized view that gets the recently updated version for each key, and if there are multiple rows with same key and updated, then I'll take the one with the recent ingestion_time ( from the group that has the recently updated date). is that possible in kusto? it should be a valid materialized view query.
Materialized view with inner join over 2 tables skips data
Hi, I have 2 tables which are ingested from event hubs with standard injection policy (10 minutes etc). I have created a materialized view which has roughly the following definition: .create materialized-view TransactionsWithCustDataMatView on table Transactions { Transactions | join kind=inner Customers on CustomerId | where CreatedOn > LastModifiedOn1 | summarize arg_max(LastModifiedOn1, *) by TransactionId } The problem is that sometimes a customer registers and within 1 minute also performs a transactions. However, since Customers and Transactions are different tables, each with its own ingestion (batching), it happens that sometimes the transaction of a new customer is ingested before the corresponding customer is ingested, and then the above materialized view "skips" the transaction due to the inner join. Is there a way how to control/sync the ingestions of the 2 source table, or schedule the materialized view to run with some delay of 10-20 minutes (when ingestions to both tables would have happened)? Br, DeyanWhy does ADX caching result from related dimension table/mv/function
I'm testing materialized views based on the sample queries below: Lookup to another materialized view (bar_mv) storing last timestamps in field LAST_FOO_TS for some key(a,b,c) .create materialized-view with (dimensionMaterializedViews = "bar_mv") foo_mv on table events_table{ events_table |lookup materialized_view('bar_mv') on a,b,c |where TIMESTAMP > LAST_FOO_TS } Lookup to stored function (bar_v) returning last timestamps in field LAST_FOO_TS for some key(a,b,c) .create materialized-view foo_mv1 on table events_table{ events_table |lookup bar_v() on a,b,c |where TIMESTAMP > LAST_FOO_TS } In both cases, looks like ADX is caching results of the lookup materialized view/function and not refresing it for long time or ever. Is there any way to force refresh of those to get the newest values of LAST_FOO_TS in "where" condition?
