Set up search_path for the ODBC Source

Forum to discuss topics related to accessing and using Vertica via Quest Software's Toad for Data Analysts

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1736
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Set up search_path for the ODBC Source

Post by JimKnicely » Tue Feb 14, 2012 8:27 pm

You can add a database schema to your search_path variable so that when you log in you won't have to prefix table names with that schema.

For instance, if I log into Vertica as the user dbadmin and I want to query the employee_zip_dim table in the cognos schema, normally I would have to write the following query in the Toad SQL editor window:

Code: Select all

SELECT * FROM cognos.employee_zip_dim;
But if I add the cognos schema to my schema search path then I won't have to specify the schema in my query. To do that in Toad, I would edit the ODBC connection and add the following text to the "ConnectionString" field:
  • ConnSettings=SET SEARCH_PATH TO "$user", cognos, public, v_catalog, v_monitor, v_internal
(See the attachment below for screen shot of the ODBC Connection Properties window)

If I reconnect I'll only have to type:

Code: Select all

SELECT * FROM employee_zip_dim;
Attachments
Toad_ODBC.png
Toad ODBC Connection Properties window
Toad_ODBC.png (18.33 KiB) Viewed 14769 times
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

bkozorra
Newbie
Newbie
Posts: 1
Joined: Mon Feb 06, 2012 6:07 pm

Re: Set up search_path for the ODBC Source

Post by bkozorra » Tue Feb 14, 2012 8:35 pm

What if you have 2 or more tables with the same name in different schemas?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1736
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Set up search_path for the ODBC Source

Post by JimKnicely » Tue Feb 14, 2012 9:31 pm

The Vertica search_path works just like the the PATH variable in Linux or DOS.

If a table with the same name exists in more than one schema, Vertica will simply use the first of those schemas listed in your search_path variable.

Example:

Code: Select all

dbadmin=> create schema test1;
CREATE SCHEMA

dbadmin=> create schema test2;
CREATE SCHEMA

dbadmin=> create table test1.weather_dim (key1 int);
CREATE TABLE

dbadmin=> create table test2.weather_dim (key1 int);
CREATE TABLE

dbadmin=> insert into test1.weather_dim values (1);
 OUTPUT
--------
      1
(1 row)

dbadmin=> insert into test2.weather_dim values (0);
 OUTPUT
--------
      1
(1 row)

dbadmin=> show search_path;
    name     |                             setting
-------------+-----------------------------------------------------------------
 search_path | "$user", public, v_catalog, v_monitor, v_internal
(1 row)

dbadmin=> set search_path="$user", test2, test1, public, v_catalog, v_monitor, v_internal;
SET

dbadmin=> select * from weather_dim;
 key1
------
    0
(1 row)

dbadmin=> set search_path="$user", test1, test2, public, v_catalog, v_monitor, v_internal;
SET
dbadmin=> select * from weather_dim;
 key1
------
    1
(1 row)
The example was demonstrated using vsql, however the concept applies across any connection type.
Jim Knicely

Image

Note: I work for HPE. My views, opinions, and thoughts expressed here do not represent those of my employer.

matthewcornell
Beginner
Beginner
Posts: 30
Joined: Mon Dec 09, 2013 2:30 pm

Re: Set up search_path for the ODBC Source

Post by matthewcornell » Thu Jan 09, 2014 10:03 pm

Super helpful, Jim!

Post Reply

Return to “Toad for Data Analysts”

Who is online

Users browsing this forum: No registered users and 1 guest