Example of Vertica Eon mode Using Multiple S3 Endpoints with Pure Storage FlashBlade

A colleague asked a question today about using Vertica Eon mode with multiple FlashBlades. Pure Storage FlashBlade is an awesome S3 Communal Storage solution for Vertica Eon mode. The challenge is that there is a single AWSEndpoint and AWSAuth config parameter value set for the entire database, so it can only point to one FlashBlade S3 account at a time. What if you want to work with multiple FlashBlades (e.g., production and archive for External Tables)?

The answer is to use the ALTER SESSION command to modify those config parameters for the duration of the session. Here’s an example:

[dbadmin@vertica-node001 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit
dbadmin=> select count(*) from WebPages;
count
-------
4846
(1 row)
--- DEFAULT ENDPOINT AND CREDENTIALS:
dbadmin=> select get_config_parameter('AWSEndpoint');
get_config_parameter
----------------------
snfb-11-rr
(1 row)
dbadmin=> select get_config_parameter('AWSAuth');
get_config_parameter
------------------------------------------------------
XXXX:YYYY
(1 row)
--- NEW ENDPOINT AND CREDENTIALS:
dbadmin=> ALTER SESSION SET AWSAuth = 'AAAA:BBBB';
ALTER SESSION
dbadmin=> ALTER SESSION SET AWSEndpoint = 'snfb-17-data';
ALTER SESSION
--- EXPORT TABLE TO SECOND FLASHBLADE:
dbadmin=> export to parquet(directory='s3://testsrc/webpages')
          as select * from WebPages;
Rows Exported
---------------
4846
(1 row)
--- CREATE EXTERNAL TABLE ON SECOND FLASHBLADE:
dbadmin=> create external table MyPages (
             PageId int, LoadedOn timestamp, ModifiedOn timestamp, 
             Updates int, HeadersLength int, StatsLength int, 
             TotalLength numeric(20,0), HREFs int, HashURL int, 
             HashHTML long varbinary(1048576), URL varchar(2048), 
             HTML long varbinary(1048576)
           ) as copy from 's3://testsrc/webpages/*.parquet' parquet;
CREATE TABLE
dbadmin=> select count(*) from MyPages;
count
-------
4846
(1 row)

So this lets me access the second FlashBlade for exporting data from my Vertica Eon mode database. But those sessions are temporary and defined only for the current session. If I end the session, they go away:

dbadmin=> \q
[dbadmin@vertica-node001 ~]$ vsql
Welcome to vsql, the Vertica Analytic Database interactive terminal.
Type:  \h or \? for help with vsql commands
       \g or terminate with semicolon to execute query
       \q to quit
dbadmin=> select count(*) from MyPages;
ERROR 7160:  Cannot expand glob pattern due to error: The specified bucket does not exist.

Vertica can’t find the bucket used to define the External Table because it’s on a different FlashBlade from the default. We need to repeat the ALTER SESSION commands in order to access that FlashBlade again:

dbadmin=> ALTER SESSION SET AWSEndpoint = 'snfb-17-data';
ALTER SESSION
dbadmin=> ALTER SESSION SET AWSAuth = 'AAAA:BBBB';
ALTER SESSION
dbadmin=> select count(*) from MyPages;
 count
-------
  4846
(1 row)

It’s that simple! That’s how you can take advantage of multiple FlashBlades in your environment with Vertica Eon mode.