Datalake Compass View to SFTP

I'm wondering what the best approach is for executing custom SQL against the datalake and passing it to an SFTP site as CSV? i have my SFTP endpoint setup successfully and a Compass View.

However, it appears using the AnySQL modeler within the Query object of a DataLake flow, does not allow me to specify SQL, but only build by object. Nor does it seem to have exposure to a Compass View.

Any thoughts?

Find more posts tagged with

ION Connect

Accepted answers

All comments

Rene Bottern

I assume you are trying to create a 'local application' to handle the logic?

If so, you can, for instance, create a .Net application that will

Call the ION API's necessary to Query out your Data.
- You can find the API's here in MT Cloud
- You then have to execute the API's in order to execute the SQL and get back the Result
  - Jobs - Execute your SQL
  - Jobs/{id}/status - This verifies if SQL has completed running
  - Jobs/{id}/Result - Returns you Resultset in JSON format
Now you can convert the JSON to a CSV and send to SFTP Site

Hopefully this helps (or at least gets you going in the right direction).

Legacy Contributor

Actually, I'm trying to do the work in an ION dataflow.

Legacy Contributor

Steve,

You can do this but it can be a lot of steps! and potentially have problems if you grab too large a block of data and exceed ESB 5MB limitations, so orchestration of this can be critical. Of course, tedious as it may be using the AnySQL interface you can probably make it work using the Retrieve By Query feature - if you take this approach it is much simpler to just create a flow from your query. (Retrieve By Query uses AnySQL against the Data Lake)

But when a little brute force is called for I've used this approach:

In a Data Flow, submit your DL Query via API*

Create a custom BOD to pass the QueryID to a Workflow

Trigger the workflow (it just check the query status every minute until finished

Then pass back to your Data Flow

retrieve results as an AnyDocument and route to your sFTP connection.

Cheers,

David

P.S. * in the initial step you may want ot use a script to dynamically construct your query based on current datetime) to grab consecutive ranges depending on how often you are calling or how much data you want (remember - 5MB limit!)

Copyright © 2025 Infor. All rights reserved.