Rompió muchos círculos en busca de una solución para obtener rápidamente historiales de precios largos para una gran cantidad de activos en Python. También tuve el coraje de querer trabajar con precios en matrices numpy, pero mejor inmediatamente en pandas.
Los enfoques estándar de la frente funcionaron decepcionantemente, lo que llevó a la ejecución de la consulta a la base de datos durante 30 segundos o más. No queriendo aguantar, encontré varias soluciones que me satisficieron por completo.
Las piernas crecen fuera de la naturaleza del objeto de Python. Después de todo, incluso los números enteros son objetos, lo que afecta extremadamente negativamente la velocidad del trabajo. Categóricamente no quería cambiar el idioma.
La primera solución fue una agrupación del historial de precios por PostgreSQL, lo que condujo a una caída insignificante en el rendimiento en el lado de la base de datos, pero aceleró la tarea aproximadamente ~ 3 veces. El método se describe con más detalle en otro artículo.
El resultado fue un entendimiento de que en Python necesita de alguna manera obtener todo el conjunto de datos en una sola pieza, al menos una cadena. Y analizar por matrices numpy o inmediatamente en pandas.
Los resultados finales:
Solución de frente para PostgreSQL
Hacemos la agrupación de datos en una consulta SQL. Un ejemplo:
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}')
Analizar los datos es fácil:
{ 'symbol': np.array(r[0].split(',')),
Productividad en ~ 1.7 millones de líneas:
%timeit get_prices_fast(is_adj=False)
Paquetes Python listos para usar
Python es bueno para su comunidad, que enfrenta problemas similares. Los siguientes son adecuados para nuestro propósito:
- odo : creado para optimizar la velocidad de transferencia de datos de una fuente a otra. Completamente en Python. Interactúa con PostgreSQL a través de SQLAlchemy.
- warp_prism : la extensión C utilizada por el proyecto Quantopian para recuperar datos de PostgreSQL. La base es la funcionalidad de odo.
Ambos paquetes usan la capacidad de PostgreSQL para copiar datos a CSV:
COPY {query} TO :path WITH ( FORMAT CSV, HEADER :header, DELIMITER :delimiter, QUOTE :quotechar, NULL :na_value, ESCAPE :escapechar, ENCODING :encoding )
La salida se analiza en pandas.DataFrame () o numpy.ndarray ().
Dado que warp_prism está escrito en C, tiene una ventaja significativa en términos de análisis de datos. Pero al mismo tiempo tiene un inconveniente significativo: soporte limitado para los tipos de datos. Es decir, analiza int, float, date y str, pero no es numérico. Odo no tiene tales restricciones.
Para su uso, es necesario describir la estructura de la tabla y la consulta utilizando el paquete 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')
Pruebas de velocidad:
%timeit odo(query, pd.DataFrame, bind=engine)
warp_prism.to_arrays () - prepara un diccionario de python con matrices numpy.
¿Qué se puede hacer con ClickHouse?
PostgreSQL es bueno para todos, excepto por el apetito por el tamaño de almacenamiento y la necesidad de configurar sharding para tablas grandes. ClickHouse se fragmenta, almacena todo de forma compacta y funciona a la velocidad del rayo. Por ejemplo, una tabla PostgreSQL con un tamaño de ~ 5 Gb en ClickHouse cabe en ~ 1 Gb. El uso de ClickHouse para almacenar precios se describe en otro artículo.
Para mi disgusto, odo no ayudó, aunque hay una extensión de clickhouse para sqlalchemy. La memoria de la velocidad de clickhouse en la consola me llevó a la idea de acceder a la base de datos mediante la creación de un proceso separado. Sé que es largo y consume muchos recursos, pero los resultados fueron más allá de los elogios.
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'])
Resultado:
%timeit ch_pandas(cmd)
Solicitud de puerto HTTP de ClickHouse
Los resultados empeoraron un poco al acceder directamente al puerto 8123, donde la base de datos responde:
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'])
Pero no sin una mosca en la pomada.
Vuela en la pomada con ClickHouse
La base de datos fue impresionante en muestras grandes, pero en resultados pequeños fue decepcionante. ~ 20 veces peor que el odo. Pero este es el costo de un kit adicional con el lanzamiento del proceso o el acceso a través de HTTP.
Resultados:
Conclusión
Con este artículo, la búsqueda de acelerar la interacción entre Python y las bases de datos ha terminado. Para PostgreSQL con campos estándar y la necesidad de acceso universal a los precios, la mejor manera es usar el paquete warp_prism de Quantopian. Si necesita almacenar grandes volúmenes de historial y una alta frecuencia de solicitudes para una gran cantidad de líneas, ClickHouse es ideal.