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?
Best 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.
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
Answers
-
@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
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.
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_US
1 -
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
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.6K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 60 App Studio
- 41 Variables
- 687 Automate
- 177 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 397 Distribute
- 114 Domo Everywhere
- 276 Scheduled Reports
- 7 Software Integrations
- 126 Manage
- 123 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 109 Community Announcements
- 4.8K Archive