{"id":203516,"date":"2025-03-12T11:23:27","date_gmt":"2025-03-12T14:23:27","guid":{"rendered":"https:\/\/goias.gov.br\/ligo\/?p=203516"},"modified":"2025-08-29T10:39:39","modified_gmt":"2025-08-29T13:39:39","slug":"tutorial-copia-de-tabelas-relacionais-para-a-estrutura-de-dados-do-big-data-estadual","status":"publish","type":"post","link":"https:\/\/goias.gov.br\/ligo\/tutorial-copia-de-tabelas-relacionais-para-a-estrutura-de-dados-do-big-data-estadual\/","title":{"rendered":"Tutorial \u2013 C\u00f3pia de tabelas relacionais para a estrutura de dados do Big Data Estadual."},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

Nesse pequeno tutorial demonstrar-se-\u00e1 o procedimento para copia de dados de tabelas de um BD relacional para a estrutura de dados do ambiente de Big Data Cloudera.<\/p>\n\n\n\n

Esta tarefa ser\u00e1 realizada com a utiliza\u00e7\u00e3o do PySpark via JupyterNotebook dispon\u00edvel na su\u00edte do Big Data da Supadia\/SGG.<\/p>\n\n\n\n

\u00c9 necess\u00e1rio ter acesso ao Jupyter Notebook do Big Data. O mesmo encontra-se no endere\u00e7o abaixo:<\/p>\n\n\n\n

https:\/\/jupyter.bigdata.go.gov.br\/jupyter<\/a><\/p>\n\n\n\n

Para a realiza\u00e7\u00e3o da tarefa o usu\u00e1rio dever\u00e1 estar logado no ambiente.<\/p>\n\n\n\n

1 \u2013 Configura\u00e7\u00e3o do ambiente para a correta conex\u00e3o com o Cloudera.<\/h2>\n\n\n\n

Na tela inicial do Jupyter escolher a op\u00e7\u00e3o Notebook->Python 3(ipykernel)<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Ap\u00f3s clicar no \u00edcone anterior ser\u00e1 aberta uma nova Aba com um notebook em branco:<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

Colar o c\u00f3digo abaixo na c\u00e9lula edit\u00e1vel<\/p>\n\n\n\n

<\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
# <\/span>In\u00edcio<\/span> <\/span>do<\/span> <\/span>c\u00f3digo<\/span> (<\/span>cortar<\/span> <\/span>e<\/span> <\/span>colar<\/span> <\/span>o<\/span> <\/span>c\u00f3digo<\/span> <\/span>abaixo<\/span>)<\/span><\/span>\n---- (<\/span>Corte<\/span> <\/span>aqui<\/span>)----<\/span><\/span>\nimport<\/span> <\/span>base64<\/span><\/span>\nimport<\/span> <\/span>json<\/span><\/span>\n<\/span>\n# <\/span>Preencher<\/span> <\/span>suas<\/span> <\/span>credenciais<\/span> <\/span>abaixo<\/span><\/span>\nusuario<\/span> = <\/span>"meu.usuario"<\/span> (<\/span>usuario<\/span> <\/span>do<\/span> <\/span>AD<\/span> <\/span>o<\/span> <\/span>mesmo<\/span> <\/span>do<\/span> <\/span>email<\/span> @<\/span>goias<\/span>)<\/span><\/span>\nsenha<\/span>    = <\/span>"minha@senha"<\/span>  (<\/span>senha<\/span> <\/span>do<\/span> <\/span>AD<\/span> <\/span>a<\/span> <\/span>mesma<\/span> <\/span>do<\/span> <\/span>email<\/span> @<\/span>goias<\/span>)<\/span><\/span>\n<\/span>\nconfigJson<\/span> = {<\/span><\/span>\n<\/span>\n"<\/span>session_configs<\/span>": {<\/span><\/span>\n    "<\/span>conf<\/span>": {<\/span><\/span>\n      "<\/span>spark<\/span>.<\/span>yarn<\/span>.<\/span>queue<\/span>": "<\/span>default<\/span>"<\/span><\/span>\n    }<\/span><\/span>\n},<\/span><\/span>\n"livy_session_startup_timeout_seconds"<\/span>: 3600,<\/span><\/span>\n"should_heartbeat"<\/span>: <\/span>True<\/span>,<\/span><\/span>\n"livy_server_heartbeat_timeout_seconds"<\/span>: 0,<\/span><\/span>\n"heartbeat_refresh_seconds"<\/span>: 5,<\/span><\/span>\n"heartbeat_retry_seconds"<\/span>: 1,<\/span><\/span>\n"ignore_ssl_errors"<\/span>: <\/span>True<\/span>,<\/span><\/span>\n"retry_policy"<\/span>: <\/span>"configurable"<\/span>,<\/span><\/span>\n"retry_seconds_to_sleep_list"<\/span>: [0.2, 0.5, 1, 3, 5],<\/span><\/span>\n"configurable_retry_policy_max_retries"<\/span>: 8,<\/span><\/span>\n"spark.yarn.queue"<\/span>: <\/span>"developers"<\/span>,<\/span><\/span>\n"kernel_python_credentials"<\/span>: {<\/span><\/span>\n"<\/span>username<\/span>": "",<\/span><\/span>\n"<\/span>base64_password<\/span>": "",<\/span><\/span>\n"<\/span>url<\/span>": "<\/span>https<\/span>:<\/span>\/\/cdpprodedgenode01.intra.goias.gov.br:8443\/gateway\/cdp-proxy-api\/livy",<\/span><\/span>\n"<\/span>auth<\/span>": "<\/span>Basic_Access<\/span>"<\/span><\/span>\n},<\/span><\/span>\n"custom_headers"<\/span>: {<\/span><\/span>\n"<\/span>X<\/span>-<\/span>Requested<\/span>-<\/span>By<\/span>": "<\/span>jupyter<\/span>-<\/span>hub<\/span>"<\/span><\/span>\n}<\/span><\/span>\n}<\/span><\/span>\n<\/span>\nconfigJson<\/span>[<\/span>"kernel_python_credentials"<\/span>][<\/span>"username"<\/span>] = <\/span>usuario<\/span><\/span>\nconfigJson<\/span>[<\/span>"kernel_python_credentials"<\/span>][<\/span>"base64_password"<\/span>] = <\/span>base64<\/span>.<\/span>b64encode<\/span>(<\/span>senha<\/span>.<\/span>encode<\/span>()).<\/span>decode<\/span>(<\/span>'utf-8'<\/span>)<\/span><\/span>\n<\/span>\nwith<\/span> <\/span>open<\/span>(<\/span>'.sparkmagic\/config.json'<\/span>, <\/span>'w'<\/span>) <\/span>as<\/span> <\/span>f<\/span>:<\/span><\/span>\n    <\/span>json<\/span>.<\/span>dump<\/span>(<\/span>configJson<\/span>, <\/span>f<\/span>)<\/span><\/span>\n---- (<\/span>Corte<\/span> <\/span>aqui<\/span>)----<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Ap\u00f3s colar o c\u00f3digo a tela ficar\u00e1 semelhante a seguir:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Executar o c\u00f3digo colado acima utilizando o bot\u00e3o (ou utilizando Shift+Enter)<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
po\u015b a execu\u00e7\u00e3o ser\u00e1 criado o arquivo \u201c.sparkmagic\/config.json\u201d no seu home<\/p>\n\n\n\n
Se nenhum erro ocorreu o ambiente est\u00e1 apto a conectar e criar sess\u00f5es Spark no Cloudera.<\/p>\n\n\n\n
2 \u2013 Cria\u00e7\u00e3o de uma sess\u00e3o Spark no Hadoop<\/h2>\n\n\n\n
Na tela inicial do Jupyter criar um novo \u201cLauncher\u201d. Isso pode ser feito no menu File->New Launcher,, ou com o bot\u00e3o \u201c+\u201d no topo esquerdo da janela logo abaixo do Menu principal,<\/p>\n\n\n\n
ou pode ser aberto diretamente pela combina\u00e7\u00e3o de teclas Ctrl+Shift+L.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
ou<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Uma nova Aba ser\u00e1 aberta como a seguir:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Criar um novo notebook PySpark. Para isso, selecionar \u201cPySpark\u201d na se\u00e7\u00e3o \u201cNotebook\u201d do Launcher (criado no passo anterior) como mostrado abaixo:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Ser\u00e1 exibida uma nova aba com um notebook PySpark como mostrado abaixo:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
No campo edit\u00e1vel, digitar (ou copiar e colar) o c\u00f3digo abaixo para criar uma sess\u00e3o Spark:<\/p>\n\n\n\n
<\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
sc<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Ap\u00f3s a inclus\u00e3o do c\u00f3digo acima a tela dever\u00e1 ser semelhante a mostrada a seguir:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Executar o c\u00f3digo anterior. Utilizar o bot\u00e3o como mostrado a seguir (ou usar a combina\u00e7\u00e3o de teclas Shift+Enter com a c\u00e9lula em quest\u00e3o selecionada):<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Aguardar alguns instantes. Uma nova sess\u00e3o ser\u00e1 criada no servidor. Ser\u00e1 exibida uma mensagem semelhante \u00e0 seguinte:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
3 \u2013 Copiar uma tabela do seu BD relacional<\/h2>\n\n\n\n
Ap\u00f3s o passo 2 na mesma tela em que foi criada a sess\u00e3o Spark, copiar e colar o c\u00f3digo abaixo na pr\u00f3xima linha edit\u00e1vel (se n\u00e3o houver nenhuma c\u00e9lula acrescentar. Utilizar o bot\u00e3o \u201c+\u201d como mostrado na figura seguinte):<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Copiar e colar o c\u00f3digo seguinte:<\/p>\n\n\n\n
<\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
from<\/span> <\/span>pyspark<\/span>.<\/span>sql<\/span> <\/span>import<\/span> <\/span>SparkSession<\/span><\/span>\nspark<\/span> = <\/span>SparkSession<\/span>.<\/span>builder<\/span>.<\/span>getOrCreate<\/span>()<\/span><\/span>\n<\/span>\njdbcDF<\/span> = <\/span>spark<\/span>.<\/span>read<\/span> \\<\/span><\/span>\n    .<\/span>format<\/span>(<\/span>"jdbc"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"url"<\/span>, <\/span>"jdbc:banco_de_dados:\/\/ip_ou_dns_do_servidor:porta_do_servidor;trustServerCertificate=true;databaseName=nome_do_banco_de_dados"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"user"<\/span>, <\/span>"usuario do seu banco"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"password"<\/span>, <\/span>"senha do seu BD"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"driver"<\/span>, <\/span>"jdbc_driver_do_seu_banco"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"dbtable"<\/span>, <\/span>"esquema.nome_da_tabela"<\/span>) \\<\/span><\/span>\n    .<\/span>load<\/span>()<\/span><\/span>\njdbcDF<\/span>.<\/span>show<\/span>(5)<\/span><\/span>\njdbcDF<\/span>.<\/span>write<\/span>.<\/span>mode<\/span>(<\/span>"overwrite"<\/span>).<\/span>saveAsTable<\/span>(<\/span>"esquema.nome_da_tabela"<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Detalhamento do c\u00f3digo acima que dever\u00e1 ser adaptado ao ambiente de BD relacional de onde deseja-se copiar as tabelas.<\/p>\n\n\n\n
Na primeira linha executa-se a importa\u00e7\u00e3o da lib necess\u00e1ria para que se possa ler da base de dados relacional e escrever no cloudera.<\/p>\n\n\n\n
Na segunda linha cria-se uma vari\u00e1vel com uma inst\u00e2ncia de sess\u00e3o Spark (j\u00e1 instanciada na primeira linha de c\u00f3digo) a qual ser\u00e1 o objeto com as fun\u00e7\u00f5es necess\u00e1rias para realizar a tarefa de c\u00f3pia.<\/p>\n\n\n\n
Caso n\u00e3o haja sess\u00e3o ela ser\u00e1 criada com o comando .getOrCreate()<\/p>\n\n\n\n
Os par\u00e2metros das linhas da fun\u00e7\u00e3o read da vari\u00e1vel spark:<\/p>\n\n\n\n
<\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
.<\/span>format<\/span>(<\/span>"jdbc"<\/span>) - <\/span>indica<\/span> <\/span>o<\/span> <\/span>formato<\/span> <\/span>da<\/span> <\/span>fonte<\/span> <\/span>de<\/span> <\/span>dados<\/span> ,  <\/span>no<\/span> <\/span>caso<\/span> <\/span>em<\/span> <\/span>quest\u00e3o<\/span> <\/span>\u00e9<\/span> <\/span>o<\/span> <\/span>jdbc<\/span>.<\/span><\/span>\n<\/span>\n .<\/span>option<\/span>(<\/span>"url"<\/span>, .....) -  <\/span>indica<\/span> <\/span>a<\/span> <\/span>url<\/span>\/<\/span>string<\/span> <\/span>JDBC<\/span> <\/span>de<\/span> <\/span>conex\u00e3o<\/span> <\/span>que<\/span> <\/span>ser\u00e1<\/span> <\/span>utilizada<\/span><\/span>\n<\/span>\nOs<\/span> <\/span>par\u00e2metros<\/span> <\/span>da<\/span> <\/span>url<\/span> <\/span>s\u00e3o<\/span>:<\/span><\/span>\n<\/span>\n    <\/span>banco_de_dados<\/span> - <\/span>\u00e9<\/span> <\/span>o<\/span> <\/span>nome<\/span> <\/span>do<\/span> <\/span>SGBD<\/span> (<\/span>sistema<\/span> <\/span>gerenciador<\/span> <\/span>de<\/span> <\/span>banco<\/span> <\/span>de<\/span> <\/span>dados<\/span>) <\/span>em<\/span> <\/span>quest\u00e3o<\/span>. <\/span>Ex<\/span>.: <\/span>mysql<\/span>, <\/span>postgresql<\/span>, <\/span>sqlserver<\/span>, <\/span>oracle<\/span>, <\/span>db2<\/span>, <\/span>etc<\/span>...<\/span><\/span>\n<\/span>\n    <\/span>ip_ou_dns_do_servidor<\/span> - <\/span>\u00e9<\/span> <\/span>o<\/span> <\/span>endere\u00e7o<\/span> <\/span>ou<\/span> <\/span>nome<\/span> <\/span>do<\/span> <\/span>servidor<\/span> <\/span>de<\/span> <\/span>banco<\/span> <\/span>de<\/span> <\/span>dados<\/span>. <\/span>Ex<\/span>.: <\/span>localhost<\/span>, <\/span>10.6<\/span>.<\/span>32.19<\/span>, <\/span>meuservidor<\/span>.<\/span>org<\/span>, <\/span>etc<\/span>...<\/span><\/span>\n<\/span>\n    <\/span>porta_do_servidor<\/span> - <\/span>\u00e9<\/span> <\/span>a<\/span> <\/span>porta<\/span> <\/span>utilizada<\/span> <\/span>para<\/span> <\/span>as<\/span> <\/span>conex\u00f5es<\/span> <\/span>com<\/span> <\/span>o<\/span> <\/span>banco<\/span> <\/span>de<\/span> <\/span>dados<\/span>. <\/span>Ex<\/span>.: <\/span>5432<\/span> (<\/span>porta<\/span> <\/span>padr\u00e3o<\/span> <\/span>do<\/span> <\/span>postgresql<\/span>), <\/span>1433<\/span> (<\/span>porta<\/span> <\/span>padr\u00e3o<\/span> <\/span>do<\/span> <\/span>sqlserver<\/span>), <\/span>3306<\/span> (<\/span>porta<\/span> <\/span>padr\u00e3o<\/span> <\/span>do<\/span> <\/span>mysql<\/span>), <\/span>1521<\/span> (<\/span>porta<\/span> <\/span>padr\u00e3o<\/span> <\/span>do<\/span> <\/span>oracle<\/span>) , <\/span>50000<\/span> (<\/span>porta<\/span> <\/span>padr\u00e3o<\/span> <\/span>do<\/span> <\/span>DB2<\/span>), <\/span>etc<\/span>...<\/span><\/span>\n<\/span>\n    <\/span>trustServerCertificate<\/span> - <\/span>\u00e9<\/span> <\/span>um<\/span> <\/span>parametro<\/span> <\/span>opcional<\/span> <\/span>caso<\/span> <\/span>utilize<\/span>-<\/span>se<\/span> <\/span>um<\/span> <\/span>sevidor<\/span> <\/span>de<\/span> <\/span>banco<\/span> <\/span>de<\/span> <\/span>dados<\/span> <\/span>com<\/span> <\/span>certificado<\/span> <\/span>autoassinado<\/span>. <\/span>Incluir<\/span> <\/span>essa<\/span> <\/span>op\u00e7\u00e3o<\/span> <\/span>com<\/span> <\/span>o<\/span> <\/span>valor<\/span> <\/span>booleano<\/span> <\/span>true<\/span> <\/span>para<\/span> <\/span>confiar<\/span> <\/span>no<\/span> <\/span>certificado<\/span>: <\/span>trustServerCertificate<\/span>=<\/span>true<\/span><\/span>\n<\/span>\n    <\/span>databaseName<\/span> - <\/span>\u00e9<\/span> <\/span>o<\/span> <\/span>nome<\/span> <\/span>do<\/span> <\/span>banco<\/span> <\/span>de<\/span> <\/span>dados<\/span> <\/span>de<\/span> <\/span>onde<\/span> <\/span>ser\u00e1<\/span> <\/span>feita<\/span> <\/span>a<\/span> <\/span>c\u00f3pia<\/span> <\/span>da<\/span> <\/span>tabela<\/span>.<\/span><\/span>\n    <\/span><\/span>\nContinuando<\/span> <\/span>a<\/span> <\/span>descri\u00e7\u00e3o<\/span> <\/span>dos<\/span> <\/span>par\u00e2metros<\/span>:<\/span><\/span>\n<\/span>\n  .<\/span>option<\/span>(<\/span>"user"<\/span>, <\/span>"..."<\/span>) - <\/span>indica<\/span> <\/span>o<\/span> <\/span>nome<\/span> <\/span>de<\/span> <\/span>usuario<\/span> <\/span>de<\/span> <\/span>conex\u00e3o<\/span> <\/span>com<\/span> <\/span>o<\/span> <\/span>banco<\/span> <\/span>de<\/span> <\/span>dados<\/span>. <\/span>Ex<\/span>. <\/span>root<\/span>, <\/span>joao<\/span>.<\/span>silveira<\/span>, <\/span>maria<\/span>, <\/span>etc<\/span>...<\/span><\/span>\n<\/span>\n .<\/span>option<\/span>(<\/span>"password"<\/span>, <\/span>"..."<\/span>) - <\/span>indica<\/span> <\/span>a<\/span> <\/span>senha<\/span> <\/span>do<\/span> <\/span>usu\u00e1rio<\/span> <\/span>de<\/span> <\/span>conex\u00e3o<\/span> <\/span>com<\/span> <\/span>o<\/span> <\/span>banco<\/span> <\/span>de<\/span> <\/span>dados<\/span>.<\/span><\/span>\n<\/span>\n .<\/span>option<\/span>(<\/span>"driver"<\/span>, <\/span>"..."<\/span>) - <\/span>indica<\/span> <\/span>a<\/span> <\/span>classe<\/span> <\/span>de<\/span> <\/span>driver<\/span> <\/span>JDBC<\/span> <\/span>do<\/span> <\/span>banco<\/span> <\/span>de<\/span> <\/span>dados<\/span>.  <\/span>Ex<\/span>.: <\/span>org<\/span>.<\/span>postgresql<\/span>.<\/span>Driver<\/span>(<\/span>postgesql<\/span>), <\/span>com<\/span>.<\/span>microsoft<\/span>.<\/span>sqlserver<\/span>.<\/span>jdbc<\/span>.<\/span>SQLServerDriver<\/span>(<\/span>sqlserver<\/span>), <\/span>org<\/span>.<\/span>mariadb<\/span>.<\/span>jdbc<\/span>.<\/span>Driver<\/span> (<\/span>mysql<\/span>\/<\/span>MariaDB<\/span>), <\/span>oracle<\/span>.<\/span>jdbc<\/span>.<\/span>driver<\/span>.<\/span>OracleDriver<\/span> (<\/span>oracle<\/span>), <\/span>com<\/span>.<\/span>ibm<\/span>.<\/span>db2<\/span>.<\/span>jcc<\/span>.<\/span>DB2Driver<\/span>(<\/span>DB2<\/span>), <\/span>etc<\/span>...<\/span><\/span>\n<\/span>\n .<\/span>option<\/span>(<\/span>"dbtable"<\/span>, <\/span>"..."<\/span>) - <\/span>indica<\/span> <\/span>o<\/span> <\/span>nome<\/span> <\/span>do<\/span> <\/span>banco<\/span> <\/span>no<\/span> <\/span>SGBD<\/span> <\/span>onde<\/span> <\/span>encontra<\/span>-<\/span>se<\/span> <\/span>a<\/span>(<\/span>s<\/span>) <\/span>tabela<\/span>(<\/span>s<\/span>) <\/span>a<\/span> <\/span>serem<\/span> <\/span>copiada<\/span>(<\/span>s<\/span>). <\/span>Ex<\/span>.: <\/span>meuBanco<\/span>, <\/span>etc<\/span>...<\/span><\/span>\n<\/span>\n .<\/span>load<\/span>() - <\/span>carrega<\/span> <\/span>as<\/span> <\/span>op\u00e7\u00f5es<\/span> <\/span>para<\/span> <\/span>o<\/span> <\/span>leitor<\/span> <\/span>de<\/span> <\/span>dados<\/span> <\/span>da<\/span> <\/span>sess\u00e3o<\/span> <\/span>spark<\/span>.<\/span><\/span>\n<\/span>\n .<\/span>show<\/span>(<\/span>5<\/span>) - <\/span>exibe<\/span> <\/span>as<\/span> <\/span>5<\/span> <\/span>primeiras<\/span> <\/span>linhas<\/span> <\/span>da<\/span> <\/span>tabela<\/span> <\/span>em<\/span> <\/span>quest\u00e3o<\/span><\/span>\n<\/span>\n .<\/span>write<\/span>.<\/span>mode<\/span>(<\/span>"overwrite"<\/span>).<\/span>saveAsTable<\/span>(<\/span>"esquema.nome_da_tabela"<\/span>) - <\/span>realiza<\/span> <\/span>a<\/span> <\/span>escrita<\/span> <\/span>da<\/span> <\/span>tabela<\/span> <\/span>no<\/span> <\/span>BigData<\/span><\/span>\n   <\/span>A<\/span> <\/span>op\u00e7\u00e3o<\/span> <\/span>"overwrite"<\/span> <\/span>indica<\/span> <\/span>que<\/span> <\/span>qualquer<\/span> <\/span>tabela<\/span>\/<\/span>arquivo<\/span> <\/span>anterior<\/span> <\/span>com<\/span> <\/span>mesmo<\/span> <\/span>nome<\/span> <\/span>ser\u00e1<\/span> <\/span>sobrescrito<\/span>. <\/span>Ao<\/span> <\/span>utilizar<\/span> <\/span>este<\/span> <\/span>modo<\/span> <\/span>de<\/span> <\/span>grava\u00e7\u00e3o<\/span>, <\/span>o<\/span> <\/span>Spark<\/span> <\/span>excluir\u00e1<\/span> <\/span>o<\/span> <\/span>arquivo<\/span> <\/span>existente<\/span> <\/span>ou<\/span> <\/span>deletar\u00e1<\/span> <\/span>a<\/span> <\/span>tabela<\/span> <\/span>existente<\/span> <\/span>no<\/span> <\/span>Bigdata<\/span> <\/span>antes<\/span> <\/span>de<\/span> <\/span>gravar<\/span> <\/span>a<\/span> <\/span>nova<\/span> <\/span>c\u00f3pia<\/span>.<\/span><\/span>\n<\/span>\n .<\/span>saveAsTable<\/span>(<\/span>"esquema.nome_da_tabela"<\/span>) - <\/span>indica<\/span> <\/span>o<\/span> <\/span>nome<\/span> <\/span>da<\/span> <\/span>tabela<\/span> <\/span>que<\/span> <\/span>ser\u00e1<\/span> <\/span>gravada<\/span> <\/span>no<\/span> <\/span>Big<\/span> <\/span>Data<\/span>;  <\/span><\/span>\n    <\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Mais op\u00e7\u00f5es de escrita podem se encontradas na refer\u00eancia abaixo :
https:\/\/sparkbyexamples.com\/spark\/spark-write-modes-explained\/<\/p>\n\n\n\n
A seguir encontra-se um exemplo de acesso a um banco de dados MS-SqlServer :<\/p>\n\n\n\n
<\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
jdbcDF<\/span> = <\/span>spark<\/span>.<\/span>read<\/span> \\<\/span><\/span>\n    .<\/span>format<\/span>(<\/span>"jdbc"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"url"<\/span>, <\/span>"jdbc:sqlserver:\/\/10.206.122.31:2733;trustServerCertificate=true;databaseName=sigeon"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"user"<\/span>, <\/span>"rodrigo.furtado"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"password"<\/span>, <\/span>"XXXXXX"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"driver"<\/span>, <\/span>"com.microsoft.sqlserver.jdbc.SQLServerDriver"<\/span>) \\<\/span><\/span>\n    .<\/span>option<\/span>(<\/span>"dbtable"<\/span>, <\/span>"dbo.PAP_uf"<\/span>) \\<\/span><\/span>\n    .<\/span>load<\/span>()<\/span><\/span>\njdbcDF<\/span>.<\/span>show<\/span>(<\/span>5<\/span>)<\/span><\/span>\njdbcDF<\/span>.<\/span>write<\/span>.<\/span>mode<\/span>(<\/span>"overwrite"<\/span>).<\/span>saveAsTable<\/span>(<\/span>"sbx_ligo.PAP_uf"<\/span>)<\/span><\/span><\/code><\/pre><\/div>\n\n\n\n
Apo\u015b configurar corretamente os par\u00e2metros do c\u00f3digo anterior de acordo com o ambiente de onde deseja-se copiar as tabelas, executar o mesmo (Shift+Enter). Se nenhum erro ocorrer haver\u00e1 um retorno semelhante a figura a seguir:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
Pronto, a importa\u00e7\u00e3o da tabela para a estrutura do Big Data foi realizada.<\/p>\n\n\n\n
\"\"
Autor: Rodrigo Peres Franco Furtado<\/figcaption><\/figure>\n\n\n\n
<\/p>\n\n\n\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Nesse pequeno tutorial demonstrar-se-\u00e1 o procedimento para copia de dados de tabelas de um BD relacional para a estrutura de dados do ambiente de Big Data Cloudera. Esta tarefa ser\u00e1 realizada com a utiliza\u00e7\u00e3o do PySpark via JupyterNotebook dispon\u00edvel na su\u00edte do Big Data da Supadia\/SGG. \u00c9 necess\u00e1rio ter acesso ao Jupyter Notebook do Big Data. O mesmo encontra-se no endere\u00e7o abaixo: https:\/\/jupyter.bigdata.go.gov.br\/jupyter Para a realiza\u00e7\u00e3o da tarefa o usu\u00e1rio dever\u00e1 estar logado no ambiente. 1 \u2013 Configura\u00e7\u00e3o do ambiente para a correta conex\u00e3o com o Cloudera. Na tela inicial do Jupyter escolher a op\u00e7\u00e3o Notebook->Python 3(ipykernel) Ap\u00f3s clicar no \u00edcone anterior ser\u00e1 aberta uma nova Aba com um notebook em branco: Colar o c\u00f3digo abaixo na c\u00e9lula edit\u00e1vel Ap\u00f3s colar o c\u00f3digo a tela ficar\u00e1 semelhante a seguir: Executar o c\u00f3digo colado acima utilizando o bot\u00e3o (ou utilizando Shift+Enter) po\u015b a execu\u00e7\u00e3o ser\u00e1 criado o arquivo \u201c.sparkmagic\/config.json\u201d no seu home Se nenhum erro ocorreu o ambiente est\u00e1 apto a conectar e criar sess\u00f5es Spark no Cloudera. 2 \u2013 Cria\u00e7\u00e3o de uma sess\u00e3o Spark no Hadoop Na tela inicial do Jupyter criar um novo \u201cLauncher\u201d. Isso pode ser feito no menu File->New Launcher,, ou com o bot\u00e3o \u201c+\u201d no topo esquerdo da janela logo abaixo do Menu principal, ou pode ser aberto diretamente pela combina\u00e7\u00e3o de teclas Ctrl+Shift+L. ou Uma nova Aba ser\u00e1 aberta como a seguir: Criar um novo notebook PySpark. Para isso, selecionar \u201cPySpark\u201d na se\u00e7\u00e3o \u201cNotebook\u201d do Launcher (criado no passo anterior) como mostrado abaixo: Ser\u00e1 exibida uma nova aba com um notebook PySpark como mostrado abaixo: No campo edit\u00e1vel, digitar (ou copiar e colar) o c\u00f3digo abaixo para criar uma sess\u00e3o Spark: Ap\u00f3s a inclus\u00e3o do c\u00f3digo acima a tela dever\u00e1 ser semelhante a mostrada a seguir: Executar o c\u00f3digo anterior. Utilizar o bot\u00e3o como mostrado a seguir (ou usar a combina\u00e7\u00e3o de teclas Shift+Enter com a c\u00e9lula em quest\u00e3o selecionada): Aguardar alguns instantes. Uma nova sess\u00e3o ser\u00e1 criada no servidor. Ser\u00e1 exibida uma mensagem semelhante \u00e0 seguinte: 3 \u2013 Copiar uma tabela do seu BD relacional Ap\u00f3s o passo 2 na mesma tela em que foi criada a sess\u00e3o Spark, copiar e colar o c\u00f3digo abaixo na pr\u00f3xima linha edit\u00e1vel (se n\u00e3o houver nenhuma c\u00e9lula acrescentar. Utilizar o bot\u00e3o \u201c+\u201d como mostrado na figura seguinte): Copiar e colar o c\u00f3digo seguinte: Detalhamento do c\u00f3digo acima que dever\u00e1 ser adaptado ao ambiente de BD relacional de onde deseja-se copiar as tabelas. Na primeira linha executa-se a importa\u00e7\u00e3o da lib necess\u00e1ria para que se possa ler da base de dados relacional e escrever no cloudera. Na segunda linha cria-se uma vari\u00e1vel com uma inst\u00e2ncia de sess\u00e3o Spark (j\u00e1 instanciada na primeira linha de c\u00f3digo) a qual ser\u00e1 o objeto com as fun\u00e7\u00f5es necess\u00e1rias para realizar a tarefa de c\u00f3pia. Caso n\u00e3o haja sess\u00e3o ela ser\u00e1 criada com o comando .getOrCreate() Os par\u00e2metros das linhas da fun\u00e7\u00e3o read da vari\u00e1vel spark: Mais op\u00e7\u00f5es de escrita podem se encontradas na refer\u00eancia abaixo :https:\/\/sparkbyexamples.com\/spark\/spark-write-modes-explained\/ A seguir encontra-se um exemplo de acesso a um banco de dados MS-SqlServer : Apo\u015b configurar corretamente os par\u00e2metros do c\u00f3digo anterior de acordo com o ambiente de onde deseja-se copiar as tabelas, executar o mesmo (Shift+Enter). Se nenhum erro ocorrer haver\u00e1 um retorno semelhante a figura a seguir: Pronto, a importa\u00e7\u00e3o da tabela para a estrutura do Big Data foi realizada.  <\/p>\n","protected":false},"author":713,"featured_media":205159,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":"","_links_to":"","_links_to_target":""},"categories":[54,26],"tags":[27],"class_list":["post-203516","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-programacao","category-publicacoes-ligo","tag-big-data"],"rttpg_featured_image_url":{"full":["https:\/\/goias.gov.br\/ligo\/wp-content\/uploads\/sites\/61\/2025\/03\/Imagem-jupyter-reduzida.png",200,260,false],"landscape":["https:\/\/goias.gov.br\/ligo\/wp-content\/uploads\/sites\/61\/2025\/03\/Imagem-jupyter-reduzida.png",200,260,false],"portraits":["https:\/\/goias.gov.br\/ligo\/wp-content\/uploads\/sites\/61\/2025\/03\/Imagem-jupyter-reduzida.png",200,260,false],"thumbnail":["https:\/\/goias.gov.br\/ligo\/wp-content\/uploads\/sites\/61\/2025\/03\/Imagem-jupyter-reduzida-150x150.png",150,150,true],"medium":["https:\/\/goias.gov.br\/ligo\/wp-content\/uploads\/sites\/61\/2025\/03\/Imagem-jupyter-reduzida.png",200,260,false],"large":["https:\/\/goias.gov.br\/ligo\/wp-content\/uploads\/sites\/61\/2025\/03\/Imagem-jupyter-reduzida.png",200,260,false],"1536x1536":["https:\/\/goias.gov.br\/ligo\/wp-content\/uploads\/sites\/61\/2025\/03\/Imagem-jupyter-reduzida.png",200,260,false],"2048x2048":["https:\/\/goias.gov.br\/ligo\/wp-content\/uploads\/sites\/61\/2025\/03\/Imagem-jupyter-reduzida.png",200,260,false]},"rttpg_author":{"display_name":"C\u00e9sar Augusto","author_link":"https:\/\/goias.gov.br\/ligo\/author\/cesarpacheco\/"},"rttpg_comment":0,"rttpg_category":"Programa\u00e7\u00e3o<\/a> Publica\u00e7\u00f5es Ligo<\/a>","rttpg_excerpt":"Nesse pequeno tutorial demonstrar-se-\u00e1 o procedimento para copia de dados de tabelas de um BD relacional para a estrutura de dados do ambiente de Big Data Cloudera. Esta tarefa ser\u00e1 realizada com a utiliza\u00e7\u00e3o do PySpark via JupyterNotebook dispon\u00edvel na su\u00edte do Big Data da Supadia\/SGG. \u00c9 necess\u00e1rio ter acesso ao Jupyter Notebook do Big…","_links":{"self":[{"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/posts\/203516","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/users\/713"}],"replies":[{"embeddable":true,"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/comments?post=203516"}],"version-history":[{"count":2,"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/posts\/203516\/revisions"}],"predecessor-version":[{"id":205160,"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/posts\/203516\/revisions\/205160"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/media\/205159"}],"wp:attachment":[{"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/media?parent=203516"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/categories?post=203516"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/goias.gov.br\/ligo\/wp-json\/wp\/v2\/tags?post=203516"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}