Ingestão via BigQuery, transformação em arquitetura Medallion e consumo analítico com R/dbplyr
Este relatório apresenta uma análise da produção de grãos no Brasil utilizando dados oficiais do IBGE/Pesquisa Agrícola Municipal (PAM), processados em ambiente cloud com Google BigQuery e analisados em R através do pacote dbplyr.
O objetivo é demonstrar um pipeline de dados completo, da ingestão à geração de resultados.
O projeto segue a arquitetura Medallion (Bronze, Silver e Gold), estruturada em cinco etapas:
Os dados da PAM/IBGE foram extraídos do datalake público da Base dos Dados, hospedado no BigQuery. Nesta camada, os dados permanecem em formato bruto com filtros mínimos aplicados apenas às culturas de interesse, preservando a integridade original e permitindo reprocessamento futuro quando necessário.
A camada Silver consolida dados limpos e padronizados, incluindo:
Tabelas agregadas por município e unidade federativa reduzem o volume de dados e preparam a base para análises subsequentes, priorizando qualidade.
A camada Gold contém tabelas orientadas a questões de negócio, incluindo:
As métricas são pré-calculadas e armazenadas no BigQuery para consumo.
O acesso aos dados ocorre diretamente no BigQuery através do dbplyr, que traduz operações R para SQL.
As consultas executam exclusivamente sobre tabelas Gold, garantindo menor volume processado e maior eficiência de custos.
library(pacman)
pacman::p_load(
DBI, bigrquery, dbplyr,
dplyr, ggplot2, scales)
# Conexão com o dataset Gold no BigQuery
# Assume que as tabelas da camada Gold estão no dataset com sufixo "_gold"
con <- DBI::dbConnect(
bigrquery::bigquery(),
project = Sys.getenv("GCP_PROJECT_ID", "pipeline-pam"),
dataset = paste0(Sys.getenv("BQ_DATASET_ID", "analise_lavouras"), "_gold"),
billing = Sys.getenv("GCP_PROJECT_ID", "pipeline-pam"))cafe_pal <- c(
"Soja" = "#224573", # azul escuro
"Milho" = "#6FA8DC", # azul claro
"Arroz" = "#6B4F4F" # marrom intenso
)
cafe_bg <- "#F7F2EE"
cafe_strip <- "#BFA5A0"
theme_cafe <- function(base_size = 13) {
theme_classic(base_size = base_size) +
theme(
plot.background = element_rect(fill = cafe_bg, color = NA),
panel.background = element_rect(fill = cafe_bg, color = NA),
panel.grid.major = element_line(color = "grey85"),
panel.grid.minor = element_blank(),
strip.background = element_rect(fill = cafe_strip, color = NA),
strip.text = element_text(face = "bold"),
axis.text = element_text(color = "#2F2F2F"),
axis.title = element_text(face = "bold"),
plot.title = element_text(face = "bold", size = 16),
plot.subtitle = element_text(size = 12),
legend.position = "top",
legend.title = element_blank())
}Nesta seção examinamos a evolução da produção de grãos (Soja, Milho e Arroz) a partir do ano 2000. A consulta é realizada no dataset Gold e agrupa os dados por ano e produto.
gold_evolucao <- tbl(con, "gold_evolucao_temporal") |>
filter(ano >= 2000) |>
select(ano, produto_grupo, producao_total_ton, produtividade_media)! Using an auto-discovered, cached token. To suppress this message, modify your code or options to clearly consent to
the use of a cached token. See gargle's "Non-interactive auth" vignette for more details: <https://gargle.r-lib.org/articles/non-interactive-auth.html>ℹ The bigrquery package is using a cached token for
'jenniferlopesagronomia@gmail.com'.# Exibir SQL gerado para conferência (boa prática)
dbplyr::sql_render(gold_evolucao)<SQL> SELECT `ano`, `produto_grupo`, `producao_total_ton`, `produtividade_media`
FROM `gold_evolucao_temporal`
WHERE (`ano` >= 2000.0)df_evolucao <- gold_evolucao |> collect()ggplot(
df_evolucao,
aes(x = ano, y = producao_total_ton / 1e6, color = produto_grupo)) +
geom_line(linewidth = 1.3) +
geom_point(size = 2.5) +
scale_color_manual(values = cafe_pal) +
scale_y_continuous(labels = label_number(suffix = " M t")) +
labs(
title = "Evolução da Produção Nacional de Grãos",
subtitle = "Soja, Milho e Arroz",
x = NULL,
y = "Produção (Milhões de toneladas)") +
theme_cafe()
Aqui selecionamos os 10 estados com maior produtividade média no último ano disponível, separando por cultura. A consulta utiliza os dados da tabela gold_ranking_estados.
gold_ranking <- tbl(con, "gold_ranking_estados") |>
filter(rank_producao <= 10)
dbplyr::sql_render(gold_ranking)<SQL> SELECT `gold_ranking_estados`.*
FROM `gold_ranking_estados`
WHERE (`rank_producao` <= 10.0)df_ranking <- gold_ranking |> collect()ggplot(
df_ranking,
aes(
x = reorder(sigla_uf, prod_media),
y = prod_media,
fill = produto_grupo
)) +
geom_col(width = 0.7) +
coord_flip() +
facet_wrap(~ produto_grupo, scales = "free_y") +
scale_fill_manual(values = cafe_pal) +
labs(
title = "Top 10 Estados em Produtividade Média",
subtitle = "Ranking por cultura – último ano disponível",
x = NULL,
y = "Produtividade (t/ha)") +
theme_cafe() +
theme(legend.position = "none")
Esta análise mostra a participação de cada região na produção nacional para cada cultura. Utilizamos a tabela gold_perfil_regional para computar a especialização.
gold_regional <- tbl(con, "gold_perfil_regional") |>
select(regiao, produto_grupo, especializacao_regional)
dbplyr::sql_render(gold_regional)<SQL> SELECT `regiao`, `produto_grupo`, `especializacao_regional`
FROM `gold_perfil_regional`df_regional <- gold_regional |> collect()ggplot(
df_regional,
aes(
x = regiao,
y = especializacao_regional,
fill = produto_grupo)) +
geom_col(position = "dodge", width = 0.75) +
facet_wrap(~ produto_grupo) +
scale_fill_manual(values = cafe_pal) +
labs(
title = "Especialização Regional da Produção",
x = NULL,
y = "Participação na Produção Nacional (%)") +
theme_cafe() +
theme(
axis.text.x = element_text(angle = 30, hjust = 1),
legend.position = "none")
A tabela gold_municipios_emergentes contém os municípios que apresentaram maiores ganhos de produtividade ou crescimento de produção nos últimos anos. Apresentamos os top registros para cada cultura.
gold_emergentes <- tbl(con, "gold_municipios_emergentes") |>
arrange(desc(crescimento_producao_pct))
dbplyr::sql_render(gold_emergentes)<SQL> SELECT `gold_municipios_emergentes`.*
FROM `gold_municipios_emergentes`
ORDER BY `crescimento_producao_pct` DESCdf_emergentes <- gold_emergentes |> collect()head(df_emergentes, 10)# A tibble: 10 × 10
crescimento_producao_pct prod_inicial produto_grupo prod_final sigla_uf
<dbl> <dbl> <chr> <dbl> <chr>
1 90650 1 Milho 3.17 SE
2 53233. 2.7 Arroz 6 MG
3 31907. 3.20 Soja 3.60 MT
4 28180 1.25 Arroz 1.4 PI
5 23900 1 Arroz 4 MG
6 21500 0.2 Milho 4.8 PE
7 19900 1.2 Milho 6 PE
8 18525 1.2 Arroz 1.85 PI
9 14900 3 Arroz 3 RO
10 14900 4.8 Milho 6.3 MT
# ℹ 5 more variables: producao_final <int>, id_municipio_nome <chr>,
# ganho_produtividade <dbl>, producao_inicial <int>, id_municipio <int>DBI::dbDisconnect(con)Base dos Dados IBGE – PAM Lavoura Temporária (BigQuery)
ID Projeto: pipeline-pam