Não me importo com as diferenças gerais entre SQL e NoSQL (ou suas diferenças tradicionais).
Atualmente, estou analisando a alteração do armazenamento de nossa série temporal interna. Todos eles contêm dados financeiros de várias fontes diferentes. Atualmente, estamos armazenando nossos dados em um banco de dados proprietário. É muito NoSQL, que tem sua própria linguagem de consulta.
Estou interessado na entrada da comunidade: Como você armazenaria os dados em um banco de dados SQL? Quais são os méritos de usar SQL em vez de NoSQL, especificamente para séries temporais? Estou louco por considerar armazenar isso no SQL?
Nosso conjunto de dados consiste em milhões de séries temporais, com cerca de 10% delas contendo milhões de registros cada. As séries temporais são organizadas hierarquicamente: /Mercado/Instrumento/Valor/Frequência, onde:
- Mercado é uma bolsa de valores, etc, basicamente uma coleção de instrumentos, geralmente instrumentos semelhantes.
- Instrumento é um instrumento. Pode ser um indicador (Brent Crude), um patrimônio (GOOG), etc.
- O valor é um dos vários tipos de dados para um instrumento. Este pode ser um próximo, alto, baixo, etc.
- Frequência é a frequência de valores de uma determinada série temporal. Semanal, diário, mensal, tick, arbitrário, etc.
Como os dados seriam armazenados em um banco de dados SQL? Uma mesa grande (talvez dividida por algo), uma mesa por mercado ou instrumento, uma mesa por série temporal.
Agradeço antecipadamente.
Em geral, para um conjunto de dados tão estruturado, suspeito que você poderia escrever um formato de dados personalizado que fosse mais rápido para a maioria das operações diárias (ou seja, pequenos dados puxados de um momento arbitrário). O benefício de mudar para uma ferramenta de banco de dados padrão provavelmente está em alguns dos extras, por exemplo, consultas ad hoc, acesso múltiplo, replicação, disponibilidade etc. Também é mais fácil contratar ajuda para manter um armazenamento de dados baseado em padrões.
Se me pedissem para configurar um banco de dados para armazenar esses dados, eu faria o seguinte:
Esquema proposto
(1) Os dados principais são colocados em várias (milhares) de tabelas individuais, cada uma contendo duas colunas:
Essas tabelas ficarão muito grandes e você pode querer particioná-las manualmente por (por exemplo) ano. Mas você terá que verificar o desempenho do sistema e ajustar conforme apropriado.
Essas tabelas precisam de nomes exclusivos e há algumas opções. Eles podem ser legíveis por humanos (por exemplo, nyse_goog_dailyhighs_2010) ou (minha preferência) aleatórios. De qualquer forma, um conjunto de tabelas de metadados é necessário e os nomes de tabelas aleatórios impedem que os desenvolvedores infiram qualquer coisa no nome que não deveria ser inferida.
(2) Os metadados são armazenados em tabelas separadas, conforme exigido pelo aplicativo :
É necessária uma tabela ou conjunto de tabelas adicional para acompanhar os metadados. Essas tabelas conterão dados sobre câmbio, instrumento, valor, frequência, intervalos de datas, proveniência (de onde vieram os dados), além de qualquer outra coisa que você precisar. Eles são mapeados para nomes de tabelas de dados.
Se houver dados suficientes, essa pesquisa pode fornecer um nome de tabela e um nome de banco de dados, permitindo uma espécie de fragmentação de dados auto-implementada (se esse for o uso correto do termo). Mas eu manteria isso em reserva.
Em seguida, na camada de aplicativo, eu consultaria as tabelas de metadados para determinar onde meus dados estavam localizados e, em seguida, realizaria consultas relativamente simples nas tabelas de big data para obter meus dados.
Vantagens:
Minha experiência (relativamente limitada) é que os bancos de dados geralmente podem lidar com um grande número de tabelas pequenas com mais facilidade do que um número menor de tabelas grandes. Essa abordagem também facilita a manutenção (por exemplo, limpeza de dados antigos, reconstrução de uma tabela corrompida, criação/recarregamento de backups, adição de uma nova entidade). Isso separa completamente os diferentes tipos de dados, se (por exemplo) você tiver dados em taxas diferentes ou exigir diferentes tipos de dados.
Esse conceito de tabela fina também deve permitir acesso rápido ao disco para o que suspeito ser a consulta mais comum, um intervalo contíguo de dados de uma única entidade. A maioria dos aplicativos de dados é limitada por E/S de disco, portanto, vale a pena considerar isso. Como um comentarista já insinuou, esta pode ser uma aplicação ideal para um banco de dados orientado a colunas, mas ainda não encontrei um produto orientado a colunas que seja mainstream o suficiente para eu apostar minha carreira. Este esquema chega bem perto.
Desvantagens:
Cerca de metade do seu espaço em disco é dedicado ao armazenamento de carimbos de data/hora, quando francamente 100 ou 1000 das tabelas terão exatamente os mesmos dados na coluna de carimbo de data/hora. (Na verdade, este é um requisito se você deseja realizar junções de tabela fáceis).
Armazenar nomes de tabelas e realizar a pesquisa dinâmica requer muita complexidade de aplicativos e operações de string, o que me faz estremecer. Mas ainda parece melhor do que as alternativas (discutidas abaixo).
Considerações:
Tenha cuidado ao arredondar em seu campo de tempo. Você deseja que seus valores sejam arredondados o suficiente para permitir junções (se apropriado), mas precisos o suficiente para não serem ambíguos.
Tenha cuidado com os fusos horários e o horário de verão. Estes são difíceis de testar. Eu aplicaria um requisito UTC no armazenamento de dados (o que pode me tornar impopular) e lidaria com conversões no aplicativo.
Variações:
Algumas variações que considerei são:
Dobragem de dados: se a série temporal for igualmente espaçada, use uma coluna de carimbo de data/hora e (por exemplo) 10 colunas de dados. O timestamp agora se refere ao horário da primeira coluna de dados, e as outras colunas de dados são assumidas igualmente espaçadas entre esse timestamp e o próximo. Isso economiza muito armazenamento que era usado anteriormente para armazenar carimbos de data/hora, a um custo significativo de consulta e/ou complexidade do aplicativo. Intervalo contíguo, consultas de entidade única agora exigem menos acesso ao disco.
Multiplexação: se várias séries temporais forem conhecidas por usar a mesma série temporal, use um carimbo de data/hora e (por exemplo) 10 colunas de dados conforme descrito acima. Mas agora cada coluna representa uma série temporal diferente. Isso requer uma atualização na tabela de metadados, que não é uma pesquisa no nome da tabela e da coluna. O espaço de armazenamento é reduzido. As consultas permanecem simples. Por mais contíguo que seja, as consultas de entidade única agora exigem muito mais acesso ao disco.
Mega-tabela: Leve o conceito de "multiplexação" ao extremo e coloque todos os dados em uma única tabela, uma vez por coluna. Isso requer grandes quantidades de acesso ao disco para intervalos contíguos, consultas de entidade única e é um pesadelo de manutenção. Por exemplo, adicionar uma nova entidade agora requer um comando MODIFY TABLE em uma tabela de muitos TB.
Para uma discussão adicional sobre este formato, veja as várias respostas em: Muitas colunas no MySQL
Tabela totalmente normalizada: em vez de usar muitas tabelas de 2 colunas, você pode usar uma tabela de três colunas, onde as colunas são hora, dataid e valor. Agora, suas tabelas de metadados precisam apenas pesquisar valores de ID, em vez de nomes de tabelas ou nomes de colunas, o que permite enviar mais lógica para as consultas SQL, em vez da camada do aplicativo.
Aproximadamente 2/3 do armazenamento agora é consumido com as colunas de normalização, portanto, isso usará muito espaço em disco.
Você pode usar uma ordem de chave primária de (dataid, timestamp) para consultas de entidade única contíguas e rápidas. Ou você pode usar uma ordem de chave primária de (timestamp. dataid) para inserções mais rápidas.
No entanto, mesmo depois de considerar essas variações, meu plano para meu próximo desenvolvimento é muitas tabelas, duas colunas cada. Isso, ou o método que em breve será postado por alguém mais sábio do que eu :).
Use o MongoDB, você pode criar coleções rapidamente. Veja como organizar seus dados em bancos de dados separados e coleções nesses bancos de dados. Considere quanta memória você precisaria para tentar manter cada fragmento na memória do sistema - se precisar de recuperação rápida. Tolice ficar com uma solução interna, se houver algo mais novo por aí que evoluirá ao longo das linhas que você precisa. Parece uma boa iniciativa.