Query ADX table name dynamically

Question

I have a need to be able to query Azure Data Explorer (ADX) tables dynamically, that is, using application-specific metadata that is also stored in ADX.

If this is even possible, the way to do it seems to be via the table() function. In other words, it feels like I should be able to simply write:

let table_name = <non-trivial ADX query that returns the name of a table as a string>;
table(table_name) | limit 10

But this query fails since I am trying to pass a variable to the table() function, and "a parameter, which is not scalar constant string can't be passed as parameter to table() function". The workaround provided doesn't really help, since all the possible table names are not known ahead of time.

Is there any way to do this all within ADX (i.e. without multiple queries from the client) or do I need to go back to the drawing board?

1) in essence, what is the logic of non-trivial ADX query...; 2) what is the number of distinct table names that non-trivial ADX query... may return? — Yoni L.
Thanks for the quick reply, @YoniL! A simplified version of it is something like: let LabelTable = datatable(table_name:string, label:string, updated:datetime) [ "TableA", "MyLabel", "2019-10-08", "TableB", "MyLabel", "2019-10-02" ]; let GetLabeledTable = (l:string) { toscalar( LabelTable | where label == l | order by updated desc | limit 1 ); }; let table_name = GetLabeledTable('MyLabel'); table(table_name) | limit 10 — jlhjr

Yoni L. Yoni L. · Accepted Answer · 2019-10-09T01:01:13

if you know the desired output schema, you could potentially achieve that using union (note that in this case, the result schema will be the union of all tables, and you'll need to explicitly project the columns you're interested in)

let TableA = view() { print col1 = "hello world"};
let TableB = view() { print col1 = "goodbye universe" };
let LabelTable = datatable(table_name:string, label:string, updated:datetime)
[ 
    "TableA", "MyLabel", datetime(2019-10-08),
    "TableB", "MyLabel", datetime(2019-10-02) 
];
let GetLabeledTable = (l:string)
{
    toscalar(
        LabelTable
        | where label == l
        | order by updated desc
        | limit 1
    )
};
let table_name = GetLabeledTable('MyLabel');
union withsource = T *
| where T == table_name
| project col1

Query ADX table name dynamically

1 Answers