KNOWLEDGE BASE

Error "[Amazon][Amazon Redshift] (30) Error Occurred While Trying To Execute A Query: [Sqlstate 22P02] Error: Invalid Input Syntax For Integer" When Refreshing Extracts, Workbooks Or Running Subscription


Published: 01 Jun 2020
Last Modified Date: 15 Jun 2020

Issue

When attempting to refresh an Amazon RedShift workbook/data source or run a subscription, the following error occurs:

[Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 22P02] ERROR:  invalid input syntax for integer: ""
[Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 22P02] ERROR:  invalid input syntax for integer: ""
The table "TABLE" does not exist.
Unable to connect to the Amazon Redshift server "SERVER". Check that the server is running and that you have access privileges to the requested database.

Environment

  • Tableau Online
  • Tableau Desktop
  • Driver Version: 1.4.10.1000 or later. 

Resolution

As a temporary workaround, use one of the below options.

Tableau Online:

Option 1 Update the impacted database views to use the correct data types for impacted columns.
  1. Run the following SQL on the database to identify the impacted columns:
with lbv_cols as (
SELECT lbv_cols.table_schema,
      lbv_cols.table_name,
      lbv_cols.column_name,
      REGEXP_REPLACE(REGEXP_REPLACE(lbv_cols.columntype, '\\(.*\\)'), '^_.+', 'ARRAY') AS columntype_rep,
     lbv_cols.columntype,
      lbv_cols.columnnum
FROM pg_get_late_binding_view_cols() lbv_cols(table_schema name, TABLE_NAME name, COLUMN_NAME name, columntype text, columnnum int))
select * from lbv_cols where columntype in ('numeric','decimal','varchar','character varying','char'); 
  1. Based on the results of the query, identify the impacted late binding views (table_name) and columns (column_name) in those views. If precision+scale (numeric/decimal) or size (varchar/char) information is missing from the columntype value, it is an impacted column and will need an explicit cast in the late binding view definition. 
  1. Get the view definitions for the impacted views: 
select view_definition from information_schema.views where table_name=‘{table_name}’; 
  1. Modify the view to cast the affected column(s) to a type with specified size/precision/scale.

 Example:
 
dev=# create table t(c numeric(38,6), c2 int);
dev=# create or replace view v as select s from (select * from public.t left join (select sum(c) as s from public.t) on 1=1 ) with no schema binding;
/* running the above query … table_name refers to the object (late binding view) and column_name is which column is causing the issue  */
dev=#
table_schema | table_name | column_name | columntype_rep | columntype | columnnum
--------------+------------+-------------+----------------+------------+-----------
public       | v          | s           | numeric        | numeric    |         1
dev=# select view_definition from information_schema.views where table_name='v';
                                                                 view_definition
-------------------------------------------------------------------------------------------------------------------------------------------------
create or replace view v as select s from (select * from public.t left join (select sum(c) as s from public.t) on 1=1 ) with no schema binding;
/* cast output to expected precision+scale or character length in the case of varchar */
dev=#  create or replace view v2 as select s::numeric(38,6) from (select * from public.t left join (select sum(c) as s from public.t) on 1=1 ) with no schema binding;
/* query from above without the columntype filter.  Note how the columntype now includes precision and scale. It will no longer throw the error */
def=# …
select * from lbv_cols where table_name='v2';
table_schema | table_name | column_name | columntype_rep |  columntype   | columnnum
--------------+------------+-------------+----------------+---------------+-----------
public       | v2         | s           | numeric        | numeric(38,6) |         1

Option 2 Convert any impacted late-binding views to standard views. 

Option 3 Use Tableau Bridge with version 1.4.2 of the Redshift driver to refresh the affected data sources. See Expand Data Freshness Options by Using Tableau Bridge and Use Tableau Bridge to Keep Tableau Online Data Fresh for more information. 

Option 4 Manually refresh the published data source with version 1.4.2 of the Redshift driver. See Refresh Published Extracts from Tableau Desktop for more information. 


Tableau Desktop: Downgrade the Amazon Redshift driver to 1.4.2. 

Cause

This issue is being investigated by Tableau development team.
Did this article resolve the issue?