Eventos programados de AWS Serverless Lambda para almacenar tuits en Couchbase explicó cómo almacenar tweets en Couchbase utilizando AWS Serverless Lambda. Ahora, esta función Lambda se ha estado ejecutando durante unos días y ha recopilado 269 tweets de @realDonaldTrump. Este blog , inspirado en SQL en Twitter: El análisis más fácil con N1QLmostrará cómo se pueden analizar estos tweets utilizando N1QL.
N1QL es un lenguaje de consulta tipo SQL de Couchbase que opera sobre documentos JSON. Diferencias entre N1QL y SQL proporcionan diferencias entre N1QL y SQL. Vamos a utilizar N1QL para revelar alguna información interesante de @realDonaldTrumptweets. Muchas gracias a Sitaram del equipo N1QL para ayudar a piratear las consultas.
Cuántos Tweets
La primera consulta consiste en averiguar cuántos tweets hay disponibles en la base de datos. La consulta es bastante sencilla: Consulta:
|
1 2 |
SELECT COUNT(*) tweet_count FROM twitter; |
Como puede observar, la sintaxis es muy similar a la de SQL. SELECCIONE, CONTAR y DESDE son las que ya conoce de la sintaxis SQL. recuento_tweets es un alias definido para el resultado devuelto. twitter es el bucket donde se almacenan todos los documentos JSON. Resultados:
|
1 2 3 4 5 |
[ { "tweet_count": 269 } ] |
El resultado también es un documento JSON.
Tweet Ejemplo de documento JSON
Para escribir consultas sobre un documento JSON, necesitas conocer la estructura del documento. La siguiente consulta te lo proporcionará. Consulta:
|
1 2 3 |
SELECT * FROM twitter LIMIT 1; |
La nueva cláusula introducida es LÍMITE. Esto permite restringir el número de objetos que se devuelven en un conjunto de resultados de SELECCIONE. 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 } } ] |
Los 5 mejores días para tuitear
Una vez realizadas las consultas básicas, veamos ahora algunos datos interesantes. ¿Cuáles son los 5 días en los que @realDonaldTrump tuiteó y el recuento de tuits? 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; |
Habitual GRUPO POR y ORDENAR POR Las cláusulas SQL realizan la misma función. Funciones N1QL aplicar una función a los valores. La dirección createdAt se devuelve un número como cadena. TO_NUM convierte la cadena en un número. MILLIS_TO_STR convierte la cadena en una fecha. Finalmente, SUBSTR extrae la parte relevante de la fecha. 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" } ] |
El 17 de enero de 2017 es el día más tuiteado. Ahora bien, este resultado se limita, por supuesto, a los datos de los documentos JSON almacenados en la base de datos. Alguien tiene una base de datos más completa de tuits de @realDonaldTrump?
Frecuencia de tweets
OK, nuestra base de datos muestra que ese número máximo de tweets en un día fueron 13. Cómo puedo saber cuántos días @realDonaldTrump tuiteó un determinado número de veces? 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; |
Esto se consigue fácilmente utilizando Consultas anidadas 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 } ] |
En 47 días, sólo hay un día con un único tuit. Una suma total de recuento_tweets demuestra que no hay un solo día sin un tuit :)
La hora más habitual del día para tuitear
@realDonaldTrump es conocido por tuitear a las 3 de la mañana. Veamos cuáles son las horas más habituales en las que tuitea. 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" } ] |
Ahora parece que los tweets polémicos vienen a las 3am. Pero 39 tweets vienen a la 1pm ET, probablemente justo después del almuerzo y mientras se toma un postre ;)
Día común de la semana para tuitear
Averigüemos cuáles son los días de la semana más habituales para tuitear. 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 es una nueva función que devuelve parte de la fecha. Más información en día_de_la_semana se utiliza para obtener el día de la 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 el martes es el día más habitual para tuitear. Luego vienen el domingo y el miércoles al mismo nivel. El rendimiento tiende a desvanecerse cuando se acerca el fin de semana.
#22417 debería permitir informar de la parte del día de la semana en inglés.
Las 5 menciones más importantes en 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 es una matriz anidada en el documento JSON. UNNEST realiza conceptualmente una unión del array anidado con su objeto padre. Cada objeto unido resultante se convierte en una entrada para la 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 } ] |
Ni que decir tiene que es el que más menciona su propio nombre en los tuits. Y sus dos canales de televisión favoritos Noticias Fox y CNN.
Los 5 Tweets con más RTs
Lambda Function se despierta cada 3 horas y obtiene los últimos tweets. Así que la base de datos es una instantánea de los tweets y la información asociada, como RTs y Favoritos. Así que dependiendo de cuándo se archivó el tweet, los RTs y Favoritos pueden no ser una representación exacta. Pero dada esta información, echemos un vistazo a los tweets con más RTs. Consulta:
|
1 2 3 4 |
SELECT retweetCount, text FROM twitter ORDER BY retweetCount LIMIT 5; |
Una consulta bastante sencilla. 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
¿Cuántos tweets se escribieron frente a cuántos se retuitearon? 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 } ] |
La mayoría de los tweets son originales con sólo unos pocos RTs.
Palabras más comunes en 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; |
Esta consulta utiliza SPLIT función 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" } ] |
Frecuencia de las palabras "media", "fake" y "America" en los 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; |
BAJO se utiliza para comparar palabras independientemente del 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" } ] |
Función lambda seguirá almacenando tweets en la base de datos.
¿Intenta realizar estas consultas usted mismo?
- Iniciar un servidor Couchbase
- Utilizar el archivo twitter-backups-2017-01-20-06-07-49.tar como se explica en Restaurar datos en Couchbase
- Utilice Workbench de consulta para lanzar las consultas
Referencias N1QL
- Tutorial interactivo N1QL
- Hoja de trucos N1QL
- Referencia del lenguaje N1QL
- Ejecute su primera consulta N1QL
