¡Hola, Hivers!
Luego de conversar con @victoriabsb y ella plantearme la necesidad de que no quería sacar la tabla a mano relacionada con la iniciativa de "hivepowerbday", me pidió el favor de hacerlo de manera automatizada y acepté el reto, porque ya tengo experiencia extrayendo data de Hive vía Hive SQL
Ella me dijo, quiero una tabla con lo siguiente:
Participantes de la iniciativa que tenga menos de 25k de HP y mas de 39 en Reputación, pero que hicieron Power UP durante la fecha de la iniciativa.
Vamos a la obra:
Inicié realizando un Query para comprobar que estoy usando de manera correcta el lenguaje y buscando los siguientes resultados
Autor de post, permlink, reputación y los vests
SELECT Comments.author, Comments.permlink, Accounts.reputation_ui, Accounts.vesting_shares
FROM comments (NOLOCK), Accounts
where Comments.depth = 0 and comments.created >= CONVERT(DATE,'2023-03-20') and comments.created < CONVERT(DATE,'2023-03-21')
and Comments.author = Accounts.name
order by comments.created DESC
Luego procedí a ajustar más el Query para acercarme al resultado final que quiere @victoriabsb, entonces debo filtrar de esos post las personas que ese día publicaron y usaron el tag de la iniciativa.
El tag es: hivepowerbday
¿Cómo puedo hago eso? - Extrayendo con Tags
En Hive los tags se almacenan el JSON_METADATA de la publicación, así debo aplicar el filtro basado en esa información, pero usaré la tabla tags para que sea más fácil , como muestro a continuación:
SELECT Comments.author, Comments.permlink, Accounts.reputation_ui, Accounts.vesting_shares
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 >= CONVERT(DATE,'2023-03-20') and comments.created < CONVERT(DATE,'2023-03-21')
and Tags.tag = 'hivepowerbday'
order by comments.created DESC
Lo otro que me pidió victoria es que la hora pueda ajustar a la zona horaria de muchos países, así que voy a usar lo siguiente:
where Comments.depth = 0 and (comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00' and (comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00
Donde indico que la zona horaria sea UTC y la fecha final tenga 6 horas extras para incluir post que no publicaron al tiempo de UTC - 0
Ahora vamos con la fase final que es filtrar los usuarios que tienen reputación mayor a 39 y HP menor a 25k HP
Lo primero es convertir vests a Hive Power, para ello uso la tabla de DynamicGlobalProperties y utilizar el dato de hive_per_vest
SELECT
Comments.author, Comments.permlink, Accounts.reputation_ui,
(Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) as hive_power
FROM
Tags
INNER JOIN Comments ON Tags.comment_id = Comments.ID
INNER JOIN Accounts ON Comments.author = Accounts.name
CROSS JOIN (SELECT hive_per_vest FROM DynamicGlobalProperties) DynamicGlobalProperties
where Comments.depth = 0 and (comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00' and (comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'
and Tags.tag = 'hivepowerbday'
order by
comments.created DESC
Ahora filtrar por reputación y HP, quedando de la siguiente manera:
SELECT
Comments.author,
Comments.permlink,
Accounts.reputation_ui AS REP,
(Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) AS HP
FROM
Tags
INNER JOIN Comments ON Tags.comment_id = Comments.ID
INNER JOIN Accounts ON Comments.author = Accounts.name
CROSS JOIN (SELECT hive_per_vest FROM DynamicGlobalProperties) DynamicGlobalProperties
where
Comments.depth = 0
and (comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00'
and (comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'
and Accounts.reputation_ui >= '39'
and (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) < '25000'
and Tags.tag = 'hivepowerbday'
order by
comments.created DESC
Ahora viene la parte un poco más complicada y por crear el QUERY que filtre quien hizo Power UP mayor a 10 HIVE, así que lo hice de la siguiente manera:
SELECT
Comments.author,
Comments.permlink,
Accounts.reputation_ui AS REP,
(Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) AS HP,
COALESCE(SUM(TxTransfers.amount), 0) AS POWER_UP
FROM
Tags
INNER JOIN Comments ON Tags.comment_id = Comments.ID
INNER JOIN Accounts ON Comments.author = Accounts.name
CROSS JOIN (SELECT hive_per_vest FROM DynamicGlobalProperties) DynamicGlobalProperties
LEFT JOIN TxTransfers ON Comments.author = TxTransfers."from" AND TxTransfers.type = 'transfer_to_vesting'
AND (TxTransfers.timestamp AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00'
AND (TxTransfers.timestamp AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'
where
Comments.depth = 0
and (comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00'
and (comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'
and Accounts.reputation_ui >= '39'
and (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) < '25000'
and Tags.tag = 'hivepowerbday'
and TxTransfers.amount >= '10'
group by
Comments.author,
Comments.permlink,
Comments.created,
Accounts.reputation_ui,
Accounts.vesting_shares,
DynamicGlobalProperties.hive_per_vest
order by Comments.created DESC;
Parte del resultado:
Ya tengo todo lo basé para poder hacer una tabla para mostrar todos los usuarios que participaron en la iniciativa, pero ahora la pregunta es ¿Cómo hago eso?
Ok lo haré usando Python
Procedí a realizar la operación y quedó de la siguiente manera:
import pymssql
import configparser
# Read the configuration file
config = configparser.ConfigParser()
config.read('config.ini')
# Get the Hivesql credentials
hivesql_account = config.get('hivesql', 'account')
hivesql_password = config.get('hivesql', 'password')
connection = pymssql.connect(server='vip.hivesql.io',
database='DBHive',
user=hivesql_account,
password=hivesql_password)
cursor = connection.cursor()
SQLCommand = ("""
SELECT
Comments.author,
Comments.permlink,
Accounts.reputation_ui AS REP,
(Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) AS HP,
COALESCE(SUM(TxTransfers.amount), 0) AS POWER_UP
FROM
Tags
INNER JOIN Comments ON Tags.comment_id = Comments.ID
INNER JOIN Accounts ON Comments.author = Accounts.name
CROSS JOIN (SELECT hive_per_vest FROM DynamicGlobalProperties) DynamicGlobalProperties
LEFT JOIN TxTransfers ON Comments.author = TxTransfers."from" AND TxTransfers.type = 'transfer_to_vesting'
AND (TxTransfers.timestamp AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00'
AND (TxTransfers.timestamp AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'
WHERE
Comments.depth = 0
AND (Comments.created AT TIME ZONE 'UTC') >= '2023-03-20 00:00:00'
AND (Comments.created AT TIME ZONE 'UTC') <= '2023-03-21 06:00:00'
AND Accounts.reputation_ui >= '39'
AND (Accounts.vesting_shares * DynamicGlobalProperties.hive_per_vest) < '25000'
AND Tags.tag = 'hivepowerbday'
AND TxTransfers.amount >= '10'
GROUP BY
Comments.author,
Comments.permlink,
Comments.created,
Accounts.reputation_ui,
Accounts.vesting_shares,
DynamicGlobalProperties.hive_per_vest
ORDER BY
Comments.created DESC;
""")
cursor.execute(SQLCommand)
results = cursor.fetchall()
data = []
data.append(["Usuario", "Link", "Reputación", "HP", "Power UP"])
for row in results:
author, permlink, rep, hp, power_up = row
author_link = f"[{author}](peakd.com/@{author})"
post_link = f"[Link](peakd.com/@{author}/{permlink})"
data.append([author_link, post_link, round(rep), round(hp), round(power_up)])
connection.close()
# Markdown
with open('tabla.txt', 'w', encoding='utf-8') as file:
# Write the header
file.write("| " + " | ".join(data[0]) + " |\n")
file.write("|" + "----|" * len(data[0]) + "\n")
# Write the data
for row in data[1:]:
file.write("| " + " | ".join([str(col) for col in row]) + " |\n")
print("Datos guardados en tabla.txt")
------------
Use la librería de pymssql para conectarme a HIVESQL, también usé un config.ini para guardar mis credenciales de HiveSQL
Luego que hice unas pruebas y adapté todo a lo que necesitaba que es una tabla que me permita pegar en peakd, el resultado es el siguiente.
Pero hagamos que sea más divertida la tabla, ordenando por la cantidad de HP colocado en Stake y que pueda en enumerar las filas.
Espero que le funcione a @victoriabsb toda la información suministrada en la publicación.
El que es programador puede copiar mi código y usarlo.
