Pinot 嵌套 json 摄取

Question

我有这个 json 模式

{
  "name":"Pete"
  "age":24,
  "subjects":[
    {
      "name":"maths"
      "grade":"A"
    },
    {
      "name":"maths"
      "grade":"B"
    }
  ]
}

我想将其提取到 pinot 表中以运行如下查询

select age,subjects_grade,count(*) from table group by age,subjects_grade

有没有办法在 pinot 工作中做到这一点？

Answer 1

Pinot有两种处理JSON记录的方式:

<强>1。在摄取时间内压平记录:在这种情况下，我们将每个嵌套字段视为一个单独的字段，因此需要:

• 在表模式中定义那些字段
• 定义转换函数以展平表配置中的嵌套字段

请参阅下面如何定义 subjects_name 和 subjects_grade 列。由于它是一个数组，所以这两个字段在 Pinot 中都是多值列。

<强>2。直接提取 JSON 记录

在这种情况下，我们将每个嵌套字段视为一个单独的字段，因此需要:

• 将表模式中的 JSON 字段定义为具有 maxLength 值的字符串
• 将此字段放入表配置中的noDictionaryColumns和jsonIndexColumns
• 定义转换函数jsonFormat以将表配置中的JSON字段字符串化

请参阅下面如何定义 subjects_str 列。

下面是示例表架构/配置/查询:

示例 Pinot 架构:

{
  "metricFieldSpecs": [],
  "dimensionFieldSpecs": [
    {
      "dataType": "STRING",
      "name": "name"
    },
    {
      "dataType": "LONG",
      "name": "age"
    },
    {
      "dataType": "STRING",
      "name": "subjects_str"
    },
    {
      "dataType": "STRING",
      "name": "subjects_name",
      "singleValueField": false
    },
    {
      "dataType": "STRING",
      "name": "subjects_grade",
      "singleValueField": false
    }
  ],
  "dateTimeFieldSpecs": [],
  "schemaName": "myTable"
}

示例表配置:

{
    "tableName": "myTable",
    "tableType": "OFFLINE",
    "segmentsConfig": {
        "segmentPushType": "APPEND",
        "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy",
        "schemaName": "myTable",
        "replication": "1"
    },
    "tenants": {},
    "tableIndexConfig": {
        "loadMode": "MMAP",
        "invertedIndexColumns": [],
        "noDictionaryColumns": [
            "subjects_str"
        ],
        "jsonIndexColumns": [
            "subjects_str"
        ]
    },
    "metadata": {
        "customConfigs": {}
    },
    "ingestionConfig": {
        "batchIngestionConfig": {
            "segmentIngestionType": "APPEND",
            "segmentIngestionFrequency": "DAILY",
            "batchConfigMaps": [],
            "segmentNameSpec": {},
            "pushSpec": {}
        },
        "transformConfigs": [
            {
                "columnName": "subjects_str",
                "transformFunction": "jsonFormat(subjects)"
            },
            {
                "columnName": "subjects_name",
                "transformFunction": "jsonPathArray(subjects, '$.[*].name')"
            },
            {
                "columnName": "subjects_grade",
                "transformFunction": "jsonPathArray(subjects, '$.[*].grade')"
            }
        ]
    }
}

示例查询:

select age, subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade

select age, json_extract_scalar(subjects_str, '$.[*].grade', 'STRING') as subjects_grade, count(*) from myTable GROUP BY  age, subjects_grade

比较两种方式，我们推荐解决方案1，当字段密度高时(例如每个文档都有字段name和grade，那么它是值得的将它们提取出来作为新列)，它提供了更好的查询性能和更好的存储效率。

对于解决方案2，它的配置更简单，并且适用于稀疏字段(例如只有少数文档有特定字段)。它需要使用 json_extract_scalar 函数来访问嵌套字段。

另请注意 Pinot GROUP BY 在多值列上的行为。

更多引用:

Pinot Column Transformation

Pinot JSON Functions

Pinot JSON Index

Pinot Multi-value Functions