Apache DataSketches Frequent Items Sketches for Google BigQuery

Frequent Items (FI) sketches are used to estimate the frequencies of items in a dataset. They are effective for identifying the most frequent items, such as the top products purchased or the most popular search queries.

Please visit Frequent Items Sketches for more information about this sketch family.

Please visit the main Apache DataSketches website for more information about DataSketches library.

If you are interested in making contributions to this project please see our Community page for how to contact us.

Aggregate Functions

frequent_strings_sketch_merge(sketch BYTES, lg_max_map_size BYTEINT NOT AGGREGATE)

Merges sketches from the given column.

• Param sketch: the column of values.
• Param lg_max_map_size: the sketch accuracy/size parameter as an integer not less than 3.
• Returns: a serialized Frequent Strings sketch as BYTES.

frequent_strings_sketch_build(item STRING, weight INT64, lg_max_map_size BYTEINT NOT AGGREGATE)

Creates a sketch that represents frequencies of the given column.

• Param item: the column of STRING values.
• Param weight: the amount by which the weight of the item should be increased.
• Param lg_max_map_size: the sketch accuracy/size parameter as a BYTEINT not less than 3.
• Returns: a Frequent Strings Sketch, as bytes.

Scalar Functions

frequent_strings_sketch_to_string(sketch BYTES)

Returns a summary string that represents the state of the given sketch.

• Param sketch: the given sketch as sketch encoded bytes.
• Returns: a string that represents the state of the given sketch.

frequent_strings_sketch_get_result(sketch BYTES, error_type STRING, threshold INT64)

Returns an array of rows that include frequent items, estimates, lower and upper bounds given an error_type and a threshold.

• Param sketch: the given sketch as sketch encoded bytes.
• Param error_type: determines whether no false positives or no false negatives are desired.
• Param threshold: a threshold to include items in the result list. If NULL, the maximum error of the sketch is used as a threshold.
• Returns: an array of frequent items with frequency estimates, lower and upper bounds.

Examples

select bqutil.datasketches.frequent_strings_sketch_to_string(bqutil.datasketches.frequent_strings_sketch_build(str, 1, 5)) from unnest(["a", "b", "c"]) as str;

create or replace temp table fs_sketch(sketch bytes);

insert into fs_sketch
(select bqutil.datasketches.frequent_strings_sketch_build(str, 1, 5) from unnest(["a", "b", "c", "d"]) as str);

insert into fs_sketch
(select bqutil.datasketches.frequent_strings_sketch_build(str, 1, 5) from unnest(["a", "a", "c"]) as str);

select bqutil.datasketches.frequent_strings_sketch_get_result(bqutil.datasketches.frequent_strings_sketch_merge(sketch, 5), "NO_FALSE_NEGATIVES", null) from fs_sketch;

drop table fs_sketch;