zirobtc/pump-fun-data-v2 / assets /filter_training.py
zirobtc's picture
download
raw
16.5 kB
"""Publish slot-stratified training ledgers after complete wallet replay."""
import argparse
import json
from pathlib import Path
import subprocess
import time
from huggingface_hub import HfApi
from run_epoch import LEDGERS, file_record, query, read_state, verify_upload, write_state
def execute(sql):
started = time.monotonic()
result = query(sql)
print(f'SQL seconds={time.monotonic() - started:.3f}', flush=True)
return result
def union_table(epochs, table, columns='*', condition='1'):
return ' UNION ALL '.join(
f'SELECT {columns} FROM epoch{epoch}.{table} WHERE {condition}'
for epoch in epochs
)
def select_tokens(epochs, bins, target, seed):
execute('CREATE DATABASE IF NOT EXISTS training_retention')
if int(query("SELECT count() FROM system.tables WHERE database='training_retention'")):
raise RuntimeError('Selection already exists. Use --selection-ready to reuse it')
execute(f"""CREATE TABLE training_retention.mints
ENGINE=MergeTree ORDER BY mint_address AS
SELECT mint_address,
argMin(tuple(slot,timestamp,initial_base_liquidity,
initial_quote_liquidity,token_decimals,transaction_index,instruction_index),
tuple(slot,transaction_index,instruction_index)) AS launch,
launch.1 AS mint_slot, launch.2 AS mint_time,
launch.3 AS base_reserve, launch.4 AS quote_reserve,
launch.5 AS base_decimals
FROM ({union_table(epochs, 'mints', condition='success')})
GROUP BY mint_address""")
execute(f"""CREATE TABLE training_retention.decimals
ENGINE=MergeTree ORDER BY mint_address AS
SELECT d.mint_address AS mint_address,
min(d.decimals) AS decimals, max(d.decimals) AS maximum
FROM ({union_table(epochs, 'mint_decimals')}) d GROUP BY d.mint_address""")
if int(query('SELECT count() FROM training_retention.decimals WHERE decimals!=maximum')):
raise RuntimeError('Conflicting immutable mint decimals')
execute(f"""CREATE TABLE training_retention.prices
ENGINE=MergeTree ORDER BY (mint_address,quote_address) AS
SELECT t.base_address AS mint_address, t.quote_address AS quote_address,
maxIf(t.price,isFinite(t.price) AND t.price>0) AS peak,
countIf(isFinite(t.price) AND t.price>0) AS valid_prices,
countIf(NOT isFinite(t.price) OR t.price<=0) AS invalid_prices,
countIf(t.protocol=1 AND isFinite(t.price) AND t.price>0) AS curve_prices
FROM ({union_table(epochs, 'trades', condition='success')}) t
INNER JOIN training_retention.mints m ON t.base_address=m.mint_address
WHERE tuple(t.slot,t.transaction_index,t.instruction_index)>=
tuple(m.launch.1,m.launch.6,m.launch.7)
GROUP BY t.base_address,t.quote_address""")
execute("""CREATE TABLE training_retention.assessments
ENGINE=MergeTree ORDER BY mint_address AS
SELECT mint_address,mint_slot,
multiIf(base_reserve=0 OR quote_reserve=0,NULL,
valid_prices=0 AND invalid_prices=0,1.,
quote_count=1 AND curve_prices>0 AND invalid_prices=0
AND quote_decimals IS NOT NULL AND mint_price>0
AND isFinite(peak/mint_price),greatest(1.,peak/mint_price),
NULL) AS peak_multiple
FROM (
SELECT m.mint_address AS mint_address,m.mint_slot AS mint_slot,
m.base_reserve AS base_reserve,m.quote_reserve AS quote_reserve,
p.quote_count AS quote_count,p.valid_prices AS valid_prices,
p.invalid_prices AS invalid_prices,p.curve_prices AS curve_prices,
p.peak AS peak,
if(p.quote_address='So11111111111111111111111111111111111111112',
toNullable(toUInt8(9)),
if(d.mint_address!='',toNullable(d.decimals),NULL)) AS quote_decimals,
(m.quote_reserve/pow(10,quote_decimals)) /
nullIf(m.base_reserve/pow(10,m.base_decimals),0) AS mint_price
FROM training_retention.mints m
LEFT JOIN (
SELECT mint_address,count() AS quote_count,
any(quote_address) AS quote_address,max(peak) AS peak,
sum(valid_prices) AS valid_prices,sum(invalid_prices) AS invalid_prices,
sum(curve_prices) AS curve_prices
FROM training_retention.prices GROUP BY mint_address
) p ON p.mint_address=m.mint_address
LEFT JOIN training_retention.decimals d ON d.mint_address=p.quote_address
)""")
first_slot = min(epochs) * 432000
slot_count = (max(epochs) + 1) * 432000 - first_slot
quota = target // bins
execute(f"""CREATE TABLE training_retention.low_pool
ENGINE=MergeTree ORDER BY (slot_bin,mint_address) AS
SELECT mint_address,mint_slot,peak_multiple,
intDiv((mint_slot-{first_slot})*{bins},{slot_count}) AS slot_bin,
row_number() OVER (PARTITION BY slot_bin
ORDER BY sipHash64(mint_address,toUInt64({seed})),mint_address) AS sample_rank
FROM training_retention.assessments WHERE peak_multiple<=3""")
execute(f"""CREATE TABLE training_retention.discarded_mints
ENGINE=MergeTree ORDER BY mint_address AS
SELECT mint_address FROM training_retention.low_pool WHERE sample_rank>{quota}""")
pools = []
for epoch in epochs:
pools.extend([
f'SELECT pool_address FROM epoch{epoch}.mints WHERE success AND {discarded("mint_address")}',
f'SELECT pool_address FROM epoch{epoch}.trades WHERE success AND ({discarded("base_address")} OR {discarded("quote_address")})',
f'SELECT pool_address FROM epoch{epoch}.pool_creations WHERE success AND ({discarded("base_address")} OR {discarded("quote_address")} OR {discarded("lp_token_address")})',
f'SELECT pool_address FROM epoch{epoch}.migrations WHERE success AND {discarded("mint_address")}',
f'SELECT virtual_pool_address AS pool_address FROM epoch{epoch}.migrations WHERE success AND {discarded("mint_address")}',
])
execute(f"""CREATE TABLE training_retention.discarded_pools
ENGINE=MergeTree ORDER BY pool_address AS
SELECT DISTINCT pool_address FROM ({' UNION ALL '.join(pools)})
WHERE pool_address!=''""")
write_state(Path('/workspace/retention-selection.msgpack'),
dict(epochs=epochs,bins=bins,target=target,seed=seed,quota=quota))
def discarded(column):
return f'{column} IN (SELECT mint_address FROM training_retention.discarded_mints)'
def keep_rows(table):
pool = 'pool_address IN (SELECT pool_address FROM training_retention.discarded_pools)'
token_columns = {
'trades': ['base_address', 'quote_address'],
'pool_creations': ['base_address', 'quote_address', 'lp_token_address'],
'fee_collections': ['token_0_mint_address', "ifNull(token_1_mint_address,'')"],
'sol_usd_daily': [],
'liquidity': [],
}
conditions = [discarded(column) for column in token_columns.get(table, ['mint_address'])]
if table == 'liquidity':
conditions.append(pool)
return 'NOT (' + ' OR '.join(conditions) + ')' if conditions else '1'
def read_chain(api, bucket, epoch, root):
local = root / f'{epoch}-head.msgpack'
api.download_bucket_files(bucket, [(f'epochs/{epoch}/checkpoint.msgpack', local)], raise_on_missing_files=True)
head = read_state(local)
if not head['eof']:
raise RuntimeError('Raw extraction is incomplete')
obsolete = []
raw_bytes = 0
slots = []
current = head
while True:
raw_bytes += sum(entry['bytes'] for entry in current['files'])
slots.extend(current[key] for key in ['first_slot','last_slot'] if current[key] is not None)
obsolete.extend(entry['path'] for entry in current['files'] + current['state_files'])
obsolete.append(current['manifest_path'])
if current['previous'] is None:
break
api.download_bucket_files(bucket, [(current['previous'], local)], raise_on_missing_files=True)
current = read_state(local)
head = dict(head,first_slot=min(slots),last_slot=max(slots))
return head, sorted(set(obsolete)), raw_bytes
def aggregation_manifest(api, bucket, epoch, root):
local = root / f'{epoch}-aggregation.msgpack'
prefix = f'epochs/{epoch}/aggregation'
api.download_bucket_files(bucket, [(prefix+'/manifest.msgpack', local)], raise_on_missing_files=True)
manifest = read_state(local)
if manifest['source_index'] != 1:
raise RuntimeError('Wallet aggregation has not consumed the complete epoch')
verify_upload(api, bucket, prefix, manifest['files'])
return manifest
def main():
parser = argparse.ArgumentParser()
parser.add_argument('--bucket', required=True)
parser.add_argument('--epochs', nargs=2, type=int, required=True)
parser.add_argument('--target', type=int, default=10000)
parser.add_argument('--bins', type=int, default=96)
parser.add_argument('--seed', type=int, required=True)
parser.add_argument('--selection-ready', action='store_true')
parser.add_argument('--selection-only', action='store_true')
parser.add_argument('--deadline', type=float, required=True)
args = parser.parse_args()
if args.epochs[1] != args.epochs[0]+1 or not 0 < args.bins <= args.target:
raise ValueError('Require two consecutive epochs and a positive per-bin quota')
root = Path('/workspace/training-export')
root.mkdir(exist_ok=True)
api = HfApi()
if args.selection_ready:
saved = read_state(Path('/workspace/retention-selection.msgpack'))
expected = dict(epochs=args.epochs,bins=args.bins,target=args.target,seed=args.seed,quota=args.target//args.bins)
if saved != expected:
raise RuntimeError('Selection parameters differ from the saved selection')
else:
select_tokens(args.epochs,args.bins,args.target,args.seed)
summary = json.loads(query(f"""SELECT count() AS minted_tokens,
countIf(peak_multiple>3) AS above_3x,
countIf(peak_multiple<=3) AS low_pool,
countIf(peak_multiple IS NULL) AS unknown_return,
(SELECT count() FROM training_retention.discarded_mints) AS discarded_tokens,
(SELECT count() FROM training_retention.low_pool WHERE sample_rank<={args.target//args.bins}) AS retained_low
FROM training_retention.assessments FORMAT JSONEachRow"""))
print(f'Token selection {summary}', flush=True)
print(query(f"""SELECT slot_bin,count() AS low_pool,
countIf(sample_rank<={args.target//args.bins}) AS retained
FROM training_retention.low_pool GROUP BY slot_bin ORDER BY slot_bin FORMAT PrettyCompact"""), flush=True)
if args.selection_only:
return
plan_path = root/'publication.msgpack'
if plan_path.exists():
plan = read_state(plan_path)
else:
plan = {}
for epoch in args.epochs:
head, obsolete, raw_bytes = read_chain(api,args.bucket,epoch,root)
if head.get('view') == 'filtered_training':
raise RuntimeError('Epoch is already filtered without a local publication plan')
aggregation = aggregation_manifest(api,args.bucket,epoch,root)
plan[str(epoch)] = dict(head=head,obsolete=obsolete,
aggregation=aggregation,original_parquet_bytes=raw_bytes)
write_state(plan_path,plan)
for epoch in args.epochs:
item = plan[str(epoch)]
prefix = f'epochs/{epoch}/training'
epoch_root = root/str(epoch)
epoch_root.mkdir(exist_ok=True)
records = []
counts = {}
for table in LEDGERS:
if time.time() >= args.deadline:
raise RuntimeError('Training export deadline reached')
started = time.monotonic()
predicate = keep_rows(table)
counts[table] = int(query(f'SELECT count() FROM epoch{epoch}.{table} WHERE {predicate}'))
if int(query(f'SELECT count() FROM epoch{epoch}.{table}')) != item['head']['ledger_rows'][table]:
raise RuntimeError(f'Full source changed before filtering {epoch}.{table}')
export_table = f'training_retention.export_{epoch}_{table}'
order = query(f"SELECT sorting_key FROM system.tables WHERE database='default' AND name='{table}'")
query(f'DROP TABLE IF EXISTS {export_table}')
query(f'CREATE TABLE {export_table} AS default.{table} ENGINE=MergeTree ORDER BY ({order})')
query(f'INSERT INTO {export_table} SELECT * FROM epoch{epoch}.{table} WHERE {predicate}')
if int(query(f'SELECT count() FROM {export_table}')) != counts[table]:
raise RuntimeError(f'Filtered table row count differs for {epoch}.{table}')
path = epoch_root/f'{table}.parquet'
with path.open('wb') as output:
query(f'SELECT * FROM {export_table} FORMAT Parquet',output=output)
count = int(subprocess.check_output(['clickhouse','local','--query',
f"SELECT count() FROM file('{path}',Parquet)"],text=True))
if count != counts[table]:
raise RuntimeError(f'Exported Parquet row count differs for {epoch}.{table}')
record = dict(file_record(path,f'{prefix}/{table}.parquet'),table=table,rows=count)
api.batch_bucket_files(args.bucket,add=[(path,record['path'])])
verify_upload(api,args.bucket,prefix,[record])
records.append(record)
path.unlink()
query(f'DROP TABLE {export_table}')
print(f'Filtered epoch={epoch} table={table} rows={count} bytes={record["bytes"]} seconds={time.monotonic()-started:.3f}',flush=True)
manifest = dict(item['head'],generation=0,previous=None,start_offset=0,
files=records,state_files=[],ledger_rows=counts,
manifest_path=f'{prefix}/manifest.msgpack',view='filtered_training',
retention=dict(summary,bins=args.bins,seed=args.seed,target=args.target))
local = epoch_root/'manifest.msgpack'
write_state(local,manifest)
verify_upload(api,args.bucket,prefix,records)
api.batch_bucket_files(args.bucket,add=[(local,manifest['manifest_path'])])
item['filtered'] = manifest
write_state(plan_path,plan)
# Both complete training views exist before either original ledger is removed.
for epoch in args.epochs:
item = plan[str(epoch)]
prefix = f'epochs/{epoch}/training'
verify_upload(api,args.bucket,prefix,item['filtered']['files'])
current_aggregation = aggregation_manifest(api,args.bucket,epoch,root)
if current_aggregation != item['aggregation']:
raise RuntimeError('Complete wallet outputs changed during filtering')
local = root/str(epoch)/'manifest.msgpack'
write_state(local,item['filtered'])
api.batch_bucket_files(args.bucket,add=[(local,f'epochs/{epoch}/checkpoint.msgpack')])
pointer = root/f'{epoch}-published.msgpack'
api.download_bucket_files(args.bucket,[(f'epochs/{epoch}/checkpoint.msgpack',pointer)],raise_on_missing_files=True)
if read_state(pointer) != item['filtered']:
raise RuntimeError('Published training pointer differs from the complete filtered manifest')
existing = {e.path for e in api.list_bucket_tree(args.bucket,prefix=f'epochs/{epoch}',recursive=True) if e.type=='file'}
obsolete = sorted(existing.intersection(item['obsolete']))
if obsolete:
api.batch_bucket_files(args.bucket,delete=obsolete)
remaining = {e.path for e in api.list_bucket_tree(args.bucket,prefix=f'epochs/{epoch}',recursive=True) if e.type=='file'}
if remaining.intersection(item['obsolete']):
raise RuntimeError('Obsolete raw outputs remain after filtering')
print(f'Published filtered epoch={epoch} original_raw_bytes={item["original_parquet_bytes"]} '
f'filtered_raw_bytes={sum(e["bytes"] for e in item["filtered"]["files"])} '
f'wallet_bytes={sum(e["bytes"] for e in item["aggregation"]["files"])}',flush=True)
print(f'Complete training filtering {summary}',flush=True)
if __name__ == '__main__':
main()

Xet Storage Details

Size:
16.5 kB
·
Xet hash:
c6fbce8fd68ce77be1b79718efccc752b0baca8f4c80b15365480838d46ed81f

Xet efficiently stores files, intelligently splitting them into unique chunks and accelerating uploads and downloads. More info.