Coletor Sharepoint

Esta página tem como finalidade documentar o processo de coleta dos dados de planilhas presentes no Sharepoint.

A extração dos dados foi realizada utilizando linguagem de programação Python, com framework Spark em um Cluster Databrics da Dexco.

Este processo consome as APIs oficiais do Sharepoint via biblioteca escrita em Python.

Resumo do Processo

Baseado em configurações informadas ao job no momento de configuração, o coletor realiza as seguintes etapas:

Recupera secrets de chave USER e PASSWORD do escopo USER_DIEGO
Autentica no Sharepoint
Realiza o download da planilha em ambiente local do cluster
Transforma os dados da planilha e aba especificada durante a etapa de configuração do job em tabela Spark

Configuração do Job

Embaixo da chave tasks do arquivo .json de configuração de um agendamento de job do Databricks, utilize a seguinte estrutura, conforme utilizada na orquestração da extração da planilha Market Consensus.xlsx :

{
...
"tasks": [
    {
        "task_key": "coletor_market_consensus",
        "notebook_task": {
            "notebook_path": "/Repos/paulo.werneck-ext@dex.co/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint",
            "base_parameters": {
                "database": "indicadores_mercado",
                "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento",
                "schema": "{'data': 'date', 'ebitda_2022': 'smallint', 'ebitda_2023': 'smallint', 'ebitda_2024': 'smallint', 'ebitda_2025': 'smallint', 'ebitda_2026': 'smallint', 'ev_ebitda': 'float', 'media_5y': 'float', 'maior_1dp': 'float', 'menor_1dp': 'float'}",
                "remove_columns": "[]",
                "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Market Consensus/Market Consensus.xlsx",
                "sheet_name": "Consensus",
                "remove_lines": "[0, 1]",
                "table": "market_consensus"
            },
            "source": "WORKSPACE"
        },
        "existing_cluster_id": "0523-180911-cnxb45ry",
        "timeout_seconds": 0,
        "email_notifications": {}
    }
  ],
...
}

OBS: É possível adicionar mais de uma task, de acordo com a necessidade.

Para cada Task de um job, os seguintes parâmetros serão necessários serem informados:

“base_url“ (String) : URL do Diretório base onde os arquivos Excel estão armazenados dentro do Sharepoint
“file“ (String) : Caminho dentro da pasta do Sharepoint onde o arquivo Excel que será coletado
“sheet_name“ (String) : Nome da Aba do Excel no Sharepoint que será aberto para realizar a extração
“remove_lines“ ( Lista de Inteiros -> [0,1] ) : Números das linhas que serão puladas/removidas da extração dos dados (inicia sempre em zero e incremental em 1 para várias linhas sendo separado por vírgula), informe [] para não remover nenhuma linha.
“remove_columns” ( Lista de String -> ['Col1', 'Col2'] ) : Nome das colunas que serão puladas/removidas da extração dos dados, , informe [] para não remover nenhuma coluna.
“schema“ (Dict de Key (String) e Value (String) -> { 'Col1' : 'date', 'Col2' : 'smallint' }) :
“database” (String) : Nome do Banco de dados a ser criado a tabela
“table” (String): Nome da Tabela no Banco de dados do parâmetro “database“ que será criado a tabela

Databricks:

Relação de Jobs x Notebooks que compõem o processo:

Databrics job: coletor_excel_sharepoint

Task	Parâmetros	Periodicidade	Notebook	Repositório
coletor_energia_eletrica	{ "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Energia Elétrica/Energia Elétrica.xlsx", "sheet_name": "Sheet1", "remove_lines": "[0,1,2,3]", "remove_columns": "['ID:']", "database": "indicadores_mercado", "table": "tb_energia_eletrica", "schema": "{'mes':'date','energia_eletrica_brasil':'double','energia_eletrica_brasil_projecao':'double'}" }	Diário (13 0 0 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/
coletor_economia_vs_poa	{ "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Economia vs POA (Suprimentos)/Economia vs POA.xlsx", "sheet_name": "Economia", "remove_lines": "[0]", "remove_columns": "['Unnamed: 6','Dados para Gráfico','Unnamed: 8','Unnamed: 9','Unnamed: 10']", "database": "indicadores_mercado", "table": "tb_economia_vs_poa", "schema": "{'mes_ytd':'date','inflacao_acumulada_reais_madeira':'double','inflacao_acumulada_reais_deca':'double','inflacao_acumulada_reais_rc':'double','inflacao_acumulada_reais_total':'double', 'perc_spend_total':'double'}" }	Diário (13 0 0 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/
coletor_market_consensus	{ "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Market Consensus/Market Consensus.xlsx", "sheet_name": "Consensus", "remove_lines": "[0, 1]", "remove_columns": "[]", "database": "indicadores_mercado", "table": "tb_market_consensus", "schema": "{'data': 'date', 'ebitda_2022': 'smallint', 'ebitda_2023': 'smallint', 'ebitda_2024': 'smallint', 'ebitda_2025': 'smallint', 'ebitda_2026': 'smallint', 'ev_ebitda': 'float', 'media_5y': 'float', 'maior_1dp': 'float', 'menor_1dp': 'float'}" }	Diário (13 0 0 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/
coletor_hoff_solucoes	{ "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Hoff Soluções/Hoff Soluções.xlsx", "sheet_name": "Projetos Arquitetônicos", "remove_lines": "[0]", "remove_columns": "[]", "database": "hoff_solucoes", "table": "hoff_solucoes", "schema": "{'data': 'date', 'ano': 'smallint', 'proj_arquit': 'int', 'proj_arquit_reformas': 'int', 'proj_arquit_empreendimentos': 'int', 'proj_arquit_reformas_pf': 'int', 'proj_arquit_reformas_pj': 'int', 'proj_arquit_reformas_2019_10': 'int', 'proj_arquit_empreendimentos_2019_100': 'int', 'proj_arquit_empreendimentos_n': 'int', 'proj_arquit_empreendimentos_ne': 'int', 'proj_arquit_empreendimentos_co': 'int', 'proj_arquit_empreendimentos_se': 'int', 'proj_arquit_empreendimentos_s': 'int', 'ano2': 'int', 'proj_arquit_reformas_ano': 'string', 'proj_arquit_empreendimentos_ano': 'string'}" }	Diário (13 0 0 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/
coletor_sell_out_deca	{ "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Sell-out Deca/Sell-out Deca.xlsx", "sheet_name": "Sheet1", "remove_lines": "[0]", "remove_columns": "[]", "database": "indicadores_mercado", "table": "tb_sell_out_deca", "schema": "{'ano': 'int', 'mes': 'int', 'ano_mes': 'string', 'Sellout_Metais': 'float', 'Sellout_Loucas': 'float', 'Sellout_Hydra': 'float', 'Sellout_RC': 'float'}" }	Diário (13 0 0 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/
coletor_investing_taxa_de_longo_prazo	{ "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Taxa de Longo Prazo/Taxa de Longo Prazo.xlsx", "sheet_name": "Onda1", "remove_lines": "[]", "remove_columns": "[]", "database": "investing", "table": "tb_brazil_10_year_bond_yield_sharepoint_silver", "schema": "{'Data': 'date','10y_Brazilian_Bond': 'double'}" }	Diário (13 0 0 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/

Tabelas referentes ao job: coletor_excel_sharepoint

indicadores_mercado.tb_energia_eletrica
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
mes	Não	Não	Não		date
energia_eletrica_brasil	Não	Não	Não		double
energia_eletrica_brasil_projecao	Não	Não	Não		double

indicadores_mercado.tb_economia_vs_poa
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
mes_ytd	Não	Não	Não		date
inflacao_acumulada_reais_madeira	Não	Não	Não		double
inflacao_acumulada_reais_deca	Não	Não	Não		double
inflacao_acumulada_reais_rc	Não	Não	Não		double
inflacao_acumulada_reais_total	Não	Não	Não		double
perc_spend_total	Não	Não	Não		double

indicadores_mercado.tb_market_consensus
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
data	Não	Não	Não		date
ebitda_2022	Não	Não	Não		smallint
ebitda_2023	Não	Não	Não		smallint
ebitda_2024	Não	Não	Não		smallint
ebitda_2025	Não	Não	Não		smallint
ebitda_2026	Não	Não	Não		smallint
ev_ebitda	Não	Não	Não		float
media_5y	Não	Não	Não		float
maior_1dp	Não	Não	Não		float
menor_1dp	Não	Não	Não		float

hoff_solucoes.hoff_solucoes
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
data	Não	Não	Não		date
ano	Não	Não	Não		smallint
proj_arquit	Não	Não	Não		int
proj_arquit_reformas	Não	Não	Não		int
proj_arquit_empreendimentos	Não	Não	Não		int
proj_arquit_reformas_pf	Não	Não	Não		int
proj_arquit_reformas_pj	Não	Não	Não		int
proj_arquit_reformas_2019_10	Não	Não	Não		int
proj_arquit_empreendimentos_2019_100	Não	Não	Não		int
proj_arquit_empreendimentos_n	Não	Não	Não		int
proj_arquit_empreendimentos_ne	Não	Não	Não		int
proj_arquit_empreendimentos_co	Não	Não	Não		int
proj_arquit_empreendimentos_se	Não	Não	Não		int
proj_arquit_empreendimentos_s	Não	Não	Não		int
ano2	Não	Não	Não		int
proj_arquit_reformas_ano	Não	Não	Não		string
proj_arquit_empreendimentos_ano	Não	Não	Não		string

indicadores_mercado.tb_sell_out_deca
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
ano	Não	Não	Não		int
mes	Não	Não	Não		int
ano_mes	Não	Não	Não		string
Sellout_Metais	Não	Não	Não		float
Sellout_Loucas	Não	Não	Não		float
Sellout_Hydra	Não	Não	Não		float
Sellout_RC	Não	Não	Não		float

investing.tb_brazil_10_year_bond_yield_sharepoint_silver
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
Data	Não	Não	Não		date
10y_Brazilian_Bond	Não	Não	Não		double

Databricks job: coletor_excel_sharepoint_bases_modelos_projecao

Task	Parâmetros	Periodicidade	Notebook	Repositório
coletor_demanda_deca	{ "database": "bases_modelos_projecoes", "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "schema": "{'Data':'date','Metais_Basicos':'int','Metais_Competitivos':'int','Metais_Medio_Luxo':'int','Metais_Exportacao':'int','Loucas_Bacias_Competitivas':'int','Loucas_Bacias_Izy':'int','Loucas_Bacias_Medio_Luxo':'int','Loucas_Cubas':'int','Loucas_Outros':'int','Loucas_Exportacao':'int','Chuveiros_Duchas':'int','Chuveiros_Torneiras_Eletricas':'int','Chuveiros_Torneiras_Plasticas':'int','Chuveiros_Outros':'int','Chuveiros_Exportacao':'int'}", "remove_columns": "[]", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Onda 3/Demanda Deca.xlsx", "sheet_name": "Onda3", "remove_lines": "[0]", "table": "demanda_deca" }	Diário (13 0 1 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/
coletor_demanda_rc	{ "database": "bases_modelos_projecoes", "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "schema": "{'Data':'date','Porcelanato':'int'}", "remove_columns": "[]", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Onda 3/Demanda RC.xlsx", "sheet_name": "Onda3", "remove_lines": "[0]", "table": "demanda_rc" }	Diário (13 0 1 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/
coletor_demanda_deca_anual	{ "database": "bases_modelos_projecoes", "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "schema": "{'data': 'string', 'Metais_MI_Total': 'float', 'Loucas_MI_Total': 'float'}", "remove_columns": "['Unnamed: 3']", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Onda 3/Demanda Deca_Anual.xlsx", "sheet_name": "Sheet1", "remove_lines": "[0]", "table": "demanda_deca_anual" }	Diário (13 0 1 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/
coletor_projecoes_longo_prazo	{ "database": "bases_modelos_projecoes", "base_url": "https://duratexsa.sharepoint.com/sites/JornadaCrescimento", "schema": "{'indices': 'string', '2009': 'double', '2010': 'double', '2011': 'double', '2012': 'double', '2013': 'double', '2014': 'double', '2015': 'double', '2016': 'double', '2017': 'double', '2018': 'double', '2019': 'double', '2020': 'double', '2021': 'double', '2022': 'double', '2023': 'double', '2024': 'double', '2025': 'double', '2026': 'double', '2027': 'double'}", "remove_columns": "[]", "file": "/sites/JornadaCrescimento/Documentos Partilhados/Processar/Onda 3/Projeções-Longo-Prazo.xlsx", "sheet_name": "Plan1", "remove_lines": "[]", "table": "projecoes_longo_prazo" }	Diário (13 0 1 ? * *)	/analytics-databricks-inteligencia-mercado/Workspace/bronze/inteligencia-mercado/coletor-excel-sharepoint	https://bitbucket.org/duratex/analytics-databricks-inteligencia-mercado/src/master/

Tabelas referentes ao job: coletor_excel_sharepoint_bases_modelos_projecao

bases_modelos_projecoes.demanda_deca
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
Data	Não	Não	Não		date
Metais_Basicos	Não	Não	Não		int
Metais_Competitivos	Não	Não	Não		int
Metais_Medio_Luxo	Não	Não	Não		int
Metais_Exportacao	Não	Não	Não		int
Loucas_Bacias_Competitivas	Não	Não	Não		int
Loucas_Bacias_Izy	Não	Não	Não		int
Loucas_Bacias_Medio_Luxo	Não	Não	Não		int
Loucas_Cubas	Não	Não	Não		int
Loucas_Outros	Não	Não	Não		int
Loucas_Exportacao	Não	Não	Não		int
Chuveiros_Duchas	Não	Não	Não		int
Chuveiros_Torneiras_Eletricas	Não	Não	Não		int
Chuveiros_Torneiras_Plasticas	Não	Não	Não		int
Chuveiros_Outros	Não	Não	Não		int
Chuveiros_Exportacao	Não	Não	Não		int

bases_modelos_projecoes.demanda_rc
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
Data	Não	Não	Não		date
Porcelanato	Não	Não	Não		int

bases_modelos_projecoes.coletor_demanda_deca_anual
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
data	Não	Não	Não		string
Metais_MI_Total	Não	Não	Não		float
Loucas_MI_Total	Não	Não	Não		float

bases_modelos_projecoes.projecoes_longo_prazo
Field	Key	Not Null	Partition	Element	Type	Size	Decimal	Description
indice	Não	Não	Não		string
2009	Não	Não	Não		double
2010	Não	Não	Não		double
2011	Não	Não	Não		double
2012	Não	Não	Não		double
2013	Não	Não	Não		double
2014	Não	Não	Não		double
2015	Não	Não	Não		double
2016	Não	Não	Não		double
2017	Não	Não	Não		double
2018	Não	Não	Não		double
2019	Não	Não	Não		double
2020	Não	Não	Não		double
2021	Não	Não	Não		double
2022	Não	Não	Não		double
2023	Não	Não	Não		double
2024	Não	Não	Não		double
2025	Não	Não	Não		double
2026	Não	Não	Não		double
2027	Não	Não	Não		double

Coletor Sharepoint

Resumo do Processo

Configuração do Job

Databricks:

Task

Parâmetros

Periodicidade

Notebook

Repositório

Field

Key

Not Null

Partition

Element

Type

Size

Decimal

Description

Field

Key

Not Null

Partition

Element

Type

Size

Decimal

Description

Field

Key

Not Null

Partition

Element

Type

Size

Decimal

Description

Field

Key

Not Null

Partition

Element

Type

Size

Decimal

Description

Field

Key

Not Null

Partition

Element

Type

Size

Decimal

Description

Field

Key

Not Null

Partition

Element

Type

Size

Decimal

Description

Task

Parâmetros

Periodicidade

Notebook

Repositório

Field

Key

Not Null

Partition

Element

Type

Size

Decimal

Description

Field

Key

Not Null