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.
– Yoni L., Commented Oct 8, 2019 at 21:23
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
– jlhjr, Commented Oct 8, 2019 at 21:39

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

0

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

answered Oct 9, 2019 at 1:01

Yoni L.

26.5k3 gold badges46 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

jlhjr Over a year ago

Thanks, did not know about the union withsource = T *, but it seems to return all rows and columns in the database as a single tabular expression with an additional column saying which table the row came from. We actually do know the output schema - it's the same as the table schema for the table T == table_name, but it seems like those columns would need to be explicitly referenced in the | project ... statement, so that doesn't work. Also, in some cases, the column name could have changed during the union to append the column type.

jlhjr Over a year ago

I tried find withsource as well, but it doesn't seem like the withsource column is included as part of the columns that find searches. Basically, it sounds like what I am trying to do is not supported, but the above answer might be a workaround for some.

Collectives™ on Stack Overflow

Query ADX table name dynamically

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related