Loading Compressed EBCDIC files to load tables
Moderator: NorbertKrupa
Loading Compressed EBCDIC files to load tables
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.
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: Loading Compressed EBCDIC files to load tables
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.
Re: Loading Compressed EBCDIC files to load tables
Yes juniorfoo, i need help in writing the parser for ebcdic files. Can you throw some light on how to write it?
-
- Intermediate
- Posts: 149
- Joined: Mon Apr 30, 2012 10:04 pm
- Location: New York
- Contact:
Re: Loading Compressed EBCDIC files to load tables
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).
Contact me via private message for further help around the code itself (including creating the entire thing).
-
- Newbie
- Posts: 5
- Joined: Sat May 25, 2013 4:03 pm
Re: Loading Compressed EBCDIC files to load tables
Dear jpcavanaugh,
I do need this module to load EBCIDIC input file. Can you please help me?
Thanks,
Ramvertica
I do need this module to load EBCIDIC input file. Can you please help me?
Thanks,
Ramvertica
Re: Loading Compressed EBCDIC files to load tables
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.
http://www.vertica.com/category/data-loading/
http://www.vertica.com/category/user-defined-load-udl/
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:
Data in UTF-8
Code: Select all
daniel@synapse:~$ cat /tmp/ebcdic.dat �O���%�O���%
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)
http://www.vertica.com/category/data-loading/
http://www.vertica.com/category/user-defined-load-udl/