Temporary data and Vertica catalog

Eyal_YurmanEyal_Yurman Registered User

Hi there!

Normally I would open a ticket but I thought this could be an interesting open discussion.

I'm performing a lot of ETL within Vertica. This requires using large number of CREATE TABLE/TRUNCATE TABLE/MOVE_PARTITIONS_TO_TABLE operations.

I realize that these DDLs will have two issues:

  • require global locks (short block of query initiation).
  • Increase catalog size.

I could go to local temporary table solution. This hopefully doesn't require any global lock on the catalog or have any footprint on catalog size. The issue with that is it's not resilience to session disconnects.
There for I'm considering few other options.

Can anyone tell me how heavy does each of these options is on catalog size and global locks?
(For the matter, let's assume latest patched Vertica 8.1 running on AWS.)

  • Global temportary table
  • Local temporary table
  • Flex table
  • External table (as copy)
  • And finally, regular table

Best regards,
Eyal Yurman.

Comments

  • Jim_KnicelyJim_Knicely Employee, Registered User, VerticaExpert
    edited March 20

    Hi,

    You can check the LOCK_USAGE system table to find out which statements take a GCL:

    For example, creating a GLOBAL TEMP table takes a Global Catalog Lock:

    dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
     object_name | mode | count
    -------------+------+-------
    (0 rows)
    
    dbadmin=> create global temp table test (c1 int);
    CREATE TABLE
    
    dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
       object_name    | mode | count
    ------------------+------+-------
     Cluster Topology | S    |     1
     Local Catalog    | X    |     1
     Global Catalog   | X    |     1
    (3 rows)
    

    Whereas a LOCAL TEMP table does not take a Global Catalog Lock:

    dbadmin=> \q
    
    [[email protected] ~]$ 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 object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
     object_name | mode | count
    -------------+------+-------
    (0 rows)
    
    dbadmin=> create local temp table test_loc (c1 int);
    CREATE TABLE
    
    dbadmin=> select object_name, mode, count(*) from lock_usage where session_id = current_session() group by object_name, mode;
     object_name | mode | count
    -------------+------+-------
    (0 rows)
    
  • KWilletsKWillets Registered User

    We had the regular table version on my last job, and the short answer is that we spent a lot of time chasing GCL timeouts. We would have used either global or local temp, but our ETL tool did not guarantee connection continuity between statements.

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file