KQL: 如何获取所有(字符串类型)列的max(strlen(colname))?

huangapple go评论45阅读模式
英文:

KQL: How to get max(strlen(colname)) for all (string type) columns in general?

问题

我有一个包含各种字符串类型列的表。我想要一个以(列名 | 最大列长度)形式的“摘要”。

我看到getschema函数可以获取带有类型的列,但我不知道如何利用它来获取最大列长度,而不是明确引用每个列。我正在寻找一种通用的(查询中没有明确名称)方法来实现这一点,它将针对运行的任何表都能正常工作。

在SQL中,我会认为这是一个动态SQL用例。

在Kusto Query Language (KQL) 中应该如何做呢?

英文:

I have a table with various string type columns. I want a "summary" of the form (colname | maxcollength).

I see the getschema function gets me the columns with types, but I have no idea how to leverage that to pull maxcollength without explicitly referencing each column. I'm looking for a general (no explicit names in the query) way to achieve this, which will work OOB against any table it runs against.

In sql I would think of this as a dynamic sql use case.

How would one do it in kql?

答案1

得分: 1

以下是翻译好的部分:

你可以运行此查询以生成一个会产生所需结果的查询:

let TableName = "MyTable";
let TimeFilter = "where Timestamp > ago(1d)";
table(TableName)
| getschema
| where ColumnType == "string"
| summarize Temp = array_strcat(make_list(strcat("max(strlen(", ColumnName, "))")), ", ")
| project strcat(TableName, " | ", TimeFilter, " | summarize ", Temp)

只需更新前两行,设置表名和所需的时间筛选条件。

结果将是这样的查询:

MyTable
| where LastUpdated > ago(1d)
| summarize max(strlen(MyStringColumn)), max(strlen(AnotherStringColumn)), ...

这正是你需要运行以获得所需结果的查询。

英文:

You can run this query to generate a query that will produce the results you need:

let TableName = "MyTable";
let TimeFilter = "where Timestamp > ago(1d)";
table(TableName)
| getschema
| where ColumnType == "string"
| summarize Temp = array_strcat(make_list(strcat("max(strlen(", ColumnName, "))")), ", ")
| project strcat(TableName, " | ", TimeFilter, " | summarize ", Temp)

Just update the first two lines, where you set the table name, and the desired time filter.

As a result, you'll get a query that looks like this:

MyTable
| where LastUpdated > ago(1d)
| summarize max(strlen(MyStringColumn)), max(strlen(AnotherStringColumn)), ...

This is exactly the query you need to run to get the desired result.

huangapple
  • 本文由 发表于 2023年4月4日 08:27:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/75924638.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定