pg_approx_hll

[milanbella]

Check List

• Tests has been run in packages where changes made if available
• Linter has been run for changed code
• Tests for the changes have been added if not covered yet
• Docs have been added / updated if required

Issue Reference this PR resolves

[For example #12]

Description of Changes Made (if issue reference is not provided)

[Description goes here]

[norbertbede]

xref:
DisctinctCountApprox Postgres HLL support #563

[norbertbede]

manual testing result

tested schema:
Orders.txt

cubejs create valid hll column included table when hit distinct apprx - preaggregation see valid DDL command
`-- Table: stb_pre_aggregations.orders__distinct_approx_3yrotyzx_gymlzk4i_1587213010940

DROP TABLE stb_pre_aggregations.orders__distinct_approx_3yrotyzx_gymlzk4i_1587213010940;

CREATE TABLE stb_pre_aggregations.orders__distinct_approx_3yrotyzx_gymlzk4i_1587213010940
(
orders__dateordered timestamp(6) without time zone,
orders__ordersource character varying COLLATE pg_catalog."default",
orders__dateordered_day timestamp without time zone,
orders__order_distinct_approx hll
)
WITH (
OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE stb_pre_aggregations.orders__distinct_approx_3yrotyzx_gymlzk4i_1587213010940
OWNER to cubejsrole;`

then generate valid sql

` Executing SQL: 03601c10-804d-4a40-8c8b-f2894ddb5b21-span-1
select max(("orders".dateordered::timestamptz AT TIME ZONE 'UTC')) from (
SELECT
o.c_order_id,
ol.c_orderline_id,
o.dateordered,
ol.ad_client_id,
ol.QtyOrdered,
COALESCE(sr.lastname,'Empty') order_salesrep_name,

COALESCE(ords.name,'Empty') as order_source_name,
o.issotrx
FROM c_orderline ol
JOIN c_order o ON (o.c_order_id=ol.c_order_id)
LEFT JOIN ad_user sr ON o.salesrep_id = sr.ad_user_id
LEFT JOIN c_ordersource ords ON o.c_ordersource_id = ords.c_ordersource_id

WHERE ol.ad_client_id=1000026
AND (o.docstatus = ANY (ARRAY['CO'::text, 'CL'::text]))
AND o.issotrx='Y'
AND o.dateordered = '2020-04-01'

) AS "orders"

Performing query completed: 03601c10-804d-4a40-8c8b-f2894ddb5b21-span-1 (242ms)

Executing Load Pre Aggregation SQL: 03601c10-804d-4a40-8c8b-f2894ddb5b21-span-1

CREATE TABLE stb_pre_aggregations.orders__distinct_approx_3yrotyzx_gymlzk4i_1587213010940 AS SELECT
"orders".dateordered "orders__dateordered", "orders".order_source_name "orders__ordersource", date_trunc('day', ("orders".dateordered::tim$
stamptz AT TIME ZONE 'UTC')) "orders__dateordered_day", hll_add_agg(hll_hash_any("orders".c_order_id)) "orders__order_distinct_approx"
FROM
(
SELECT
o.c_order_id,
ol.c_orderline_id,
o.dateordered,
ol.ad_client_id,
ol.QtyOrdered,
COALESCE(sr.lastname,'Empty') order_salesrep_name,

COALESCE(ords.name,'Empty') as order_source_name,
o.issotrx
FROM c_orderline ol
JOIN c_order o ON (o.c_order_id=ol.c_order_id)
LEFT JOIN ad_user sr ON o.salesrep_id = sr.ad_user_id
LEFT JOIN c_ordersource ords ON o.c_ordersource_id = ords.c_ordersource_id

WHERE ol.ad_client_id=1000026
AND (o.docstatus = ANY (ARRAY['CO'::text, 'CL'::text]))
AND o.issotrx='Y'
AND o.dateordered = '2020-04-01'

) AS "orders"
GROUP BY 1, 2, 3

Executing SQL: 03601c10-804d-4a40-8c8b-f2894ddb5b21-span-1

SELECT "orders__dateordered_day" "orders__dateordered_day", hll_cardinality(hll_union_agg("orders__order_distinct_approx")) "orders__order_dist
inct_approx" FROM stb_pre_aggregations.orders__distinct_approx_3yrotyzx_gymlzk4i_1587213010940 GROUP BY 1 ORDER BY 1 ASC LIMIT 10000

Performing query completed: 03601c10-804d-4a40-8c8b-f2894ddb5b21-span-1 (147ms)
Load Request Success: 03601c10-804d-4a40-8c8b-f2894ddb5b21-span-1 (2431ms)

{
"measures": [
"Orders.orderDistinctApprox"
],
"timeDimensions": [
{
"dimension": "Orders.dateordered",
"granularity": "day"
}
],
"filters": []
}

Load Request Success: d8a71e17-4965-43fc-bf71-b1abc7fdd6de-span-1 (6ms)
`

[paveltiunov]

I believe this one is redundant.

[milanbella]

will remove that 'return'

[paveltiunov]

This seems incorrect. Could you please test it?

[milanbella]

This worked for our test schema. I will change it for hll_cardinality(hll_add_agg(hll_hash_any(${sql}))) .

[paveltiunov]

Hey @milanbella Hey Milan! Overall looks great! Could you please check comments I provided.

[paveltiunov]

@milanbella Looks good! Thanks for contributing this!