forked from balancer/bal-mining-scripts
-
Notifications
You must be signed in to change notification settings - Fork 2
/
bal-for-gas-realtime.py
136 lines (123 loc) · 4.84 KB
/
bal-for-gas-realtime.py
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
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
from google.cloud import bigquery
import os
import pandas as pd
from datetime import datetime, timezone
from src.bal4gas_V1 import compute_bal_for_gas as compute_bal_for_gas_V1
from src.bal4gas_V2 import compute_bal_for_gas as compute_bal_for_gas_V2
import sys
project_id = os.environ['GCP_PROJECT']
start_timestamp = 1615161581 # BLOCK 11994473, which was not included in week 40
end_timestamp = int(datetime.utcnow().replace(tzinfo=timezone.utc).timestamp())
offset = 0
if len(sys.argv) > 1:
offset = int(sys.argv[1])
try:
sql = f'''
DECLARE CUR_WEEK_START TIMESTAMP;
DECLARE WEEK_START TIMESTAMP;
SET CUR_WEEK_START = TIMESTAMP_TRUNC(CURRENT_TIMESTAMP(), WEEK(MONDAY));
SET WEEK_START = TIMESTAMP_SUB(CUR_WEEK_START, INTERVAL {offset*7} DAY);
WITH t0 as (
SELECT UNIX_SECONDS(MIN(timestamp)) AS timestamp
FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE timestamp >= WEEK_START
AND timestamp <= TIMESTAMP_ADD(WEEK_START, interval 1 HOUR)
),
t1 as (
SELECT UNIX_SECONDS(MAX(timestamp)) as timestamp
FROM `bigquery-public-data.crypto_ethereum.blocks`
WHERE timestamp >= WEEK_START
AND timestamp < TIMESTAMP_ADD(WEEK_START, interval 7 DAY)
)
SELECT
t0.timestamp as t0,
t1.timestamp as t1
FROM t0 INNER JOIN t1 ON 1=1
'''
results = bigquery.Client().query(sql).result()
for row in results:
start_timestamp = row.t0
end_timestamp = row.t1
except:
raise
pass
week_1_start = '01/06/2020 00:00:00 UTC'
week_1_start = datetime.strptime(week_1_start, '%d/%m/%Y %H:%M:%S %Z')
WEEK = int(1 + (datetime.utcfromtimestamp(start_timestamp) - week_1_start).days/7)
tag = 'master'
if offset>0:
tag = F'w{WEEK}'
try:
whitelist = pd.read_json(
f'https://raw.githubusercontent.com/balancer-labs/assets/{tag}/generated/bal-for-gas.json',
orient='index').loc['homestead'].values
except:
print(f'Tag {tag} not found, resorting to master')
tag = 'master'
whitelist = pd.read_json(
f'https://raw.githubusercontent.com/balancer-labs/assets/{tag}/generated/bal-for-gas.json',
orient='index').loc['homestead'].values
gas_whitelist = pd.Series(whitelist).str.lower().tolist()
gas_whitelist.append('0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee')
bal4gas_df = compute_bal_for_gas_V1(start_timestamp, end_timestamp, gas_whitelist, plot=False, verbose=True)
bal4gas_df.to_gbq('bal_mining_estimates.gas_estimates_staging',
project_id=project_id,
if_exists='replace')
# merge staging into prod
sql = '''
MERGE bal_mining_estimates.gas_estimates prod
USING bal_mining_estimates.gas_estimates_staging stage
ON prod.transaction_hash = stage.transaction_hash
WHEN MATCHED THEN
UPDATE SET
datetime = stage.datetime,
address = stage.address,
n_swaps = stage.n_swaps,
block_median_gas_price = stage.block_median_gas_price,
eth_reimbursement = stage.eth_reimbursement,
timestamp = stage.timestamp,
price = stage.price,
bal_reimbursement = stage.bal_reimbursement
WHEN NOT MATCHED BY TARGET THEN
INSERT (datetime, transaction_hash, address, n_swaps,
block_median_gas_price, eth_reimbursement, timestamp,
price, bal_reimbursement)
VALUES (datetime, transaction_hash, address, n_swaps,
block_median_gas_price, eth_reimbursement, timestamp,
price, bal_reimbursement)
'''
client = bigquery.Client()
query = client.query(sql)
query.result()
gas_whitelist.remove('0xeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeee')
gas_whitelist.append('0x0000000000000000000000000000000000000000')
bal4gas_df = compute_bal_for_gas_V2(start_timestamp, end_timestamp, gas_whitelist, plot=False, verbose=True)
bal4gas_df.to_gbq('bal_mining_estimates.gas_estimates_staging',
project_id=project_id,
if_exists='replace')
# merge staging into prod
sql = '''
MERGE bal_mining_estimates.gas_estimates prod
USING bal_mining_estimates.gas_estimates_staging stage
ON prod.transaction_hash = stage.transaction_hash
WHEN MATCHED THEN
UPDATE SET
datetime = stage.datetime,
address = stage.address,
n_swaps = stage.n_swaps,
block_median_gas_price = stage.block_median_gas_price,
eth_reimbursement = stage.eth_reimbursement,
timestamp = stage.timestamp,
price = stage.price,
bal_reimbursement = stage.bal_reimbursement
WHEN NOT MATCHED BY TARGET THEN
INSERT (datetime, transaction_hash, address, n_swaps,
block_median_gas_price, eth_reimbursement, timestamp,
price, bal_reimbursement)
VALUES (datetime, transaction_hash, address, n_swaps,
block_median_gas_price, eth_reimbursement, timestamp,
price, bal_reimbursement)
'''
client = bigquery.Client()
query = client.query(sql)
query.result()