The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.
community Alteryx IO Alteryx.com
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

AACP Product Ideas

Share your AACP product ideas, including Designer Cloud, Intelligence Suite and more - we're listening!

BigQuery SQL Connector: Add support for WITH statements / structured SQL scripts

Status: New Idea Submitted by on ‎07-22-2021 05:28 AM
1 Comment (1 New)

When using an SQL Statement with a WITH Query Expression I am getting the following error: No select statement found. I was told that WITH statements are currently not supported at the moment.

Why this should be changed:

  1. WITH statements are very important to structure long and complex SQL scripts and reducing heavily nested (unreadable) SQL scripts.
  2. We have a lot of scripts that we want to migrate, but we are stuck as it would take too much time and effort to transform the script. Same for moving logic to Dataprep recipes.


Best regards

Marcel



Details about the syntax:

https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax

Related customer questions

  • https://community.trifacta.com/s/question/0D53j00006OatIdCAJ/is-it-possible-to-use-cte-common-table-expressions-when-importing-a-data-set-using-custom-sql-i-am-specifically-trying-to-use-with-statements


See more ideas labeled with:
1 Comment
NVaziri_Tri
Alteryx Alumni (Retired)
‎07-22-2021 11:01 AM

Hi Marcel,

Thank you for submitting your idea regarding support for SQL WITH clauses, or Common Table Expressions (CTEs). CTEs are not currently supported for Custom SQL Datasets, however they are supported in Output object Pre and Post Run SQL Scripts that can run before or after a job run to perform a variety of pre and post processing tasks such as creating or updating tables, views, or inserting log entry records into the database.

Our team is evaluating the feasibility of adding CTE support for Custom SQL Input Datasets in the future. This is a common feature request across the industry and you will find certain limitations around CTEs in other products as well.

In the meantime, there are three options we recommend:

1) Join multiple alias derived table/inline view subqueries in your Custom SQL Dataset queries instead of using CTE syntax. This will keep things as a single select statement and should still return the same results.

2) Register your CTEs as Tables or Views within the BigQuery console and then pull them into Dataprep for further wrangling. In theory, you could also add wrap these CTEs with CREATE TABLE or VIEW statements and use Dataprep to execute these as Pre and Post SQL Scripts.

3) Break each subquery from the CTE into its own Custom SQL Dataset and then use the Join recipe task to combine them within your flow.

Please keep an eye out for future product updates in this area...

Regards,

Nate