Skip to content

avz/mysql-hll

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

51 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

HyperLogLog UDF for MySQL

Examples

Incremental accumulation

CREATE TABLE `uniqueUsersPerDay`(`day` DATE PRIMARY KEY, `hll` BLOB);
SET @bits = 14; -- set storage size to 2^@bits bytes (standard error 0.81%)

SET @userId = 1;
INSERT INTO `uniqueUsersPerDay`(`day`, `hll`)
	VALUES(CURDATE(), HLL_CREATE(@bits, @userId))
	ON DUPLICATE KEY UPDATE `hll` = HLL_ADD(`hll`, @userId)
;

SET @userId = 2;
INSERT INTO `uniqueUsersPerDay`(`day`, `hll`)
	VALUES(CURDATE(), HLL_CREATE(@bits, @userId))
	ON DUPLICATE KEY UPDATE `hll` = HLL_ADD(`hll`, @userId)
;

SET @userId = 2;
INSERT INTO `uniqueUsersPerDay`(`day`, `hll`)
	VALUES(CURDATE(), HLL_CREATE(@bits, @userId))
	ON DUPLICATE KEY UPDATE `hll` = HLL_ADD(`hll`, @userId)
;
SELECT HLL_COUNT(`hll`) AS `dau` FROM `uniqueUsersPerDay` WHERE `day` = CURDATE();
+------+
| dau  |
+------+
|    2 |
+------+

COUNT DISTINCT replacement

SELECT HLL_COUNT_DISTINCT(`key`) AS `uniq` FROM (
	SELECT 1 AS `key`
	UNION SELECT 2
	UNION SELECT 3
	UNION SELECT 4
	UNION SELECT 2
	UNION SELECT 3
) AS t;
+------+
| uniq |
+------+
|    4 |
+------+

Installation (how to draw an owl)

Build and install plugin library

% make
# make install

Install functions in MySQL

CREATE FUNCTION HLL_COUNT RETURNS INTEGER SONAME 'mysql-hll.so';
CREATE FUNCTION HLL_CREATE RETURNS STRING SONAME 'mysql-hll.so';
CREATE FUNCTION HLL_ADD RETURNS STRING SONAME 'mysql-hll.so';
CREATE FUNCTION HLL_MERGE RETURNS STRING SONAME 'mysql-hll.so';
CREATE AGGREGATE FUNCTION HLL_GROUP_COUNT RETURNS INTEGER SONAME 'mysql-hll.so';
CREATE AGGREGATE FUNCTION HLL_GROUP_MERGE RETURNS STRING SONAME 'mysql-hll.so';
CREATE AGGREGATE FUNCTION HLL_GROUP_CREATE RETURNS STRING SONAME 'mysql-hll.so';
CREATE AGGREGATE FUNCTION HLL_COUNT_DISTINCT RETURNS INTEGER SONAME 'mysql-hll.so';

API

Basic functions

  • blob HLL_CREATE(bits[, key1[, key2[, ...]]])
  • blob HLL_ADD(blob, key1[, key2[, ...]])
  • int HLL_COUNT(blob)
  • blob HLL_MERGE(blob1[, blob2[, ...]])

Aggregation functions

  • blob HLL_GROUP_CREATE(bits, *keys)
  • int HLL_GROUP_COUNT(*blobs)
  • blob HLL_GROUP_MERGE(*blobs)
  • int HLL_COUNT_DISTINCT(*keys)

About

HyperLogLog UDF for MySQL

Resources

License

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published