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.
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:
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.
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: 2df9b50c4a8b4918871b04fe9768f4ec' \
--header 'Content-Type: application/json' \
--data-raw '{
"mdmOrgId": "{{carol_orgid}}",
"mdmTenantId": "{{carol_envid}}",
"query": "SELECT COUNT(*) FROM mdbusinesspartnergroup",
"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: 2df9b50c4a8b4918871b04fe9768f4ec' \
--header 'Content-Type: application/json' \
--data-raw '{
"queryId": "carol-a654e18d03a34aa2aef75becba74-cb73-4b27-9ccf-f1dbb8342c13"
}'
Se a consulta ainda estiver em processamento, a resposta indicará:
{
"queryPending": true
}
Quando os resultados estiverem prontos, a resposta incluirá o esquema (schema) e as linhas (rows):
{
"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).
Staging Tables: Utilize o formato stg<nome_do_conector><nome_da_tabela>. Exemplo: stg_protheus_carol_ct1.
Data Models (Golden Records): Utilize o nome do Data Model exatamente como apresentado na Carol UI (não utilize o label). Exemplo (Data Model de Marcações do Clock In): clockinrecords (se este for o nome do Data Model).
Para ter acesso ao BigQuery via PyCarol, você precisará da versão PyCarol ≥ 2.47.4 e uma Service Account com acesso ao BigQuery.
É possível consultar tanto registros de staging quanto Data Models.
Exemplo de Consulta a Data Models (usando dm_clockinrecords como exemplo):
import pycarol
SA_FILEPATH = "/home/jro/wk/totvs/sa.json"
TEST_QUERY1 = """
SELECT *
FROM `dm_clockinrecords`
LIMIT 100
"""
with open(SA_FILEPATH, "r") as file:
service_account = json.loads(file.read())
return service_account
carol = Carol()
sql = pycarol.SQL(carol)
result = sql.query(TEST_QUERY1, method="bigquery", service_account=service_account, dataframe=True)
A PyCarol também suporta o uso de syntactic sugar para referenciar tabelas de staging sem inserir o connector id.
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.
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.
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)
O cenário otimizado, que trata corretamente a partição e irá ler menos dados, utiliza uma variável declarada (constante) para o filtro de data:
DECLARE dateB TIMESTAMP;
SET dateB = (SELECT B_date FROM B LIMIT 1);
SELECT * FROM A WHERE A_date >= dateB;
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.
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`"
}
]
}
Acesso na Tenant Destino:
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
Apenas os atributos informados no Sharing Group estarão disponíveis na view.
Qualquer dúvida adicional deve ser direcionada via Ticket.
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/.