hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nitin Vijayvargiya <nitinvija...@gmail.com>
Subject Erroneous column selection in subquery
Date Tue, 12 Sep 2017 01:59:45 GMT
Hi all,
Noticed something extremely strange and concerning regarding subquery column
selection. Here's the query, and the sample results in presto vs hive
SELECT    ipm.ip,    ipm.master_id AS mid,    COALESCE(ad.cookie, '') AS cid,
    COALESCE(ad.url_cid, '') AS campaign_id,    COALESCE(ad.url_sid, '') AS
segment_id,    CASE        WHEN ad.url_event='clk'        THEN 'media_click'

ELSE 'media_impression'    END AS event,    count(*) AS count,    CASE
        WHEN COALESCE(o.name, '') != ''        THEN 'first_party_media'
        ELSE ot.token_type END AS campaign_type,    ad.dt,
    lower(COALESCE(ot.organization_name, o.name)) AS orgFROM    (        SELECT
            base_ip,            url_event,            url_orgid,
            url_token, url_cid,            url_sid,            cookie,
            url,            date_time,            dt        FROM
            tag.pixel        WHERE            dt BETWEEN '2017-08-23'
AND
'2017-08-23'            AND user_agent NOT LIKE 'ELB-HealthChecker%'
        GROUP BY            1, 2, 3, 4, 5, 6, 7, 8, 9, 10    ) ad    JOIN
default.ip_to_master_v2 ipm        ON ipm.ip = ad.base_ip    lEFT JOIN
default.organization_token ot        ON ot.token = ad.url_token    LEFT JOIN
aa.organization o        ON ad.url_orgid = o.idWHERE 
COALESCE(ot.organization_name, o.name, '') != '' GROUP BY    1, 2, 3, 4, 5, 6,
8, 9, 10LIMIT 10
Presto       ip                 | mid                          
               
            | cid                                            
                 
             | campaign_id | segment_id | event                      | count
|
campaign_type    | dt                | org (ommitted)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 216.110.25.2     | 93c8a694f6c0d39-                               |
420ad817041900006549e258d9030000aba10300 | 50                 | 2            
 
   | media_impression | 1        | first_party_media | 2017-08-23 | ******
 14.140.116.135 | 6dfbca80b99c365-60934b2c332bcaa |
96453917a70d0000cbeb59598e010000fe970400  | 43                 | 7          
   
    | media_impression | 2        | first_party_media | 2017-08-23 | ******
Hive103.4.16.84 51 | ba82f9c1e7ef7-51ba82f9c1e7ef7       | 50    | 50    | 2   |
media_impression | 2   | first_party_media | 2017-08-23 | 5012.175.247.66  |
c4028d27f0ebbb2-ef86eff7815a004   | 109  | 109  | 21 | media_impression | 23 |
first_party_media | 2017-08-23 | 109116.197.188.12 |
e78268e3ce3e13c-19ffd91bd5236a8 | 43    | 43    | 7   | media_impression | 4   |
first_party_media | 2017-08-23 | 43
Alright, so clearly the results are very different in presto compared to hive. 
What's interesting is that columns 3, 4, and 10 show the same stat: campaign_id.
I tried a bunch of different things (different aliasing, grouping, etc.), but
nothing worked. The thing that did the trick was pulling out the subquery into
its own table
DROP TABLE IF EXISTS aa.tmp_subquery;CREATE TABLE IF NOT EXISTS aa.tmp_subquery
ASSELECT    base_ip,    url_event,    url_orgid,    url_token, url_cid,
    url_sid,    cookie,    url,    date_time,    dtFROM    tag.pixelWHERE    dt
BETWEEN '{start-yyyy-mm-dd}' AND '{end-yyyy-mm-dd}'    AND user_agent NOT LIKE
'ELB-HealthChecker%'GROUP BY    1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
INSERT OVERWRITE TABLE aa.campaign_accounts PARTITION (campaign_type, dt, org)
SELECT    ipm.ip,    ipm.master_id AS mid,    COALESCE(ad.cookie, '') AS cid,
    COALESCE(ad.url_cid, '') AS campaign_id,    COALESCE(ad.url_sid, '') AS
segment_id,    CASE        WHEN ad.url_event='clk'        THEN 'media_click'

ELSE 'media_impression'    END AS event,    count(*) AS count,    CASE
        WHEN COALESCE(o.name, '') != ''        THEN 'first_party_media'
        ELSE ot.token_type END AS campaign_type,    ad.dt,
    lower(COALESCE(ot.organization_name, o.name)) AS orgFROM    aa.tmp_subquery
ad    JOIN default.ip_to_master_v2 ipm        ON ipm.ip = ad.base_ip    lEFT
JOIN default.organization_token ot        ON ot.token = ad.url_token    LEFT
JOIN aa.organization o        ON ad.url_orgid = o.idWHERE 
COALESCE(ot.organization_name, o.name, '') != '' GROUP BY    1, 2, 3, 4, 5, 6,
8, 9, 10DISTRIBUTE BY    campaign_type, dt, orgSORT BY    mid;
The hive output matches presto's.Seems like a bug that's easy to miss and can
cause some major damage.
Anyone encountered this before?
Thanks,
Nitin
Mime
View raw message