We are going to consider setting up a sandbox in a windows laptop (8.x or 10) to play around HP Vertica or to build a single node proof of concept.
Step 1. Download and install VMware player from vmware.com. This is a virtualization tool which is free for non-commercial use.
Step 2: Download sandbox zip file. This is available free from HP marketplace. You need to create a free account in order to get access (https://haven.hpwsportal.com/catalog.html#/Product/%7B%22productId%22%3A%221826%22%7D/Show)
Once you download the 3 + GB zip file, unzip and place the ova file in your local windows machine.
Step 3: Start vmware player and File->Open appliance, Then select the .ova sandbox image to import. This may take some time and will ask for server parameters such as memory, hard disk , processor etc. Consider minimum needed and your resource availability. Once import is done, you can start/play the new virtual machine. This is a Centos image where Hadoop and Vertica are preconfigured.
Step 4: Once the system is started you will see black landing screen with an IP address mentioned there. Note that IP address. I will mention it as YourIP going forward in this document.
Step 5: Open a browser and navigate to http://YourIP . This will take you to a registration screen. Give the details and register yourself.
Step 6: Access Hue. Open a browser and navigate to http://YourIP:8000. The default userid/password is hue/1111
Step 7: Enable Ambari by clicking on the button “Enable” to the right side of “Ambari” in the list. Ambari will give you an insight on what is going on in the server.
Step 8: Connect Putty , give YourIP, port :22, SSH . This will connect you to the linux machine. Login using root with password ‘hadoop’
Step 9: There is a default database created in vertica already “vive”. Start admin tool to initiate the database.
$ cd /opt/vertica/bin
$ su dbadmin
Use arrow keys and hit “Start Database”. Select the listed database (vive) by pressing spacebar which will print an ‘X’ next to VIVE. Give OK. When asked for password give ‘vive’ Wait until you see “Database vive started successfully”. You can go back to the admintools menu and see the database status by hitting on “View database Cluster state”
Hit exit to quit from the admin tools.
Step 10: You can start the demo by running the script available in the image.
$ cd /home/dbadmin/demo/vDemo
You can browse the demo database and query it using an online tool available.
Step 11: Connecting to Vertica from host Windows.
Download DBeaver database explorer tool which is available free to download.
Open a new connection , give YourIP as host , port:5433, database :vive
Step 12: To load data from SQL Server using SSIS
Download Vertica ODBC Driver from My.Vertica.com. Register and Download windows ODBC 64 bit driver. Install the exe.
Open an SSIS package and connect Source SQL Server database, and setup transformation. You can create destination table using DBeaver. Select ODBC destination and create new dsn. Replace the IP with YourIP in the below screen. Map the Columns and Run the package.
Go to DBeaver and select the table. Good Luck.