AWS Serverless Lambda запланированных событий для хранения твитов в Couchbase объяснил, как хранить твиты в Couchbase, используя AWS Serverless Lambda. Теперь эта лямбда-функция работает уже несколько дней и собрала 269 твитов от @realDonaldTrump . Этот блог, вдохновленный SQL на Twitter: анализ стал проще с помощью N1QL , покажет, как эти твиты можно анализировать с помощью N1QL.
N1QL — это SQL-подобный язык запросов от Couchbase, который работает с документами JSON. Различия N1QL и SQL обеспечивают различия между N1QL и SQL. Давайте использовать N1QL, чтобы раскрыть некоторую интересную информацию из твитов @realDonaldTrump .
Большое спасибо Sitaram из команды N1QL за помощь в взломе запросов.
Сколько твитов
Первый запрос — узнать, сколько твитов доступно в базе данных. Запрос довольно прост:
Запрос:
|
1
2
|
SELECT COUNT(*) tweet_count FROM twitter; |
Как вы заметили, синтаксис очень похож на SQL. SELECT , COUNT и FROM — это то, с чем вы уже знакомы по синтаксису SQL. tweet_count — псевдоним, определенный для возвращаемого результата. twitter — это корзина, в которой хранятся все документы JSON.
Полученные результаты:
|
1
2
3
4
5
|
[ { "tweet_count": 269 }] |
Результатом является также документ JSON.
Tweet Образец документа JSON
Чтобы писать запросы к документу JSON, вам необходимо знать структуру документа. Следующий запрос даст вам это.
Запрос:
|
1
2
3
|
SELECT * FROM twitter LIMIT 1; |
Введенное здесь новое предложение — LIMIT . Это позволяет ограничить количество объектов, возвращаемых в результирующем наборе SELECT .
Полученные результаты:
|
001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
|
[ { "twitter": { "accessLevel": "0", "contributors": [], "createdAt": "1480828438000", "currentUserRetweetId": "-1", "displayTextRangeEnd": "-1", "displayTextRangeStart": "-1", "favoriteCount": "116356", "favorited": false, "geoLocation": null, "hashtagEntities": [], "id": "805278955150471168", "inReplyToScreenName": null, "inReplyToStatusId": "-1", "inReplyToUserId": "-1", "lang": "en", "mediaEntities": [], "place": null, "possiblySensitive": false, "quotedStatus": null, "quotedStatusId": "-1", "rateLimitStatus": null, "retweet": false, "retweetCount": "28330", "retweeted": false, "retweetedByMe": false, "retweetedStatus": null, "scopes": null, "source": "<a href=\"http://twitter.com/download/android\" rel=\"nofollow\">Twitter for Android</a>", "symbolEntities": [], "text": "Just tried watching Saturday Night Live - unwatchable! Totally biased, not funny and the Baldwin impersonation just can't get any worse. Sad", "truncated": false, "urlentities": [], "user": { "accessLevel": "0", "biggerProfileImageURL": "http://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_bigger.jpg", "biggerProfileImageURLHttps": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_bigger.jpg", "contributorsEnabled": false, "createdAt": "1237383998000", "defaultProfile": false, "defaultProfileImage": false, "description": "President-elect of the United States", "descriptionURLEntities": [], "email": null, "favouritesCount": "46", "followRequestSent": false, "followersCount": "19294404", "friendsCount": "42", "geoEnabled": true, "id": "25073877", "lang": "en", "listedCount": "52499", "location": "New York, NY", "miniProfileImageURLHttps": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_mini.jpg", "name": "Donald J. Trump", "originalProfileImageURLHttps": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2.jpg", "profileBackgroundColor": "6D5C18", "profileBackgroundImageURL": "http://pbs.twimg.com/profile_background_images/530021613/trump_scotland__43_of_70_cc.jpg", "profileBackgroundImageUrlHttps": "https://pbs.twimg.com/profile_background_images/530021613/trump_scotland__43_of_70_cc.jpg", "profileBackgroundTiled": true, "profileBannerIPadRetinaURL": "https://pbs.twimg.com/profile_banners/25073877/1479776952/ipad_retina", "profileBannerMobileRetinaURL": "https://pbs.twimg.com/profile_banners/25073877/1479776952/mobile_retina", "profileImageURLHttps": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_normal.jpg", "profileLinkColor": "0D5B73", "profileSidebarBorderColor": "BDDCAD", "profileSidebarFillColor": "C5CEC0", "profileTextColor": "333333", "profileUseBackgroundImage": true, "protected": false, "rateLimitStatus": null, "screenName": "realDonaldTrump", "showAllInlineMedia": false, "status": null, "statusesCount": "34269", "timeZone": "Eastern Time (US & Canada)", "translator": false, "urlentity": { "end": "23", "start": "0", }, "utcOffset": "-18000", "verified": true, "withheldInCountries": null }, "userMentionEntities": [], "withheldInCountries": null } }] |
Лучшие 5 Дней Чириканья
После того, как основные запросы ушли, давайте посмотрим на некоторые интересные данные.
Каковы первые 5 дней, в которые @realDonaldTrump написал твит и количество твитов?
Запрос:
|
1
2
3
4
5
6
|
SELECT SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 0, 10) tweet_date, COUNT(1) tweet_countFROM twitter GROUP BY SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 0, 10) ORDER BY COUNT(1) DESCLIMIT 5; |
Обычные предложения GROUP BY и ORDER BY SQL выполняют одну и ту же функцию.
Функции N1QL применяют функцию к значениям. Поле createdAt возвращает число в виде строки. Функция TO_NUM преобразует строку в число. Функция MILLIS_TO_STR преобразует строку в дату. Наконец, функция SUBSTR извлекает соответствующую часть даты.
Полученные результаты:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[ { "tweet_count": 13, "tweet_date": "2017-01-17" }, { "tweet_count": 12, "tweet_date": "2017-01-06" }, { "tweet_count": 11, "tweet_date": "2016-12-04" }, { "tweet_count": 10, "tweet_date": "2017-01-03" }, { "tweet_count": 10, "tweet_date": "2017-01-04" }] |
17 января 2017 года — самый чириканный день. Теперь этот результат, конечно, ограничен данными из документов JSON, хранящихся в базе данных.
У кого-нибудь есть более полная база данных твитов @realDonaldTrump?
Чирикать Частота
Итак, наша база данных показывает, что максимальное количество твитов в день было 13. Как узнать, сколько дней @realDonaldTrump твитнул определенное количество раз?
Запрос:
|
1
2
3
4
5
6
7
8
|
SELECT a.tweet_count, count(1) days FROM (SELECT SUBSTR(millis_to_str(to_num(createdAt)), 0, 10) tweet_date, COUNT(1) tweet_countFROM twitter GROUP BY SUBSTR(millis_to_str(to_num(createdAt)), 0, 10)) aGROUP BY a.tweet_countORDER BY a.tweet_count DESC; |
Это легко достигается с помощью вложенных запросов N1QL .
Полученные результаты:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
[ { "days": 1, "tweet_count": 13 }, { "days": 1, "tweet_count": 12 }, { "days": 1, "tweet_count": 11 }, { "days": 2, "tweet_count": 10 }, { "days": 1, "tweet_count": 9 }, { "days": 7, "tweet_count": 8 }, { "days": 3, "tweet_count": 7 }, { "days": 7, "tweet_count": 6 }, { "days": 5, "tweet_count": 5 }, { "days": 5, "tweet_count": 4 }, { "days": 11, "tweet_count": 3 }, { "days": 3, "tweet_count": 2 }, { "days": 1, "tweet_count": 1 }] |
В 47 днях только один день с одним твитом. Итого tweet_count показывает, что нет одного дня без твита
Самый распространенный час за день, чтобы чирикать
@realDonaldTrump, как известно, чирикать в 3 часа ночи . Давайте посмотрим, какие часы для него наиболее распространены.
Запрос:
|
1
2
3
4
5
6
|
SELECT SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 11, 2) tweet_hour, COUNT(1) tweet_countFROM twitter GROUP BY SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 11, 2) ORDER BY tweet_count DESCLIMIT 5; |
Полученные результаты:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[ { "tweet_count": 39, "tweet_hour": "13" }, { "tweet_count": 27, "tweet_hour": "12" }, { "tweet_count": 26, "tweet_hour": "11" }, { "tweet_count": 20, "tweet_hour": "14" }, { "tweet_count": 15, "tweet_hour": "00" }] |
Теперь кажется, что спорные твиты приходят в 3 часа ночи. Но 39 твитов приходят в 13:00 по восточному времени, вероятно, сразу после обеда и во время десерта 
Общий день недели, чтобы чирикать
Давайте выясним, какой самый распространенный день недели в твиттере.
Запрос:
|
1
2
3
4
5
|
SELECT DATE_PART_STR(MILLIS_TO_STR(TO_NUM(createdAt)), "day_of_week") day_of_week, COUNT(1) tweet_countFROM twitter GROUP BY DATE_PART_STR(MILLIS_TO_STR(TO_NUM(createdAt)), "day_of_week")ORDER BY tweet_count DESC; |
DATE_PART_STR — это новая функция, возвращающая часть даты даты. Далее атрибут day_of_week используется для получения дня недели.
Полученные результаты:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
[ { "day_of_week": 2, "tweet_count": 49 }, { "day_of_week": 3, "tweet_count": 40 }, { "day_of_week": 0, "tweet_count": 40 }, { "day_of_week": 5, "tweet_count": 38 }, { "day_of_week": 4, "tweet_count": 36 }, { "day_of_week": 6, "tweet_count": 33 }, { "day_of_week": 1, "tweet_count": 33 }] |
Кажется, вторник — самый распространенный день для твитов. Затем наступают воскресенье и среда на одном уровне. Спектакль имеет тенденцию кончаться ближе к выходным.
Вот хороший график, который показывает ту же тенденцию:
№ 22417 должен позволять сообщать о части дня недели на английском языке.
Топ 5 упоминаний в твитах
Запрос:
|
1
2
3
4
5
6
|
SELECT COUNT(1) user_count, ue.screenName FROM twitter UNNEST userMentionEntities ue GROUP by ue.screenName ORDER by user_count DESC LIMIT 5; |
userMentionEntities — это вложенный массив в документе JSON. UNNEST концептуально выполняет объединение вложенного массива с его родительским объектом. Каждый результирующий объединенный объект становится входом для запроса.
Полученные результаты:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[ { "screenName": "realDonaldTrump", "user_count": 11 }, { "screenName": "FoxNews", "user_count": 7 }, { "screenName": "CNN", "user_count": 6 }, { "screenName": "NBCNews", "user_count": 5 }, { "screenName": "DanScavino", "user_count": 5 }] |
Излишне говорить, что он чаще всего упоминает свое имя в твитах! И его две любимые телеканалы Fox News и CNN .
Топ 5 твитов с RT
Лямбда-функция активируется каждые 3 часа и получает последние твиты. Таким образом, база данных представляет собой снимок твитов и связанной с ними информации, такой как RT и избранное. Таким образом, в зависимости от того, когда твит был заархивирован, RT и избранное могут быть неточным представлением. Но, учитывая эту информацию, давайте посмотрим на твиты с большинством RT.
Запрос:
|
1
2
3
4
|
SELECT retweetCount, textFROM twitterORDER BY retweetCountLIMIT 5; |
Довольно простой запрос.
Полученные результаты:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[ { "retweetCount": "10110", "text": "the American people. I have no doubt that we will, together, MAKE AMERICA GREAT AGAIN!" }, { "retweetCount": "10140", "text": "Thank you to all of the men and women who protect & serve our communities 24/7/365! \n#LawEnforcementAppreciationDay… https://t.co/aqUbDipSgv" }, { "retweetCount": "10370", "text": "We had a great News Conference at Trump Tower today. A couple of FAKE NEWS organizations were there but the people truly get what's going on" }, { "retweetCount": "10414", "text": "these companies are able to move between all 50 states, with no tax or tariff being charged. Please be forewarned prior to making a very ..." }, { "retweetCount": "10416", "text": "Somebody hacked the DNC but why did they not have \"hacking defense\" like the RNC has and why have they not responded to the terrible......" }] |
Оригинал против РЦ
Сколько твитов было написано против ретвита?
Запрос:
|
1
2
3
|
SELECT retweet, count(1) countFROM twitterGROUP BY retweet; |
Полученные результаты:
|
01
02
03
04
05
06
07
08
09
10
|
[ { "count": 253, "retweet": false }, { "count": 15, "retweet": true }] |
Большинство твитов являются оригинальными с несколькими RT.
Самые распространенные слова в твиттере
Запрос:
|
1
2
3
4
5
|
SELECT COUNT(1) count, word FROM twitter UNNEST SPLIT(text) wordGROUP BY wordORDER BY count DESC; |
Этот запрос использует функцию SPLIT которая
Полученные результаты:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
|
[ { "count": 189, "word": "the" }, { "count": 151, "word": "to" }, { "count": 115, "word": "and" }, . . . { "count": 1, "word": "presented...Trump's" }, { "count": 1, "word": "jobs." }, { "count": 1, "word": "Doing" }] |
Частота слов «медиа», «фальшивка» и «Америка» в твитах
Запрос:
|
1
2
3
4
5
6
|
SELECT COUNT(1) count, LOWER(w) wordFROM twitter UNNEST SPLIT(text) w WHERE LOWER(w) IN [ "media", "fake", "america"] GROUP by LOWER(w) ORDER BY count DESC; |
Функция LOWER используется для сравнения слов независимо от регистра.
Результат:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
|
[ { "count": 12, "word": "media" }, { "count": 9, "word": "fake" }, { "count": 8, "word": "america" }] |
Лямбда-функция будет продолжать хранить твиты в базе данных.
Попробуйте сами эти вопросы?
- Запустите сервер Couchbase
- Используйте архив twitter-backups-2017-01-20-06-07-49.tar, как описано в разделе « Восстановление данных в Couchbase».
- Используйте Query Workbench для запуска запросов
Ссылки N1QL
- Интерактивное учебное пособие по N1QL
- N1QL Cheatsheet
- Справочник по языку N1QL
- Запустите свой первый N1QL-запрос
| Ссылка: | Проанализируйте твиты Дональда Трампа с помощью Couchbase и N1QL от нашего партнера по JCG Аруна Гупта из блога Miles to go 3.0… . |