Skip to content

Instantly share code, notes, and snippets.

@Slach

Slach/README.md

Last active July 16, 2020 09:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save Slach/a70193200df3e3e8f738f411a836b84a to your computer and use it in GitHub Desktop.
Save Slach/a70193200df3e3e8f738f411a836b84a to your computer and use it in GitHub Desktop.
Download ZIP
Raw
README.md

For try to reproduce run following command in bash

docker-compose down && docker-compose up -d clickhouse && sleep 5 && docker-compose exec clickhouse bash ./reproduce.sh
Raw
docker-compose.yaml
version: "3"
services:
clickhouse:
image: yandex/clickhouse-server:20.3.12.112
ports:
- 8123:8123
- 9000:9000
volumes:
- ./init_schema.sql:/docker-entrypoint-initdb.d/init_schema.sql
- ./reproduce.sh:/reproduce.sh
Raw
init_schema.sql
DROP TABLE IF EXISTS default.raw_data;
CREATE TABLE IF NOT EXISTS default.raw_data
(
/* main fields which describe event */
event_date DateTime DEFAULT toDateTime('0000-00-00 00:00:00'),
event_type Enum8('transaction'=0, 'session'=1) DEFAULT 'transaction',
import_date DateTime DEFAULT toDateTime(now()),
/* exists only on prod.session2 */
uid String DEFAULT '', -- hash
session2_id UInt64, -- auto increment on prod.sesssion2, 0 on archives.*
date DateTime DEFAULT toDateTime('0000-00-00 00:00:00'),
datefin DateTime DEFAULT toDateTime('0000-00-00 00:00:00'),
wid Int64 DEFAULT 0,
famillewap Int32 DEFAULT 0,
ratio Float64 DEFAULT 0,
Status UInt8 DEFAULT 0,
periode UInt16 DEFAULT 0,
uacore LowCardinality(String) DEFAULT '',
langue String DEFAULT '',
ml LowCardinality(String) DEFAULT '',
video LowCardinality(Nullable(String)),
xhtml LowCardinality(String) DEFAULT 'non',
telechargement UInt8 DEFAULT 0,
uaextension LowCardinality(Nullable(String)),
multiobject UInt8 DEFAULT 0,
mms UInt8 DEFAULT 0,
best_ml LowCardinality(String) DEFAULT 'wml',
"3g" FixedString(1) DEFAULT '0',
age LowCardinality(String) DEFAULT '',
login LowCardinality(String) DEFAULT '',
famille LowCardinality(String) DEFAULT '',
/* TODO need modify MySQL session2 table sturcture and make this fields as DateTime */
created Date DEFAULT toDate('0000-00-00'),
modified Date DEFAULT toDate('0000-00-00'),
familledld LowCardinality(String) DEFAULT '',
chatvalidation UInt8 DEFAULT 0,
https UInt8 DEFAULT 0,
motclef LowCardinality(String) DEFAULT '',
bgcolor Enum8('0'=0, '1'=1) DEFAULT '0',
http_x_nokia_bearer LowCardinality(String) DEFAULT '',
stream UInt8 DEFAULT 0,
ip LowCardinality(Nullable(String)),
tactile Enum8('0'=0, '1'=1) DEFAULT '0',
familledldvideo LowCardinality(String) DEFAULT '',
/* exists only REPORTING.transactions */
transaction_id UInt32,
trxidpartenaire Nullable(String),
date_achat DateTime DEFAULT toDateTime('0000-00-00 00:00:00'),
idoffre Nullable(UInt32),
offre LowCardinality(Nullable(String)),
price_point_code LowCardinality(String) DEFAULT 'MISC',
price_point LowCardinality(String) DEFAULT 'Various',
typeachat FixedString(1) DEFAULT '',
type LowCardinality(Nullable(String)),
offer_type LowCardinality(Nullable(String)),
groupe LowCardinality(Nullable(String)),
distributeur LowCardinality(Nullable(String)),
affilie LowCardinality(Nullable(String)),
ope_factu LowCardinality(Nullable(String)) DEFAULT 'NULL',
booster UInt8 DEFAULT 0,
abo_id Nullable(UInt32),
/* REPORTING.transactions money */
/* TODO change to Decimal, wait when resolve https://github.com/ClickHouse/ClickHouse/issues/7690 */
prix Nullable(Float64),
cawister Nullable(Float64),
castats Nullable(Float64),
ca Nullable(Float64),
/* same field for sessions and transactions */
sessionid String DEFAULT '',
device_family LowCardinality(String) DEFAULT 'Other',
code_service LowCardinality(String),
nom_service LowCardinality(Nullable(String)),
opco LowCardinality(String) DEFAULT '', -- nullable on MySQL side
nom_operateur LowCardinality(Nullable(String)),
origine LowCardinality(String),
/* transactions have country name, but session have country code */
pays LowCardinality(String) DEFAULT 'France',
pays_code LowCardinality(String) DEFAULT 'FRA',
ope_telecom LowCardinality(String) DEFAULT 'FRA_WISTEr',
ope_mobile LowCardinality(String) DEFAULT 'INC',
crm Nullable(UInt32),
stamp LowCardinality(Nullable(String)),
stat_mktg_tracker LowCardinality(Nullable(String)),
stat_crm_tracker LowCardinality(Nullable(String)),
optin LowCardinality(String) DEFAULT 'INC',
code_affilie LowCardinality(String) DEFAULT '',
id_site Nullable(UInt32),
/* advanced fields for zistat */
partco LowCardinality(String),
is_bot UInt8, -- session2 useragent google|yahoo|bot
stat_tracker LowCardinality(String),
md_ad_format LowCardinality(String),
md_ad_id LowCardinality(String),
md_ad_type LowCardinality(String),
md_app_id LowCardinality(String),
md_app_name LowCardinality(String),
md_banner_id LowCardinality(String),
md_banner_name LowCardinality(String) ,
md_bid LowCardinality(String),
md_bid_id LowCardinality(String),
md_blp_id LowCardinality(String),
md_blp_name LowCardinality(String),
md_browser LowCardinality(String),
md_campaign_id LowCardinality(String),
md_carrier LowCardinality(String),
md_category LowCardinality(String),
md_category_id LowCardinality(String),
md_click_id LowCardinality(String),
md_custom1 LowCardinality(String),
md_custom2 LowCardinality(String),
md_custom_deux LowCardinality(String),
md_custom_un LowCardinality(String),
md_country LowCardinality(String),
md_device LowCardinality(String),
md_lp LowCardinality(String),
md_lp_id LowCardinality(String),
md_os LowCardinality(String),
md_pricing_model LowCardinality(String),
md_pub_id LowCardinality(String),
md_publisher_id LowCardinality(String),
md_site_id LowCardinality(String),
md_site_name LowCardinality(String),
md_stat_tracker LowCardinality(String),
md_target_name LowCardinality(String),
md_timestamp LowCardinality(String),
md_zone LowCardinality(String),
md_zone_id LowCardinality(String)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(event_date)
ORDER BY (toDate(event_date), event_type, code_affilie, device_family, code_service);
CREATE TABLE IF NOT EXISTS default.rtb_and_mb_left_join_raw_data (
/* dimensions which in zistatsmb */
event_date Date DEFAULT toDate('0000-00-00'), -- rtb.event_date
import_date DateTime DEFAULT toDateTime(now()),
idoffre UInt32,
price_point_code LowCardinality(String) DEFAULT 'MISC',
typeachat FixedString(1) DEFAULT '',
offer_type LowCardinality(String) DEFAULT 'undefined',
ope_factu LowCardinality(String) DEFAULT 'NULL',
booster UInt8 DEFAULT 0,
device_family LowCardinality(String) DEFAULT 'Other',
code_service LowCardinality(String),
opco LowCardinality(String) DEFAULT '',
pays LowCardinality(String) DEFAULT 'France',
pays_code LowCardinality(String) DEFAULT 'FRA',
ope_telecom LowCardinality(String) DEFAULT 'FRA_WISTER',
ope_mobile LowCardinality(String) DEFAULT 'INC',
stamp LowCardinality(String) DEFAULT '',
stat_mktg_tracker LowCardinality(String) DEFAULT 'NULL',
stat_crm_tracker LowCardinality(String) DEFAULT 'NULL',
optin LowCardinality(String) DEFAULT 'INC',
code_affilie LowCardinality(String) DEFAULT '',
id_site UInt32 DEFAULT 0,
partco LowCardinality(String),
is_bot UInt8,
/* synthetic calculated measures */
transactions UInt32, /* countIf(event_type='transaction') */
sessions UInt32, /* countIf(event_type='session') */
/* REPORTING.transactions money */
/* TODO change to Decimal, wait when resolve https://github.com/ClickHouse/ClickHouse/issues/7690 */
prix Nullable(Float64),
cawister Nullable(Float64),
castats Nullable(Float64),
ca Nullable(Float64),
/* mediabuy/RTB campaign_tracker */
stat_tracker LowCardinality(String),
/* mediabuy dimensions equivalent of md_stamp.md_stamp_id */
md_ad_format LowCardinality(String),
md_ad_id LowCardinality(String),
md_ad_type LowCardinality(String),
md_app_id LowCardinality(String),
md_app_name LowCardinality(String),
md_banner_id LowCardinality(String),
md_banner_name LowCardinality(String),
md_bid LowCardinality(String),
md_bid_id LowCardinality(String),
md_blp_id LowCardinality(String),
md_blp_name LowCardinality(String),
md_browser LowCardinality(String),
md_campaign_id LowCardinality(String),
md_carrier LowCardinality(String),
md_category LowCardinality(String),
md_category_id LowCardinality(String),
md_click_id LowCardinality(String),
md_custom1 LowCardinality(String),
md_custom2 LowCardinality(String),
md_custom_deux LowCardinality(String),
md_custom_un LowCardinality(String),
md_country LowCardinality(String),
md_device LowCardinality(String),
md_lp LowCardinality(String),
md_lp_id LowCardinality(String),
md_os LowCardinality(String),
md_pricing_model LowCardinality(String),
md_pub_id LowCardinality(String),
md_publisher_id LowCardinality(String),
md_site_id LowCardinality(String),
md_site_name LowCardinality(String),
md_stat_tracker LowCardinality(String),
md_target_name LowCardinality(String),
md_timestamp LowCardinality(String),
md_zone LowCardinality(String),
md_zone_id LowCardinality(String),
/* mediabuy measures */
clicks Decimal64(10),
prints Decimal64(10),
cost Decimal64(10),
/* rtb measures */
bids Nullable(Decimal64(10)),
no_bids Nullable(Decimal64(10)),
wins Nullable(Decimal64(10)),
bid_price Nullable(Decimal64(10)),
mab_price Nullable(Decimal64(10)),
bid_price_min Nullable(Decimal64(10)),
bid_price_max Nullable(Decimal64(10)),
mab_price_min Nullable(Decimal64(10)),
mab_price_max Nullable(Decimal64(10)),
win_price_min Nullable(Decimal64(10)),
win_price_max Nullable(Decimal64(10)),
is_orphan UInt8 DEFAULT (sessions=0 AND transactions=0)
/*
please read https://clickhouse.tech/docs/en/operations/table_engines/replacingmergetree/
replace values for same ORDER BY values and max(import date)
*/
) ENGINE ReplacingMergeTree(import_date)
PARTITION BY toYYYYMM(event_date)
ORDER BY (
/* media buy and RTB dimensions */
event_date,
stat_tracker,
code_affilie, /* also exists in raw_data */
is_orphan,
md_site_name,
md_site_id,
md_zone,
md_zone_id,
md_banner_id,
md_category_id,
md_bid,
md_pricing_model,
md_ad_format,
md_pub_id,
md_carrier,
md_country,
md_device,
md_lp,
md_campaign_id,
md_ad_id,
md_ad_type,
md_app_id,
md_app_name,
md_banner_name,
md_bid_id,
md_blp_id,
md_blp_name,
md_browser,
md_category,
md_click_id,
md_custom1,
md_custom2,
md_custom_deux,
md_custom_un,
md_lp_id,
md_os,
md_publisher_id,
md_stat_tracker,
md_target_name,
md_timestamp,
/* raw_data dimensions used for MB/RTB measurement spreading by sessions and transactions */
typeachat,
idoffre,
price_point_code,
offer_type,
ope_factu,
booster,
device_family,
code_service,
opco,
pays,
pays_code,
ope_telecom,
ope_mobile,
stamp,
stat_mktg_tracker,
stat_crm_tracker,
optin,
id_site,
partco,
is_bot
)
/* reduce number of primary key fields, for improve scan performance? */
PRIMARY KEY (
/* media buy and RTB dimensions */
event_date,
stat_tracker,
code_affilie, /* also exists in raw_data */
is_orphan,
md_site_name,
md_site_id,
md_zone,
md_zone_id,
md_banner_id,
md_category_id,
md_bid,
md_pricing_model,
md_ad_format,
md_pub_id,
md_carrier,
md_country,
md_device
);
DROP TABLE IF EXISTS default.rtb_and_mb_left_join_raw_data_buffer;
CREATE TABLE IF NOT EXISTS default.rtb_and_mb_left_join_raw_data_buffer AS default.rtb_and_mb_left_join_raw_data
ENGINE Buffer('default','rtb_and_mb_left_join_raw_data', 16, 10, 60, 10, 1000, 1048576, 2097152);
INSERT INTO default.raw_data(event_date, event_type, code_affilie, typeachat, idoffre, price_point_code, offer_type, ope_factu, booster, device_family, code_service, opco, pays, pays_code, ope_telecom, ope_mobile, stamp, stat_mktg_tracker, stat_crm_tracker, optin, id_site, partco, is_bot, prix, cawister, castats, ca, md_ad_format, md_ad_id, md_ad_type, md_app_id, md_app_name, md_banner_id, md_banner_name, md_bid, md_bid_id, md_blp_id, md_blp_name, md_browser, md_campaign_id, md_carrier, md_category, md_category_id, md_click_id, md_custom1, md_custom2, md_custom_deux, md_custom_un, md_country, md_device, md_lp, md_lp_id, md_os, md_pricing_model, md_pub_id, md_publisher_id, md_site_id, md_site_name, md_stat_tracker, md_target_name, md_timestamp, md_zone, md_zone_id) VALUES ('2020-07-12 22:25:49', 'session', 'aff_affil4you_8665', ' ', 0, 'MISC', '', 'NULL', 0, 'iPhone', 'XB7H', 'OFF', 'France', 'FRA', 'FRA_BOUYGTEL', 'FRA_BOUYGTEL', '355-strainer-oxiweb-jemontremabite.com-fr-jmmbite_MB:[[32684][jemontremabite.com][unknown][][banner_300x250][15028][N/A][162][FRA][FRA_BOUYGTEL][iPhone][banner][-_-40]]', 'MKTG_RTB', 'CRM_BACK_BROWSER_EXIT_CONF', '15027', 86817, 'BOUYGTEL', 0, null, null, null, null, 'banner_300x250', '', 'banner', '', '', '32684', '', '', '', '', '', '', '', 'FRA_BOUYGTEL', '', '', '', '', '', '', '', 'FRA', 'iPhone', '15028', '', '', '', 'N/A', '', '', 'jemontremabite.com', '', '', '', 'unknown', '162');
INSERT INTO default.raw_data(event_date, event_type, code_affilie, typeachat, idoffre, price_point_code, offer_type, ope_factu, booster, device_family, code_service, opco, pays, pays_code, ope_telecom, ope_mobile, stamp, stat_mktg_tracker, stat_crm_tracker, optin, id_site, partco, is_bot, prix, cawister, castats, ca, md_ad_format, md_ad_id, md_ad_type, md_app_id, md_app_name, md_banner_id, md_banner_name, md_bid, md_bid_id, md_blp_id, md_blp_name, md_browser, md_campaign_id, md_carrier, md_category, md_category_id, md_click_id, md_custom1, md_custom2, md_custom_deux, md_custom_un, md_country, md_device, md_lp, md_lp_id, md_os, md_pricing_model, md_pub_id, md_publisher_id, md_site_id, md_site_name, md_stat_tracker, md_target_name, md_timestamp, md_zone, md_zone_id) VALUES ('2020-07-12 22:25:50', 'session', 'aff_affil4you_8665', ' ', 0, 'MISC', '', 'NULL', 0, 'iPhone', 'XB7H', 'OFF', 'France', 'FRA', 'FRA_BOUYGTEL', 'FRA_BOUYGTEL', '355-strainer-oxiweb-jemontremabite.com-fr-jmmbite_MB:[[32684][jemontremabite.com][unknown][][banner_300x250][15028][N/A][162][FRA][FRA_BOUYGTEL][iPhone][banner][-_-40]]', 'MKTG_RTB', 'CRM_BACK_BROWSER_EXIT_CONF', '15033', 86817, 'BOUYGTEL', 0, null, null, null, null, 'banner_300x250', '', 'banner', '', '', '32684', '', '', '', '', '', '', '', 'FRA_BOUYGTEL', '', '', '', '', '', '', '', 'FRA', 'iPhone', '15028', '', '', '', 'N/A', '', '', 'jemontremabite.com', '', '', '', 'unknown', '162');
INSERT INTO default.raw_data(event_date, event_type, code_affilie, typeachat, idoffre, price_point_code, offer_type, ope_factu, booster, device_family, code_service, opco, pays, pays_code, ope_telecom, ope_mobile, stamp, stat_mktg_tracker, stat_crm_tracker, optin, id_site, partco, is_bot, prix, cawister, castats, ca, md_ad_format, md_ad_id, md_ad_type, md_app_id, md_app_name, md_banner_id, md_banner_name, md_bid, md_bid_id, md_blp_id, md_blp_name, md_browser, md_campaign_id, md_carrier, md_category, md_category_id, md_click_id, md_custom1, md_custom2, md_custom_deux, md_custom_un, md_country, md_device, md_lp, md_lp_id, md_os, md_pricing_model, md_pub_id, md_publisher_id, md_site_id, md_site_name, md_stat_tracker, md_target_name, md_timestamp, md_zone, md_zone_id) VALUES ('2020-07-12 22:25:52', 'session', 'aff_affil4you_8665', ' ', 0, 'MISC', '', 'NULL', 0, 'iPhone', 'XB7H', 'OFF', 'France', 'FRA', 'FRA_BOUYGTEL', 'FRA_BOUYGTEL', '355-strainer-oxiweb-jemontremabite.com-fr-jmmbite_MB:[[32684][jemontremabite.com][unknown][][banner_300x250][15028][N/A][162][FRA][FRA_BOUYGTEL][iPhone][banner][-_-40]]', 'MKTG_RTB', 'CRM_BACK_BROWSER_EXIT_CONF', '15721', 86817, 'BOUYGTEL', 0, null, null, null, null, 'banner_300x250', '', 'banner', '', '', '32684', '', '', '', '', '', '', '', 'FRA_BOUYGTEL', '', '', '', '', '', '', '', 'FRA', 'iPhone', '15028', '', '', '', 'N/A', '', '', 'jemontremabite.com', '', '', '', 'unknown', '162');
INSERT INTO default.raw_data(event_date, event_type, code_affilie, typeachat, idoffre, price_point_code, offer_type, ope_factu, booster, device_family, code_service, opco, pays, pays_code, ope_telecom, ope_mobile, stamp, stat_mktg_tracker, stat_crm_tracker, optin, id_site, partco, is_bot, prix, cawister, castats, ca, md_ad_format, md_ad_id, md_ad_type, md_app_id, md_app_name, md_banner_id, md_banner_name, md_bid, md_bid_id, md_blp_id, md_blp_name, md_browser, md_campaign_id, md_carrier, md_category, md_category_id, md_click_id, md_custom1, md_custom2, md_custom_deux, md_custom_un, md_country, md_device, md_lp, md_lp_id, md_os, md_pricing_model, md_pub_id, md_publisher_id, md_site_id, md_site_name, md_stat_tracker, md_target_name, md_timestamp, md_zone, md_zone_id) VALUES ('2020-07-12 22:25:43', 'session', 'aff_affil4you_8665', ' ', 0, 'MISC', '', 'NULL', 0, 'iPhone', 'XB7H', 'OFF', 'France', 'FRA', 'FRA_BOUYGTEL', 'FRA_BOUYGTEL', '355-strainer-oxiweb-jemontremabite.com-fr-jmmbite_MB:[[32684][jemontremabite.com][unknown][][banner_300x250][15028][N%2FA][162][FRA][FRA_BOUYGTEL][iPhone][banner][-_-40]]', 'MKTG_RTB', '', '15028', 86817, 'BOUYGTEL', 0, null, null, null, null, 'banner_300x250', '', 'banner', '', '', '32684', '', '', '', '', '', '', '', 'FRA_BOUYGTEL', '', '', '', '', '', '', '', 'FRA', 'iPhone', '15028', '', '', '', 'N/A', '', '', 'jemontremabite.com', '', '', '', 'unknown', '162');
INSERT INTO default.raw_data(event_date, event_type, code_affilie, typeachat, idoffre, price_point_code, offer_type, ope_factu, booster, device_family, code_service, opco, pays, pays_code, ope_telecom, ope_mobile, stamp, stat_mktg_tracker, stat_crm_tracker, optin, id_site, partco, is_bot, prix, cawister, castats, ca, md_ad_format, md_ad_id, md_ad_type, md_app_id, md_app_name, md_banner_id, md_banner_name, md_bid, md_bid_id, md_blp_id, md_blp_name, md_browser, md_campaign_id, md_carrier, md_category, md_category_id, md_click_id, md_custom1, md_custom2, md_custom_deux, md_custom_un, md_country, md_device, md_lp, md_lp_id, md_os, md_pricing_model, md_pub_id, md_publisher_id, md_site_id, md_site_name, md_stat_tracker, md_target_name, md_timestamp, md_zone, md_zone_id) VALUES ('2020-07-13 13:24:52', 'session', 'aff_affil4you_8665', ' ', 0, 'MISC', '', 'NULL', 0, 'iPhone', 'XB7H', 'OFF', 'France', 'FRA', 'FRA_BOUYGTEL', 'INC', '355-strainer-oxiweb-jemontremabite.com-fr-jmmbite_MB:[[32684][jemontremabite.com][unknown][][banner_300x250][15722][N%2FA][162][FRA][FRA_BOUYGTEL][iPhone][banner_300x250][-_-40]]', 'MKTG_RTB', '', '15722', 86817, 'BOUYGTEL', 0, null, null, null, null, 'banner_300x250', '', 'banner_300x250', '', '', '32684', '', '', '', '', '', '', '', 'FRA_BOUYGTEL', '', '', '', '', '', '', '', 'FRA', 'iPhone', '15722', '', '', '', 'N/A', '', '', 'jemontremabite.com', '', '', '', 'unknown', '162');
INSERT INTO default.raw_data(event_date, event_type, code_affilie, typeachat, idoffre, price_point_code, offer_type, ope_factu, booster, device_family, code_service, opco, pays, pays_code, ope_telecom, ope_mobile, stamp, stat_mktg_tracker, stat_crm_tracker, optin, id_site, partco, is_bot, prix, cawister, castats, ca, md_ad_format, md_ad_id, md_ad_type, md_app_id, md_app_name, md_banner_id, md_banner_name, md_bid, md_bid_id, md_blp_id, md_blp_name, md_browser, md_campaign_id, md_carrier, md_category, md_category_id, md_click_id, md_custom1, md_custom2, md_custom_deux, md_custom_un, md_country, md_device, md_lp, md_lp_id, md_os, md_pricing_model, md_pub_id, md_publisher_id, md_site_id, md_site_name, md_stat_tracker, md_target_name, md_timestamp, md_zone, md_zone_id) VALUES ('2020-07-13 19:37:24', 'session', 'aff_affil4you_8665', ' ', 0, 'MISC', '', 'NULL', 0, 'iPhone', 'XB7H', 'OFF', 'France', 'FRA', 'FRA_BOUYGTEL', 'FRA_BOUYGTEL', '355-strainer-oxiweb-jemontremabite.com-fr-jmmbite_MB:[[32684][jemontremabite.com][unknown][][banner_300x250][15028][N%2FA][162][FRA][FRA_BOUYGTEL][iPhone][banner_300x250][-_-40]]', 'MKTG_RTB', '', '15028', 86817, 'BOUYGTEL', 0, null, null, null, null, 'banner_300x250', '', 'banner_300x250', '', '', '32684', '', '', '', '', '', '', '', 'FRA_BOUYGTEL', '', '', '', '', '', '', '', 'FRA', 'iPhone', '15028', '', '', '', 'N/A', '', '', 'jemontremabite.com', '', '', '', 'unknown', '162');
Raw
reproduce.sh
#!/usr/bin/env bash
set -euo pipefail
SQL="INSERT INTO default.rtb_and_mb_left_join_raw_data_buffer (
/* raw_data dimensions used for MB/RTB measurement spreading by sessions and transactions */
typeachat,
idoffre,
price_point_code,
offer_type,
ope_factu,
booster,
device_family,
code_service,
opco,
pays,
pays_code,
ope_telecom,
ope_mobile,
stamp,
stat_mktg_tracker,
stat_crm_tracker,
optin,
id_site,
partco,
is_bot, /* synthetic calculated measures */
transactions, /* countIf(event_type='transaction') */
sessions, /* countIf(event_type='session') */ /* REPORTING.transactions money */
prix,
cawister,
castats,
ca,
stat_tracker,
code_affilie,
event_date,
prints,
clicks,
cost,
wins,
bids,
no_bids,
win_price_min,
win_price_max,
mab_price,
mab_price_min,
mab_price_max,
bid_price,
bid_price_min,
bid_price_max, /* media buy dimensions */
md_ad_format,
md_ad_id,
md_ad_type,
md_app_id,
md_app_name,
md_banner_id,
md_banner_name,
md_bid,
md_bid_id,
md_blp_id,
md_blp_name,
md_browser,
md_campaign_id,
md_carrier,
md_category,
md_category_id,
md_click_id,
md_custom1,
md_custom2,
md_custom_deux,
md_custom_un,
md_country,
md_device,
md_lp,
md_lp_id,
md_os,
md_pricing_model,
md_pub_id,
md_publisher_id,
md_site_id,
md_site_name,
md_stat_tracker,
md_target_name,
md_timestamp,
md_zone,
md_zone_id
)
SELECT
/* raw_data dimensions used for MB/RTB measurement spreading by sessions and transactions */
typeachat,
idoffre,
price_point_code,
offer_type,
ope_factu,
booster,
device_family,
code_service,
opco,
pays,
pays_code,
ope_telecom,
ope_mobile,
stamp,
stat_mktg_tracker,
stat_crm_tracker,
optin,
id_site,
partco,
is_bot, /* synthetic calculated measures */
transactions, /* countIf(event_type='transaction') */
sessions, /* countIf(event_type='session') */ /* REPORTING.transactions money */
prix,
cawister,
castats,
ca,
t1.stat_tracker,
t2.code_affilie,
t2.event_date,
prints,
clicks,
cost,
wins,
bids,
no_bids,
win_price_min,
win_price_max,
mab_price,
mab_price_min,
mab_price_max,
bid_price,
bid_price_min,
bid_price_max,
md_ad_format,
md_ad_id,
md_ad_type,
md_app_id,
md_app_name,
md_banner_id,
md_banner_name,
md_bid,
md_bid_id,
md_blp_id,
md_blp_name,
md_browser,
md_campaign_id,
md_carrier,
md_category,
md_category_id,
md_click_id,
md_custom1,
md_custom2,
md_custom_deux,
md_custom_un,
md_country,
md_device,
md_lp,
md_lp_id,
md_os,
md_pricing_model,
md_pub_id,
md_publisher_id,
md_site_id,
md_site_name,
md_stat_tracker,
md_target_name,
md_timestamp,
md_zone,
md_zone_id
FROM (
SELECT '355-strainer-oxiweb-jemontremabite.com-fr-jmmbite' AS stat_tracker,
'aff_affil4you_8665' AS code_affilie ) AS t1
ALL INNER JOIN (
SELECT
toDate(event_date) AS event_date,
code_affilie,
typeachat,
coalesce(idoffre, 0) AS idoffre,
price_point_code,
coalesce(offer_type, '') AS offer_type,
ope_factu,
booster,
device_family,
code_service,
opco,
pays,
pays_code,
ope_telecom,
ope_mobile,
stamp,
stat_mktg_tracker,
stat_crm_tracker,
optin,
id_site,
partco,
is_bot,
countIf(event_type = 'session') AS sessions,
countIf(event_type = 'transaction') AS transactions,
sum(prix) AS prix,
sum(cawister) AS cawister,
sum(castats) AS castats,
sum(ca) AS ca,
(sessions / 6) * 104 AS prints,
(sessions / 6) * 0 AS clicks,
(sessions / 6) * 0 AS cost,
(sessions / 6) * 104 AS wins,
(sessions / 6) * 104 AS bids,
(sessions / 6) * 0 AS no_bids,
0 AS win_price_min,
0 AS win_price_max,
(sessions / 6) * 0.0071580961 AS mab_price,
0.0387380408 AS mab_price_min,
0.1173510842 AS mab_price_max,
(sessions / 6) * 0.007158096 AS bid_price,
0.0387380441 AS bid_price_min,
0.1173510711 AS bid_price_max,
md_ad_format,
md_ad_id,
md_ad_type,
md_app_id,
md_app_name,
md_banner_id,
md_banner_name,
md_bid,
md_bid_id,
md_blp_id,
md_blp_name,
md_browser,
md_campaign_id,
md_carrier,
md_category,
md_category_id,
md_click_id,
md_custom1,
md_custom2,
md_custom_deux,
md_custom_un,
md_country,
md_device,
md_lp,
md_lp_id,
md_os,
md_pricing_model,
md_pub_id,
md_publisher_id,
md_site_id,
md_site_name,
md_stat_tracker,
md_target_name,
md_timestamp,
md_zone,
md_zone_id
FROM default.raw_data AS r
WHERE r.is_bot = 0
AND toDate(r.event_date) = '2020-07-13'
AND r.stamp LIKE '355-strainer-oxiweb-jemontremabite.com-fr-jmmbite%'
AND r.code_affilie = 'aff_affil4you_8665'
AND md_ad_format = 'banner_300x250'
AND md_banner_id = '32684'
AND md_carrier = 'FRA_BOUYGTEL'
AND md_country = 'FRA'
AND md_device = 'iPhone'
AND md_pub_id = 'N/A'
AND md_site_name = 'jemontremabite.com'
AND md_zone = 'unknown'
AND md_zone_id = '162'
GROUP BY /* RTB and MB dimensions */
toDate(r.event_date),
r.code_affilie, /* also exists in raw_data */ /* see all_mb_dims */
md_ad_format, md_ad_id, md_ad_type, md_app_id, md_app_name,
md_banner_id, md_banner_name, md_bid, md_bid_id, md_blp_id,
md_blp_name, md_browser, md_campaign_id, md_carrier,
md_category, md_category_id, md_click_id, md_custom1,
md_custom2, md_custom_deux, md_custom_un, md_country,
md_device, md_lp, md_lp_id, md_os, md_pricing_model,
md_pub_id, md_publisher_id, md_site_id, md_site_name,
md_stat_tracker, md_target_name, md_timestamp, md_zone,
md_zone_id, /* raw_data dimensions used for MB/RTB measurement spreading by sessions and transactions */
typeachat, idoffre, price_point_code, offer_type, ope_factu,
booster, device_family, code_service, opco, pays, pays_code,
ope_telecom, ope_mobile, stamp, stat_mktg_tracker,
stat_crm_tracker, optin, id_site, partco, is_bot ) AS t2
ON t1.code_affilie = t2.code_affilie;"
while [[ true ]]; do
printf "."
clickhouse-client -mn -q "${SQL}"
done
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment