d4nielfr4nco's questions

Estou trabalhando com um conjunto de dados de eventos como o do exemplo abaixo:

Event_Type  Event_Timestamp         Is_Active
A           2010-10-01 00:00:00     1
B           2010-10-01 00:00:01     1
A           2010-10-01 00:00:02     0
D           2010-10-01 00:00:03     1
B           2010-10-01 00:00:04     0
C           2010-10-01 00:00:05     1
A           2010-10-01 00:00:06     1
A           2010-10-01 00:00:07     1
A           2010-10-01 00:00:08     0

O conjunto de dados é ordenado pelo timestamp dos eventos e os dados crescem à medida que os eventos acontecem em tempo real. Mais tipos de eventos podem ser incluídos no conjunto de dados a qualquer momento (não limitado a apenas A, B, C, D como no exemplo) e o mesmo tipo de evento pode aparecer na tabela várias vezes. O campo booleano Is_Active serve como forma de indicar se o evento ainda está ativo (1) ou não (0).

Nesse sentido, tentei fazer algumas transformações nesses dados usando SQL Window Functions. Não estou necessariamente restrito a um produto ou tecnologia específico, portanto, sinta-se à vontade para dizer como você resolveria o problema abaixo.

O que eu quero fazer é emparelhar dinamicamente cada evento do mesmo tipo quando eles têm valores Is_Active opostos e, em seguida, obter quanto tempo esse evento estava ativo. Em outras palavras, dado um evento X, eu precisaria obter o Event_Timestamp para ele quando Is_Active tivesse 1 pela primeira vez (Begin_Timestamp) e, em seguida, ignorar o restante das linhas para este evento X com Is_Active 1 até obter Is_Active 0 , para que eu pudesse pegar o Event_Timestamp novamente (End_Timestamp). Então, eu continuaria aplicando essa lógica quando encontrasse o evento X tendo 1 na coluna Is_Active novamente.

Um exemplo da tabela resultante seria:

Event_Type  Begin_Timestamp         End_Timestamp           Duration
A           2010-10-01 00:00:00     2010-10-01 00:00:02     2 seconds
B           2010-10-01 00:00:01     2010-10-01 00:00:04     3 seconds
D           2010-10-01 00:00:03     null                    null
C           2010-10-01 00:00:05     null                    null
A           2010-10-01 00:00:06     2010-10-01 00:00:08     2 seconds

Existe alguma função de janela que me ajude a obter esses pares de eventos para que eu possa calcular a duração de cada evento?

O Google Cloud SQL suporta a extensão PostGIS, mas não vi nenhuma documentação sobre como trabalhar com dados raster nele. Para carregar dados raster em uma tabela, temos que usar o driver raster2pgsql.

Nesse sentido, o Cloud SQL é compatível? Além disso, seria muito útil se tivesse suporte raster fora do banco de dados, considerando que poderíamos armazenar dados no Cloud Storage e consultá-los a partir daí.

Na documentação do PostgreSQL sobre preenchimento de bancos de dados, eles mencionam como podemos melhorar as operações de carregamento em massa desabilitando restrições e índices, mas vendo como INSERT ... ON CONFLICT (upsert) requer o uso de uma restrição exclusiva para funcionar, e isso faz todo o sentido, Eu estive me perguntando o quão boa é a seguinte estratégia:

1. Crie várias conexões para carregar dados em lotes em paralelo em uma tabela de teste não registrada

2. Crie restrições adiadas para fazer validação de integridade nos dados da tabela

3. Faça o upsert dos dados de preparo em uma grande tabela de destino (5 GB+), que também precisaria ter um PK, índice exclusivo ou restrição exclusiva para possibilitar o upsert.

A abordagem de inserção em conflito é conhecida por ser melhor do que criar manualmente funções SQL ou scripts para unir as duas tabelas para descobrir os "novos registros" a serem inseridos e os "registros comuns" a serem atualizados. Esse é o motivo pelo qual INSERT ... ON CONFLICT existe.

No entanto, ainda estou me perguntando as implicações de desempenho para ler os dados após carregá-los.

Perguntas:

1. Em termos de inchaço do índice, um upsert seria pior do que simples inserções de cópia em massa + atualização para uma tabela sem restrições? O upsert é ruim para a manutenção do índice?

2. Se for esse o caso, acredito que carregamentos mais rápidos também implicariam em um desempenho de consulta ruim após o carregamento da tabela. Devo recriar os índices após INSERT ... ON CONFLICT?

3. Comparado com INSERT ... ON CONFLICT, a forma de função/script de fazer isso, embora com menos desempenho para cargas, seria melhor para manutenção de índice, pois a tabela de destino não precisa ter um índice para fazer o upserting?

Ao transferir dados entre dois bancos de dados, gostaria de saber se há alguma desvantagem na seguinte abordagem:

psql -h [HOST1] -U postgres -d [DB1] -c [SQL1] | psql -h [HOST2]  -d [DB2] -U postgres -c [SQL2]

Onde

[SQL1]="\copy (SELECT [FIELDS_SUBSET] FROM [TABLE_NAME]) TO STDOUT"

[SQL2]="\copy [NEW_TABLE_NAME]( [FIELDS_SUBSET] ) FROM STDIN"

Basicamente, extraio alguns dados do DB1 para o stdout e, em seguida, leio-os imediatamente do stdin para importar esses dados para o DB2. Ambos os bancos de dados estão em redes diferentes. Essa abordagem elimina a necessidade de um arquivo intermediário.

Neste contexto, gostaria de saber se esta estratégia tem alguma desvantagem se comparada ao uso de arquivos intermediários. Por exemplo, essa abordagem é adequada para transferir grandes volumes de dados (GBs de dados)?

Além disso, usando COPY com STDIN e STDOUT, ainda preciso me preocupar com as advertências do comando COPY? ( Referência )