Keep Temp Tables in New Sessions

Moderator: NorbertKrupa

Post Reply
User avatar
janice
Intermediate
Intermediate
Posts: 51
Joined: Wed May 30, 2012 1:14 pm

Keep Temp Tables in New Sessions

Post by janice » Tue Oct 01, 2013 1:53 pm

Hello!

Is there a way to keep temporary tables from being dropped after logging out?

If I create a temp table and then log off and back on again, the table is gone.

Code: Select all

dbadmin=> create local temporary table loc_temp (my_col int) on commit preserve rows;
CREATE TABLE
dbadmin=> insert into loc_temp values (10);
 OUTPUT 
--------
      1
(1 row)

dbadmin=> commit;
COMMIT
dbadmin=> select * from loc_temp;
 my_col 
--------
     10
(1 row)

dbadmin=> \q
[dbadmin@Vertica1 ~]$ 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 * from loc_temp;
ERROR 4566:  Relation "loc_temp" does not exist
Is it possible too keep the temp table?
Everyday is an adventure!

scutter
Master
Master
Posts: 302
Joined: Tue Aug 07, 2012 2:15 am

Re: Keep Temp Tables in New Sessions

Post by scutter » Tue Oct 01, 2013 2:34 pm

Hi Janice,

Use a GLOBAL temporary table - or leave off the LOCAL keyword.

"Global temporary table definitions persist in the database catalogs until they are removed explicitly through a DROP TABLE statement."

Local tempoary tables:

"Specifies that the table definition is visible only to the session in which it is created."

--Sharon
Sharon Cutter
Vertica Consultant, Zazz Technologies LLC

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: Keep Temp Tables in New Sessions

Post by nnani » Tue Oct 01, 2013 2:43 pm

Hello Janice,

You can use Global temporary tables for achieving this.
Global temporary table definitions are accessible to all users and sessions, so that two (or more) users can access the same global table concurrently. However, whenever a user commits or rolls back a transaction, or ends the session, Vertica removes the global temporary table data automatically, so users see only data specific to their own transactions or session.
Global temporary table definitions persist in the database catalogs until they are removed explicitly through a DROP TABLE statement.
If you are expecting the data to be present after opening a new session, then this will no suffice your requirement.
Temporary tables cannot hold data after a session is closed. they are session specific.

Code: Select all

nnani=>   CREATE GLOBAL TEMPORARY GT_test
nnani->   (
nnani(>   test_id int
nnani(>   ,test_tab_name varchar(30)
nnani(>   ,test_date date
nnani(>   );
CREATE TABLE

nnani=> select * from GT_test;
 test_id | test_tab_name | test_date
---------+---------------+-----------
(0 rows)


nnani=> insert into GT_test values(2,'global_temp','2013-10-01');
 OUTPUT
--------
      1
(1 row)

nnani=> commit;
COMMIT
nnani=> select * from GT_test;
 test_id | test_tab_name | test_date
---------+---------------+-----------
(0 rows)

\q

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

Bad terminal type: "xterm". Will assume vt100.
nnani=> select * from GT_test;
 test_id | test_tab_name | test_date
---------+---------------+-----------
(0 rows)
nnani=> \d+ GT_test
                                              List of Fields by Tables
       Schema       |  Table  |    Column     |    Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------------------+---------+---------------+-------------+------+---------+----------+-------------+-------------
 public| GT_test | test_id       | int         |    8 |         | f        | f           |
 public| GT_test | test_tab_name | varchar(30) |   30 |         | f        | f           |
 public| GT_test | test_date     | date        |    8 |         | f        | f           |
(3 rows)

Using Global temporary tables you can have table structure persistent but data will be lost everytime.
Workaround - You can think of Views

Hope this helps.. :)
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
janice
Intermediate
Intermediate
Posts: 51
Joined: Wed May 30, 2012 1:14 pm

Re: Keep Temp Tables in New Sessions

Post by janice » Tue Oct 01, 2013 6:14 pm

Thanks, scutter and nnani! I missed that info. about global tables in the docs. :roll: It works!
Everyday is an adventure!

rajasekhart
Intermediate
Intermediate
Posts: 87
Joined: Wed Apr 25, 2012 8:10 am

Re: Keep Temp Tables in New Sessions

Post by rajasekhart » Mon Oct 07, 2013 8:06 am

Hi Janice,

How you are using Temp Tables in your Project.

In which scenario , it is useful to you??

As we dont have stored procedures, how can we use them?

PS: Are you using them with External Stored Procedures??

Regards,
Raj!!
Rajasekhar.T|HP ATP Vertica Big Data Solutions V1

User avatar
janice
Intermediate
Intermediate
Posts: 51
Joined: Wed May 30, 2012 1:14 pm

Re: Keep Temp Tables in New Sessions

Post by janice » Wed Oct 09, 2013 2:17 pm

Raj,

We are using the global temp tables to hold data "temporarily" as a staging area prior to actually loading the data into fact tables. We have a bunch of processes loading data from different sources. These processes can all use the same global temp table and it's nice that the tables automatically clean up the temp data once the users log off.
Everyday is an adventure!

Post Reply

Return to “New to Vertica Database Development”