Options
Sybase IQ to vertica Migration
Hi ,
Looking forward for the best & Cost effective approach for migrating Sybase IQ to Vertica .
Regards
Sunil
Looking forward for the best & Cost effective approach for migrating Sybase IQ to Vertica .
Regards
Sunil
0
Comments
The best - means, the only (would you use anything except "the best"?)
The only - means no other alternatives
No alternatives - means no other ways, exists only one - a best one.
So use in it - the only way (no other ways)
If there a couple ways how to do it, so all of them will have advantages and disadvantaged and you need to choose what match to your requirements.
But it depends on your requirements.
---
Where is a question?
My concern is that i want to use minimal or no investment on any tools for migration . Hence i was high lighting on the cost effective part . In such a scenario which is the approach that i should follow for IQ to Vertica Migration .
Export your metadata and create the layout in Vertica!
Next dump your data into csv and create dynamic scripts for loading into Vertica.
As for the user defined functions and proc. , this will be entirely up to you.
I would recommend Pentaho DI for your data migration, but if you have no exp with it by the time you learn how to use it you will be able to migrate by hand!
Pentaho gives you a 30 days trial(you might use this time to move your data and map your objects from Sybase to Vertica)
To start with analyze your data types needs(explore Vertica data type limitation as well any custom Sybase data types).
Design your "new" Vertica layout looking at your actual Sybase design.
One thing i have encountered to be the most important when doing a migration to Vertica is the target limitations.
Vertica it is a great db but it has it's "soft" parts.(no triggers,no db_links,limited insert performance,etc..) study those and see if you hove them in your Sybase db and try to find a way to workaround.
As for the data dump from Sybase and load
1- go for the old reliable hand made script
2- use the pentaho or talend tools
http://www.pentaho.com/product/data-integration
Just a migration? No more IQ anymore after it? What about data source after a migration?
Mostly agree with Adrian, but some steps are missing, Sybase IQ != Vertica, so you need:
- to learn your use case
- to learn Vertica architecture
- to create a design for Vertica that depends on your business requirements (Sybase IQ != Vertica, they do a same thing but in differ ways - functions are not same, Vertica has no indexes, etc...)
- to do a small PoC (unit test for your design)
- finalize your design
Only after it you can start to think about ETL process (or migration):- do you need a complex ETL(normalization/dermolization) or ETL is dump -> load&go?
- do you need an ETL tool after a migration(on constant basis) or not?
And a last factor - DEAD LINE?Self-written ETL has a big disadvantage - DIY, design and implementation
Tools - you need to learn them, and may be tool is free and gives good performance but a learning curve is very complex (Talend for example)
>> with your experience which will be the recommended tool
I can only say my opinion about a few tools (Im a strong in python, so all my ETL I do with python and bash, no tools):
All these tools will require a JVM, so JVM should be configured properly too (at least memory&heap)
Pentaho
Talend
- performance - medium(Ab Initio much more better
- Job Flow looks much more cleaner
- community - 6/10 (see note below)
- documentation - 3/10
Talend documentation is very poor, i think that Talend trying to create a dependency on their support. Community is also "blind" in many aspects.Clover ETL
Thanks fo rthe information. Can i do a export from my IQ to CSV file and import to new Vertica DB using Restore option . Once that is done use shell scripts to move my stored procedures and Codes to Vertica . As a third stage, i wanted to take a downtime and cut over my balance data to vertica . Is there any way i can take incremental back up from IQ and restore into vertica
I don't think the scenario you have stated is possible.
Vertica can restore the data which it has taken as a backup.
As vertica's backup is vertica specific with its filetypes,sceenshots,catalogs,datafiles
while considering restore to Vertica option Vertica will expect the same filetypes to be restored in database.
If you want to dump data from IQ to Vertica, the best way would be dump into flat files and load those files into Vertica.
As a part of first statement, you cannot even take a incremental backup from IQ to restore into Vertica.
If you scenario is taking the incremental data from IQ and dump into Vertica on a daily basis, take help of a shell script / external procedure and schedule it with a good scheduler.
Hope this helps
I think you still on wrong way.
1. Whats going on with IQ after? cut-off? bye-bye? Or still in usage?
If for for IQ will be cut-off so you need to create a migration from data source and don't worry about IQ. Start load data to Vertica from data source, cut-of IQ from data source and no more incremental backups - just bulk load from IQ to Vertica (no loads to IQ on this step)
2. No cut-off - IQ is data source for Vertica
Does data in IQ is "update-able" or just "append-able"? If "update-able" so its not a migration its a data synchronization and the only advice I can give you - use in tools.
As Daniel said you need to state what is the end of your migration process!
1- Does your Sybase db will still be operative after the migration ?
2- Will you keep Sybase db after and load the new data to Vertica on a scheduled base ?
After you answer this then one can an answer or an idea on how you can continue with your migration/data sync !
No , the Sybase DB will be discarded once all tables/Stored Proc are migrated to Vertica.
All data will be fed to vertica from the Source systems directly once the end to end migration is completed.
>> Which is the archival tables..
Ok, so why you need an "incremental backup" and not just a bulk load?
I don't know your requirement so in general:
* Vertica copy is very powerful, but Vertica do not rejects PK dups data, you will need it manually. You can do it in several ways:
* As staging you can use in:
- flex tables
- external tables
- temporary tables
* I suggest you do not use in CSV files(too much IO, overhead), but load data directly from IQ to Vertica. You can achieve it with:- ODBCLoader
- unix piping (IQ: select ... to stdin | vsql -c "copy from stdin")
you can easily to do it in parallel "select ... where <COL> between LOW and UP" - define different lower and upper bounds and run it in parallel (Vertica COPY/INSERT do not locks table for loads)* If IQ is not a player any more so IMO you don't need any tools especially for migration from IQ - its an overhead. Just do a bulk load and forget about IQ.