# Vertica

Connect to Vertica for high-performance analytics

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

| Setting | Value |
|---------|-------|
| Default Port | 5433 |
| Connection Type | TCP/IP |
| SSL | Recommended |

## 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:

```sql
-- 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

```sql
-- 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