Importanto,Lendo,Exportando dados via EXTERNAL TABLES

Versão para impressãoEnviar para amigoVersão PDF

O recurso de EXTERNAL TABLES já existia no Informix XPS , que é uma versão antiga e especifica do Informix para Data Warehouse. Existem vários recursos interessante nesta versão (XPS) que nunca haviam sido implementadas na versão IDS.
Na versão IDS a utilização não é 100% igual ao do XPS mas é bem próximo e traz inumeras vantagens

Eu tinha na minha lista de to-do (pendencias a fazer) escrever um artigo de como acessar um arquivo texto fora do banco de dados como se fosse uma tabela, porém com a falta de tempo não tinha feito até este momento. Mas eu pretendia demonstrar isso utilizando Datablades, bom, ao a IBM disponibilizar o Release 11.50 xC6 ela incluiu este recurso que praticamente teria "matado" meu artigo.
A utilização de acesso a arquivos textos como tabelas via Datablade , atualmente possui algumas vantagens, como personalizar uma criação de indice, mas isso não é algo simples de fazer. Neste momento vamos focar no EXTERNAL TABLES que já é meio caminho andado.

Apesar de simples, existe muitas situações e detalhes para explicar sobre este recurso, portanto tentarei listar as informações mais relevantes.

  1. O que pode, o que não pode
  2. Recursos
    1. Detalhes
  3. Sintaxe básica
  4. Exemplos
    1. Carregando um arquivo delimitado
    2. Descarregando/carregando um arquivo no formato fixo
    3. Trabalhando com formatos binários
  5. Performance
    1. Teste de Carga
    2. Teste de Descarga


O que pode, o que não pode


É possível executar um select em uma external table , fazendo relacionamentos com outras tabelas de modo transparente. Porém todos os acessos serão feitos de modo sequencial, uma vez que não existe indice para este tipo de tabela.
É possível exportar para um arquivo texto o resultado de um select facilmente (uma alternativa ao UNLOAD, HPL)
Não é possível criar indices.
Não é possível criar uma replicação em ambientes com HDR, SDS, RSS.
Não é possível inserir, atualizar ou apagar um unico registro.
Em ambientes de alta disponibilidade, nos servidores secundários é possível ler uma EXTERNAL TABLE, porém se tentar descarregar os dados nela, os arquivos serão criados no servidor secundário.


Recursos


  • Importar dados de um arquivo externo (file system)
  • Exportar dados para um arquivo externo (file system)
  • Ler/gravar em 3 formatos de arquivo: Delimitado, Fixo e binário-informix
  • Importar/exportar para mais de um arquivo/PIPE, utilizando paralelismo
  • Para carga de mais de um arquivo é possivel utilizar uma mascara para evitar inumeras declarções.
  • A carga pode ser utilizando o padrão DELUXE ou EXPRESS igual ao HPL .
  • Registros inválidos podem ser filtrados para um Reject File igual ao HPL.
  • Importar/exportar campos BLOB/CLOB
  • Definir uma representação par valores nulos na importacao/exportacao.
  • Especificar vários parametros como: formato de data, separador de casa decimal para valor numerico, caracter separador de campo, separador de registro, caracter de escape, quantidade estimada de resgistros, quantidade de erros tolerados, arquivo de registros com erros, definir valores defauls para campos nulos durante a carga
  • Novo tipo de VP é utilizado, o FIFO VP , utilizado apenas para acesso arquivos do tipo PIPE.
  • Suporta as sintaxes: CREATE EXTERNAL, INSERT...SELECT, SELECT...INTO EXTERNAL..., SELECT, DROP TABLE, GRANT (apenas SELECT e INSERT), REVOKE, CREATE SYNONYM, CREATE SCHEMA, RENAME COLUMN, RENAME TABLE.
  • Quando utilizado o comando SELECT...INTO EXTERNAL , a tabela especificada é criada de modo persistente (sobrevive entre as sessões).

Detalhes


  • Os dados não são bufferizados, a cada select, um novo acesso é realizado no arquivo.
  • Reject files sao sobrescritos em cada load
  • O formato FIXED SIZE não é suportado com o comando SELECT...INTO EXTERNAL
  • Para carga de arquivo FIXED obrigatoriamente todas as colunas na external precisam especificar 2 tipos de dados, um o tipo real e outro chamado EXTERNAL CHAR, utilizado para realizar a conversão para o modo FIXED SIZE.
  • Ao criar uma EXTERNAL baseada em uma tabela já existente, ela automaticamente herda as constrains NOT NULL e DEFAULT
  • As demais constraints de check, primary keys, foreing keys não são herdadas
  • A clausula SAMEAS *não* pode ser utilizado no formato FIXED SIZE.
  • Para uma lista completa das restrições das EXTERNALS TABLES veja no manual

Sintaxe básica


Esta sintaxe é o modo mais básico de trabalhar com ela, para maiores detalhes, favor consultar o manual.
    CREATE EXTERNAL TABLE [nova_tabela] 
    [SAMEAS [tabela_base] | [(coluna tipo EXTERNAL CHAR(n), ...)]]
    USING (
      DATAFILES ( [arquivo 1], [arquivo 2],...,[arquivo N] ),
      FORMAT [FIXED|INFORMIX|DELIMITED],
      [EXPRESS|DELUXE],
      DBDATE [formato],
      DBMONEY [formato],
      DELIMITER [delimitador_coluna],
      RECORDEND [delimitador_linha],
      MAXERRORS [maximo_registros_com_erros],
      REJECTFILE [arquivo_de_registros_rejeitados],
      ESCAPE,
      [NUMROWS|SIZE] [estimativa_qtd_linhas]
    )
    
    # Criação de uma EXTERNAL TABLE baseado na saida de um
    # SELECT (igual ao INTO TEMP)
    SELECT ... FROM ... INTO EXTERNAL [nova_tabela]
    USING (
      DATAFILES ( [arquivo 1], [arquivo 2],...,[arquivo N] ),
      FORMAT [INFORMIX|DELIMITED],
      [EXPRESS|DELUXE],
      DBDATE [formato],
      DBMONEY [formato],
      DELIMITER [delimitador_coluna],
      RECORDEND [delimitador_linha],
      MAXERRORS [maximo_registros_com_erros],
      REJECTFILE [arquivo_de_registros_rejeitados],
      ESCAPE,
      [NUMROWS|SIZE] [estimativa_qtd_linhas]
    )
    


Exemplos


Nos exemplos a seguir , irei utilizar a estrutura da tabela "fs_full" e dados do arquivo exemplificado no tutorial Montando uma Base de teste .

Carregando um arquivo delimitado


    # Para realizar a carga, já possuo o arquivo "dados.unl" criado (veja no tutorial 
    # como cria-lo) , para exemplificar a carga em paralelo, dividi o arquvo em 3, utilizando o 
    # comando split.
    /tmp$ ls -l dados.unl
    -rw-r----- 1 ix_dbsa ix_dbsa 79791527 2010-02-04 10:12 dados.unl
    /tmp$ split -l 117000 dados.unl
    /tmp$ ls -l x* dados.unl
    -rw-r----- 1 ix_dbsa ix_dbsa 79791527 2010-02-04 10:12 dados.unl
    -rw-r----- 1 ix_dbsa ix_dbsa 30594817 2010-02-04 10:51 xaa
    -rw-r----- 1 ix_dbsa ix_dbsa 26414984 2010-02-04 10:51 xab
    -rw-r----- 1 ix_dbsa ix_dbsa 22781726 2010-02-04 10:51 xac
    /tmp$ wc x* dados.unl
       117000    501138  30594817 xaa
       117000    468152  26414984 xab
       101799    441702  22781726 xac
       335799   1410992  79791527 dados.unl
    
    # Neste ponto, já tenho os dados pronto para carga, agora precisamos apenas
    # recriar as tabelas (fs_full e ex_fs_full) pois no meu caso elas já existiam
    # devido os testes realizado anteriormente no meu micro.
    # 
    # ATENCAO: Para boa performance, utilizando o recurso de light appends
    # e evitar problemas com Logical Log, é muito importante criar ou alterar a tabela 
    # destino no modo RAW
    $ cat fs_full.sql  ext.sql  | dbaccess -e myfs_db
    Database selected.
    
    drop table fs_full;
    Table dropped.
    
    CREATE RAW TABLE fs_full
      (
        diretorio         NCHAR(300),
        nome_arquivo      NCHAR(100) not null ,
        path_nome_arquivo NCHAR(400),
        link_destino      NCHAR(400),
        permissao_octal   SMALLINT,
        permissao_str     NCHAR(10),
        filesystem_armazenado NCHAR(10),
        tipo1             NCHAR(1),
        tipo2             NCHAR(1),
        owner_user        NCHAR(15) not null ,
        owner_group       NCHAR(15) not null ,
        owner_uid         INTEGER not null ,
        owner_gid         INTEGER not null ,
        tamanho_bytes     BIGINT,
        inode             BIGINT check ( inode > 0),
        ultimo_acesso     DATETIME YEAR TO FRACTION(3),
        ultima_mod_status DATETIME YEAR TO FRACTION(3),
        ultima_mod_dados  DATETIME YEAR TO FRACTION(3),
        device_number     INTEGER default 0
      )
    extent size 160000 next size 10240 LOCK MODE ROW ;
    Table created.
    
    drop table ex_fs_full;
    Table dropped.
    
    create external table ex_fs_full SAMEAS fs_full
    USING (
      DATAFILES ( 'DISK:/tmp/xaa',
                  'DISK:/tmp/xab',
                  'DISK:/tmp/xac'),
      FORMAT 'DELIMITED',
      REJECTFILE '/tmp/dados.rej',
      MAXERRORS 10,
      EXPRESS
    )
    Table created.
    Database closed.
    
    # Forço um checkpoint para evitar qualquer problema de performance
    # na carga devido a necessidade de gerar o before images
    $ onmode -c
    
    # Agora basta executar a carga do arquivo. No comando abaixo estou
    # lendo a EXTERNAL TABLE e transferindo os dados para a tabela atual
    $ time { echo "set explain on; insert into fs_full select * from ex_fs_full" | \
     dbaccess myfs_db; }
    
    Database selected.
    Explain set.
    335799 row(s) inserted.
    Database closed.
    
    real    0m28.655s
    user    0m0.013s
    sys     0m0.025s
    
    # Para monitorar a carga você pode utilizar alguns comandos do onstat:
    #
    # Quando a tabela destino é criada em modo RAW os dados são gravados em light appends
    # o que pode ser monitorado pelo comando abaixo.
    # Se sua tabela for fragmentada/particionada, ative o PDQPRIORITY com valor igual
    # a 1 para realizar a gravação em paralelo.
    $ onstat -g lap
    IBM Informix Dynamic Server Version 11.50.UC6DE -- On-Line -- Up 01:25:37 -- 353916 Kbytes
    Light Append Info
    session id  address   cur_ppage   la_npused   la_ndata    la_nrows    bufcnt
    139         4f49fce8  b82a        18          22906       22900       68701
    
    # Para visualizar a leitura dos arquivos utilize o comando abaixo:
    # Se o PDQPRIORITY estiver ativado e a tabela destino for particionado,
    # a EXTERNAL TABLE irá ler de mais de um arquivo em paralelo, o que você 
    # pode visualizar através deste comando.
    $ onstat -g iof
    ...
    23  sqexplain.out
    
    24  /tmp/xac
    

Descarregando/carregando um arquivo no formato fixo


Arquivos gerados no modo FIXED SIZE não possuem caracter separador de registro, sendo assim todos os registros ficam sequencialmente na mesma linha. Na versão atual (11.50 xC6) o parâmetro RECORDEND não é suportado para este tipo de formatação.
    # Para o utilizar o formato fixo, especifique as colunas no 
    # tipo de dado original e no tipo CHAR que será exportado 
    $ cat ext2.sql | dbaccess -e myfs_db
    Database selected.
    
    CREATE EXTERNAL TABLE ex2_fs_full  (
      arq char(300) EXTERNAL CHAR(100),
      mod smallint  EXTERNAL CHAR(4),
      fs  nchar(10) EXTERNAL CHAR(10))
    USING (
      DATAFILES ( 'DISK:/tmp/fixed.unl'),
      FORMAT 'FIXED',
      MAXERRORS 10,
      EXPRESS
    );
    Table created.
    Database closed.
    
    # Exporto os dados assim como feito anteriormente, porém aqui
    # especifico apenas as colunas desejadas (compativel com a 
    # EXTERNAL TABLE criada)
    $ echo "insert into ex2_fs_full select
      trim(nome_arquivo), permissao_octal, filesystem_armazenado 
      from fs_full" | dbaccess myfs_db
    Database selected.
    335799 row(s) inserted.
    Database closed.
    
    # Para visualizar o conteúdo do arquivo, utilizo o comando head
    # especificando para exibir apenas os primeiros 500 bytes.
    # Repare que os registros estão colados um ao outro.
    # OBSERVAÇÃO: Devido a falta da quebra de linha, você terá que 
    # rolar o código para a direito para visualizar os dados.
    $ head -c 500 fixed.unl
    suse-build-key.gpg                                                                                   644ext4      dumpsigs                                                                                             755ext4      pubring.gpg                                                                                          644ext4      pubring.gpg~                                                                                         644ext4      perl.req  
    
    # Para carregar o arquivo , utiliza-se a mesma forma já exemplificada
    # onde neste caso o unico cuidado é em especificar apenas as colunas
    # certas.
    $ echo "insert into fs_full (nome_arquivo, permissao_octal,filesystem_armazenado) 
       select * from ex2_fs_full" | dbaccess myfs_db; 
    Database selected.
    335799 row(s) inserted.
    Database closed.
    

Trabalhando com formatos binários


A geração e carga de arquivos binários é normalmente a melhor opção quando é preciso mover dados entre a mesma versão do IDS, SO e arquitetura pois além de ser o meio mais rápido de movimentação de modo flexivel (com filtros, novas partições e etc) ela é 100% compátivel com o modo binário do HPL.
    # Crio uma nova tabela com formação do tipo INFORMIX , repare
    # que não há mais nenhuma diferença de quando utilizado o DELIMTED
    $ cat ext3.sql  | dbaccess -e myfs_db
    Database selected.
    
    CREATE EXTERNAL TABLE ex3_fs_full SAMEAS fs_full
    USING (
      DATAFILES ( 'DISK:/tmp/bin.unl'),
      FORMAT 'INFORMIX',
      REJECTFILE '/tmp/dados.rej',
      MAXERRORS 10,
      EXPRESS
    )
    Table created.
    Database closed.
    
    # Exporto os dados da tabela no formato binário.
    $ echo "insert into ex3_fs_full select * from fs_full" | dbaccess myfs_db;
    Database selected.
    335799 row(s) inserted.
    Database closed.
    
    # Uma das desvantagens de gerar o arquivo no formato binário é que 
    # ele gera cada registro com exatamente o ROW SIZE da estrutura, o que
    # se você comparar com uma tabela no formato texto, normalmente fica bem
    # maior. Mas isso não é regra e sim apenas o que é mais comum de ocorrer
    $ ls -oh dados.unl bin.unl
    -rw-r----- 1 ix_dbsa 421M 2010-02-04 15:45 bin.unl
    -rw-r----- 1 ix_dbsa  77M 2010-02-04 15:09 dados.unl
    
    # Aqui executo a carga dos dados, igual ao utilizado com as 
    # outras formatações do EXTERNAL TABLE
    $ echo "insert into fs_full select * from ex3_fs_full" | dbaccess myfs_db;
    Database selected.
    335799 row(s) inserted.
    Database closed.
    


Performance


Agora chegamos ao ponto que mais achei interessante deste novo recurso, Performance!!
Em vários testes executado aqui, quando trabalhado no formato delimitado , obtive uma melhora de 20% no tempo total se comparado ao HPL.
Quando trabalhado com o formato binário, consegui diminuir o tempo de um processo em *quase* 3 vezes!

Todos os testes que executei foram com intuito de evitar algum gargalo de I/O , onde os arquivos salvos no file system eram salvos em TMPFS (que é um filesystem especial que fica 100% na memória RAM). Desta forma mantive o gargalo de I/O apenas nos chunks do banco (que estão em um HD externo com um I/O de 20 MB/s, file system EXT2).

Mas há alguns poréns, sempre há "um porém". No caso em que diminui em quase 3 vezes o tempo do processo, descobri que tive uma boa ajuda do cache de file system, pois meus chunks estão alocados diretamente em um EXT2 , sendo assim eles estão no cache.
Quando forcei o IDS a trabalhar com DIRECT_IO (ignorando estes caches) ou se eu forçava um flush do cache do file system antes de executar meus testes o IDS tinha que reler o device, fazendo com que o tempo subisse , mas ainda assim as EXTERNALS TABLES sempre mativeram uma performance 20% mais rápida que o HPL.

Veja alguns numeros de testes em uma tabela com ROW SIZE de 1312 e 335799 registros , totalizando 440568288 bytes.

Teste de Carga


  1. Formato DELIMITADO por "|", DIRECT_IO=0, database em NO_LOG, Tabela em modo Standard com +2 CONSTRAINT
    - SQL LOAD : 3m19seg
    - HPL : 44seg
    - EXTERNAL DELUXE : 3m46seg
    - EXTERNAL EXPRESS : 3m08seg
  2. Tabela alterada para modo RAW, as CONSTRAINT foram excluidas, database em LOG e NO_LOG
    - SQL LOAD : 2m39seg
    - HPL : 33seg
    - EXTERNAL EXPRESS :22seg
  3. Formato de teste alterado para binário
    - HPL : 40seg
    - EXTERNAL EXPRESS :28seg

Teste de Descarga


  1. Formato binário, sem cache de File System
    - HPL : 30seg
    - EXTERNAL EXPRESS :22seg
  2. com cache de File System, resultados sempre a partir da 2a execução
    - HPL : 22seg
    - EXTERNAL EXPRESS :9seg

Desta maneira chegamos a conclusão de que a utilização de EXTERNAL TABLES com certeza é bem mais otimizada e com menos overhead do que a utilização do HPL. Também vemos que provavelmente ela aproveita mais o cache de file system gerando boa vantagem sobre o HPL neste tipo de ambiente.

4
Média: 4 (2 votos)
Sua avaliação: Nenhum

Comentar

O conteúdo deste campo é privado não será exibido ao público.
  • Endereços de páginas de internet e emails viram links automaticamente.
  • Você pode usar tags BBCode no texto.
  • Tags HTML permitidas: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

Mais informações sobre as opções de formatação

CAPTCHA
Este teste é para bloquear programas automatizados e previnir spams
CAPTCHA de Imagem
Digite o texto exibido na imagem.