Sybase IQ to vertica Migration

Hi!

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?

Hi DL , 

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 . 

Also DL , If I am using any toold for the migration , with your experience which will be the recommended tool ( Again considering the cost !! ) 

The most cost effective way it will be if you do it by yourself !
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)

Thanks Adrian , Will you be having any Writeup's which can help us to get a detailed approach .. Or can you suggest some links . 

Depends on your approach !  
 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 

Hi!

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

• performance - not a best
• best community,
• documentation is very good
• IMO: job flow  in Pentaho looks very complex and scary
  
• Enterprise edition very expencive

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

• performance - medium
• Job Flows - are easy
• can be embedded
  
• very good documentation
  

Great detailed response !

Hi Adrain and Daniel,

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

Hi Sunil,

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

Hi!

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.

Hi Sunil,
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 !

Hi All 

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.

@Daniel - Why i wanted to move data from IQ - There are few tables which are there in IQ which are not currently available in the source . Which is the archival tables.. 

Hi Sunil.

>> 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:

• stage tables (and/or MERGE)
  
• load to base table => select analyze_constraints() => delete dups
• you can load thousands files with a single COPY statement, just use in wild cards - https://my.vertica.com/docs/7.0.x/HTML/index.htm#Authoring/SQLReferenceManual/Statements/COPY/Parame...

The pathToData can optionally contain wildcards to match more than one file. The file or files must be accessible to the local client or the host on which the COPY statement runs.

You can use variables to construct the pathname as described in Using Load Scripts.

The supported patterns for wildcards are specified in the Linux Manual Page for Glob (7), and for ADO.net platforms, through the .NET Directory.getFiles Method.

* 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.

Hey i want to covert sysbase IQ data to vertica database how is it possible let me know