How to Replace ODBC Connection with Trino CLI in Alteryx Designer (DAL)

[ahmadhw]

Hi team,

I’m looking to integrate Trino CLI directly into Alteryx Designer to allow end users to input their SQL queries and get the results directly in the Designer -Results could be millions of records!!--

(without using an ODBC connection due to odbc low performance).

I was thinking about run command tool but failed to pass the results directly

Example of command :

java -jar "C:\Trino\trino-cli-474-executable.jar" trino://<hostname>:8443?SSL=true --execute "Select * from table" --truststore-path "C:\Trino\prddal.crt" --user alteryxsrvacc --password

If possible:. The goal is to avoid storing query results in a permanent output file on the server, as there will be multiple queries being run. I’d prefer not to store files at all, and I want the data to be streamed directly into Alteryx for further processing.

Key requirements:

# Allow end users to input their own SQL queries.

# Get query results directly into Alteryx Designer.

#If possible : Avoid file-based outputs, as there will be many queries executed.

#Replace the ODBC connection mechanism with Trino CLI for performance and scalability.

Has anyone successfully integrated Trino CLI this way in Alteryx Designer? If so, could you provide guidance or an example of how to achieve this?

[apathetichell]

"Has anyone successfully integrated Trino CLI this way in Alteryx Designer? If so, could you provide guidance or an example of how to achieve this?"

my hunch is no. I doubt it. Trino isn't very common in the Alteryx ecosystem. You can do what you are doing via run command assuming that all machines have java environments and access to any Jars. you can execute via run command. It's up to you and/or your consultants to get this to work though.

[ahmadhw]

I tried to use run command tool but failed 😕 as I'm not expert with it in this kind of issues 

If you have any idea how to use it through this issue, please guide me 

How can I display the results that comes in CMD in Alteryx as output of run command tool

[apathetichell]

create your run command in your formula tool - export it to csv - run the command - see the error. Run this locally - run it on server. keep in mind any paths you are passing in must be accurate on server any requirements (java/jar files) must be preset up - or you must script those into your run command. This is not easy. The best thing to do first is to test your run command syntax locally to make sure it works.

if you are running on vms -> and they already have python - maybe use python? take baby steps.

[apathetichell]

some additional testing - I don't have Trino - but maybe i'll get around to playing with the docker container of it this weekend. I had some fears that the jar wouldn't execute on windows and you'd need to run it in a WSL. That is not the case - you can run the JAR on windows. Having said that - you will need to create some kind of output files - they can be temp - but you'll need to generate them if you want to read your query results into Alteryx. you can get around this with https://github.com/trinodb/trino-python-client -> which can create a pandas dataframe which Alteryx can process in memory.

[ahmadhw]

Its running fine in the server using CMD command without any issues with results in blue bracket 
My confusion only is how to pass these results through Alteryx to use it inside workflow ( Keep in mind many users may run different queries and run workflows simultaneously) 🤔

[apathetichell]

As I mentioned - you cannot get that output directly into Alteryx. You will need to pipe it via the output format tag and > into a file. you can parameterize all of this in the formula tool then output to a .bat file and run the .bat file. your file should be a a variable (as should your query) and your output on your run command has to be set to no delimiter (/n), no header csv with one column of data going in (your fixed name .bat file). you set up a block until done beforehand.

you take your filename and read it in via batch macro from another end of your BUD tool so you can access whatever came out of your query.

so short is two fields:

1) query

2) static filename which has your local path (working directory), a standard name and probably a timestamp affixed to it.

3) - these go into your new fields which is the content of your .bat file.

4) BUD Tool.

5) BUD A) drops fields 1/2/ uses run command to ouptut and run field 3)

6) BUD B) launches batch macro and feeds in filename from 2)

you may want to hire a consultant.