Brisé de nombreux cercles à la recherche d'une solution pour obtenir rapidement de longs historiques de prix pour un grand nombre d'actifs en Python. J'ai également eu le courage de vouloir travailler avec des prix dans des tableaux numpy, mais mieux immédiatement dans des pandas.
Les approches standard du front ont fonctionné de manière décevante, ce qui a conduit à l'exécution de la requête dans la base de données pendant 30 secondes ou plus. Ne voulant pas la mettre en place, j'ai trouvé plusieurs solutions qui m'ont complètement satisfait.
Les jambes se développent hors de la nature d'objet de Python. Après tout, même les nombres entiers sont des objets, ce qui affecte extrêmement négativement la vitesse de travail. Je ne voulais absolument pas changer de langue.
La première solution a été un regroupement de l'historique des prix par PostgreSQL, ce qui a entraîné une baisse insignifiante des performances côté base de données, mais a accéléré la tâche d'environ 3 fois. La méthode est décrite plus en détail dans un autre article.
Le résultat a été de comprendre qu'en Python, vous devez en quelque sorte obtenir l'ensemble des données en une seule pièce, au moins une chaîne. Et analyser par numpy-tableaux ou immédiatement dans les pandas.
Les résultats finaux:
Solution de front pour PostgreSQL
Nous effectuons le regroupement des données dans une requête SQL. Un exemple:
SELECT string_agg(symbol::text, ',') AS symbol_list , string_agg(dt::text, ',') AS dt_list , string_agg(open::text, ',') AS open_list , string_agg(high::text, ',') AS high_list , string_agg(low::text, ',') AS low_list , string_agg("close"::text, ',') AS close_list , string_agg(volume::text, ',') AS volume_list , string_agg(adj::text, ',') AS adj_list FROM v_prices_fast WHERE symbol IN ('{symbols}')
L'analyse des données est facile:
{ 'symbol': np.array(r[0].split(',')),
Productivité à ~ 1,7 million de lignes:
%timeit get_prices_fast(is_adj=False)
Packages Python prêts à l'emploi
Python est bon pour sa communauté, qui fait face à des problèmes similaires. Les éléments suivants conviennent à notre objectif:
- odo - créé pour optimiser la vitesse de transfert de données d'une source à une autre. Complètement en Python. Il interagit avec PostgreSQL via SQLAlchemy.
- warp_prism - L'extension C utilisée par le projet Quantopian pour récupérer les données de PostgreSQL. La base est la fonctionnalité d'odo.
Les deux packages utilisent la capacité de PostgreSQL pour copier des données vers CSV:
COPY {query} TO :path WITH ( FORMAT CSV, HEADER :header, DELIMITER :delimiter, QUOTE :quotechar, NULL :na_value, ESCAPE :escapechar, ENCODING :encoding )
La sortie est analysée dans pandas.DataFrame () ou numpy.ndarray ().
Étant donné que warp_prism est écrit en C, il présente un avantage significatif en termes d'analyse des données. Mais en même temps, il présente un inconvénient important: une prise en charge limitée des types de données. Autrement dit, il analyse int, float, date et str, mais pas numérique. Odo n'a pas de telles restrictions.
Pour l'utiliser, il est nécessaire de décrire la structure de la table et la requête à l'aide du package sqlalchemy:
tbl_prices = sa.Table( 'prices', metadata, sa.Column('symbol', sa.String(16)), sa.Column('dt', sa.Date), sa.Column('open', sa.FLOAT), sa.Column('high', sa.FLOAT), sa.Column('low', sa.FLOAT), sa.Column('close', sa.FLOAT), sa.Column('volume', sa.BIGINT), sa.Column('adj', sa.FLOAT), ) query = sa.select(tbl_prices.c).where( tbl_prices.c.symbol.in_(SYMBOLS) ).order_by('symbol', 'dt')
Tests de vitesse:
%timeit odo(query, pd.DataFrame, bind=engine)
warp_prism.to_arrays () - préparation d'un dictionnaire python avec des tableaux numpy.
Que peut-on faire avec ClickHouse?
PostgreSQL est bon pour tout le monde, sauf pour l'appétit pour la taille du stockage et la nécessité de configurer le partage pour les grandes tables. ClickHouse lui-même éclate, stocke tout de manière compacte et fonctionne à une vitesse fulgurante. Par exemple, une table PostgreSQL d'une taille de ~ 5 Go dans ClickHouse tient dans ~ 1 Go. L'utilisation de ClickHouse pour stocker les prix est décrite dans un autre article.
À mon grand regret, odo n'a pas aidé, bien qu'il existe une extension clickhouse pour sqlalchemy. La mémoire de la vitesse de clickhouse dans la console m'a conduit à l'idée d'accéder à la base de données par la création d'un processus distinct. Je sais que cela prend du temps et consomme beaucoup de ressources, mais les résultats ont été au-delà des louanges.
sql = 'SELECT days.symbol, days.date, days.open/10000, days.high/10000, days.low/10000, days.close/10000, days.volume FROM days ' \ 'WHERE days.symbol IN (\'{0}\') ORDER BY days.symbol, days.date;'.format("','".join(SYMBOLS)) cmd = 'clickhouse-client --query="{0}"'.format(sql) def ch_pandas(cmd): p = subprocess.Popen([cmd], stdin=subprocess.PIPE, stdout=subprocess.PIPE, stderr=subprocess.PIPE, shell=True) return pd.io.parsers.read_csv(p.stdout, sep="\t", names=['symbol', 'date', 'open', 'high', 'low', 'close', 'volume'])
Résultat:
%timeit ch_pandas(cmd)
ClickHouse HTTP Port Request
Les résultats se sont un peu détériorés lors de l'accès direct au port 8123, où la base de données répond:
import urllib %timeit pd.io.parsers.read_csv('http://localhost:8123/?{0}'.format(urllib.parse.urlencode({'query': sql})), sep="\t", names=['symbol', 'date', 'open', 'high', 'low', 'close', 'volume'])
Mais pas sans une mouche dans la pommade.
Volez dans la pommade avec ClickHouse
La base de données était impressionnante sur de grands échantillons, mais sur de petits résultats, elle était décevante. ~ 20 fois pire que l'odeur. Mais c'est le coût d'un kit supplémentaire avec le lancement du processus ou l'accès via HTTP.
Résultats:
Conclusion
Avec cet article, la poursuite de l'accélération de l'interaction entre Python et les bases de données est terminée. Pour PostgreSQL avec des champs standard et la nécessité d'un accès universel aux prix, le meilleur moyen est d'utiliser le package warp_prism de Quantopian. Si vous avez besoin de stocker de grands volumes d'historique et une fréquence élevée de demandes pour un grand nombre de lignes, ClickHouse est idéal.