Hey guys, I tried out DBVisualizer earlier. I liked it and I have blogged about setting that up for your Vertica database. But it is expensive. I mean I can’t buy it for my personal use unless a company actually sponsors me. I’m a poor developer you see. So I searched for alternatives and I kind of liked SQuirrel SQL client. It is a java based open source SQL client which is pretty awesome. First you should go and download it to know what I mean: http://squirrel-sql.sourceforge.net/. ...

Hey there, I guess you have been having a lot fun with your Vertica databases. I thought I’d share some of the things that I did to improve my query performance. Tables and Projections in Vertica In Vertica even though we access data through tables everything is stored in projections which are stored among nodes in various ways depending on the K Safety and the Segmentation options that we choose when creating the table. We can make modifications to these projections manually or run the Database Designer to generate projections for us. Sometimes to get the best performance we have to write query specific projections. ...

I don’t know if you have even heard of this operating system named OpenVMS by HP. Unfortunately or maybe fortunately I have had a chance to work on it. The operating system has a cool feature, file version is built into the OpenVMS file system. But the problem with this is that if you go into a log directory and list out files, the list could get really long. I was just trying to make it display things that I usually use on Linux. ls -lrt ...

I was playing around with Vertica and my friend suddenly asked me what version of Vertica I was using. This was because he was trying to debug something and posted a question to guys from Vertica and they asked him that question. At my workplace we use the same Vertica cluster for development. It is a playground with a lot of restrictions. Without DB Admin access you can’t really explore much in Vertica. But yea we are trying to get the most out it despite all that. ...

Vertica has a special feature to join tables and fill in the last available values where values would otherwise be null. I had a similar use case and wanted to test how I could make the best use of it. I played with simple examples and got it right, but with a slightly more complex one, I was stuck or maybe I just couldn’t understand what was happening. So I posted a question at Stackoverflow ...

I had a problem creating a table with just SQL that is supported by Vertica. Most of the solutions that I came across online were using procedures. But after a lot of playing around and reading vertica documentation I found a solution to my problem. read more about the problem and the solution at Stackoverflow

Hey if you have worked on Linux then you have interacted with bash. Maybe not. But it is mostly likely that your default shell is bash. I’m not surprised. I’m among the few people on this planet who regularly interacts with cshell also. Thanks to the kind of work I do. One great feature of bash that I miss in cshell is: Functions. Surprised? Cshell doesn’t support functions. It doesn’t have any concept of function. If you wanted to simulate a function, you have to write another cshell script that does what your function would do and define an alias that calls it. ...

I don’t know if you have tried this. But I did try to read function definition from the system tables in Vertica. I used the table called USER_FUNCTIONS which is a table in the V_CATALOG system schema. More details can be found here: https://my.vertica.com/docs/6.1.x/HTML/index.htm#15021.htm But unfortunately when I try and do something like this: vertdeva01:20130604-113434 > select function\_definition from v\_catalog.user\_functions where schema\_name='calc' and function\_name='getCYForDate'; \---------------------------------------------------------------------------------------------------------------------------------- RETURN to\_date(concat(((((date\_part('year', (processDate)::timestamp))::int - CASE WHEN ((date\_part('month', (processDate)::time (1 row) If you notice the function definition is only partly visible. The rest of it gets truncated. I don’t know how to set it right. If you know how, feel free to comment on this post. ...

Download your free copy of DBVisualizer from http://www.dbvis.com/download/ I am going to give you the steps to setup vertica connection to DB Visualizer on a windows 7. Our windows might look different. But I suppose the terms used on the GUI would match that on any operating system. I know this might be a big assumption as I don’t work for DBVisualizer and I haven’t installed it on any other platform. But it is always worth giving this a try if you are starting to use Vertica and would like to see your tables and write queries on a GUI based environment. I am not a big fan of GUIs. But some times it might become handy. Especially if you are a DBA and you manage quite a lot of databases. ...

I work on CSHELL these days. Probably the most unpopular shell today. Anyone would recommend not to use it. Because of the absurdness of its syntax and quoting. For an example: Top 10 reasons to avoid using CShell My intention was to write a generic wrapper script that I could use to invoke another script that supposedly attempts to update a database and in the unlikely event of failing to do so, the wrapper would wait for a while and retry again. I thought of this approach as we had been facing some database locking issues and some data that was supposed to updated wasn’t getting updated. And this was causing even more problems for us. Since some of our systems rely on this data. By the way, to give you an idea of the locking problem, let me tell you, we have one MySQL database, that is updated by several processes running on 7 different boxes. Now you can understand the number of processes trying to access the database at similar intervals. ...