Is there a way to run ad-hoc SQL queries against arbitrary PostgreSQL tables?

maodag · 2022-11-25T02:06:41+00:00

There was an error rendering this rich post.

My company is migrating from Mode Analytics to Domo, and I'm trying to understand whether and how I can run ad-hoc SQL queries against arbitrary tables. As a software engineer, I regularly use Mode's [query editor](https://mode.com/help/articles/querying-data/) to gain insights into our PostgreSQL database data in real-time and run EXPLAIN to see SQL queries' query plans.

So far, I've managed to set up a federated agent and was able to create Federated DataSets that use the agent, but I could only find the Analyzer's GUI interface for querying data.

Does Domo actually have no support for running ad-hoc SQL queries?

Also, would I need to manually create a Federated DataSet for each database table that I'd want to query?

Find more posts tagged with

SQL

Federated

Accepted answers

maodag

Yes, I have a PostgreSQL SSH connector set up, and it does allow me to write arbitrary queries (including `EXPLAIN`). However, it's more of a workaround than a full-fledged solution for running exploratory queries in real-time.

The query editor is a simple textarea
Editing the query and re-running take multiple steps (the dataset settings page has a preview table that can be refreshed at will without moving away from the settings page, but it doesn't let you resize its columns to view the full values)

So I was hoping that the Federated Data solution which said it lets you query data on-demand would have better support for this kind of use case.

It seems like running exploratory SQL queries in real-time is not a paradigm supported by Domo.

(Thanks for your help so far, by the way!)

All comments

MichelleH

@maodag What connector are you using to create your Federated Datasets? Some connectors allow you to write SQL queries directly in the request. You could also use MySQL dataflows, though that requires having a dataset created for each input table.

Here is a KB article that outlines all of the available data processing tools and when to use them: https://domo-support.domo.com/s/article/360042935434?language=en_US#3.4.

maodag

I believe the connector is "PostgreSQL Federated Query" or "postgres-federated".

I can only seem to choose which table to connect it to and there's no option to enter a raw SQL query.

You could also use MySQL dataflows, though that requires having a dataset created for each input table.

This wouldn't allow me to run EXPLAIN against the original PostgreSQL database, I guess? And I when I try to select the federated DataSet as the input, it says that it's not supported.

Here is a KB article that outlines all of the available data processing tools and when to use them

Thanks - this doesn't seem to have anything that'd allow me to write arbitrary SQL queries.

MichelleH

@maodag Have you tried the PostgreSQL connector (non-federated)? It looks like you can write a query directly in this version of the connector: https://domo-support.domo.com/s/article/360043436273?language=en_US

maodag

The query editor is a simple textarea
Editing the query and re-running take multiple steps (the dataset settings page has a preview table that can be refreshed at will without moving away from the settings page, but it doesn't let you resize its columns to view the full values)

So I was hoping that the Federated Data solution which said it lets you query data on-demand would have better support for this kind of use case.

It seems like running exploratory SQL queries in real-time is not a paradigm supported by Domo.

(Thanks for your help so far, by the way!)