yarn logs -applicationId application_1555511316215_187017

/srv/deployment/analytics/refinery/bin/yarn-logs application_1555511316215_187017

wikidataParquetPath = '/user/joal/wmf/data/wmf/wikidata/item_page_link/20190204'
spark.read.parquet(wikidataParquetPath).createOrReplaceTempView('wikidata')

articles_viewed = spark.sql("""

WITH wikidata_idwiki_pages AS (
  SELECT DISTINCT
    item_id AS idwiki_item_id,
    page_id AS idwiki_page_id
  FROM wikidata
  WHERE wiki_db = 'idwiki'
    AND page_namespace = 0
),

wikidata_items_reduced AS (
  SELECT DISTINCT
    d1.wiki_db as wiki_db,
    d1.item_id as item_id,
    d1.page_id as page_id,
    d2.idwiki_page_id as idwiki_page_id
  FROM wikidata d1
    LEFT JOIN wikidata_idwiki_pages d2
      ON d1.item_id = d2.idwiki_item_id

),

toledo_base AS (
    SELECT DISTINCT
      r.page_id AS page_id,
      CONCAT(r.pageview_info.project, '.org') AS project
    FROM wmf.webrequest r
    WHERE webrequest_source = 'text'
        AND year = 2019
        AND month = 5
        AND is_pageview
        AND namespace_id = 0
        AND x_analytics_map['translationengine'] = 'GT'
        AND parse_url(referer, 'QUERY') like '%client=srp%'
        AND (regexp_extract(parse_url(referer, 'QUERY'), '(^|[&?])hl=([^&]*)', 2) = 'id'
          OR regexp_extract(parse_url(referer, 'QUERY'), '(^|[&?])tl=([^&]*)', 2) = 'id')
),

toledo_proj AS (
  SELECT
    tb.page_id,
    w.database_code as wiki_db
  FROM toledo_base tb
    JOIN canonical_data.wikis w
      ON tb.project = w.domain_name
)

SELECT
  t.page_id as toledo_page_id,
  t.wiki_db,
  d.item_id,
  d.idwiki_page_id as idwiki_page_id
FROM toledo_proj t
  LEFT JOIN wikidata_items_reduced d
    ON t.page_id = d.page_id
      AND t.wiki_db=d.wiki_db
""")
articles_viewed.count()

spark.conf.set('spark.driver.memory', '4g')