[Resolvido] Oracle Database - Trabalhando com dados externos util

[RSS iMasters 5]

Neste artigo, abordarei orecurso External Tables do Oracle Database, que permite ao DBAconsultar e importar dados externos em um banco de dados Oracle, exportar dadospara um outro banco de dados Oracle e também realizar operações de ETL com oOracle Database.

 

O ExternalTables é um dos tópicos cobrados nos exames de certificação Oracle 1Z0-052(OCA Oracle Database 11g: Administration I), 1Z0-042(OCA Oracle Database 10g: Administration I) e 1Z0-047(OCE SQL Expert).

 

 

 

 

1.Tabelas externas

Tabela externa (External Table)é um recurso bastante útil do software Oracle Database, presente a partir daversão 9i e melhorado na seguinte, a 10g. Com tabelas externas, é possível:

 

• Consultar dados externos ao banco de dadosOracle sem ter que importá-los;
• Importar dados externos para dentro do banco dedados Oracle;
• Realizar operações de ETL em bancos de dadosOracle;
• Mover (ou compartilhar) dados entre bancos dedados Oracle.

Uma tabela externa é umatabela somente para leitura, criada internamente no banco de dados Oracle, talqual ocorre com uma tabela comum. Uma tabela externa é, sobalguns aspectos, semelhante a uma View somente para leitura. Seus metadados residem dentrodo banco de dados Oracle. Porém, os seus dados ficam armazenados externamenteem um arquivo texto, localizado no sistema de arquivos do servidor. Tabelasexternas apresentam algumas limitações:

 

• Não é possível criar constraints em tabelasexternas;
• Não é possível criar índices em tabelasexternas;
• Não é possível criar campos do tipo LOB emtabelas externas;
• Não é possível definir um campo de uma tabelaexterna como UNUSED;
• Não é possível executar instruções DML (Insert, Update, Delete) em tabelasexternas.

Só é possível consultar dados armazenados em tabelasexternas, e elas também podem ser criadas com instruções do tipo CTAS,com a finalidade de exportação de dados.

 

 

2.Cenários típicos para utilização de tabelas externas

Um cenário típico parautilização de tabelas externas é quando há a necessidade de ler dadosprovenientes de outras fontes de dados, como os dados provenientes deum mainframe, exportados como um arquivo texto em formato SDF ou CSV, residenteno sistema de arquivos do servidor.

 

Outro cenário típico é quandohá a necessidade de exportar (ou compartilhar) dados entre bancos de dadosOracle. Neste cenário, cria-se uma tabela externa em um banco de dados Oraclecom uma instrução do tipo CTAS. A tabela externa criada poderá ser acessada poroutro banco de dados Oracle para consulta. Esta é uma das formas de exportação/importação (ou compartilhamento) dedados entre bancos de dados Oracle, porém, não é a única.

 

Tabelas externas são tambémuma opção para operações de ETL. Os dados ?brutos? podem ser salvos em arquivosde texto (com formatos SDF ou CSV) no sistema de arquivos do servidor e, a partirdestes, é possível criar tabelas externas no banco de dados Oracle e acessá-loscomo tabelas ?normais?. Basta, então, realizar as transformações necessáriasnos dados ?brutos?, beneficiando-se de toda a funcionalidade das linguagens SQLe PL/SQL do Oracle, e carregar os dados processados nas tabelas apropriadas.

 

 

3.O objeto Directory

Para poder ler dados a partirde tabelas externas, ou para poder exportar dados a partir do banco de dadosOracle para tabelas externas, é necessário, antes, criar um objeto Directory nobanco de dados Oracle.

 

O objeto Directory é um alias criado no banco de dados Oracle para referenciar um diretóriofísico do sistema de arquivos do servidor. Um objeto Directory é um objeto emnível de banco de dados e não um objeto de esquema.

 

Para criar objetos Directory énecessário ter o privilégio de sistema CreateAny Directory que, por padrão, somente os usuários SYS e SYSTEM possuem.Este privilégio de sistema pode ser concedido a outros usuários do banco dedados Oracle, mas, recomendo que conceda este privilégio de sistema com bastantecautela, pois ele pode fragilizar a segurança dos dados, especialmente no quetange à confidencialidade dos mesmos. Para criar objetos Directory, utilize aseguinte instrução:

 

Create [Or Replace] Directory <Alias_Diretorio> As <Diretorio_Fisico_Sistema_Arquivos>; Onde:

 

• [Or Replace]: (cláusula opcional) quandoutilizada, altera a definição de um objeto Directory (caso este já exista nobanco de dados Oracle);
• <Alias_Diretorio>: (cláusulaobrigatória) é o nome (apelido) dado ao objeto Directory seguindo as convençõesde nomenclatura para objetos de banco de dados Oracle, podendo ter até 30caracteres de comprimento;

• <Diretorio_Fisico_Sistema_Arquivos>: (cláusula obrigatória) é o caminhocompleto do diretório físico no sistema de arquivos do servidor, é case-sensitive.

 Exemplos:

 

Em sistemas GNU/Linux:

 

 Create Directory Dados_Logistica As ?/media/Logistica/ArquivosTexto?; Create Or Replace Directory Dados_Logistica As ?/media/Logistica/ArquivosTexto?;Em sistemas MS Windows:

 

 Create Directory Dados_Logistica As ?D:LogisticaArquivosTexto?;Create Or Replace Directory Dados_Logistica As ?D:LogisticaArquivosTexto?;O usuário que cria um objeto Directory já tem, porpadrão, os privilégios do READ e WRITE neste objeto. Porém, parapermitir que outro(s) usuário(s) do banco de dados possa(m) consultar, ou criartabelas externas utilizando o objeto Directory criado, é necessário conceder taisprivilégios no objeto Directory a estes usuários. Para conceder taisprivilégios, utilize uma das seguintes instruções DCL, conforme a necessidade:

 

GRANT READ,WRITE ON DIRECTORY <Objeto_Directory> TO <Usuario_BD>;ou

 

GRANT READ ON DIRECTORY <Objeto_Directory> TO <Usuario_BD>;ou

 

GRANT WRITE ON DIRECTORY <Objeto_Directory> TO <Usuario_BD>;

Onde:

 

• <Objeto_Directory>: (cláusulaobrigatória) é o nome do objeto Directory existente no banco de dados;
• <Usuario_BD>: (cláusulaobrigatória) é o nome do usuário do banco de dados ao qual se está concedendo oprivilégio; é possível especificar vários nomes de usuários ao mesmo tempo,bastando separar seus nomes por vírgulas.

Exemplos:

 

 GRANT READ,WRITE ON DIRECTORY Dados_RH TO HR;GRANT READ ON DIRECTORY Dados_Logistica TO OE;

 GRANT WRITE ON DIRECTORY Dados_Vendas TO BI;Ao criar um objeto Directory, o software Oracle Databasenão gera um erro, caso o diretório físico referenciado pelo objeto não existe nosistema de arquivos do servidor. Portanto, é possível criar um objeto Directoryantes de criar o diretório físico referenciado por este objeto no sistema dearquivos do servidor. Porém, quando um objeto Directory for utilizado paraacessar, ou criar uma tabela externa, o software Oracle Database gerará um erro,caso o diretório físico referenciando por este objeto não exista no sistema dearquivos do servidor.

 

Para excluir (dropar) umobjeto Directory do banco de dados Oracle, será necessário ter o privilégio desistema DROP ANY DIRECTORY. Os usuários SYS e SYSTEM, por padrão, já possuemestes privilégios. Este é um privilégio de sistema que eu, particularmente,como DBA, dificilmente concedo a outros usuários do banco de dados. A instruçãoutilizada para excluir um objeto Directory é a seguinte:

 

 DROP DIRECTORY <Objeto_Directory>; Onde:

 

•  <Objeto_Directory>: (cláusulaobrigatória) é o nome do objeto Directory existente no banco de dados Oracle;

Quando um objeto Directory é excluído (dropado) do bancode dados Oracle, o diretório físico referenciado por ele no sistema de arquivosdo servidor (bem como seu conteúdo) não é excluído. Caso queira excluir odiretório físico do sistema de arquivos do servidor, deverá fazê-lomanualmente.

 

 

 4.Criação de tabelas externas para acesso a dados externos

Para criar uma tabela externapara acessar dados armazenados fisicamente em um arquivo localizado no sistemade arquivos do servidor, é necessário, antes, ter um objeto Directory criado nobanco de dados Oracle. Caso o objeto Directory tenha sido criado por outrousuário, será necessário ter os privilégios READ/WRITEneste objeto Directory.

 

É necessário conhecerpreviamente o layout (formato) dos dados contidos no arquivo externo parapoder definir corretamente os parâmetros de acesso (Access Parameters) dainstrução Create Table que criaráa tabela externa no banco de dados Oracle. Os layouts (formatos) mais comunssão:

 

• SDF:registros com tamanho fixo e campos determinados para sua posição;
• CSV: registroscontendo campos de tamanhos variáveis separados por vírgulas;
• TXT:registros contendo campos de tamanhos variáveis, ou fixos que podem serseparados por vírgulas, ou por algum caracter específico, ou por tabulações.

Quando se cria uma tabela externa no banco de dadosOracle, ao especificar seus atributos (campos), somente é permitido declarar onome do campo, seu tipo de dados e seu tamanho. Não é possível criar campos dotipo LOB, ou criar constraints e índices para tabelas externas.

 

Para criar um tabela externa,utiliza-se um dos seguintes drivers do banco de dados Oracle:

 

• ORACLE_LOADER:para acessar dados contidos em arquivo externo com lay-out (formato) SDF, CSVou TXT;
• ORACLE_DATAPUMP:para acessar dados contidos em arquivo externo criado pelo banco de dadosOracle para exportação/ compartilhamento de dados.

4.1.Criação de tabela externa para acessar dados organizados em um arquivo comlayout SDF

 

CREATE TABLE Funcionarios_Externa (

Codigo VARCHAR2(5 BYTE)

,Nome VARCHAR2(30 BYTE)

,DtNasc VARCHAR2(8 BYTE)

,DtContrat VARCHAR2(8 BYTE)

,SalInicial NUMBER(8,2)

,SalAtual NUMBER(8,2)

,DtReajSal VARCHAR2(8 BYTE)

,DtSaida VARCHAR2(8 BYTE)

) ORGANIZATION EXTERNAL (

TYPE ORACLE_LOADER

DEFAULT DIRECTORY Dir_DadosExternos_RH

ACCESS PARAMETERS (

RECORDS DELIMITED BY newline

FIELDS

MISSING FIELD VALUES ARE NULL (

Codigo POSITION(1:5) CHAR

,Nome POSITION(6:35) CHAR

,DtNasc POSITION(36:43) CHAR

,DtContrat POSITION(44:51) CHAR

,SalInicial POSITION(52:59) CHAR

,SalAtual POSITION(60:67) CHAR

,DtReajSal POSITION(68:75) CHAR

,DtSaida POSITION(76:83) CHAR

)

)

LOCATION ('Funcionarios.sdf')

) REJECT LIMIT UNLIMITED;4.2.Criação de tabela externa para acessar dados organizados em um arquivo comlayout CSV

 

CREATE TABLE Funcionarios_Externa (

Codigo VARCHAR2(5 BYTE)

,Nome VARCHAR2(30 BYTE)

,DtNasc VARCHAR2(8 BYTE)

,DtContrat VARCHAR2(8 BYTE)

,SalInicial NUMBER(8,2)

,SalAtual NUMBER(8,2)

,DtReajSal VARCHAR2(8 BYTE)

,DtSaida VARCHAR2(8 BYTE)

) ORGANIZATION EXTERNAL (

TYPE ORACLE_LOADER

DEFAULT DIRECTORY Dir_DadosExternos_RH

ACCESS PARAMETERS (

RECORDS DELIMITED BY newline

SKIP 1

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

MISSING FIELD VALUES ARE NULL

)

LOCATION ('Funcionarios.csv')

) REJECT LIMIT UNLIMITED;4.3.Criação de tabela externa para acessar dados organizados em um arquivo com camposseparados por tabulações

 

CREATE TABLE Funcionarios_Externa (

Codigo VARCHAR2(5 BYTE)

,Nome VARCHAR2(30 BYTE)

,DtNasc VARCHAR2(8 BYTE)

,DtContrat VARCHAR2(8 BYTE)

,SalInicial NUMBER(8,2)

,SalAtual NUMBER(8,2)

,DtReajSal VARCHAR2(8 BYTE)

,DtSaida VARCHAR2(8 BYTE)

) ORGANIZATION EXTERNAL (

TYPE ORACLE_LOADER

DEFAULT DIRECTORY Dir_DadosExternos_RH

ACCESS PARAMETERS (

RECORDS DELIMITED BY newline

SKIP 1

FIELDS TERMINATED BY 't' OPTIONALLY ENCLOSED BY '"'

MISSING FIELD VALUES ARE NULL

)

LOCATION ('Funcionarios.txt')

) REJECT LIMIT UNLIMITED;4.4.Criação de tabela externa para acessar dados exportados por um banco de dadosOracle como uma tabela externa utilizando o driver ORACLE_DATAPUMP

 

CREATE TABLE Funcionarios_Externa (

Codigo VARCHAR2(5 BYTE)

,Nome VARCHAR2(30 BYTE)

,DtNasc VARCHAR2(8 BYTE)

,DtContrat VARCHAR2(8 BYTE)

,SalInicial NUMBER(8,2)

,SalAtual NUMBER(8,2)

,DtReajSal VARCHAR2(8 BYTE)

,DtSaida VARCHAR2(8 BYTE)

) ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY Dir_DadosExternos_RH

LOCATION ('Funcionarios.dat')

) REJECT LIMIT UNLIMITED;Após uma tabela externa sercriada com sucesso no banco de dados Oracle, é possível consultar seus dadosnormalmente, como qualquer tabela ?normal?, ou como uma View somente paraleitura. Porém, não épossível executar instruções DML (INSERT, UPDATE, DELETE) em uma tabela externa.

 

Consultando os dados da tabelaexterna Funcionarios_Externa criada:

 

SELECT * FROM Funcionarios_Externa;

 

 

 

5.Criação de tabelas externas para exportação de dados para outro banco de dadosOracle

Para criar uma tabela externapara exportação de dados, é necessário, antes, ter um objeto Directory criadono banco de dados Oracle. Caso esse objeto tenha sido criado por outrousuário do banco de dados Oracle, será necessário ter os privilégios READ/WRITEneste objeto Directory.

 

A criação de uma tabelaexterna, a partir de um banco de dados Oracle, para exportação de dados paraoutro banco de dados Oracle gera um arquivo no sistema de arquivos doservidor, na localização referenciada pelo objeto Directory utilizado. A criação da tabela externa éfeita com uma instrução CTAS, utilizando o driver ORACLE_DATAPUMP. Veja:

 

CREATE TABLE Funcionarios_Exportacao

ORGANIZATION EXTERNAL (

TYPE ORACLE_DATAPUMP

DEFAULT DIRECTORY Dir_DadosExternos_RH

LOCATION ('Funcionarios.dat')

) AS

SELECT *

FROM Funcionarios;

 

Após a tabela externa paraexportação de dados ter sido criada com sucesso, os dados exportados, contidosno arquivo externo (gerados no sistema de arquivos do servidor), poderão seracessados por outro banco de dados Oracle como uma tabela externa criadanaquele outro banco de dados.

 

É possível dropar a tabela externa criada após exportaçãode dados. Ao fazer isso, o arquivo externo gerado não será excluídoautomaticamente. Caso precise excluir esse arquivo, deverá fazê-lo manualmente.Portanto, ao tentar criar novamente a mesma tabela externa para exportação dedados, utilizando o mesmo nome para o arquivo externo a ser gerado no mesmoobjeto Directory, o Oracle Database emitirá uma mensagem de erro, pois jáexiste um arquivo externo com o mesmo nome na localização referenciada peloobjeto Directory.

 

 

6.Importação de dados utilizando tabelas externas

Para importar dados em bancode dados Oracle de destino, basta criar neste uma tabela externa, a partir doarquivo externo que já foi exportado. Feito isso, é só utilizar, por exemplo, ainstrução MERGE para realizar as transformações necessárias nos dadosimportados e carregá-los na tabela de destino. Essa combinação de recursostambém permite realizar operações de ETL. Veja:

 

MERGE INTO Funcionarios F

USING Funcionarios_Externa FE ON (F.Codigo = FE.Codigo)

WHEN MATCHED THEN

UPDATE SET

F.SalAtual = TO_NUMBER(FE.SalAtual)

,F.DtReajSal = TO_DATE(FE.DtReajSal,'YYYYMMDD')

,F.DtSaida = TO_DATE(FE.DtSaida,'YYYYMMDD')

WHEN NOT MATCHED THEN

INSERT (

Codigo

,Nome

,DtNasc

,DtContrat

,SalInicial

,SalAtual

,DtReajSal

,DtSaida

) VALUES (

FE.Codigo

,FE.Nome

,TO_DATE(FE.DtNasc,'YYYYMMDD')

,TO_DATE(FE.DtContrat,'YYYYMMDD')

,FE.SalInicial

,FE.SalAtual

,TO_DATE(FE.DtReajSal,'YYYYMMDD')

,TO_DATE(FE.DtSaida,'YYYYMMDD')

);

 

COMMIT;

 

Consultando os dadosimportados para a tabela Funcionarios:

 

 

 

SELECT * FROM Funcionarios ORDER BY DtContrat ASC;

 

 

 

 

http://imasters.com.br/artigo/22474/oracle/oracle-database-trabalhando-com-dados-externos-utilizando-external-tables