15 Декабря 2016г., Четверг

Первоначально опубликовано на cloud.google.com.

Изучение скрытых тенденций и взаимосвязей в данных Stack Overflow - хороший урок при проведении аналитики SQL с помощью BigQuery.

Отличные новости: мы только что добавили историю вопросов и ответов Stack Overflow в коллекцию общедоступных наборов данных на BigQuery. Это означает, что любой, у кого есть учетная запись Google Cloud Platform, может использовать SQL-запросы (или какой-нибудь другой любимый инструмент), чтобы копаться в этой сокровищнице данных.

Вы можете найти несколько примеров запросов на странице документации Stack Overflow Data, например:

  • «На какой процент вопросов были даны ответы за эти годы?»
  • «Какова репутация и количество значков пользователей на разных сроках пребывания в Stack Overflow?»
  • «Какие 10« самых простых »золотых значков заработать?»
  • «В какой день недели ответы на большинство вопросов можно получить в течение часа?»

Возьмите эти вопросы в качестве отправной точки, а затем поделитесь с нами своими результатами и вариантами запросов через reddit.com/r/bigquery. А если у вас есть вопросы, задавайте вопросы сообществу на Stack Overflow.

Погружение в данные

Вы можете спросить: что такого особенного в запросах Stack Overflow с помощью BigQuery? В конце концов, Stack Overflow уже отсылает пользователей к Stack Exchange Data Explorer (SEDE), сайту, ориентированному на данные, где пользователи поделились тысячами вопросов и расставили приоритеты - и это действительно хорошо работает. Итак, давайте рассмотрим некоторые преимущества наличия данных Stack Overflow в BigQuery:

  • Превзойдите ограничение в 50 000 строк. SEDE может выводить не более 50 000 строк. Для BigQuery это не проблема.
  • Роботы приветствуются. SEDE защищает себя от злоупотреблений с помощью CAPTCHA и не имеет API. В BigQuery для входа в систему не требуются CAPTCHA, а его REST API позволяет использовать различные инструменты для использования его возможностей. Не стесняйтесь подключать к нему Tableau, re: dash, Looker, R, pandas и свои любимые инструменты.
  • ПРИСОЕДИНЯЙТЕСЬ ко всему. В BigQuery есть множество других наборов данных, и ничто не мешает вам загрузить еще больше, в частном порядке или для общего пользования. Представьте себе вопросы, на которые вы могли бы ответить, задав им вопросы?

Давайте посмотрим на пример присоединения. В BigQuery есть терабайты открытого исходного кода GitHub. Давайте выясним, на какие вопросы о переполнении стека чаще всего ссылаются в коде GitHub, в частности, в Javascript.

#standardSQL 
SELECT a.id, title, c files, answer_count answers, favorite_count favs, view_count views, score FROM `bigquery-public-data.stackoverflow.posts_questions` a JOIN ( SELECT CAST(REGEXP_EXTRACT(content, r'stackoverflow.com/questions/([0-9]+)/') AS INT64) id, COUNT(*) c, MIN(sample_path) sample_path FROM `fh-bigquery.github_extracts.contents_js` WHERE content LIKE '%stackoverflow.com/questions/%' GROUP BY 1 HAVING id>0 ORDER BY 2 DESC LIMIT 10 ) b ON a.id=b.id ORDER BY c DESC

Вот наиболее часто задаваемые вопросы о переполнении стека в коде Javascript на GitHub:

Или мы можем посмотреть комментарии GitHub pull-request от GHTorrent (также на BigQuery):

#standardSQL 
SELECT a.id, title, c files, answer_count answers, favorite_count favs, view_count views, score FROM `bigquery-public-data.stackoverflow.posts_questions` a JOIN ( SELECT CAST(REGEXP_EXTRACT(body, r'stackoverflow.com/questions/([0-9]*)/') AS INT64) id, COUNT(*) c FROM `ghtorrent-bq.ght.pull_request_comments` WHERE body LIKE '%stackoverflow.com/questions%' GROUP BY 1 ORDER BY 2 DESC LIMIT 10 ) b ON a.id=b.id ORDER BY c DESC

Вот результаты:

Или давайте посмотрим на Hacker News. Какие самые популярные теги вопросов, которые были там размещены с 2014 года?

#standardSQL 
SELECT tag, SUM(c) c FROM ( SELECT CONCAT('stackoverflow.com/questions/', CAST(b.id AS STRING)), title, c, answer_count, favorite_count, view_count, score, SPLIT(tags, '|') tags FROM `bigquery-public-data.stackoverflow.posts_questions` a JOIN ( SELECT CAST(REGEXP_EXTRACT(text, r'stackoverflow.com/questions/([0-9]+)/') AS INT64) id, COUNT(*) c FROM `fh-bigquery.hackernews.comments` WHERE text LIKE '%stackoverflow.com/questions/%' AND EXTRACT(YEAR FROM time_ts)>=2014 GROUP BY 1 ORDER BY 2 DESC ) b ON a.id=b.id), UNNEST(tags) tag GROUP BY 1 ORDER BY 2 DESC LIMIT 10

Вот самые популярные теги на вопросы о переполнении стека, на которые ссылается Hacker News с 2014 года:

Как это соотносится с остальной частью Stack Overflow?

#standardSQL 
SELECTtag, COUNT(*) c FROM ( SELECT SPLIT(tags, '|') tags FROM `bigquery-public-data.stackoverflow.posts_questions` a WHERE EXTRACT(YEAR FROM creation_date)>=2014 ), UNNEST(tags) tag GROUP BY 1 ORDER BY 2 DESC LIMIT 10

Казалось бы, сообщество Hacker News гораздо больше заботится о Haskell, C, C ++ и производительности, чем о Stack Overflow в целом, где php, android, jquery и css перечислены в самых популярных тегах:

Следующие шаги

Если вы еще не пробовали BigQuery, следуйте этому Руководству для начинающих, в котором показано, как проанализировать 50 миллиардов просмотров страниц за 5 секунд. Затем вы можете свободно поиграть с любым другим запросом или набором данных, который вам нравится: например, с нашими официальными общедоступными наборами данных BigQuery, наборами данных, которыми поделились другие пользователи, и, конечно же, вашими собственными.

Первоначально опубликовано на cloud.google.com.