does anyone have experience ingesting records to create hll sketches? we seem to be ingesting data but all of the cardinality counts are 0

How are you ingesting the data? and how are you querying it? If you're using native ingest (instead of MSQ) the aggregator you will want to use at ingest is

HLLSketchBuild

. At query time you then can use

HLLSketchMerge

to query them. If you're using MSQ, you probably want to use

DS_HLL

when you ingest and

APPROX_COUNT_DISTINCT_DS_HLL

when you query.

hi ben - we are ingesting it using native ingest and are using the HLLSketchBuild type. We think the issue is related to how we are ingesting or how we specify the new datasource in our custom plugin. For the ingestion side - should be just be able to ingest the strings that we want to turn into a HLL as normal strings? Or should it be an array of strings, or something different?

Just ingesting the strings is fine

hi Ben-- our druid console query looks like:

SELECT COUNT( DISTINCT "schema_coordinate_cardinality") from "cardinality_stats"

does that seem right to you? it always returns 0, which is incorrect. cardinality stats has the following metricsSpec:

"metricsSpec" : [ {
      "name" : "client_name_cardinality",
      "fieldName" : "client_name",
      "type" : "HLLSketchBuild",
      "lgK" : 12,
      "tgtHllType" : "HLL_4"
    }, {
      "name" : "client_version_cardinality",
      "fieldName" : "client_version",
      "type" : "HLLSketchBuild",
      "lgK" : 12,
      "tgtHllType" : "HLL_4"
    }, {
      "name" : "operation_shape_cardinality",
      "fieldName" : "query_id",
      "type" : "HLLSketchBuild",
      "lgK" : 12,
      "tgtHllType" : "HLL_4"
    }, {
      "name" : "schema_coordinate_cardinality",
      "fieldName" : "parent_type_and_field_name",
      "type" : "HLLSketchBuild",
      "lgK" : 12,
      "tgtHllType" : "HLL_4"
    } ],

Can you try

SELECT APPROX_COUNT_DISTINCT_DS_HLL(schema_coordinate_cardinality) from "cardinality_stats"

?

that's also 0. what should our dimensions spec look like? my understanding is that it shouldn't contain the columns that we're performing an HLL sketch on

You're correct to leave it out of the dimension spec. You should not need it in the IOConfig anymore. (That post was from 2020, and we have made some changes to the schema auto-detection capabilities) What is your input source? Here is an example from the wikipedia dataset.

"type": "index_parallel",
  "spec": {
    "ioConfig": {
      "type": "index_parallel",
      "inputSource": {
        "type": "http",
        "uris": [
          "<https://druid.apache.org/data/wikipedia.json.gz>"
        ]
      },
      "inputFormat": {
        "type": "json"
      }
    },
    "dataSchema": {
      "granularitySpec": {
        "segmentGranularity": "day",
        "queryGranularity": "hour",
        "rollup": true
      },
      "dataSource": "wikipedia_rollup",
      "timestampSpec": {
        "column": "timestamp",
        "format": "iso"
      },
      "dimensionsSpec": {
        "dimensions": [
          "channel",
          "page"
        ]
      },
      "metricsSpec": [
        {
          "name": "count",
          "type": "count"
        },
        {
          "type": "HLLSketchBuild",
          "name": "distinct_users",
          "fieldName": "user"
        }
      ]
    },
    "tuningConfig": {
      "type": "index_parallel",
      "partitionsSpec": {
        "type": "hashed"
      },
      "forceGuaranteedRollup": true
    }
  }
}

got it, I'll continue to leave it out. our input source is kafka. this is what the supervisor spec looks like:

{
  "type": "kafka",
  "dataSchema": {
    "dataSource": "cardinality_stats",
    "timestampSpec": {
      "column": "timestamp",
      "format": "auto"
    },
    "dimensionsSpec": {
      "dimensions": [
        {
          "name": "id",
          "type": "string"
        },
        {
          "name": "tag",
          "type": "string"
        }
      ]
    },
    "metricsSpec": [
      {
        "name": "client_name_cardinality",
        "fieldName": "client_name",
        "type": "HLLSketchBuild",
        "lgK": 12,
        "tgtHllType": "HLL_4"
      },
      {
        "name": "client_version_cardinality",
        "fieldName": "client_version",
        "type": "HLLSketchBuild",
        "lgK": 12,
        "tgtHllType": "HLL_4"
      },
      {
        "name": "operation_shape_cardinality",
        "fieldName": "query_id",
        "type": "HLLSketchBuild",
        "lgK": 12,
        "tgtHllType": "HLL_4"
      }
    ],
    "granularitySpec": {
      "type": "uniform",
      "segmentGranularity": "HOUR",
      "queryGranularity": "MINUTE",
      "rollup": true
    }
  },
  "ioConfig": {
    "topic": "stats",
    "inputFormat": {
      "type": "stats"
    },
    "consumerProperties": {
      "bootstrap.servers": "test.server:8080",
      "max.partition.fetch.bytes": 1234567,
      "fetch.max.bytes": 12345678,
      "max.poll.records": 500
    },
    "taskCount": 1,
    "replicas": 1,
    "period": "test",
    "taskDuration": "test",
    "lateMessageRejectionPeriod": "test",
    "earlyMessageRejectionPeriod": "test",
    "useEarliestOffset": false
  },
  "tuningConfig": {
    "type": "kafka",
    "logParseExceptions": true,
    "maxParseExceptions": 0,
    "maxSavedParseExceptions": 1,
    "maxRowsPerSegment": 5000000,
    "workerThreads": 1,
    "indexSpec": {
      "stringDictionaryEncoding": {
        "type": "test",
        "bucketSize": 4,
        "formatVersion": 0
      }
    },
    "intermediatePersistPeriod": "test"
  }
}

what is the inputFormat? I am wondering if the field name(s) aren't exactly matching what is in the input data.

sorry, typo. the inputformat for the supervisor is:

"ioConfig" : {
    "topic" : "engine-reports-stats",
    "inputFormat" : {
      "type" : "mdg_cardinality_stats"
    },

and the inputFormat for the task that the supervisor submitted is:

"inputFormat": {
      "type": "mdg_cardinality_stats"
    },

should I be submitting additional tasks for ingestion?

input format should contain the data type. For example csv

"inputFormat": {
    "type": "csv",
    "columns" : ["timestamp","page","language","user","unpatrolled","newPage","robot","anonymous","namespace","continent","country","region","city","added","deleted","delta"]
  },

for both the supervisor and the task?

The supervisor controls the task- so if you set it at the supervisor it will set it in the tasks.

turns out that the type is a custom input type that we use, and the issue was in the custom input type-- the data was being put into the wrong field of the metric. this issue is solved now. thank you for your help, Ben!