SELECT DB

The SELECT DB operation allows you to select data from any supported source database and keep a high watermark to minimize the number of records read progressively.

The recommended way to manage high watermarks is to specify the @highwatermark or @highwatermarknull variable as part of your SQL command.

Executes a request against a database using the SQL command provided and returns rows and columns.

{ APPEND } 
SELECT  
  {TOP N}
  {BATCH N}
  < ... | * >
FROM DB [CONNECTION] 
  -- SQL operation to execute
  ( ... )
  
  { WITH 
    { HWM '...' }
    { WEBHOOK }
    { DEBUG_MAX_PAGES }
    { KEY COLUMNS '...' }
  }

  { APPLY TX '...' 
    { ON < '...' | N > }
    { WITH RAWCOL '...' } 
  }  

  -- Inner pipeline to be executed per paged results
  { APPLY PIPELINE (...) }

;

APPEND	Appends the output of this operation, after APPLY TX if any, to the current data pipeline and adjusts the schema accordingly to accommodate new fields if needed
BATCH	Attempts to override the default number of records fetched at one time against the source system (100000) when possible (requires the use of KEY COLUMNS or the CAPTURE operation with the KEYS option)
TOP	Client-side operator that limits the number of records after the inner pipeline has executed
DEBUG_MAX_PAGES	During preview operations, the maximum number of paging operations to perform
HWM	The column name to use as the field containing the next high watermark value if found; the @highwatermark and @highwatermarknull variables can be used in the SQL operation directly to retrieve this value
APPLY TX	Applies a document transformation on the payload assuming the payload is an XML or JSON document using the xpath provided
APPLY PIPELINE	Executes an inline pipeline consisting of one or more components for the entire data set retrieved or for each batch of data retrieved when batching is possible
ON	When content transformation is enabled, indicates the name of the field to use or its column index
RAWCOL	When content transformation is enabled, indicates the column to add to the response that contains the raw content of each row
WEBHOOK	Enables the pipeline to operate as a webhook and accept incoming HTTP push operations

Example 1

-- Execute a SELECT operation against a MySQL database using the 
-- last_update field as a high watermark. The @highwamark variable 
-- returns an empty string if empty (first-time execution) 
-- The BATCH option creates multiple inner batches of data streams that 
-- are processed within the Inner Pipeline and requires both the CAPTURE 
-- and the KEYS property to function

SELECT BATCH 500 * FROM DB [mysqldb] 
  (
    SELECT * FROM Film WHERE last_update > '@highwatermark'
  )
  WITH HWM 'last_update'

  APPLY PIPELINE (
  
    PRINT 'Inner pipeline starting with @@rowcount records';

  )

;

CAPTURE 'test' INSERT ON KEYS 'id';

Example 2

-- Execute a SELECT operation against a PostgreSQL database using the 
-- last_update field as a high watermark. The @highwamarknull variable 
-- returns null if empty (first-time execution)

SELECT * FROM DB [postgresql] 
  (
    SELECT 
           extract(epoch from last_update) "epoch", * 
           FROM film 
           WHERE  
              last_update > COALESCE('@highwatermarknull', '01/01/2000 0:00AM')::timestamp
  )
  WITH HWM 'last_update'
;

Example 3

-- Execute a SELECT operation against a Snowflake connection
-- While the @highwatermark variable can be used, this engine
-- supports automatic replace of a highwatermark value for trivial
-- operations. 

SELECT * FROM DB [snowflakeconnection] 
  (
    SELECT * FROM DATALAKE..ACTIVECAMPAIGNCONTACTS
  )
  WITH HWM 'cdate'
;