KNOWLEDGE BASE

Amazon Redshift Extract Fails or Completes with Missing Data


Published: 20 Nov 2020
Last Modified Date: 08 Apr 2021

Issue

Occasionally when refreshing large Redshift extracts, one of the following issues occurs:

  • The refresh is successful but only gathers a small subset of the data

    For example, a Redshift extract that is expected to have 100s of millions of rows will show as "successful" in Background tasks for extract but the extract will only contain less than 50k rows.

  • The refresh will fail with the error, "The connection to the data source might have been lost"

Environment

  • Tableau Server
  • Tableau Desktop
  • Amazon Redshift

Resolution

As a workaround, use a .tdc file to disable the following CAP setting

 CAP_ODBC_CONNECTION_STATE_VERIFY_PROBE_PREPARED_QUERY 

Note: Using a .tdc file on Tableau Server will override any customizations applied in Tableau Desktop using driver parameters. If other customizations are being applied using driver parameters use the following format to apply this fix to the data source in Tableau Desktop.

'CAP_ODBC_CONNECTION_STATE_VERIFY_PROBE_PREPARED_QUERY' value='no'

Cause

This issue is related to the behavior of the Redshift driver. There are two aspects to this issue:
  • A secondary, redundant, connection check is being executed with a prepare query and canceling the long-running query. 
  • When the prepare query connection check is performed the long running query is canceled and the Redshift driver fails to return an error message to Tableau Server. Therefore, Tableau Server believes it is the end of the data and marks the extract as "Successful". 

Additional Information

  • Basic .tdc file should include the redshift names and the CAP ODBC statement, as shown below:
    <connection-customization class='redshift' enabled='true' version='9.1'>
      <vendor name='redshift' />
      <driver name='redshift' />
      <customizations>
    <customization name='CAP_ODBC_CONNECTION_STATE_VERIFY_PROBE_PREPARED_QUERY' value='no' />
      </customizations>
    
    
    
See the attached redshift.tdc file for an example. 
Did this article resolve the issue?