Is there a way to view the SQL query of an output dataset in Domo?
We are working on exporting our data to Cognos and it would be nice to see the query we are working with in Domo when recreating it in Cognos.
@EWold It depends on where your dataset comes from. If the dataset is an output of a dataflow, you should be able to see the query in the dataflow editor if it is from a MySQL or Redshift dataflow. Otherwise, I don't believe there is a way to produce the SQL query from a MagicETL.
Magic ETL isn't performing SQL under the hood so there's no SQL to extract from the ETL. It's all done using Apache Spark. You'd need someone to look through your ETL logic and then write then translate it into SQL.
My dataset does come from the output of a DataFlow, but I'm unsure where to see the SQL behind the DataFlow. Where in DataFlow editor can I see it? Below is a basic example of what I'm working with.
@EWold , do you have anyone in your organization who knows SQL? The Magic ETL is just a visual representation of SQl, so if there's somebody who's knowledgeable with SQL, they should be able to use the ETL to write an equivalent query.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**
@RobSomers unfortunately, "The Magic ETL is just a visual representation of SQl" this is not an accurate statement and is potentially misleading.
Although Magic ETL transforms borrow heavily from transforms we are accustomed to performing in SQL and even the Formula Tile allows you to write functions borrowed from MySQL, it is not accurate to assume that the engine underneath Magic ETL is a SQL database.
I believe the functions under the covers are implemented in Java and they are performing operations in more of a streaming text pipeline.
This might be splitting hairs, but it's important to understand because if you think of Magic as a database layer with Tables where you're issuing UPDATE commands against a set of records, it may discourage you from thinking of ways you might try to support Magic as it subdivides your tasks across multiple nodes for parallel processing.
In fact the biggest difference and improvement in Magic over your SQL pipelines (Adrenaline, Redshift, and MySQL) include
1) Magic does not have to load all the data into a table before it can be processing the data. Because the data is moving in a text stream, as first as the first row of data arrives it can start flowing through your ETL tiles.
2) Magic does not have to Index. (this is a double edged sword). Because Magic is not a database engine there is no concept of indexing. therefore JOINs, RANK, and SORT operations would be slower than the equivalent operation performed in Redshift or Adrenaline (assuming all other factors were equal).