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 |
SELECCIONE CONTAR(*) recuento_tweets DESDE 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 |
SELECCIONAR * DESDE twitter LÍMITE 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" (nivel de acceso): "0", "colaboradores": [], "createdAt": "1480828438000", "currentUserRetweetId": "-1", "displayTextRangeEnd": "-1", "displayTextRangeStart": "-1", "favoriteCount": "116356", "favorito": falso, "geolocalización": null, "hashtagEntidades": [], "id": "805278955150471168", "inReplyToScreenName": null, "inReplyToStatusId": "-1", "inReplyToUserId": "-1", "lang": "es", "mediaEntities": [], "lugar": null, "posiblementeSensible": falso, "quotedStatus": null, "quotedStatusId": "-1", "rateLimitStatus": null, "retweet": falso, "retweetCount": "28330", "retuiteado": falso, "retweetedByMe": falso, "retweetedStatus": null, "ámbitos": null, "fuente": "<a href="http://twitter.com/download/android" rel="nofollow">Twitter para Android</a>", "símboloEntidades": [], "texto": "Acabo de intentar ver Saturday Night Live - ¡imposible! Totalmente tendencioso, sin gracia y la imitación de Baldwin no puede ser peor. Triste"., "truncado": falso, "urlentities": [], "usuario": { "accessLevel" (nivel de acceso): "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": falso, "createdAt": "1237383998000", "defaultProfile": falso, "defaultProfileImage": falso, "descripción": "Presidente electo de los Estados Unidos", "descripciónURLEntidades": [], "email": null, "favoritos": "46", "followRequestSent": falso, "followersCount": "19294404", "friendsCount": "42", "geoEnabled": verdadero, "id": "25073877", "lang": "es", "listedCount": "52499", "localización": "Nueva York, NY", "miniProfileImageURL": "http://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_mini.jpg", "miniProfileImageURLHttps": "https://pbs.twimg.com/profile_images/1980294624/DJT_Headshot_V2_mini.jpg", "nombre": "Donald J. Trump", "originalProfileImageURL": "http://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": "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": verdadero, "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": "http://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": verdadero, "protegido": falso, "rateLimitStatus": null, "screenName": "realDonaldTrump", "showAllInlineMedia": falso, "status": null, "statusesCount": "34269", "timeZone": "Hora del Este (EE.UU. y Canadá)", "traductor": falso, "url": "https://t.co/mZB2hymxC9", "urlentity": { "displayURL": "https://t.co/mZB2hymxC9", "fin": "23", "expandedURL": "https://t.co/mZB2hymxC9", "inicio": "0", "texto": "https://t.co/mZB2hymxC9", "url": "https://t.co/mZB2hymxC9" }, "utcOffset": "-18000", "verificado": verdadero, "retenidoEnPaíses": null }, "userMentionEntities": [], "retenidoEnPaíses": 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 |
SELECCIONE SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 0, 10) fecha_tweet, CONTAR(1) recuento_tweets DESDE twitter GRUPO POR SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 0, 10) PEDIR POR CONTAR(1) DESC LÍMITE 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 |
SELECCIONE a.recuento_tweets, cuente(1) días DESDE ( SELECCIONE SUBSTR(milis_to_str(to_num(createdAt)), 0, 10) fecha_tweet, CONTAR(1) recuento_tweets DESDE twitter GRUPO POR SUBSTR(milis_to_str(to_num(createdAt)), 0, 10) ) a GRUPO POR a.recuento_tweets PEDIR POR a.recuento_tweets 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 |
[ { "días": 1, "tweet_count": 13 }, { "días": 1, "tweet_count": 12 }, { "días": 1, "tweet_count": 11 }, { "días": 2, "tweet_count": 10 }, { "días": 1, "tweet_count": 9 }, { "días": 7, "tweet_count": 8 }, { "días": 3, "tweet_count": 7 }, { "días": 7, "tweet_count": 6 }, { "días": 5, "tweet_count": 5 }, { "días": 5, "tweet_count": 4 }, { "días": 11, "tweet_count": 3 }, { "días": 3, "tweet_count": 2 }, { "días": 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 |
SELECCIONE SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 11, 2) tweet_hora, CONTAR(1) recuento_tweets DESDE twitter GRUPO POR SUBSTR(MILLIS_TO_STR(TO_NUM(createdAt)), 11, 2) PEDIR POR recuento_tweets DESC LÍMITE 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_hora": "13" }, { "tweet_count": 27, "tweet_hora": "12" }, { "tweet_count": 26, "tweet_hora": "11" }, { "tweet_count": 20, "tweet_hora": "14" }, { "tweet_count": 15, "tweet_hora": "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 |
SELECCIONE DATE_PART_STR(MILLIS_TO_STR(TO_NUM(createdAt)), "día_de_la_semana") día_de_la_semana, CONTAR(1) recuento_tweets DESDE twitter GRUPO POR DATE_PART_STR(MILLIS_TO_STR(TO_NUM(createdAt)), "día_de_la_semana") PEDIR POR recuento_tweets 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 |
[ { "día_de_la_semana": 2, "tweet_count": 49 }, { "día_de_la_semana": 3, "tweet_count": 40 }, { "día_de_la_semana": 0, "tweet_count": 40 }, { "día_de_la_semana": 5, "tweet_count": 38 }, { "día_de_la_semana": 4, "tweet_count": 36 }, { "día_de_la_semana": 6, "tweet_count": 33 }, { "día_de_la_semana": 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 |
SELECCIONE CONTAR(1) recuento_usuarios, ue.screenName DESDE twitter UNNEST userMentionEntities ue GRUPO por ue.screenName PEDIR por recuento_usuarios DESC LÍMITE 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 |
SELECCIONE retweetCount, texto DESDE twitter PEDIR POR retweetCount LÍMITE 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", "texto": "al pueblo estadounidense. No tengo ninguna duda de que, juntos, ¡HACER AMÉRICA GRANDE DE NUEVO!". }, { "retweetCount": "10140", "texto": "¡Gracias a todos los hombres y mujeres que protegen y sirven a nuestras comunidades 24/7/365! n#LawEnforcementAppreciationDay... https://t.co/aqUbDipSgv" }, { "retweetCount": "10370", "texto": "Hoy hemos tenido una gran rueda de prensa en la Torre Trump. Un par de organizaciones de FAKE NEWS estaban allí, pero la gente realmente entiende lo que está pasando" }, { "retweetCount": "10414", "texto": "estas empresas son capaces de moverse entre los 50 estados, sin ningún impuesto o tarifa que se cobra. Por favor, estar prevenido antes de hacer una muy ... " }, { "retweetCount": "10416", "texto": "Alguien hackeó el DNC pero ¿por qué no tenían "piratería defensa"como lo ha hecho el RNC y por qué no han respondido a la terrible......" } ] |
Original vs RTs
¿Cuántos tweets se escribieron frente a cuántos se retuitearon? Consulta:
|
1 2 3 |
SELECCIONE retweet, cuente(1) cuente DESDE twitter GRUPO POR retweet; |
Resultados:
|
1 2 3 4 5 6 7 8 9 10 |
[ { "contar": 253, "retweet": falso }, { "contar": 15, "retweet": verdadero } ] |
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 |
SELECCIONE CONTAR(1) cuente, palabra DESDE twitter UNNEST SPLIT(texto) palabra GRUPO POR palabra PEDIR POR cuente 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 |
[ { "contar": 189, "palabra": "el" }, { "contar": 151, "palabra": "a" }, { "contar": 115, "palabra": "y" }, . . . { "contar": 1, "palabra": "presentado...de Trump" }, { "contar": 1, "palabra": "trabajos". }, { "contar": 1, "palabra": "Hacer" } ] |
Frecuencia de las palabras "media", "fake" y "America" en los tweets
Consulta:
|
1 2 3 4 5 6 |
SELECCIONE CONTAR(1) cuente, BAJO(w) palabra DESDE twitter UNNEST SPLIT(texto) w DONDE BAJO(w) EN [ "media", "falso", "america"] GRUPO por BAJO(w) PEDIR POR cuente 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 |
[ { "contar": 12, "palabra": "media" }, { "contar": 9, "palabra": "falso" }, { "contar": 8, "palabra": "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
