Is it possible to use a Data Lake Compass query to create a BOD via an ION Document Flow / Data Lake

I have a mildly complex query I wrote in Compass that pulls pricing information. I have different customers who need this information in their own format. I have the data in the query looking like I need, but I need to sent it as a BOD to ION. Is there a way to call a Compass query and feed the results into ION?

I've tried a Data Lake Flow, but it doesn't allow a query, only single files.

I've tried IONAPI in a Document Flow, but the Compass API is not listed in the IONAPI list.

I've tried writing it in a Python script, but ION blocks network access, and it times out. (This would probably time out anyways, the Compass API is slow)

Any ideas appreciated.

Find more posts tagged with

Infor Data Fabric

Accepted answers

Legacy Contributor

today that is a little tricky in a simple step - we do have a new feature coming early 2020 that will provide AnySQL over the Data Lake which will allow you to build out Compass statements to run - so depending on the complexity of your statement that might be an option in the coming months.

As a workaround today could be to write a client app to pull the data from the Data Lake to an on-premises DB, using the Compass API's - from there use the AnySQL or stored procedure DB connector to pull the data in to ION and then format and manipulate via mapping or scripts to fit your outbound needs. We do have an out-of-the-box on-premises ETL tool that can read the Data Lake and write to OP DB's but that is an add-on product, so writing the client part yourself would avoid any additional licence cost until the AnySQL over Data Lake is available

hope this is of some help

Legacy Contributor

Hey Billy,

How "mildly complex" is your query?

The reason I ask is that AnySQL will support publishing BODs as a supported data format next year (ETA is TBD). However, AnySQL supports pure data extraction insofar as it doesn't provide manipulation or data transformation features...yet. Additionally, I presume this would be a flat BOD? Or are you looking to produce a hierarchical structure?

Given the scenario, I might try to publish the contents of the query as text/CSV and land that into a file server. You could then use ION's File Templates feature to convert that to a BOD. A database scenario, rather than file server, could work just as well.

If you have a moment, head over to the Enhancement Request System if there's a particular feature you'd like to see.

All comments

Legacy Contributor

Hey Billy,

How "mildly complex" is your query?

If you have a moment, head over to the Enhancement Request System if there's a particular feature you'd like to see.

Legacy Contributor

Hello Mike;

It's pulling data from six tables with an outer join on the barcode information. I've actually written a tool to pull the information from the APIs. I thought about writing to a file system to have ION pick it up and transform it to the BOD. You are correct, I would either limit it with the where clause for each vendor and have ION handle the final processing.

Thanks;

Billy

Legacy Contributor

Greetings;

This sounds very interesting. I think that would work. For now, I think I'll have to write a pull and send it up to ION for processing. I look forward to the AnySQL update.

Thanks;

Billy

Legacy Contributor

Hey Mike,

We are in a same scenario as well looking to execute a Compass query to extract data to a csv file. So is it possible to call a Compass query from ION data flow or data lake flow? We are currently designing it in IPA and would like to use ION if possible.

Thanks

Legacy Contributor

Hey Billy,

We're expecting AnySQL to support Data Lake querying in Q1 of 2020. That would allow you to join those tables and filter on a barcode just fine. Initially, you would be able to produce a JSON or ndjson file output. That would leave you having to write a transformation step after this to produce a BOD (e.g. using ION Scripting) - one step closer to what you're looking for but one step away from a comprehensive solution

In Q2 (though, ideally sooner), AnySQL will support generating BODs, flat files (CSV, TSV, PSV), as well as JSON and ndjson. On paper, I think that would be a fairly comprehensive approach to iterate through as these features become available.

Legacy Contributor

Hey Venky,

Compass supports producing CSVs but you cannot yet call the Compass APIs via Data Flows in ION. There is a process pattern (submit, status, & retrieve) to calling the APIs.

AnySQL enhancements (referenced elsewhere) may be ideal for your solution scenario in the future.

Legacy Contributor

Any update on this feature?

Copyright © 2025 Infor. All rights reserved.