01. VISÃO GERAL

Este documento tem objetivo apresentar como é possível obter marcações, funcionários e dispositivos do Clock in utilizando consulta ao BigQuery da Plataforma Carol Named Queries. 

A Plataforma Carol disponibiliza o BigQuery como camada de armazenamento dos dados por padrão. O consumo de dados pode ser realizado através de comandos SQL.

O consumo de dados via comandos SQL pode ser efetuado através de requisições com resposta síncrona ou assíncrona utilizando API REST. Para consultas mais complexas, onde o tempo de resposta pode exceder o limite de espera (timeout), a opção de resultados assíncronos é a mais indicada.

02. AUTENTICAÇÃO

A Plataforma Carol disponibiliza duas formas de autenticação para uso de rotas privadas para Named Queries: OAuth2 e API Key. Mais detalhes acesse a documentação da Plataforma Carol conforme a seguir:

1. Clique aqui para acessar a documentação da TOTVS Plataforma Carol. 
2. Clique na Aba "Documentação"
3. No Menu a esquerda procure pela opção "Processos Gerais", depois "Autenticação e Login"
4. Na tela ao centro da opção "Autenticação e Login" irá demonstrar a documentação para obter mais detalhes.

03. CONSUMO DE DADOS VIA API REST (BigQuery)

Para realizar o consumo de dados do BigQuery através da Plataforma Carol, utilizamos requisições API REST, sendo a modalidade assíncrona a mais robusta para evitar erros de tempo de espera excedido.

03.01. Requisição de Consulta (Assíncrona)

O *endpoint* `v1/query/v1/query` (ou `/api/v1/bigQuery/query`) é responsável por criar uma *task* de consumo de dados que será repassada ao BigQuery.

**Parâmetros obrigatórios** para a consulta incluem `mdmOrgId`, `mdmTenantId`, `pageSize` e a `query` SQL a ser executada.

Exemplo de Requisição de Consulta (CURL):

curl --location --request POST 'https://api.carol.ai/sql/v1/query/v1/query' \ 
--header 'accept: application/json' \ 
--header 'Authorization: {(SEUTOKEN})' \ 
--header 'Content-Type: application/json' \ 
--data-raw '{ 
  "mdmOrgId": "{{carol_orgid}}", 
  "mdmTenantId": "{{carol_envid}}", 
  "query": "SELECT COUNT(*) FROM stg_clockinmobile_clockinrecords", 
  "page": 1, 
  "pageSize": 100 
}'

Exemplo de Resposta com ID da Requisição:

{ 
  "queryId": "carol-a654e18d03a34aa2aef75becba74-cb73-4b27-9ccf-f1dbb8342c13" 
}

Obtenção dos Resultados (Polling):

Após a requisição de consulta, para obter os resultados, é necessário realizar o polling utilizando o endpoint v1/query/v1/query_polling (ou /api/v1/bigQuery/query_polling) e fornecer o queryId gerado.

Exemplo de Requisição de Resultados (CURL):

curl --location --request POST 'https://api.carol.ai/sql/v1/query/v1/query_polling' \ 
--header 'accept: application/json' \ 
--header 'Authorization: {{SEUTOKEN}}' \ 
--header 'Content-Type: application/json' \ 
--data-raw '{ 
  "queryId": "carol-a654e18d03a34aa2aef75becba74-cb73-4b27-9ccf-f1dbb8342c13" 
}'

...

{ 
  "queryPending": true 
}

...

{ 
  "schema": { ... }, 
  "rows": [ ... ],
  "totalRows": 1, 
  "totalRowsPage": 1, 
  "lastPage": true, 
  "pageSize": 100 
}

Nomenclatura de Tabelas

O consumo de dados via SQL permite a consulta de dados originais (tabelas staging) ou dados processados (Data Models/Golden Records).

...

04. OTIMIZAÇÃO DAS CONSULTAS

Queries não otimizadas podem gerar custos adicionais no BigQuery, impactar gravemente a aplicação que concorre por recursos de slots e gerar resultados lentos. É crucial garantir um desempenho eficiente das consultas.

04.01. Armazenamento e Processamento do BigQuery

O BigQuery usa Partitioning (Particionamento) e Clustering (Agrupamento em Cluster) para melhorar o desempenho e reduzir a quantidade de dados lidos.

Partitioning: Divide a tabela em segmentos físicos (partições) com base, geralmente, no tempo de ingestão ou em uma coluna de unidade de tempo. Consultas que filtram a coluna de particionamento podem reduzir o total de dados lidos.
Clustering: Ordena os dados automaticamente com base no conteúdo de até quatro colunas (geralmente colunas de alta cardinalidade e não temporais). O clustering melhora o desempenho das consultas em cláusulas WHERE, agregações e cláusulas JOIN utilizando as colunas clusterizadas. É possível definir Partitioning e Clustering em uma única tabela.

04.02. Otimizações em SQL

Para garantir que o BigQuery utilize corretamente o particionamento e leia menos dados, siga esta regra:

Em tabelas particionadas, o atributo da partição deve ser a primeira condicional informada na query.
A condição no atributo da partição deve conter apenas constantes.

Exemplo de Otimização (Evitando o cenário que NÃO filtra partições não referenciadas):

O cenário abaixo NÃO efetua o filtro de partições não referenciadas, pois a data é obtida por subconsulta:

SELECT * FROM A WHERE A_date >= (SELECT B_date FROM B LIMIT 1)

...

DECLARE dateB TIMESTAMP;
SET dateB = (SELECT B_date FROM B LIMIT 1);
SELECT * FROM A WHERE A_date >= dateB;

04.03. Agente de Otimização de SQL

O Agente de Otimização SQL é uma ferramenta integrada à Plataforma Carol que auxilia a melhorar a performance de queries SQL. Ele analisa consultas, sugere melhorias e aponta pontos de atenção para otimização.

Como utilizar (via VS Code com extensão Carol BigQuery):

Abra o arquivo de query SQL com a extensão .csql no VS Code.
Após a execução da query (clicando em Carol - Run Query ou usando F5):
Um botão Otimizar SQL aparecerá na parte inferior do editor.
Ao clicar, o agente de IA retorna:
Sugestões de melhoria automáticas: Refaz a query aplicando boas práticas.
Dicas e alertas: Pontos de atenção que o usuário deve avaliar manualmente.

É importante lembrar que o agente não substitui a revisão humana, mas serve como uma ferramenta de apoio para melhorar performance e legibilidade. As otimizações são baseadas em boas práticas para o dialeto selecionado.

05. CRIAÇÃO DE VIEWS COM SHARED DATA

O módulo Shared Data permite o compartilhamento de dados entre Tenants, inclusive de Organizações diferentes.

O compartilhamento ocorre através de views dentro do BigQuery, sem duplicar os dados. Isso é particularmente útil para clientes que desejam consumir apenas um subconjunto específico dos dados do TOTVS Clock In (Data Models ou Staging Tables), garantindo que o consumo seja feito no formato desejado.

A Tenant que acessa os dados compartilhados (destino) paga apenas pelo uso de slots conforme consultas ou dados forem processados nas views de compartilhamento.
O recurso permite que os dados sejam utilizados por pipelines SQL ou em Insights.
Criação de Views

Ao definir a View no Shared Data, você deve definir na Query quais dados serão compartilhados.

OBSERVAÇÃO IMPORTANTE: Não é permitido utilizar select * from tabela como conteúdo das views de Shared Data. Esses compartilhamentos são frágeis e podem colocar em risco o vazamento de novos dados adicionados na tabela origem.

Exemplo de Documento de Configuração de Compartilhamento (Tenant Origem clockinTestWeb para Tenant Destino poffo):

Compartilhando o Data Model clockinrecords e selecionando colunas específicas:

{
"description" : "Sharing Clockin Records table",
"mdmTenantIdDestination" : "d8010a66659540c3a7859bf538161f95",
"name" : "clockinrecords",
"views" : [
  {
    "name" : "clockinrecords",
    "query" : "select image, devicecode, imei, appname, piscode, mdmname, mdmeventdate from `carol-b57a7e74a1764003aa98.b57a7e74a1764003aa987288a610456e.clockinrecords`"
  }
]
}

...

Na Tenant destino, a view de acesso aos dados terá o prefixo shd e a estrutura de nome {sharing_group}_{view_name}.

Exemplo de consulta na Tenant destino:

select * from shd_clockinrecords_clockinrecords

...

07. INFORMAÇÕES ADICIONAIS

Dúvidas Adicionais:

Qualquer dúvida adicional deve ser direcionada via Ticket.

Documentação da Plataforma Carol:

Para mais detalhes sobre as funcionalidades da Plataforma Carol e recursos avançados (como Autenticação e etc), é indicado o acesso à documentação disponível na URL: https://docs.carol.ai/.