Starysn
04/15/2025, 9:26 AM{
"schemaName": "invoice_document",
"enableColumnBasedNullHandling": true,
"primaryKeyColumns": ["HEADER.DOCNUMBER"],
"dimensionFieldSpecs": [
{
"name": "HEADER.DOCNUMBER",
"dataType": "STRING",
"notNull": false
},
{
"name": "ITEMS.DOCNUMBER",
"dataType": "STRING",
"notNull": false
},
}
The data example:
{
"HEADER": [
{
"DOCNUMBER": "12040901237"
}
],
"ITEMS": [
{
"DOCNUMBER": "12039475923"
}
}
Or is there any tips on setting the Primary Key for an array type of data.Xiang Fu
Starysn
04/16/2025, 6:31 AM{
"schemaName": "invoice_document",
"enableColumnBasedNullHandling": true,
"primaryKeyColumns": ["DOCNUMBER"],
"dimensionFieldSpecs": [
{
"name": "DOCNUMBER",
"dataType": "STRING",
"notNull": false
}
}
I just need to transform the jsonpathstring in the table.json?
"ingestionConfig": {
"transformConfigs": [
{
"columnName": "DOCNUMBER",
"transformFunction": "JSONPATHSTRING(HEADER, '$.DOCNUMBER')"
}
]
}Xiang Fu
Starysn
04/17/2025, 3:39 AMStarysn
04/17/2025, 7:33 AMXiang Fu
$[0].DOCNUMBER worksStarysn
04/21/2025, 2:06 AMStarysn
04/21/2025, 2:07 AM{
"HEADER": [
{
"DOCNUMBER": "12040901237"
}
],
"ITEMS": [
{
"DOCNUMBER": "12039475923"
},
{
"DOCNUMBER": "12039475924"
},
{
"DOCNUMBER": "12039475925"
},
{
"DOCNUMBER": "12039475926"
},
{
"DOCNUMBER": "12039475927"
}
}Xiang Fu
Xiang Fu
Xiang Fu
Starysn
04/21/2025, 2:47 AM{
"schemaName": "invoice_documents",
"primaryKeyColumns": [
"DOCNUMBER"
],
"enableColumnBasedNullHandling": true,
"dimensionFieldSpecs": [
{
"name": "DOCNUMBER",
"dataType": "STRING",
"notNull": false
}
],
"dateTimeFieldSpecs": [
{
"name": "__metadata$recordTimestamp",
"dataType": "TIMESTAMP",
"format": "SIMPLE_DATE_FORMAT|yyyy-MM-dd",
"granularity": "1:DAYS"
}
]
}
The table config:
{
"tableName": "invoice_documents",
"tableType": "REALTIME",
"segmentsConfig": {
"schemaName": "invoice_documents",
"timeColumnName": "__metadata$recordTimestamp",
"replicasPerPartition": "1",
"minimizeDataMovement": false
},
"tenants": {
"broker": "DefaultTenant",
"server": "DefaultTenant"
},
"tableIndexConfig": {
"streamConfigs": {
"streamType": "kafka",
"stream.kafka.consumer.type": "lowlevel",
"stream.kafka.topic.name": "invoice_documents",
"stream.kafka.decoder.class.name": "org.apache.pinot.plugin.stream.kafka.KafkaJSONMessageDecoder",
"stream.kafka.consumer.factory.class.name": "org.apache.pinot.plugin.stream.kafka20.KafkaConsumerFactory",
"stream.kafka.broker.list": "IP:9092",
"realtime.segment.flush.threshold.rows": "0",
"realtime.segment.flush.threshold.time": "24h",
"realtime.segment.flush.threshold.segment.size": "150M",
"stream.kafka.consumer.prop.auto.offset.reset": "smallest"
},
"invertedIndexColumns": [
"DOCNUMBER"
],
"aggregateMetrics": false,
"enableDefaultStarTree": false,
"nullHandlingEnabled": true,
"rangeIndexVersion": 2,
"optimizeDictionaryForMetrics": false,
"optimizeDictionary": false,
"loadMode": "MMAP",
"enableDynamicStarTreeCreation": false,
"columnMajorSegmentBuilderEnabled": true,
"noDictionarySizeRatioThreshold": 0.85,
"autoGeneratedInvertedIndex": false,
"createInvertedIndexDuringSegmentGeneration": false
},
"metadata": {
"customConfigs": {}
},
"routing": {
"instanceSelectorType": "strictReplicaGroup"
},
"upsertConfig": {
"mode": "FULL",
"consistencyMode": "NONE",
"metadataTTL": 172800,
"dropOutOfOrderRecord": false,
"enableDeletedKeysCompactionConsistency": false,
"enablePreload": true,
"deletedKeysTTL": 0,
"enableSnapshot": true,
"newSegmentTrackingTimeMs": 10000,
"hashFunction": "MD5",
"defaultPartialUpsertStrategy": "OVERWRITE",
"upsertViewRefreshIntervalMs": 3000,
"allowPartialUpsertConsumptionDuringCommit": false
},
"ingestionConfig": {
"complexTypeConfig": {
"delimiter": ".",
"fieldsToUnnest": [
"HEADER"
],
"collectionNotUnnestedToJson": "NON_PRIMITIVE"
},
"transformConfigs": [
{
"columnName": "DOCNUMBER",
"transformFunction": "JSONPATHSTRING(HEADER, '$.[0]DOCNUMBER')"
}
],
"continueOnError": false,
"rowTimeValueCheck": false,
"segmentTimeValueCheck": true
},
"isDimTable": false
}Xiang Fu
Xiang Fu
Starysn
04/21/2025, 2:49 AMStarysn
04/21/2025, 2:51 AMStarysn
04/21/2025, 2:51 AMStarysn
04/21/2025, 2:54 AMXiang Fu
Xiang Fu
{
"HEADER": [
{
"DOCNUMBER": "12040901237"
}
],
"ITEMS": [
{
"DOCNUMBER": "12039475923"
},
{
"DOCNUMBER": "12039475924"
},
{
"DOCNUMBER": "12039475925"
},
{
"DOCNUMBER": "12039475926"
},
{
"DOCNUMBER": "12039475927"
}
}Xiang Fu
Starysn
04/21/2025, 6:59 AMStarysn
04/21/2025, 7:15 AMITEMS, $[0].DOCNUMBER works for all the docnumbers inside the items, like could it read all the data and create 1 row for each?Xiang Fu
Xiang Fu
Xiang Fu
Starysn
04/21/2025, 8:16 AMXiang Fu
Starysn
04/21/2025, 9:07 AMXiang Fu
Xiang Fu
Starysn
04/22/2025, 1:08 AM__metadata$recordTimestamp, how can I remove it?Xiang Fu
Starysn
04/22/2025, 6:31 AMStarysn
04/22/2025, 6:32 AMXiang Fu
Xiang Fu
Starysn
04/22/2025, 6:32 AMXiang Fu
Xiang Fu
Starysn
04/22/2025, 6:34 AMXiang Fu
Xiang Fu
Starysn
04/22/2025, 6:39 AMXiang Fu
Starysn
04/22/2025, 7:02 AM