Is there a way to run ad-hoc SQL queries against arbitrary PostgreSQL tables?
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?
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!)0
@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.1
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
EXPLAINagainst 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.0
@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_US1
- 7.7K All Categories
- 5 Connect
- 921 Connectors
- 244 Workbench
- 477 Transform
- 1.8K Magic ETL
- 60 SQL DataFlows
- 446 Datasets
- 40 Visualize
- 199 Beast Mode
- 2K Charting
- 8 Variables
- 20 Cards, Dashboards, Stories
- 1 Automate
- 348 APIs & Domo Developer
- 82 Apps
- 14 Predict
- 3 Jupyter Workspaces
- 11 R & Python Tiles
- 241 Distribute
- 59 Domo Everywhere
- 241 Scheduled Reports
- 15 Manage
- 36 Governance & Security
- 28 Product Ideas
- 1.1K Ideas Exchange
- Community Forums
- 14 Getting Started
- 1 Community Member Introductions
- 49 Community News
- 18 Event Recordings
- 579 日本支部