Ir para o conteúdo

Queries e Templates SQL

O Extrator usa dois templates de query configuráveis por domínio. Isso permite que cada parceiro adapte a extração à estrutura do seu banco Oracle sem alterações no código.

Como funcionam os templates

Os templates são strings SQL com variáveis de substituição entre {{ }}, substituídas automaticamente a cada execução.

Variáveis disponíveis

Variável Tipo Descrição
{{ctrl_min}} string Timestamp mínimo — ponto de partida da extração atual
{{ctrl_max}} string Timestamp máximo — obtido da query de controle
{{offset}} number Offset de paginação (0, 1000, 2000…)
{{limit}} number Registros por página (definido por {PREFIXO}_SRC_QUERY_LIMIT)

Query de controle máximo ({PREFIXO}_QUERY_CTRL_MAX)

Determina até qual ponto no tempo há dados disponíveis para extração nesta execução.

Variáveis disponíveis: somente {{ctrl_min}}.
Retorno esperado: uma linha com a coluna CTRL_MAX do tipo string.

SELECT MAX(DT_DATA_REALIZADO) AS CTRL_MAX
FROM VW_CTC_PRODUCAO
WHERE DT_DATA_REALIZADO > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')

SELECT MAX(DT_DATA_PLANTIO) AS CTRL_MAX
FROM VW_CTC_PLANTIO
WHERE DT_DATA_PLANTIO > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')

SELECT MAX(DT_DATA_COLHEITA) AS CTRL_MAX
FROM VW_CTC_CTT
WHERE DT_DATA_COLHEITA > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')

SELECT MAX(DT_DATA_ORDEM) AS CTRL_MAX
FROM VW_CTC_ORDEM
WHERE DT_DATA_ORDEM > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')

SELECT MAX(DT_DATA_PRAGA) AS CTRL_MAX
FROM VW_CTC_PRAGAS
WHERE DT_DATA_PRAGA > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')

SELECT MAX(DT_DATA_CLIMA) AS CTRL_MAX
FROM VW_CTC_CLIMA
WHERE DT_DATA_CLIMA > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')

Query de extração ({PREFIXO}_QUERY_TEMPLATE)

Extrai os registros com paginação via ROWNUM (compatível com Oracle 11g+).

Variáveis disponíveis: {{ctrl_min}}, {{ctrl_max}}, {{offset}}, {{limit}}.

Retorno esperado: registros com a estrutura definida na seção de campos obrigatórios.

SELECT * FROM (
  SELECT inner_query.*, ROWNUM rn FROM (
    SELECT * FROM VW_CTC_PRODUCAO
    WHERE DT_DATA_REALIZADO > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')
      AND DT_DATA_REALIZADO <= TO_DATE('{{ctrl_max}}', 'YYYY-MM-DD HH24:MI:SS')
    ORDER BY DT_DATA_REALIZADO ASC, ID_USINA ASC, ID_TALHAO ASC
  ) inner_query WHERE ROWNUM <= {{offset}} + {{limit}}
) WHERE rn > {{offset}}

SELECT * FROM (
  SELECT inner_query.*, ROWNUM rn FROM (
    SELECT * FROM VW_CTC_PLANTIO
    WHERE DT_DATA_PLANTIO > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')
      AND DT_DATA_PLANTIO <= TO_DATE('{{ctrl_max}}', 'YYYY-MM-DD HH24:MI:SS')
    ORDER BY DT_DATA_PLANTIO ASC, ID_USINA ASC
  ) inner_query WHERE ROWNUM <= {{offset}} + {{limit}}
) WHERE rn > {{offset}}

SELECT * FROM (
  SELECT inner_query.*, ROWNUM rn FROM (
    SELECT * FROM VW_CTC_CTT
    WHERE DT_DATA_COLHEITA > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')
      AND DT_DATA_COLHEITA <= TO_DATE('{{ctrl_max}}', 'YYYY-MM-DD HH24:MI:SS')
    ORDER BY DT_DATA_COLHEITA ASC, ID_USINA ASC
  ) inner_query WHERE ROWNUM <= {{offset}} + {{limit}}
) WHERE rn > {{offset}}

SELECT * FROM (
  SELECT inner_query.*, ROWNUM rn FROM (
    SELECT * FROM VW_CTC_ORDEM
    WHERE DT_DATA_ORDEM > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')
      AND DT_DATA_ORDEM <= TO_DATE('{{ctrl_max}}', 'YYYY-MM-DD HH24:MI:SS')
    ORDER BY DT_DATA_ORDEM ASC, ID_USINA ASC
  ) inner_query WHERE ROWNUM <= {{offset}} + {{limit}}
) WHERE rn > {{offset}}

SELECT * FROM (
  SELECT inner_query.*, ROWNUM rn FROM (
    SELECT * FROM VW_CTC_PRAGAS
    WHERE DT_DATA_PRAGA > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')
      AND DT_DATA_PRAGA <= TO_DATE('{{ctrl_max}}', 'YYYY-MM-DD HH24:MI:SS')
    ORDER BY DT_DATA_PRAGA ASC, ID_USINA ASC
  ) inner_query WHERE ROWNUM <= {{offset}} + {{limit}}
) WHERE rn > {{offset}}

SELECT * FROM (
  SELECT inner_query.*, ROWNUM rn FROM (
    SELECT * FROM VW_CTC_CLIMA
    WHERE DT_DATA_CLIMA > TO_DATE('{{ctrl_min}}', 'YYYY-MM-DD HH24:MI:SS')
      AND DT_DATA_CLIMA <= TO_DATE('{{ctrl_max}}', 'YYYY-MM-DD HH24:MI:SS')
    ORDER BY DT_DATA_CLIMA ASC, ID_USINA ASC
  ) inner_query WHERE ROWNUM <= {{offset}} + {{limit}}
) WHERE rn > {{offset}}

Views Oracle necessárias

O Extrator lê os dados através de views criadas no banco do parceiro. Abaixo estão os scripts de criação para cada domínio.

Antes de criar cada view, execute a query abaixo para descobrir a menor data disponível — use esse valor em {PREFIXO}_QUERY_CTRL_MIN_DEFAULT:

-- Exemplo para Plantio
SELECT MIN(DT_DATA_PLANTIO), MAX(DT_DATA_PLANTIO), COUNT(*) FROM TB_PLANTIO;

CREATE OR REPLACE VIEW VW_CTC_PRODUCAO AS
  SELECT
    ID_USINA, CD_SAFRA, CD_SETOR, CD_BLOCO, CD_FAZENDA, ID_TALHAO,
    DS_FAZENDA, NR_AREA_TALHAO, NR_AREA_COLHEITA, CD_VARIEDADE,
    CD_AMBIENTE, CD_ESPACAMENTO, CD_TIPO_CORTE, CD_TIPO_COLHEITA,
    NR_IMP_MINERAL, NR_IMP_VEGETAL, NR_HORAS_QUEIMA,
    CD_TIPO_PROP, CD_ESTAGIO, BL_BIS, NR_IDADE, N_DISTANCIA, BL_REFORMA,
    DT_DATA_ANTERIOR, NR_TCH_ANTERIOR,
    DT_DATA_REALIZADO, NR_TCH_REALIZADO, NR_TCH_ESTIMADO,
    NR_ATR, NR_FIBRA_CANA, NR_POL_CANA, NR_POL_CALDO,
    NR_BRIX_CALDO, NR_AR_CALDO, CD_COLHEITA_PLAN
  FROM TB_PRODUCAO;

CREATE OR REPLACE VIEW VW_CTC_PLANTIO AS
  SELECT
    ID_USINA, CD_SAFRA, CD_SETOR, CD_BLOCO, CD_FAZENDA, ID_TALHAO,
    DT_DATA_PLANTIO, NR_AREA_PLANTIO, NR_FALHAS_PLANTIO,
    CD_TIPO_PLANTIO, CD_TIPO_MUDA, CD_VARIEDADE, NR_CONSUMO_MUDA,
    NR_RENDIMENTO_PLANTIO_MANUAL, NR_RENDIMENTO_PLANTIO_MECANICO
  FROM TB_PLANTIO;

CREATE OR REPLACE VIEW VW_CTC_CTT AS
  SELECT
    ID_USINA, CD_SAFRA, CD_SETOR, CD_BLOCO, CD_FAZENDA,
    NR_AREA_TALHAO, DT_DATA_COLHEITA, NR_AREA_COLHEITA,
    NR_CONSUMO_PLANTIO_MECANICO, NR_CONSUMO_COLHEITA_MECANICO,
    NR_RENDIMENTO_PLANIO_MANUAL, NR_RENDIMENTO_PLANTIO_MECANICO
  FROM TB_CTT;

CREATE OR REPLACE VIEW VW_CTC_ORDEM AS
  SELECT
    ID_USINA, CD_SAFRA, CD_SETOR, CD_BLOCO, CD_FAZENDA,
    ID_TALHAO, NR_AREA_TALHAO,
    DS_MATURADOR, NR_MATURADOR, DT_MATURADOR,
    DS_INIBIDOR, NR_INIBIDOR, DT_INIBIDOR,
    DS_IRRIGACAO_SIST, DS_IRRIGACAO_TIPO, NR_IRRIGACAO,
    DT_VINHACA, NR_VINHACA, NR_VINHACA_K2O,
    NR_TORTA, NR_CALCARIO, NR_GESSO,
    BL_AREA_RESTRICAO, DT_DATA_IRRIGACAO,
    {campo_data} AS DT_DATA_ORDEM   -- substituir pelo campo de data mais representativo
  FROM TB_ORDEM;

DT_DATA_ORDEM

Substitua {campo_data} pelo campo de data mais representativo da tabela — por exemplo: DT_MATURADOR AS DT_DATA_ORDEM.

CREATE OR REPLACE VIEW VW_CTC_PRAGAS AS
  SELECT
    ID_USINA, CD_SAFRA, CD_SETOR, CD_BLOCO, CD_FAZENDA,
    ID_TALHAO, NR_AREA_TALHAO, DT_DATA_PRAGA,
    NR_ENTRENOS_BROCADOS, NR_ENTRENOS_AMOSTRADOS,
    NR_SPHENOPHORUS, NR_CIGARRINHA, NR_MIGDOLUS, NR_NEMATOIDE
  FROM TB_PRAGAS;

CREATE OR REPLACE VIEW VW_CTC_CLIMA AS
  SELECT
    ID_USINA, CD_SAFRA, CD_SETOR, CD_BLOCO, CD_FAZENDA,
    DT_DATA_CLIMA, NR_LATITUDE_ESTACAO, NR_LONGITUDE_ESTACAO,
    NR_PRECIPITACAO, NR_TEMPERATURA_MINIMA, NR_TEMPERATURA_MAXIMA,
    NR_TEMPERATURA_MEDIA, NR_UMIDADE_MINIMA, NR_UMIDADE_MAXIMA,
    NR_RADIACAO, NR_VELOCIDADE_VENTO
  FROM TB_CLIMA;

Precisão de timestamps

Use HH24:MI:SS para evitar re-ingestão

Sem a parte de hora, o Extrator reprocessa todos os registros do mesmo dia a cada execução.

-- ❌ Errado: reprocessa o dia inteiro a cada execução
SELECT TO_CHAR(MAX(DT_DATA_REALIZADO), 'YYYY-MM-DD') AS CTRL_MAX ...

-- ✅ Correto: avança segundo a segundo
SELECT TO_CHAR(MAX(DT_DATA_REALIZADO), 'YYYY-MM-DD HH24:MI:SS') AS CTRL_MAX ...