Votre navigateur n'est pas compatible avec jmpress.js, vous avez donc une version dégradée de cette présentation.
Pour une meilleure experience, merci d'utiliser Chrome, Safari ou Firefox.
fait de la BI sur Hadoop avec
Tech : "Est-ce que mon serveur de plan #4 est aussi bon que les autres ?"
Compta : "Combien l'application iPhone PJ demande-t-elle d'itinéraires piétons en une année ?"
Métier : "Est-ce que l'application iPhone 2.3 est encore utilisée ?"
"Sur quels terminaux ?"
Communication : "Où sont partis les bretons pendant les dernières vacances d'été ?"
Pourrais tu me dire, pour chaque POI
- Le nombre d'affichage en carte ?
- Le nombre d'ouvertures de fiches ?
- Le nombre de clics ?
- ...
Pour 4 000 000 de POI,
sur une dizaines d'actions,
au jour le jour,
avec des stats par regroupements...
Traitements trop lents (sup à 24h)
Interfaces peu ergonomiques
Stockage difficilement scalable
BlackBox
Flexible
Rapide
Scalable
Performant
Facile à prendre en main
Liberté sur les datas
Fun
SELECT `bi_poi_audience`.`poi_id` AS `poi_id`,
CAST((MONTH(`bi_poi_audience`.`date`) - 1) / 3 + 1 AS BIGINT) AS `qr_date_ok`,
YEAR(`bi_poi_audience`.`date`) AS `yr_date_ok`,
SUM(`bi_poi_audience`.`nb`) AS `sum_nb_ok`
FROM `default`.`bi_poi_audience` `bi_poi_audience`
WHERE ((`bi_poi_audience`.`bot` = '') AND (DAY(`bi_poi_audience`.`lastoffertypechange`) = 16
OR DAY(`bi_poi_audience`.`lastoffertypechange`) = 23) AND (`bi_poi_audience`.`env` = 'prod')
AND ((NOT (MONTH(`bi_poi_audience`.`lastoffertypechange`) = 2))
OR (MONTH(`bi_poi_audience`.`lastoffertypechange`) IS NULL)) AND (`bi_poi_audience`.`offertype` = 'OVM')
AND (`bi_poi_audience`.`tagid_groupe` = 'Clic&Contact') AND (`bi_poi_audience`.`tagid_sous_groupe` = 'Fiche'))
GROUP BY `bi_poi_audience`.`poi_id`,
CAST((MONTH(`bi_poi_audience`.`date`) - 1) / 3 + 1 AS BIGINT),
YEAR(`bi_poi_audience`.`date`)'
- Indexes multi-dimensionnels (in-mem)
- Pré-agrégations (in-mem)
- Stockage sur disque orienté-colonne
Avril 2015
- Select * sans limit !
- Schémas de bdd non supportés
- ...
Depuis Tableau 9.0
Juin 2015
Le selecteur de dates relatives...
SELECT
`sqlproxy`.`referer_groupe` AS `referer_groupe`,
SUM(`sqlproxy`.`hit_count`) AS `sum_hit_count_ok`,
(
CASE WHEN
(
`sqlproxy`.`referer_groupe` = 'Mappy'
)
THEN 'Mappy' WHEN
(
`sqlproxy`.`referer_groupe` = 'Partenaires'
)
THEN 'Partenaires' WHEN
(
`sqlproxy`.`referer_groupe` = 'Solocal'
)
THEN 'Solocal' ELSE 'UNKNOW' END
)
AS `referer_groupe__groupe_`
FROM `default`.`bi_routemm_perf` `sqlproxy`
WHERE
(
(
`sqlproxy`.`date` >= CASE WHEN
(
IF
(
UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0,
FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'),
DATE_ADD
(
CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'),
CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)
)
)
IS NOT NULL
AND -2 IS NOT NULL
)
THEN FROM_UNIXTIME
(
UNIX_TIMESTAMP(CONCAT((YEAR(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT))))+FLOOR((MONTH(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT))))+-2)/12)), CONCAT('-', CONCAT(LPAD(PMOD(MONTH(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT))))+-2, 12), 2, '0'), SUBSTR(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT))), 8)))), SUBSTR('yyyy-MM-dd HH:mm:ss',0,LENGTH(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)))))),
'yyyy-MM-dd HH:mm:ss'
)
END
)
AND
(
`sqlproxy`.`date` < CASE WHEN
(
IF
(
UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0,
FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'),
DATE_ADD
(
CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'),
CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)
)
)
IS NOT NULL
AND 1 IS NOT NULL
)
THEN FROM_UNIXTIME
(
UNIX_TIMESTAMP(CONCAT((YEAR(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT))))+FLOOR((MONTH(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT))))+1)/12)), CONCAT('-', CONCAT(LPAD(PMOD(MONTH(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT))))+1, 12), 2, '0'), SUBSTR(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT))), 8)))), SUBSTR('yyyy-MM-dd HH:mm:ss',0,LENGTH(IF(UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') > 0, FROM_UNIXTIME(UNIX_TIMESTAMP(DATE_ADD(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)), 'yyyy-MM-dd') + (UNIX_TIMESTAMP(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), 'yyyy-MM-dd HH:mm:ss') - UNIX_TIMESTAMP(TO_DATE(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00')), 'yyyy-MM-dd')), 'yyyy-MM-dd HH:mm:ss'), DATE_ADD(CONCAT(TO_DATE('2016-03-21 17:03:52'), ' 00:00:00'), CAST(CAST(-(DAY('2016-03-21 17:03:52') - 1) AS BIGINT) AS INT)))))),
'yyyy-MM-dd HH:mm:ss'
)
END
)
)
GROUP BY (CASE WHEN (`sqlproxy`.`referer_groupe` = 'Mappy') THEN 'Mappy' WHEN (`sqlproxy`.`referer_groupe` = 'Partenaires') THEN 'Partenaires' WHEN (`sqlproxy`.`referer_groupe` = 'Solocal') THEN 'Solocal' ELSE 'UNKNOW' END),
`sqlproxy`.`referer_groupe`
SELECT
(CASE WHEN (`bi_routemm_perf`.`referer_groupe` = 'Mappy') THEN 'Mappy' WHEN (`bi_routemm_perf`.`referer_groupe` = 'Partenaires') THEN 'Partenaires' WHEN (`bi_routemm_perf`.`referer_groupe` = 'Solocal') THEN 'Solocal' ELSE 'UNKNOW' END) AS `referer_groupe__groupe_`,
`bi_routemm_perf`.`referer_groupe` AS `referer_groupe`,
SUM(`bi_routemm_perf`.`hit_count`) AS `sum_hit_count_ok`
FROM `default`.`bi_routemm_perf` `bi_routemm_perf`
WHERE
(
(`bi_routemm_perf`.`date` >= CAST('2014-01-01 00:00:00' AS TIMESTAMP))
AND (`bi_routemm_perf`.`date` < CAST('2017-01-01 00:00:00' AS TIMESTAMP))
)
GROUP BY (CASE WHEN (`bi_routemm_perf`.`referer_groupe` = 'Mappy') THEN 'Mappy' WHEN (`bi_routemm_perf`.`referer_groupe` = 'Partenaires') THEN 'Partenaires' WHEN (`bi_routemm_perf`.`referer_groupe` = 'Solocal') THEN 'Solocal' ELSE 'UNKNOW' END),
`bi_routemm_perf`.`referer_groupe`
En quelques mois
Les utilisateurs jouent avec4 milliardsde lignes,
via des interfaces graphiques
intuitives,sexyetultra-rapides.
Nous sommes allés beaucoup plus loin que ce que nous avions en tête au début du projet.
En route vers de nouveaux marchés
Utiliser la barre espace ou les flèches pour naviguer.
* Présentation optimisée pour chrome (transitions plus fluides)