Buckets:
| """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.