How to resolve an issue with all rows returned on splunk data when a query to select only 5 rows is executed ?

How to resolve an issue with all rows returned on splunk data when a query to select only 5 rows is executed ?

book

Article ID: KB0072150

calendar_today

Updated On:

Products Versions
TIBCO Data Virtualization 8.5.x

Description

A query of the type:
                'Select TOP 5 from <tablename>'
on a Splunk data source returns all rows instead of 5 rows


User-added image

Resolution

The following steps may be used to push the limit down to Splunk.

(1)  Back up the file <TDV_INSTALL_DIR>\packages\Splunk_1\conf\Splunk_properties.yaml

User-added image

(2)  Open Splunk_properties.yaml with a text editor, and insert the following lines under the   ' members:' section  located under the  ' clause_support:' section :

  limit.clause:
    display_name: LIMIT
    native_expression: LIMIT


After inserting, the Splunk_properties.yaml  file would look like this:

clause_support:
  value_required_for_adapter: true
  value_required_for_datasource: false
  value_overridable_by_datasource: true
  display_in_ui: false
  members:
    select.clause:
      display_name: SELECT
      native_expression: SELECT
    where.clause:
      display_name: WHERE
      native_expression: WHERE
      unsupported_derived_types: lobs, varbinary, longvarchar, xml
    orderby.clause:
      display_name: ORDER BY
      native_expression: ORDER BY
      unsupported_derived_types: lobs, varbinary, longvarchar, xml
    groupby.clause:
      display_name: GROUP BY
      native_expression: GROUP BY
      unsupported_derived_types: lobs, varbinary, longvarchar, xml
    having.clause:
      display_name: HAVING
      native_expression: HAVING
      unsupported_derived_types: lobs, varbinary, longvarchar, xml
    limit.clause:
      display_name: LIMIT
      native_expression: LIMIT    


(3)  Restart the TDV server.

Run a query in Scratchpad

Example
SELECT TOP 5 *  FROM  
/shared/DATA_SOURCEs/sources/cdata/Splunk/CIS/Splunk/AuditIninternal_audit_logs
      
In the above example, the expected result is that 5 rows should be returned (assuming that the table 'AuditIninternal_audit_logs' contains 5 or more rows). 


      

Issue/Introduction

When a query such as 'Select TOP 5 from ' is executed against a Splunk data source it returns all rows instead of the expected 5 rows. This article details how this issue can be resolved.