Rompeu muitos círculos em busca de uma solução para obter rapidamente históricos de preços longos para um grande número de ativos no Python. Eu também tive a coragem de querer trabalhar com preços em matrizes numpy, mas melhor imediatamente em pandas.
As abordagens padrão da testa funcionaram de maneira decepcionante, o que levou à execução da consulta no banco de dados por 30 segundos ou mais. Não querendo apresentar, encontrei várias soluções que me satisfaziam completamente.
As pernas crescem fora da natureza do objeto do Python. Afinal, até números inteiros são objetos, o que afeta extremamente negativamente a velocidade do trabalho. Eu categoricamente não queria mudar o idioma.
A primeira solução foi um agrupamento do histórico de preços do PostgreSQL, que levou a uma queda insignificante no desempenho do lado do banco de dados, mas acelerou a tarefa em cerca de ~ 3 vezes. O método é descrito em mais detalhes em outro artigo.
O resultado foi um entendimento de que no Python você precisa obter de alguma forma o conjunto de dados inteiro em uma única peça, pelo menos uma string. E analise por matrizes numpy ou imediatamente em pandas.
Os resultados finais:
Solução Forehead para PostgreSQL
Fazemos o agrupamento de dados em uma consulta sql. Um exemplo:
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}')
A análise dos dados é fácil:
{ 'symbol': np.array(r[0].split(',')),
Produtividade em ~ 1,7 milhões de linhas:
%timeit get_prices_fast(is_adj=False)
Pacotes Python prontos
O Python é bom para sua comunidade, que enfrenta problemas semelhantes. A seguir, são adequados para nosso objetivo:
- odo - criado para otimizar a velocidade da transferência de dados de uma fonte para outra. Completamente em Python. Ele interage com o PostgreSQL através do SQLAlchemy.
- warp_prism - A extensão C usada pelo projeto Quantopian para recuperar dados do PostgreSQL. A base é a funcionalidade do odo.
Ambos os pacotes usam a capacidade do PostgreSQL de copiar dados para CSV:
COPY {query} TO :path WITH ( FORMAT CSV, HEADER :header, DELIMITER :delimiter, QUOTE :quotechar, NULL :na_value, ESCAPE :escapechar, ENCODING :encoding )
A saída é analisada em pandas.DataFrame () ou numpy.ndarray ().
Como o warp_prism é escrito em C, ele possui uma vantagem significativa em termos de análise de dados. Mas, ao mesmo tempo, possui uma desvantagem significativa - suporte limitado para tipos de dados. Ou seja, ele analisa int, float, date e str, mas não numérico. Odo não tem essas restrições.
Para uso, é necessário descrever a estrutura da tabela e a consulta usando o pacote 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')
Ensaios de velocidade:
%timeit odo(query, pd.DataFrame, bind=engine)
warp_prism.to_arrays () - preparando um dicionário python com matrizes numpy.
O que pode ser feito com o ClickHouse?
O PostgreSQL é bom para todos, exceto pelo apetite pelo tamanho do armazenamento e pela necessidade de configurar o sharding para tabelas grandes. O ClickHouse em si fragmenta, armazena tudo de forma compacta e trabalha com a velocidade da luz. Por exemplo, uma tabela do PostgreSQL com um tamanho de ~ 5 GB no ClickHouse se encaixa em ~ 1 GB. O uso do ClickHouse para armazenar preços é descrito em outro artigo.
Para meu desgosto, odo não ajudou, embora exista uma extensão de clickhouse para sqlalchemy. A memória da velocidade da clickhouse no console me levou à idéia de acessar o banco de dados através da criação de um processo separado. Sei que é demorado e consome muitos recursos, mas os resultados foram além dos 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)
Solicitação de porta HTTP ClickHouse
Os resultados pioraram um pouco ao acessar diretamente a porta 8123, onde o banco de dados 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'])
Mas não sem uma mosca na pomada.
Voe com a pomada com ClickHouse
O banco de dados foi impressionante em grandes amostras, mas em pequenos resultados foi decepcionante. ~ 20 vezes pior que odo. Mas esse é o custo de um kit adicional com o lançamento do processo ou o acesso via HTTP.
Resultados:
Conclusão
Com este artigo, a busca por acelerar a interação entre Python e bancos de dados terminou. Para o PostgreSQL com campos padrão e a necessidade de acesso universal a preços, a melhor maneira é usar o pacote warp_prism da Quantopian. Se você precisar armazenar grandes volumes de histórico e uma alta frequência de solicitações para um grande número de linhas, o ClickHouse é ideal.