Hi Daniel, Thanks for your quick suggestion . How can we use business logic more interactively in vertica if their is no procedure in vertica ( like procedure in oracle) . Thanks , Dharmasis panda
Hello Dharmasis You can use the power of shell scripts OR Python to achieve a complex business logic. You need to replicate the same logic of Stored Procedures in Shell scripts or Python scripts.. Hope this helps
Hi Navin, How can we use shell scripts or Python scripts (after creating the scripts) in vertica. Is their any steps to be followed in vertica. Thanks , Dharmasis panda
Hi! >> How can we use business logic more interactively in vertica if their is no procedure in vertica (like procedure in oracle). 1. Vertica supports and provides ODBC/JDBC/ADO.NET interfaces. 2. Vertica provides SDK for UDF on C/C++ and R-language PS I think that decision to say no to Stored Procedure was intentional. Stored Procedures has a big overhead, hard to analyze for parallelism and so on (and trust me you dont want to lose a parallelism with BigData). I think Mickel Stonebraker was talking about it in "NoSQL vs NewSQL" video. That why in new DBMS he is using in term "compiled" stored procedures. FYI: 1. Rule 2: http://cacm.acm.org/magazines/2011/6/108651-10-rules-for-scalable-performance-in-simple-operation-datastores/fulltext 2. NewSQL vs NoSQL for New OLTP: http://www.slideshare.net/Dataversity/newsql-vs-nosql-for-new-oltp-michael-stonebraker-voltdb 3.
... we advocate running application logic – in the form of stored procedures – "in process" inside the database system, rather than the inter-process overheads implied by the traditional database client / server model.
Hi Daniel, As you suggest the point . >>>1. Vertica supports and provides ODBC/JDBC/ADO.NET interfaces. 2. Vertica provides SDK for UDF on C/C++ and R-language Here , ODBC and JDBC are the driver to connect between interface and database. as i have oracle experience, still in confusion in how to build my business logic . Please suggest in simplest manner with example. Thanks, Dharmasis panda
Hi! >> Please suggest in simplest manner with example. I can't. Did you specified any of your requirements or problems that you need to solve? Main problem here, is that you are looking for tool that will help you to solve a problem and not a solution itself. Can you define a problem? (SP - isn't a problem, it's a tool/technology that help to solve problems, nothing else) Your problem is interaction? So ODBC/JDBC/ADO.NET and others will help you with interaction(How? How a lot of developers do their apps and users interacts with these apps? I will not teach you programming). Your problem is complex analytics? So UDF C++ or UDF R should help you with complex calculations. This talk is too much abstract: Did you briefly explained your service/requirements? May be Vertica doesn't fits your requirements. Where do you stacked? Where is your main problem(just SP is too much abstract explanation, and one more time - it isn't a main problem)? Now SP, after it TRIGGERS, after it what? Logic can be different. Look at root.
[UPDATE] You know, may be in general I may explain. What is Stored Procedures? It's some subroutine/procedure/function, right? What can SP do? - get 0 or more arguments - define 0 or more arguments - apply it goal: administrative tasks, complex calculation and so on Now, SP must be invoked by special statement, right? Ok, how we can do it with JDBC/ODBC and others? JDBC/ODBC app can(I will call it as - "app", ok?): - get 0 or more arguments - define 0 or more arguments - apply it goal: administrative task/s, complex calculation and so on (there are no tasks that you can't do via ODBC/JDBC) But now you can apply a complex logic in your app! About calling: you can register your app as External Procedure and invocation of External Procedure desn't require a special syntax: select external_procedure(arguments) If a goal of SP is - a complex calculation(recursion for example), so Vertica provides UDF SDK for C++ and R. I don't see any problem that can't be solved without SP (triggers - it's another story). Hope it clarifies a little.
Hi Dharmasis, Can you have a look at the below link - Programmer's guide? https://my.vertica.com/docs/6.1.x/HTML/index.htm#1639.htm It will give you better picture of your questions like how to use JDBC/ODBC/ADO.Net, External Procedures ( not similar to other database base), User Defined function (UDF), R etc.
Comments
select external_procedure(arguments)If a goal of SP is - a complex calculation(recursion for example), so Vertica provides UDF SDK for C++ and R. I don't see any problem that can't be solved without SP (triggers - it's another story). Hope it clarifies a little.This is an Old Thread, but I am new to Vertica. And trust me I am loving it. Appreciate your points on SPs.
A quick question -> May be not stored ... but How can I write anonymous blocks in Vertica like we do with PL/SQL in Oracle.