Moving from SQL Server to Vertica
akshakimos6
Community Edition User
Hey all,
I have been working as a financial analyst for about 4.5 years using SQL Server and recently moved to a new firm as a Business Intelligence Analyst using HPE Vertica. I have tried to look at several comparisons online of the differences between the two, but I was hoping to get the community's feedback. In your opinion, what are the major things I should know about how Vertica operates as compared to SQL Server?
omegleThanks!
0
Answers
Vertica main four principle goals are database performance, scalability of both data and functionality, cluster reliability, and deployment freedom.
See: https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/ConceptsGuide/Other/ConceptsGuide.htm
Vertica automatically create an unoptimized super-projection upon first load of the data to a table.
Later, after queries are written, the initial super-projection can be replaced with a more optimized super-projection.
To gain best performance, this can be done automatically via Vertica database designer, or manually by the user.
In addition, Vertica provides a set of system tables that are used to monitor activity and metadata.
To collect, store, and explore semi-structured data use Vertica Flex Tables.
One can use Vertica to boost analytic performance on Hadoop or use it as a single query engine across diverse formats and structures.
Denormalized, or Vertica flattened, tables, can improve join performance and minimize the need to write joins, because it can include columns that get their values by querying other tables, automatically. Flattened, tables also minimize the overhead that is otherwise typical of denormalized tables.
Here are some more points for comparison..
Number of built-in functions in Vertica is 646 (compared to 288 in MS SQL)
Single license offers all of the built-in functions on the cloud
Vertica include MapReduce
Pure columnar storage can provide aggressive data compression when using the right field encoding
Enjoy built in High Availability with 3 or more nodes (with no need for full failover cluster nor mirroring)
Good fit for large scale data warehouse applications
The same Vertica s/w can be deployed anywhere (Same product capabilities on prem, on cloud, VM etc.)
Include Open source innovations (e.g. https://github.com/vertica/VerticaPy)
Available also on Amazon cloud or Google Cloud
Nested transactions permitted
Good performance on queries with concurrent loading
Same s/w bits allow different deployment options (e.g. separtion between compute and storage)
Free for dev & test, with no data size limitations
No need to install any additional packages for geospatial analytic, advanced analytics, ML, time series analytics
Vertica free trial version is not time-bound (e.g. standalone VM image on your PC for training, dev or QA)
I'm trying to add to what @mosheg has already contributed, trying to keep in mind your role as a BI analyst.
You will already have noticed that Vertica has no lock in as to front ends - which gives you the freedom (and sometimes embarrassment...) of choice between tools like DbVisualizer, DBeaver, Toad for ODBC and the like.
You will find out that Vertica, being a Big Data database tends towards offering you more built-in functionality - as said above, 646 vs. 288 functions - to solve your needs better than with the rather OLTP oriented approach of Stored Procedures that the SQL Servers and Oracles of this world have to offer. Also, Vertica does not work with variables on the server side. You'd have to work with variables that the front end might offer.
In general, Vertica's SQL dialect is much closer to the ANSI standard of SQL than SQL Server would be.
You'll have to give up using square brackets around your table and column names, if you used that before.
That's what comes to my mind for starters ...
Hello,
We are using Vertica at Lumuss. It is comparatively cheaper in our case to keep up with teradata development. Btw, you may use Ispirer MnMTK for a quick migration.
https://www.ispirer.com/products/microsoft-sql-server-to-vertica pimion.com