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

maodag
maodag Member
edited March 2023 in SQL DataFlows

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?

Tagged:

Best Answer

  • maodag
    maodag Member
    Answer ✓

    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.

    image.png

    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!)

Answers

  • MichelleH
    MichelleH Coach

    @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
    maodag Member

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

    image.png

    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.

    image.png


    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
    MichelleH Coach

    @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
    maodag Member
    Answer ✓

    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.

    image.png

    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!)

Categories