Hey community, I have a question, can I do a upse...
# troubleshooting
s
Hey community, I have a question, can I do a upsert when the data is complex type/array type of data, can I set the primaryKeyColumns to this?:
Copy code
{
  "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:
Copy code
{
    "HEADER": [
        {
            "DOCNUMBER": "12040901237"
        }
    ],
    "ITEMS": [
        {
            "DOCNUMBER": "12039475923"
        }
}
Or is there any tips on setting the Primary Key for an array type of data.
x
you can flat the column and make it primary key
s
So I can change it like this:
Copy code
{
  "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?
Copy code
"ingestionConfig": {
    "transformConfigs": [
     {
        "columnName": "DOCNUMBER",
        "transformFunction": "JSONPATHSTRING(HEADER, '$.DOCNUMBER')"
     }
     ]
}
x
yes
s
thank you so much for your info
the result was null, I think it is because the data was in array, but then I flatten the pinot column, so it returns all null(the DOCNUMBER = null)
x
see if
$[0].DOCNUMBER
works
s
ow yeah that works, thank you, but my upsert doesn't work, how can I fix it?
and what if The data example:
Copy code
{
    "HEADER": [
        {
            "DOCNUMBER": "12040901237"
        }
    ],
    "ITEMS": [
        {
            "DOCNUMBER": "12039475923"
        },
        {
            "DOCNUMBER": "12039475924"
        },
        {
            "DOCNUMBER": "12039475925"
        },
        {
            "DOCNUMBER": "12039475926"
        },
        {
            "DOCNUMBER": "12039475927"
        }
}
x
you need to configure upsert in the table config as well
for your data, do you have only one header docnum and many items docnums?
if so you can try to make items a json column then
s
so here's my new schema:
Copy code
{
  "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:
Copy code
{
  "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
}
x
how many kafka partitions you have?
you need to ensure all pk goes to same kafka partition
s
so I check it on redpandas and I have 8 partitions id
ohhh I see, but I dont know if the pk goes to the same kafka partition or no
how can I check it?
regarding your reply on items as a json column, I want to make like 1 row for each items, like row 1= the first item, row 2= second items, from the same header and so on, My plan was to separate the table for headers, and items, so it won't get mixed
x
when you produce the message, you can give a key which should be your header docnum
can you give an expected sample table example for your data
Copy code
{
    "HEADER": [
        {
            "DOCNUMBER": "12040901237"
        }
    ],
    "ITEMS": [
        {
            "DOCNUMBER": "12039475923"
        },
        {
            "DOCNUMBER": "12039475924"
        },
        {
            "DOCNUMBER": "12039475925"
        },
        {
            "DOCNUMBER": "12039475926"
        },
        {
            "DOCNUMBER": "12039475927"
        }
}
if you flat this to 5 rows, then you don’t have a primary key
s
ow yeah that makes sense, so if I set the pk to header.docnum, while i want to extract the items too, then it will be replaced to the newest items ingested?
Does
ITEMS, $[0].DOCNUMBER
works for all the docnumbers inside the items, like could it read all the data and create 1 row for each?
x
no, it’s 1:1 mapping
your case is 1 to many then you need to use flatten job for this
s
aa I see, so I need a custom decoder/recordreader
x
flatten can do 1:N mapping
s
oh I just use this `$MULTIPLE_RECORDS_KEY$`in the table transformation config?
x
no, just treat it as an unested field
ITEMS.DOCNUMBER
s
ooh I see, thank you so much sir, it does helps a lot, but what if there are duplicate data, if the entire row was exactly the same except the
__metadata$recordTimestamp
, how can I remove it?
x
my question here is from eyeball how can you identify the row?
s
so I sort it based on the DOCNUMBER, then I check if there are 2 or more identical data
I do this to see if the upsert works or not
x
then you can assume the first row of that DOCNUMBER is the valid row?
or last
s
the last one
x
ok
then you need to do upsert
s
the thing is I still see duplicate data, even when I add option(skipUpsert = false) in the query
x
then it means your data are spread to multiple kafka partitions
you need to repartition stream
s
aaa I see, is the reparation stream configured in pinot or kafka?
x
in kafka, when you produce to kafka, you use a different kafka producer api and you can offer docnumber as the key
s
aa I see thank you for your insight