可以使用 KQL 中的动态列名列表来转换数据吗?

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

Is it possible to transform data using a dynamic list of column names in KQL?

问题

我有一个Kusto表,其中包含压缩数据,以及一个表示包含压缩数据的列名的字符串数组。在运行查询之前无法预先知道该数组中的数据,并且每条记录可能不同。我需要解压这些列中的数据(通过gzip-base64-decompress)。我希望从类似以下的input_table转换为output_table

input_table:

compressed_properties foo bar
["foo", "bar"] "compressedData1" "compressedData2"
["foo" ] "compressedData3" "Not compressed"

output_table:

compressed_properties foo bar
["foo", "bar"] "decompressedData1" "decompressedData2"
["foo"] "decompressedData3" "Not compressed"

到目前为止,我能够获得一个包含每个需要解压缩数据的列名的表格。

input_table
| extend intermediate = parse_json(compressed_properties)
| mv-expand ColumnToCompress = intermediate
| project ColumnToCompress

从这里开始,我不太确定如何继续。

英文:

I have a Kusto table that contains compressed data, along with an array of strings representing the names of the columns containing said compressed data. The data in this array cannot be known prior to running the query, and can differ per record. I need to decompress the data in these columns (via gzip-base64-decompress). I'm hoping to go from something like the following input_table to output_table.

input_table:

compressed_properties foo bar
["foo", "bar"] "compressedData1" "compressedData2"
["foo" ] "compressedData3" "Not compressed"

output_table:

compressed_properties foo bar
["foo", "bar"] "decompressedData1" "decompressedData2"
["foo"] "decompressedData3" "Not compressed"

So far, I am able to obtain a table containing a row per column name containing data in need of decompression.

input_table
| extend intermediate = parse_json(compressed_properties)
| mv-expand ColumnToCompress = intermediate
| project ColumnToCompress

From here, I'm not sure how to proceed.

答案1

得分: 1

>我希望将类似以下的input_table转换为output_table

我拿到了如下的输入表格(我拿到了包含2个值的数组,这是一个静态列表):

现在我已经使用下面的KQL查询对其进行解压缩:

let inp = datatable(compressed_properties: dynamic, foo: string, bar: string)
[
  '["foo", "bar"]', 'H4sIAAAAAAAA/wEHAPj/Uml0aHdpa3r2/MsHAAAA', 'H4sIAAAAAAAA/wEFAPr/Ym9qamFwqHKmBQAAAA=='
];
inp
| extend foo = gzip_decompress_from_base64_string(foo)
| extend bar = gzip_decompress_from_base64_string(bar)

Fiddle

尝试按照上面的查询,并根据您的需求进行集成(如果您在数组中使用了多个元素,您需要找到一种循环遍历的方法,然后使用extend)。

英文:

>I'm hoping to go from something like the following inpute_table to output_table.

I taken an input table like below ( and i have taken array with 2 values which is static list):

可以使用 KQL 中的动态列名列表来转换数据吗?

Now I have decompressed it using below kql query:

let inp = datatable(compressed_properties: dynamic, foo: string, bar: string)
[
  '["foo", "bar"]', 'H4sIAAAAAAAA/wEHAPj/Uml0aHdpa3r2/MsHAAAA', 'H4sIAAAAAAAA/wEFAPr/Ym9qamFwqHKmBQAAAA=='
];
inp
| extend foo = gzip_decompress_from_base64_string(foo)
| extend bar = gzip_decompress_from_base64_string(bar)

可以使用 KQL 中的动态列名列表来转换数据吗?

Fiddle.

Try to follow above query and integrate according to your requirement(if you are using many elements in array, you need to find a way to loop through it and then use extend).

huangapple
  • 本文由 发表于 2023年6月16日 01:38:40
  • 转载请务必保留本文链接:https://go.coder-hub.com/76484216.html
匿名

发表评论

匿名网友

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

确定