CODEWITHSUNDEEP
snowflake-cloud-data-platform
jcollum
jcollum

Reputation: 46599

How should I test for the existence of a table and fail a script if the table does not exist?

This is what I came up with. Seems very hacky but it is what someone in the Snowflake forums suggested. Surely there's a better way.

use database baz;

-- divide by zero hack to check the existence of a table and bail if it isn't present
-- improvements to this welcome, snow sql doesn't have a clean way to bail like raiseerror
select 1/(select count(*) from information_schema.tables
    where table_schema = 'foo'
  and  table_name = 'bar')

This script is intended to run after a setup script. This is here to make sure the required tables exist after the script has been run.

Upvotes: 1

Views: 1445

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175884

The main point is that as for today Snowflake SQL does not support control structures(IF/WHILE/FOR/SWITCH/TRY/CATCH). It will probably change in the future but as for now you could use Java Script stored procedures.

Overview of Stored Procedures

Snowflake stored procedures use JavaScript and, in most cases, SQL:

  • JavaScript provides the control structures (branching and looping).

  • SQL is executed by calling functions in a JavaScript API.

The pseudocode:

CREATE OR REPLACE PROCEDURE my_proc(...)
RETURNS STRING
LANGUAGE JAVASCRIPT
AS
$$
  var tab_exists = `select count(*) from information_schema.tables
    where table_schema ILIKE 'foo'
      and table_name ILIKE 'bar'`;

  var stmt = snowflake.createStatement( {sqlText: tab_exists} );
  var resultSet = stmt.execute();
  resultSet.next();

  var result = resultSet.getColumnValue(1);

  if (result > 0){ 
 
    ...
  }
  else {
    return  'table not found';
  };

$$;


-- invocation
use database baz;
call my_proc();

EDIT:

Using Snowflake Scripting is much easier to write such scripts:

DECLARE
   my_exception EXCEPTION(-20001, 'table_not_found');
BEGIN
  IF (EXISTS (SELECT * 
              FROM information_schema.tables
              WHERE table_schema ILIKE 'PUBLIC'
                AND table_name ILIKE 'NON_EXISTING_TAB')) 
   THEN
     RETURN 'TABLE_EXISTS';
  ELSE
     --RETURN 'TABLE_NOT_FOUND';
     RAISE my_exception;
  END IF;
END;

Upvotes: 1

Felipe Hoffa
Felipe Hoffa

Reputation: 59225

If you are testing for table existence by expecting an exception if the table doesn't exist, you could just run a select on the table.

This will generate an exception if the table doesn't exist:

select count(*)
from schema.table

The proposal in the question to look on the information_schema is cool if you need a query that doesn't generate an exception, but once you divide by 0, it's clear that you were asking for one.

Upvotes: 1

Related Questions