Eventos agendados do AWS Serverless Lambda para armazenar tweets no Couchbase explicou como armazenar tweets no Couchbase usando o AWS Serverless Lambda. Agora, essa função Lambda está sendo executada há alguns dias e coletou 269 tweets de @realDonaldTrump. Este blog, inspirado por SQL no Twitter: Análise facilitada com o N1QLA partir de agora, o N1QL mostrará como esses tweets podem ser analisados usando o N1QL.
N1QL é uma linguagem de consulta semelhante ao SQL da Couchbase que opera em documentos JSON. Diferenças entre N1QL e SQL fornecem diferenças entre N1QL e SQL. Vamos usar o N1QL para revelar algumas informações interessantes de @realDonaldTrumptweets. Muito obrigado a Sitaram da equipe N1QL para ajudar a hackear as consultas.
Quantos Tweets
A primeira consulta é para descobrir quantos tweets estão disponíveis no banco de dados. A consulta é bem simples: Consulta:
|
1 2 |
SELECT COUNT(*) tweet_count FROM twitter; |
Como você pode notar, a sintaxe é muito semelhante à do SQL. SELECIONAR, CONTAGEM e DE são as cláusulas com as quais você já está familiarizado na sintaxe do SQL. tweet_count é um alias definido para o resultado retornado. twitter é o bucket em que todos os documentos JSON são armazenados. Resultados:
|
1 2 3 4 5 |
[ { "tweet_count": 269 } ] |
O resultado também é um documento JSON.
Tweet Exemplo de documento JSON
Para escrever consultas em um documento JSON, você precisa conhecer a estrutura do documento. A próxima consulta lhe dará isso. Consulta:
|
1 2 3 |
SELECT * FROM twitter LIMIT 1; |
A nova cláusula introduzida aqui é LIMITE. Isso permite restringir o número de objetos que são retornados em um conjunto de resultados de SELECIONAR. Resultados:
|
1 2 3 4 5 6 7 8 9 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 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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="https://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": "https://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", "miniProfileImageURL": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_mini.jpg", "miniProfileImageURLHttps": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_mini.jpg", "name": "Donald J. Trump", "originalProfileImageURL": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2.jpg", "originalProfileImageURLHttps": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2.jpg", "profileBackgroundColor": "6D5C18", "profileBackgroundImageURL": "https://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", "profileBannerIPadURL": "https://pbs.twimg.com/profile_banners/25073877/1479776952/ipad", "profileBannerMobileRetinaURL": "https://pbs.twimg.com/profile_banners/25073877/1479776952/mobile_retina", "profileBannerMobileURL": "https://pbs.twimg.com/profile_banners/25073877/1479776952/mobile", "profileBannerRetinaURL": "https://pbs.twimg.com/profile_banners/25073877/1479776952/web_retina", "profileBannerURL": "https://pbs.twimg.com/profile_banners/25073877/1479776952/web", "profileImageURL": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_normal.jpg", "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, "url": "https://t.co/mZB2hymxC9", "urlentity": { "displayURL": "https://t.co/mZB2hymxC9", "end": "23", "expandedURL": "https://t.co/mZB2hymxC9", "start": "0", "text": "https://t.co/mZB2hymxC9", "url": "https://t.co/mZB2hymxC9" }, "utcOffset": "-18000", "verified": true, "withheldInCountries": null }, "userMentionEntities": [], "withheldInCountries": null } } ] |
Os 5 principais dias de tweets
Depois que as consultas básicas estiverem concluídas, vamos analisar alguns dados interessantes. Quais são os 5 principais dias em que @realDonaldTrump tweetou e a contagem de tweets? Consulta:
|
1 2 3 4 5 6 |
SELECT SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 0, 10) tweet_date, COUNT(1) tweet_count FROM twitter GROUP BY SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 0, 10) ORDER BY COUNT(1) DESC LIMIT 5; |
Usual GRUPO POR e ORDER BY As cláusulas SQL executam a mesma função. Funções N1QL aplicar uma função aos valores. A função createdAt é retornado um número como uma cadeia de caracteres. TO_NUM converte o String em um número. MILLIS_TO_STR converte o String em uma data. Finalmente, SUBSTR extrai a parte relevante da data. Resultados:
|
1 2 3 4 5 6 7 8 9 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 de janeiro de 2017 é o dia mais tuitado. Agora, é claro que esse resultado está restrito aos dados dos documentos JSON armazenados no banco de dados. Alguém tem um banco de dados mais abrangente dos tweets de @realDonaldTrump?
Frequência de tweets
OK, nosso banco de dados mostra que o número máximo de tweets em um dia foi 13. Como faço para descobrir em quantos dias @realDonaldTrump tweetou um determinado número de vezes? Consulta:
|
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_count FROM twitter GROUP BY SUBSTR(millis_to_str(to_num(createdAt)), 0, 10) ) a GROUP BY a.tweet_count ORDER BY a.tweet_count DESC; |
Isso é facilmente obtido usando Consultas aninhadas N1QL. Resultados:
|
1 2 3 4 5 6 7 8 9 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 } ] |
Em 47 dias, há apenas um dia com um único tweet. Uma soma total de tweet_count mostra que não há um único dia sem um tuíte :)
Hora mais comum em um dia To Tweet
O @realDonaldTrump é conhecido por tuitar às 3 da manhã. Vamos dar uma olhada nos horários mais comuns em que ele tuíta. Consulta:
|
1 2 3 4 5 6 |
SELECT SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 11, 2) tweet_hour, COUNT(1) tweet_count FROM twitter GROUP BY SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 11, 2) ORDER BY tweet_count DESC LIMIT 5; |
Resultados:
|
1 2 3 4 5 6 7 8 9 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" } ] |
Agora parece que os tweets polêmicos chegam às 3h da manhã. Mas os 39 tweets estão chegando às 13h (horário de Brasília), provavelmente logo após o almoço e enquanto comemos uma sobremesa ;)
Dia comum da semana para twittar
Vamos descobrir quais são os dias da semana mais comuns para tuitar. Consulta:
|
1 2 3 4 5 |
SELECT DATE_PART_STR(MILLIS_TO_STR(TO_NUM(createdAt)), "day_of_week") day_of_week, COUNT(1) tweet_count FROM twitter GROUP BY DATE_PART_STR(MILLIS_TO_STR(TO_NUM(createdAt)), "day_of_week") ORDER BY tweet_count DESC; |
DATE_PART_STR é uma nova função que retorna a parte da data. Mais dia_da_semana é usado para obter o dia da semana. Resultados:
|
1 2 3 4 5 6 7 8 9 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 } ] |
Parece que terça-feira é o dia mais comum para tuitar. Em seguida, vem o domingo e a quarta-feira no mesmo nível. O desempenho tende a diminuir perto do fim de semana.
#22417 deve permitir informar a parte do dia da semana em inglês.
As 5 principais menções em tweets
Consulta:
|
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 é uma matriz aninhada no documento JSON. INÚTIL conceitualmente executa uma junção da matriz aninhada com seu objeto pai. Cada objeto unido resultante torna-se uma entrada para a consulta. Resultados:
|
1 2 3 4 5 6 7 8 9 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 } ] |
Não é preciso dizer que ele é o que mais menciona seu próprio nome nos tweets! E suas duas estações de TV favoritas Fox News e CNN.
Os 5 principais tweets com RTs
A função Lambda acorda a cada 3 horas e busca os tweets mais recentes. Portanto, o banco de dados é um instantâneo dos tweets e das informações associadas, como RTs e Favoritos. Portanto, dependendo de quando o tweet foi arquivado, os RTs e os Favoritos podem não ser uma representação precisa. Mas, com essas informações, vamos dar uma olhada nos tweets com mais RTs. Consulta:
|
1 2 3 4 |
SELECT retweetCount, text FROM twitter ORDER BY retweetCount LIMIT 5; |
Consulta bastante direta. Resultados:
|
1 2 3 4 5 6 7 8 9 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......" } ] |
Original vs. RTs
Quantos tweets foram escritos versus retweetados? Consulta:
|
1 2 3 |
SELECT retweet, count(1) count FROM twitter GROUP BY retweet; |
Resultados:
|
1 2 3 4 5 6 7 8 9 10 |
[ { "count": 253, "retweet": false }, { "count": 15, "retweet": true } ] |
A maioria dos tweets é original, com apenas alguns RTs.
Palavras mais comuns no Tweet
Consulta:
|
1 2 3 4 5 |
SELECT COUNT(1) count, word FROM twitter UNNEST SPLIT(text) word GROUP BY word ORDER BY count DESC; |
Essa consulta usa DIVIDIR função que Results:
|
1 2 3 4 5 6 7 8 9 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" } ] |
Frequência das palavras "mídia", "falso" e "América" nos tweets
Consulta:
|
1 2 3 4 5 6 |
SELECT COUNT(1) count, LOWER(w) word FROM twitter UNNEST SPLIT(text) w WHERE LOWER(w) IN [ "media", "fake", "america"] GROUP by LOWER(w) ORDER BY count DESC; |
INFERIOR é usada para comparar palavras independentemente do caso. Resultado:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[ { "count": 12, "word": "media" }, { "count": 9, "word": "fake" }, { "count": 8, "word": "america" } ] |
Função lambda continuará a armazenar tweets no banco de dados.
Tente você mesmo fazer essas consultas?
- Iniciar um servidor Couchbase
- Usar o arquivo twitter-backups-2017-01-20-06-07-49.tar conforme explicado em Restaurar dados no Couchbase
- Uso Workbench de consulta para disparar as consultas
Referências N1QL
- Tutorial interativo N1QL
- Folha de dados N1QL
- Referência da linguagem N1QL
- Execute sua primeira consulta N1QL
