Page 1 of 1
Loading Compressed EBCDIC files to load tables
Posted: Tue Jun 05, 2012 2:34 pm
by 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.
Re: Loading Compressed EBCDIC files to load tables
Posted: Wed Jun 06, 2012 8:48 pm
by 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.
Re: Loading Compressed EBCDIC files to load tables
Posted: Thu Jun 07, 2012 4:04 am
by 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?
Re: Loading Compressed EBCDIC files to load tables
Posted: Thu Jun 07, 2012 8:01 pm
by 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).
Re: Loading Compressed EBCDIC files to load tables
Posted: Sun May 26, 2013 5:12 am
by ramvertica
Dear jpcavanaugh,
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
Posted: Sun May 26, 2013 7:17 pm
by 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���%
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/