El amigo 
Todo lo haré utilizando HiveSQL con Python porque es un poco más fácil de manipular los datos.
Lo primero que hago es probar la manera de extraer los datos desde HiveSQL, así que procedí a realizar diferentes operaciones y tantas pruebas puede extraer los datos de la siguiente manera:
SELECT COALESCE(Tags.author, Categories.author) AS author,
COALESCE(Tags.json_metadata, Categories.json_metadata) AS json_metadata,
COALESCE(Tags.reputation_ui, Categories.reputation_ui) AS reputation_ui,
COALESCE(Tags.last_post, Categories.last_post) AS last_post
FROM
(
SELECT Comments.author AS author, MAX(Accounts.last_post) AS last_post, Accounts.json_metadata, Accounts.reputation_ui
FROM Tags
INNER JOIN Comments ON Tags.comment_id = Comments.ID
INNER JOIN Accounts ON Comments.author = Accounts.name
WHERE Comments.depth = 0 AND Comments.created >= '2023-03-20 00:00:00' AND Comments.created <= '2023-04-08 23:59:59'
AND Tags.tag IN ('mexico', 'hivemexico')
GROUP BY Comments.author, Accounts.json_metadata, Accounts.reputation_ui
) Tags
FULL OUTER JOIN
(
SELECT Comments.author AS author, MAX(Accounts.last_post) AS last_post, Accounts.json_metadata, Accounts.reputation_ui
FROM Comments
INNER JOIN Accounts ON Comments.author = Accounts.name
WHERE Comments.depth = 0 AND Comments.created >= '2023-03-20 00:00:00' AND Comments.created <= '2023-04-08 23:59:59'
AND Comments.category = 'hive-147104'
GROUP BY Comments.author, Accounts.json_metadata, Accounts.reputation_ui
) Categories ON Tags.author = Categories.author
ORDER BY author ASC;
Esta consulta a HiveSQL está diseñada para obtener una lista de autores que han usado los tags "mexico" o "hivemexico" en sus Post de Hive, así como aquellos que han publicado en la categoría "hive-147104" que es la comunidad de Hive Mexico.
Luego que pude conseguir los datos que queria procedí a llevar todo a Python para que todo se me haga más fácil añadiendo quedando de la siguiente manera:
def bytags(fecha_inicio, fecha_fin):
# Crear la consulta SQL utilizando las variables de fecha
query = f"""
SELECT COALESCE(Tags.author, Categories.author) AS author,
COALESCE(Tags.reputation_ui, Categories.reputation_ui) AS reputation_ui,
COALESCE(Tags.last_post, Categories.last_post) AS last_post,
COALESCE(Tags.json_metadata, Categories.json_metadata) AS json_metadata
FROM
(
SELECT Comments.author AS author, MAX(Accounts.last_post) AS last_post, Accounts.json_metadata, Accounts.reputation_ui
FROM Tags
INNER JOIN Comments ON Tags.comment_id = Comments.ID
INNER JOIN Accounts ON Comments.author = Accounts.name
WHERE Comments.depth = 0 AND Comments.created >= '{fecha_inicio}' AND Comments.created <= '{fecha_fin}'
AND Tags.tag IN ('mexico', 'hivemexico')
GROUP BY Comments.author, Accounts.json_metadata, Accounts.reputation_ui
) Tags
FULL OUTER JOIN
(
SELECT Comments.author AS author, MAX(Accounts.last_post) AS last_post, Accounts.json_metadata, Accounts.reputation_ui
FROM Comments
INNER JOIN Accounts ON Comments.author = Accounts.name
WHERE Comments.depth = 0 AND Comments.created >= '{fecha_inicio}' AND Comments.created <= '{fecha_fin}'
AND Comments.category = 'hive-147104'
GROUP BY Comments.author, Accounts.json_metadata, Accounts.reputation_ui
) Categories ON Tags.author = Categories.author
ORDER BY author ASC;
"""
# Ejecutar la consulta y obtener los resultados
cursor.execute(query)
resultados = cursor.fetchall()
column_names = [desc[0] for desc in cursor.description]
return resultados, column_names
Pero queria comprobar el idioma que usan esos usuarios de Hive que han publicado con esos tags asi que añadí esta función:
def detect_languages(username, cursor):
query = f"""
SELECT TOP 10 body
FROM Comments
WHERE author = '{username}' AND depth = 0
ORDER BY created DESC;
"""
cursor.execute(query)
posts = cursor.fetchall()
# Si el usuario no tiene publicaciones, retorna una cadena vacía
if not posts:
return ''
languages = set()
for post in posts:
try:
language = detect(post[0])
languages.add(language)
except:
pass
return ', '.join(languages)
Con esta función Busca las últimas 10 publicaciones del usuario y detecta el idioma del cuerpo del texto de cada publicación empleando la biblioteca langdetect. Retorna los idiomas detectados en las publicaciones del usuario.
Esto es un valor que me permite añadir una variable más que permitan depurar la base de dato.
Añadiendo más variables, con la siguiente función pude extraer la location que tienen los usuarios en su perfil de Hive:
def parse_and_filter_results(results, cursor):
parsed_results = []
for row in results:
author, last_post, reputation_ui, json_metadata = row
location, about = None, None
if json_metadata:
try:
metadata = json.loads(json_metadata)
about = metadata.get('profile', {}).get('about', '')
location = metadata.get('profile', {}).get('location', '')
languages = detect_languages(author, cursor)
spanish_tag = 1 if has_spanish_tag(author, cursor) else 0
parsed_results.append((author, reputation_ui, last_post, about, location, languages, spanish_tag))
except json.JSONDecodeError:
pass
return parsed_results
Así que fui añadiendo más funciones y pude llegar a una conclusión:
Existe población de 965 usuarios que han usado el tag #hivemexico #mexico o han publicado en la comunidad de Hive México.
¿Qué porcentaje de mexicanos representan?
Luego de depurar cerca de 158 usuarios de Venezuela, se eliminaron a aquellos que no son de nacionalidad mexicana, así como a aquellos que no especificaron su ubicación.
De los 728 usuarios restantes, solo alrededor de 50 tienen una ubicación geográfica en México. Se puede asumir que del 25% de los usuarios sin ubicación, alrededor de 103 son mexicanos, lo que lleva a una estimación aproximada de 150 usuarios en total ubicados en México.
Posibles soluciones para ser más detallados
Hacer iniciativas en Hive para identificar usuarios de México
Hacer base de datos anónima
Depurar la lista de usuarios que no tienen ubicación con otros filtros
A considerar
Esta base de dato solo toma a los usuarios que han publicado en Hive, no considera a los que juegan algún juego de Hive u otra aplicación.
