sudo -u analytics kerberos-run-command analytics /usr/local/bin/refine_eventlogging_legacy --table_whitelist_regex='CentralNoticeImpression|ContentTranslationError|ContentTranslationSuggestion|EchoMail|EditAttemptStep|EditorActivation|EventError|GuidedTourExited|GuidedTourGuiderHidden|InukaPageView|KaiOSAppFeedback|MobileWebSearch|MobileWebSectionUsage|MobileWebUIActionsTracking|MobileWikiAppABTest|MobileWikiAppIntents|MobileWikiAppLinkPreview|MobileWikiAppOnThisDay|MobileWikiAppRandomizer|MobileWikiAppSessions|MobileWikiAppSuggestedEdits|MobileWikiAppSuggestedEditsFeed|MobileWikiAppTabs|MobileWikiAppTalk|MobileWikiAppToCInteraction|MobileWikiAppiOSReadingLists|MobileWikiAppiOSSessions|ReferencePreviewsBaseline|TranslationRecommendationUIRequests|UploadWizardStep|WikidataCompletionSearchClicks' --since='2021-02-09T00:00:00' --until='2021-02-10T00:00:00' --ignore_failure_flag=true --ignore_done_flag=true

sudo -u analytics kerberos-run-command analytics /usr/local/bin/refine_eventlogging_analytics --table_whitelist_regex='CentralNoticeImpression|ContentTranslationError|ContentTranslationSuggestion|EchoMail|EditAttemptStep|EditorActivation|EventError|GuidedTourExited|GuidedTourGuiderHidden|InukaPageView|KaiOSAppFeedback|MobileWebSearch|MobileWebSectionUsage|MobileWebUIActionsTracking|MobileWikiAppABTest|MobileWikiAppIntents|MobileWikiAppLinkPreview|MobileWikiAppOnThisDay|MobileWikiAppRandomizer|MobileWikiAppSessions|MobileWikiAppSuggestedEdits|MobileWikiAppSuggestedEditsFeed|MobileWikiAppTabs|MobileWikiAppTalk|MobileWikiAppToCInteraction|MobileWikiAppiOSReadingLists|MobileWikiAppiOSSessions|ReferencePreviewsBaseline|TranslationRecommendationUIRequests|UploadWizardStep|WikidataCompletionSearchClicks' --since='2021-02-09T00:00:00' --until='2021-02-10T00:00:00' --ignore_failure_flag=true --ignore_done_flag=true

2021-02-13T00:38:00,776 ERROR [8a3b024d-3aca-4c23-ae05-966e6e88080d HiveServer2-Handler-Pool: Thread-164680] parse.CalcitePlanner: CBO failed, skipping CBO. 
org.apache.hadoop.hive.ql.parse.SemanticException: Line 20:4 Invalid function ''project''
        at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.getXpathOrFuncExprNodeDesc(TypeCheckProcFactory.java:939) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory$DefaultExprProcessor.process(TypeCheckProcFactory.java:1359) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.lib.DefaultRuleDispatcher.dispatch(DefaultRuleDispatcher.java:90) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatchAndReturn(DefaultGraphWalker.java:105) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.dispatch(DefaultGraphWalker.java:89) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.lib.ExpressionWalker.walk(ExpressionWalker.java:76) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.lib.DefaultGraphWalker.startWalking(DefaultGraphWalker.java:120) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.parse.TypeCheckProcFactory.genExprNode(TypeCheckProcFactory.java:229) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.optimizer.calcite.translator.JoinCondTypeCheckProcFactory.genExprNode(JoinCondTypeCheckProcFactory.java:58) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genJoinRelNode(CalcitePlanner.java:1953) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genJoinLogicalPlan(CalcitePlanner.java:2167) ~[hive-exec-2.3.6.jar:2.3.6]
...

2021-02-13T03:15:15,802 ERROR [2d5044e1-67a1-4c02-88e6-ebcb6352842b HiveServer2-Handler-Pool: Thread-171789] parse.CalcitePlanner: CBO failed, skipping CBO. 
org.apache.hadoop.hive.ql.optimizer.calcite.CalciteSemanticException: Table Sample specified for webrequest. Currently we don't support Table Sample clauses in CBO, turn off cbo for queries on tableSamples.
        at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genTableLogicalPlan(CalcitePlanner.java:2185) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.genLogicalPlan(CalcitePlanner.java:3957) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1315) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.hadoop.hive.ql.parse.CalcitePlanner$CalcitePlannerAction.apply(CalcitePlanner.java:1261) ~[hive-exec-2.3.6.jar:2.3.6]
        at org.apache.calcite.tools.Frameworks$1.apply(Frameworks.java:113) ~[calcite-core-1.10.0.jar:1.10.0]
        at org.apache.calcite.prepare.CalcitePrepareImpl.perform(CalcitePrepareImpl.java:997) ~[calcite-core-1.10.0.jar:1.10.0]

2021-02-13T00:29:20,114 ERROR [c69ed050-16b1-47f3-9306-8740b72a3aaf HiveServer2-Handler-Pool: Thread-163900] parse.Cal
citePlanner: CBO failed, skipping CBO. 
java.lang.NullPointerException

Error: Error running query: java.lang.AssertionError: Internal error: While invoking method 'public org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveRelDecorrelator$Frame org.apache.hadoop.hive.ql.optimizer.calcite.rules.HiveRelDecorrelator.decorrelateRel(org.apache.hadoop.hive.ql.optimizer.calcite.reloperators.HiveProject) throws 
org.apache.hadoop.hive.ql.parse.SemanticException' (state=,code=0)

NoViableAltException(350@[()* loopback of 430:20: ( ( LSQUARE ^ expression RSQUARE !) | ( DOT ^ identifier ) )*])

hive -f pageview_top_percountry.hql -d refinery_hive_jar_path=hdfs://analytics-hadoop/wmf/refinery/current/artifacts/org/wikimedia/analytics/refinery/refinery-hive-0.1.1.jar -d destination_directory=/user/lexnasser/test    -d source_table=wmf.pageview_actor -d 
country_blacklist_table=wmf.geoeditors_blacklist_country -d separator=\\t -d year=2021 -d month=2 -d day=16

Error: Error while compiling statement: FAILED: SemanticException [Error 10250]: Line 23:8 Invalid SubQuery expression '1': SubQuery can contain only 1 item in Select List. (state=42000,code=10250)
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10250]: Line 23:8 Invalid SubQuery expression '1': SubQuery can contain only 1 item in Select List.

ADD JAR hdfs://analytics-hadoop/wmf/refinery/current/artifacts/refinery-hive.jar;

DROP TABLE IF EXISTS joal.test_lex_pageview_top_percountry;
CREATE TABLE joal.test_lex_pageview_top_percountry AS
WITH raw_ungrouped AS (
    SELECT
        geocoded_data['country_code'] AS country_code,
        REGEXP_REPLACE(access_method, ' ', '-') AS access,
        pageview_info['project'] AS project,
        REFLECT('org.json.simple.JSONObject', 'escape', REGEXP_REPLACE(pageview_info['page_title'], '\t', '')) AS page_title,
        LPAD(year, 4, '0') as year,
        LPAD(month, 2, '0') as month,
        LPAD(day, 2, '0') as day,
        actor_signature
    FROM wmf.pageview_actor source
    WHERE
        year = 2021
        AND month = 3
        AND day = 3
        AND agent_type = 'user'
        AND pageview_info IS NOT NULL
        AND geocoded_data IS NOT NULL
        AND is_pageview
        -- Remove special unknown pageview (see T117346)
        AND pageview_info['page_title'] != '-'
        AND geocoded_data['country_code'] != '--'
        -- This NOT EXISTS executes as a map join, and was chosen after benchmarking against LEFT JOIN and NOT IN
        AND NOT EXISTS (
            SELECT country_blacklist.country_code
            FROM wmf.geoeditors_blacklist_country country_blacklist
            WHERE
                country_blacklist.country_code = source.geocoded_data['country_code']
        )
),
raw AS (
    SELECT
        access,
        year,
        month,
        day,
        country_code,
        project,
        page_title,
        COUNT(1) AS total_view_count,
        COUNT(DISTINCT actor_signature) AS unique_actor_count
    FROM raw_ungrouped
    WHERE
        year =2021
        AND month = 3
        AND day = 3
    GROUP BY
        access,
        year,
        month,
        day,
        country_code,
        project,
        page_title
    -- Union must be used rather than grouping sets due to issue with HiveRelDecorrelator.decorrelateRel
    UNION
    SELECT
        'all-access' AS access,
        year,
        month,
        day,
        country_code,
        project,
        page_title,
        COUNT(1) AS total_view_count,
        COUNT(DISTINCT actor_signature) AS unique_actor_count
    FROM raw_ungrouped
    WHERE
        year = 2021
        AND month = 3
        AND day = 3
    GROUP BY
        year,
        month,
        day,
        country_code,
        project,
        page_title
),
ranked AS (
    SELECT
        country_code,
        access,
        project,
        page_title,
        year,
        month,
        day,
        CEIL(total_view_count / 100) * 100 AS views_ceil,
        rank() OVER (PARTITION BY access, country_code, year, month, day ORDER BY total_view_count DESC, page_title) as rank,
        row_number() OVER (PARTITION BY access, country_code, year, month, day ORDER BY total_view_count DESC, page_title) as rn
    FROM raw
    WHERE unique_actor_count > 1000
),
max_rank AS (
    SELECT
        country_code,
        access,
        year,
        month,
        day,
        rank as max_rank
    FROM ranked
    WHERE rn = 1001
    GROUP BY
        country_code,
        access,
        year,
        month,
        day,
        rank
)

SELECT
        ranked.country_code as country_code,
        ranked.access as access,
        ranked.year as year,
        ranked.month as month,
        ranked.day as day,
        CONCAT(
            '[',
            CONCAT_WS(
                ',',
                COLLECT_SET(
                    CONCAT(
                        '{\"article\":\"',
                        ranked.page_title,
                        '\",\"project\":\"',
                        ranked.project,
                        '\",\"views_ceil\":',
                        CAST(ranked.views_ceil AS STRING),
                        ',\"rank\":',
                        CAST(ranked.rank AS STRING),
                        '}'
                    )
                )
            ),
            ']'
        ) as top_values
FROM ranked
LEFT JOIN max_rank ON (
    ranked.country_code = max_rank.country_code
    AND ranked.access = max_rank.access
    AND ranked.year = max_rank.year
    AND ranked.month = max_rank.month
    AND ranked.day = max_rank.day
)
WHERE
    ranked.rank < COALESCE(max_rank.max_rank, 1001)
GROUP BY
    ranked.country_code,
    ranked.access,
    ranked.year,
    ranked.month,
    ranked.day
;

ADD JAR hdfs://analytics-hadoop/wmf/refinery/current/artifacts/refinery-hive.jar;

SET hive.cbo.enable=false;
SET hive.mapred.mode=nonstrict;

DROP TABLE IF EXISTS joal.test_joal_pageview_top_percountry;
CREATE TABLE joal.test_joal_pageview_top_percountry AS
WITH base_data AS (
    SELECT
        geocoded_data['country_code'] AS country_code,
        regexp_replace(access_method, ' ', '-') AS access,
        pageview_info['project'] AS project,
        reflect('org.json.simple.JSONObject', 'escape', regexp_replace(pageview_info['page_title'], '\t', '')) AS page_title,
        actor_signature,
        LPAD(year, 4, '0') as year,
        LPAD(month, 2, '0') as month,
        LPAD(day, 2, '0') as day
    FROM wmf.pageview_actor source
    WHERE
        year = 2021
        AND month = 3
        AND day = 3
        AND agent_type = 'user'
        AND pageview_info IS NOT NULL
        AND geocoded_data IS NOT NULL
        AND is_pageview
        -- Remove special unknown pageview (see T117346)
        AND pageview_info['page_title'] != '-'
        AND geocoded_data['country_code'] != '--'
        -- This NOT EXISTS executes as a map join, and was chosen after benchmarking against LEFT JOIN and NOT IN
        AND NOT EXISTS (
            SELECT TRUE
            FROM wmf.geoeditors_blacklist_country country_blacklist
            WHERE country_blacklist.country_code = source.geocoded_data['country_code']
        )
),
raw AS (
    SELECT
        country_code,
        COALESCE(access, 'all-access') AS access,
        project,
        page_title,
        year,
        month,
        day,
        COUNT(1) AS total_view_count,
        COUNT(DISTINCT actor_signature) AS unique_actor_count
    FROM base_data
    GROUP BY
        country_code,
        access,
        project,
        page_title,
        year,
        month,
        day
    GROUPING SETS (
        (
            country_code,
            access,
            project,
            page_title,
            year,
            month,
            day
        ),
        (
            country_code,
            project,
            page_title,
            year,
            month,
            day
        )
    )
),
ranked AS (
    SELECT
        country_code,
        access,
        project,
        page_title,
        year,
        month,
        day,
        CEIL(total_view_count / 100) * 100 AS views_ceil,
        rank() OVER (PARTITION BY access, country_code, year, month, day ORDER BY total_view_count DESC, page_title) as rank,
        row_number() OVER (PARTITION BY access, country_code, year, month, day ORDER BY total_view_count DESC, page_title) as rn
    FROM raw
    WHERE unique_actor_count > 1000
),
max_rank AS (
    SELECT
        country_code,
        access,
        year,
        month,
        day,
        rank as max_rank
    FROM ranked
    WHERE rn = 1001
    GROUP BY
        country_code,
        access,
        year,
        month,
        day,
        rank
)

SELECT
        ranked.country_code as country_code,
        ranked.access as access,
        ranked.year as year,
        ranked.month as month,
        ranked.day as day,
        CONCAT('[',
            CONCAT_WS(',', collect_set(
                CONCAT('{\"article\":\"', ranked.page_title,
                    '\",\"project\":\"', ranked.project,
                    '\",\"views_ceil\":', CAST(ranked.views_ceil AS STRING),
                    ',\"rank\":', CAST(ranked.rank AS STRING), '}'))
            ),']') AS top_values
FROM ranked
LEFT JOIN max_rank ON (
    ranked.country_code = max_rank.country_code
    AND ranked.access = max_rank.access
    AND ranked.year = max_rank.year
    AND ranked.month = max_rank.month
    AND ranked.day = max_rank.day
)
WHERE
    ranked.rank < COALESCE(max_rank.max_rank, 1001)
GROUP BY
    ranked.country_code,
    ranked.access,
    ranked.year,
    ranked.month,
    ranked.day
;

 select
    (l.top_values = j.top_values),
    count(1) as c
from test_joal_pageview_top_percountry j
    inner join test_lex_pageview_top_percountry l
    on (
        j.country_code = l.country_code
       and j.access = l.access
       and j.year = l.year
       and j.month = l.month
       and j.day = l.day)
group by (l.top_values = j.top_values)
;