Working with Vertica #SQL

gistfile1.sql

-- Connecting to Vertica via pyvertica
__author__ = 'vosipov'
from pyvertica.connection import get_connection
conn = get_connection(False, driver='{Vertica}', server='192.168.30.128', database='VMart', port=5433, uid='dbadmin', pwd='password')

cursor = conn.cursor()
for row in cursor.execute("SELECT table_name FROM tables;"):
    print row
conn.close()

-- Connecting to Vertica via pyodbc
import pyodbc
#conn = pyodbc.connect("DRIVER=Vertica;SERVER=192.168.30.128;DATABASE=VMart;PORT=5433;UID=dbadmin;PWD=password")
conn = pyodbc.connect(driver='{Vertica}', server='192.168.30.128', database='VMart', port=5433, uid='dbadmin', pwd='password')

cursor = conn.cursor()

for row in cursor.execute("SELECT table_name FROM tables;"):
    print row

conn.close

-- Подключение sublime-sqlexec к Vertica
 {
    "connections": {
        "Connection 1"  : {
            "type"      :   "vertica",
            "host"      :   "192.168.30.128",
            "port"      :   5433,
            "username"  :   "dbadmin",
            "password"  :   "password",
            "database"  :   "VMart"
        }
    }
}

-- For people who use vsql and the editor VIM, you can enable syntax highlight by adding the following lines to ~/.vimrc :
if $_ == '/opt/vertica/bin/vsql'
        set syntax=sql
endif

-- Run a simple query. For example, to count all the records in the store_sales_fact table:
SELECT COUNT(1) FROM store.store_sales_fact;

-- В версии 7.1 для проекций введена возможность указать как столбцы, так и выражения:
CREATE PROJECTION sales_proj (sale_id, sale_count,  sale_price, sale_value) AS
  SELECT sale_id, sale_count, sale_price, sale_count * sale_price
  FROM sales 
  ORDER BY sale_count * sale_price
  SEGMENTED BY HASH(sale_id) ALL NODES KSAFE 1;

-- Следующий запрос к созданной проекции таблицы:
SELECT *
FROM sales_proj_b0
WHERE value > 1000000
ORDER BY value;