Loading Compressed EBCDIC files to load tables

[nikx_8_7]

Does COPY command supports loading of data from EBCDIC format compressed files from Mainframes directly to vertica tables? If yes, please let me know how we can achieve that.

[jpcavanaugh]

This can be achieved via the User Defined Load in vertica 6. You would just have to write your own parser. If you need assistance, please let me know.

[nikx_8_7]

Yes juniorfoo, i need help in writing the parser for ebcdic files. Can you throw some light on how to write it?

[jpcavanaugh]

I would start reading here - http://my.vertica.com/docs/6.0.0/HTML/i ... #17840.htm

Contact me via private message for further help around the code itself (including creating the entire thing).

[ramvertica]

Dear jpcavanaugh,

I do need this module to load EBCIDIC input file. Can you please help me?

Thanks,
Ramvertica

[id10t]

Hi!

Vertica already provides all you need:
1. https://github.com/vertica/Vertica-Exte ... Filter.cpp
2. https://github.com/vertica/Vertica-Exte ... Source.cpp
3. /opt/vertica/sdk/examples/FilterFunctions/Iconverter.cpp
4. iconv -f EBCDIC-US /path/to/data | vsql -c "copy ... from stdin ..."

I will show option 3.

• Compile filter (ensure that all dependencies satisfied: g++, iconv are installed)

  Code: Select all

  daniel@synapse:~$ g++ -D HAVE_LONG_INT_64 -I /opt/vertica/sdk/include -Wall -shared -Wno-unused-value \
  -fPIC -o /tmp/Iconverter.so /opt/vertica/sdk/examples/FilterFunctions/Iconverter.cpp /opt/vertica/sdk/include/Vertica.cpp
  

• Data as is:

  Code: Select all

  daniel@synapse:~$ cat /tmp/ebcdic.dat 
  �O���%�O���%
  

  Data in UTF-8

  Code: Select all

  daniel@synapse:~$ iconv -f EBCDIC-US -t UTF-8  /tmp/ebcdic.dat 
  1|foo
  2|bar
  

• Register a filter:

  Code: Select all

  daniel=> CREATE LIBRARY IconverterLib AS '/tmp/Iconverter.so';
  CREATE LIBRARY
  daniel=> CREATE FILTER Iconverter AS LANGUAGE 'C++' NAME 'IconverterFactory' LIBRARY IconverterLib;
  CREATE FILTER FUNCTION
  

• Test

  Code: Select all

  daniel=> create table ebcdic(id int, v char(3));
  CREATE TABLE
  daniel=> copy ebcdic from '/tmp/ebcdic.dat'  with filter Iconverter(from_encoding='EBCDIC-US');
   Rows Loaded 
  -------------
             2
  (1 row)
  
  daniel=> select * from ebcdic ;
   id |  v  
  ----+-----
    1 | foo
    2 | bar
  (2 rows)
  

See examples here:
http://www.vertica.com/category/data-loading/
http://www.vertica.com/category/user-defined-load-udl/