You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by GitBox <gi...@apache.org> on 2021/07/09 06:59:53 UTC

[GitHub] [airflow] aaron-ang opened a new issue #16906: SQL query in Airflow job differs from that in yaml

aaron-ang opened a new issue #16906:
URL: https://github.com/apache/airflow/issues/16906


   -->
   
   **Apache Airflow version**: 1.10.10
   
   
   **Kubernetes version (if you are using kubernetes)** (use `kubectl version`):
   
   **Environment**: 
   
   - **Cloud provider or hardware configuration**: 
   - **OS** (e.g. from /etc/os-release): Ubuntu 18.04.2 LTS (Bionic Beaver)
   - **Kernel** (e.g. `uname -a`): 5.4.0-1041-aws
   - **Install tools**:
   - **Others**:
   
   **What happened**: After a SQL CTE, the next select statement is not captured by the Airflow job, causing the SQL to produce an error as the structure is altered
   
   <!-- (please include exact error messages if you can) -->
   
   **What you expected to happen**: Expected the job to capture the whole SQL query in the yaml file entirely
   
   <!-- What do you think went wrong? -->
   
   **How to reproduce it**: Use a Qualify statement(using Snowflake ANSI SQL) right before the end of the CTE in a yaml file
   <!---
   
   As minimally and precisely as possible. Keep in mind we do not have access to your cluster or dags.
   
   If you are using kubernetes, please attempt to recreate the issue using minikube or kind.
   
   ## Install minikube/kind
   
   - Minikube https://minikube.sigs.k8s.io/docs/start/
   - Kind https://kind.sigs.k8s.io/docs/user/quick-start/
   
   If this is a UI bug, please provide a screenshot of the bug or a link to a youtube video of the bug in action
   
   You can include images using the .md style of
   ![original query](https://ibb.co/ssmKmtj)
   ![job query](https://ibb.co/b5W6jKN)
   
   To record a screencast, mac users can use QuickTime and then create an unlisted youtube video with the resulting .mov file.
   
   --->
   
   
   **Anything else we need to know**: Issue occurs and is causing failure in every run
   ![original query](https://ibb.co/ssmKmtj)
   ![job query](https://ibb.co/b5W6jKN)
   
   <!--
   
   Any relevant logs to include? Put them here in side a detail tag:
   <details><summary>x.log</summary> lots of stuff </details>
   
   -->
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang edited a comment on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang edited a comment on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang edited a comment on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang edited a comment on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174


   perhaps I should share the structure of my query to make myself clearer. My query works perfectly in Snowflake, but when it is picked up by Airflow, it gets cut off.
   
   ```
   WITH SCFS_COMPANIES AS (
     select...
     from...
     where...
     QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) = 1
   )
   "select text as" column name
   from...
   ```
   Unless there is something regarding semicolon rules that I am unaware of? Thanks.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang edited a comment on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang edited a comment on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174


   perhaps I should share the structure of my query to make myself clearer. My query works perfectly in Snowflake, but when the yaml is picked up by Airflow, the query gets cut off.
   
   ```
   WITH SCFS_COMPANIES AS (
     select...
     from...
     where...
     QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) = 1
   )
   "select md5(coalesce(...)) as column_name,"
   column2 as column_name_2,
   ...
   from...
   ```
   Basically the line in quotes got ignore/did not get captured. Unless there is something regarding semicolon rules that I am unaware of? Thanks.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang edited a comment on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang edited a comment on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174


   perhaps I should share the structure of my query to make myself clearer. My query works perfectly in Snowflake, but when it is picked up by Airflow, it gets cut off.
   
   ```
   WITH SCFS_COMPANIES AS (
     select...
     from...
     where...
     QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) = 1
   )
   "select column as column_name"
   column2 as column_name_2
   from...
   ```
   Basically the line in quotes got ignore/did not get captured. Unless there is something regarding semicolon rules that I am unaware of? Thanks.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang edited a comment on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang edited a comment on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174


   perhaps I should share the structure of my query to make myself clearer. My query works perfectly in Snowflake, but when it is picked up by Airflow, it gets cut off.
   
   ```
   WITH SCFS_COMPANIES AS (
     select...
     from...
     where...
     QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) = 1
   )
   select ...
   from...
   ```
   Unless there is something regarding semicolon rules that I am unaware of? Thanks.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] potiuk commented on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
potiuk commented on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877084332


   Like in all sql dialects, you need to separate statements with `;`. I believe snowflake hook supports it already. Make sure to use the latest snowflake provider, as there were some improvements in this part. https://airflow.apache.org/docs/apache-airflow-providers-snowflake/stable/index.html


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang edited a comment on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang edited a comment on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] potiuk closed issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
potiuk closed issue #16906:
URL: https://github.com/apache/airflow/issues/16906


   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang edited a comment on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang edited a comment on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174






-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang commented on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang commented on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877305595


   @potiuk do you mean inserting a `;` right after the CTE? My entire query consists of a CTE, along with a select statement. It does not make sense to break them apart using `;`


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] boring-cyborg[bot] commented on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
boring-cyborg[bot] commented on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-876962720


   Thanks for opening your first issue here! Be sure to follow the issue template!
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang commented on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang commented on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174


   perhaps I should share the structure of my query to make myself clearer. My query works perfectly in Snowflake, but when it is picked up by Airflow, it gets cut off.
   
   `
   WITH SCFS_COMPANIES AS (
     select...
     from...
     where...
     QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) = 1
   )
   select ...
   from...
   `
   Unless there is something regarding semicolon rules that I am unaware of? Thanks.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org

[GitHub] [airflow] aaron-ang edited a comment on issue #16906: SQL query in Airflow job differs from that in yaml

Posted by GitBox <gi...@apache.org>.
aaron-ang edited a comment on issue #16906:
URL: https://github.com/apache/airflow/issues/16906#issuecomment-877308174


   perhaps I should share the structure of my query to make myself clearer. My query works perfectly in Snowflake, but when it is picked up by Airflow, it gets cut off.
   
   `
   WITH SCFS_COMPANIES AS (\n
     select...
     from...
     where...
     QUALIFY ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) = 1
   )
   select ...
   from...
   `
   Unless there is something regarding semicolon rules that I am unaware of? Thanks.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: commits-unsubscribe@airflow.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org