Vertica

Vertica is a columnar database management system designed for high-performance analytics on large datasets. Scoop connects directly to Vertica, enabling AI-powered analysis on your analytical data.

Connection Details

Prerequisites

Before connecting Scoop to Vertica:

1. Vertica cluster (version 10.x or later recommended)
2. Read-only user with SELECT permissions
3. Network access from Scoop's IP addresses

Create a Read-Only User

Connect to your Vertica database as an administrator and run:

-- Create the read-only user
CREATE USER scoop_user IDENTIFIED BY 'your_secure_password';

-- Grant USAGE on schema
GRANT USAGE ON SCHEMA public TO scoop_user;

-- Grant SELECT on all tables in the schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO scoop_user;

-- Grant SELECT on future tables (recommended)
ALTER DEFAULT PRIVILEGES FOR dbadmin IN SCHEMA public
GRANT SELECT ON TABLES TO scoop_user;

-- Or grant access to a specific database
GRANT USAGE ON DATABASE your_database TO scoop_user;

Replace the password and database name with your actual values.

Whitelist Scoop IP Addresses

For Vertica on Cloud (Vertica in Eon Mode)

Configure your cloud provider's network security (VPC, security groups) to allow connections from Scoop's IP addresses on port 5433.

For On-Premises Vertica

Configure your firewall to allow incoming connections on port 5433 from Scoop's IP addresses.

Contact Scoop support for the current list of IP addresses to whitelist.

Connect in Scoop

1. Navigate to Datasets and click New Dataset
2. Select Database as your source
3. Choose Vertica from the database type dropdown
4. Enter your connection details:
   • Server Host: Your Vertica hostname (any node in the cluster)
   • Port: 5433 (or your custom port)
   • Database: Your database name
   • Username: scoop_user
   • Password: The user's password
5. Click Test Connection to verify
6. Enter your SQL query and proceed

Connection URL Format

If using a JDBC URL directly:

jdbc:vertica://hostname:5433/database_name

With SSL:

jdbc:vertica://hostname:5433/database_name?ssl=true

With backup nodes for failover:

jdbc:vertica://hostname:5433/database_name?BackupServerNode=host2:5433,host3:5433

Query Optimization

Vertica performs best when queries leverage its columnar architecture:

• Select only needed columns - columnar storage benefits from column pruning
• Use projections - Vertica's pre-sorted, pre-aggregated structures
• Filter early - push predicates as close to the data as possible

-- Good: Select specific columns
SELECT customer_id, order_date, amount
FROM orders
WHERE order_date >= '2024-01-01'

-- Check query plan
EXPLAIN SELECT ...

Best Practices

• Design projections for your query patterns
• Use partition columns in WHERE clauses
• Analyze statistics regularly: SELECT ANALYZE_STATISTICS('your_table');
• Monitor resource pools for query performance

Troubleshooting

Connection Refused

• Verify Vertica is running: admintools -t view_cluster
• Check that port 5433 is open in your firewall
• Ensure you're connecting to a valid node IP

Authentication Failed

• Verify the username and password are correct
• Check if the user account is locked: SELECT * FROM users WHERE user_name = 'scoop_user';
• Ensure the user was created successfully

Permission Denied

• Verify GRANT statements were executed
• Check current permissions: SELECT * FROM grants WHERE grantee = 'scoop_user';
• Ensure USAGE is granted on the schema