You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "FridayPush (via GitHub)" <gi...@apache.org> on 2024/12/04 00:09:22 UTC
[I] SQLToGoogleSheetOperator unintuitive handling of nulls [airflow]
FridayPush opened a new issue, #44628:
URL: https://github.com/apache/airflow/issues/44628
### Apache Airflow Provider(s)
google
### Versions of Apache Airflow Providers
apache-airflow-providers-google==10.22.0
### Apache Airflow version
2.10.1
### Operating System
Linux
### Deployment
Amazon (AWS) MWAA
### Deployment details
_No response_
### What happened
When using the `SQLToGoogleSheets` operator data that is null is incorrectly updated in the Google Sheet; primarily if pre-existing data is in place.
### What you think should happen instead
The Range of cells updated by the function should be cleared before uploading the new values, so that null values are updated correctly. For example the following Operator resolves the issue.
```python
class SqlToGoogleSheetWithClear(SQLToGoogleSheetsOperator):
def execute(self, context: Any) -> None:
sheet_hook = GSheetsHook(
gcp_conn_id=self.gcp_conn_id,
delegate_to=self.delegate_to,
impersonation_chain=self.impersonation_chain,
)
sheet_hook.clear(
spreadsheet_id=self.spreadsheet_id,
range_=f"{self.spreadsheet_range}!A1:ZZ", #More complex logic needed if not the raw sheet
)
super().execute(context)
```
### How to reproduce
To recreate: Populate a google sheet's First row so all columns have the value `1`. In the operator add the query
```sql
select 2::int as id, null as name, 'William' as last_name
```
The google sheet update process sees the 'null' name column as empty and doesn't update the existing cell populated with the value `1` resulting in:
```
id, name, last_name
2, 1, William
```
### Anything else
I'm willing to submit a PR but unsure the best approach to handle backwards compatibility. An additional parameter could be included to truncate before load, but I do believe this feels like a bug to me. If my SQL returns Null I expect the cell's value to be empty on a successful run of the operator.
### Are you willing to submit PR?
- [X] Yes I am willing to submit a PR!
### Code of Conduct
- [X] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
--
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.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] SQLToGoogleSheetOperator unintuitive handling of nulls [airflow]
Posted by "eladkal (via GitHub)" <gi...@apache.org>.
eladkal commented on issue #44628:
URL: https://github.com/apache/airflow/issues/44628#issuecomment-2544037861
> The google sheet update process sees the 'null' name column as empty
I am not convinced this is a bug in the operator.
This feels like the data in the Sheet is not so clean to begin with. I would suggest to subclass the operator and implement `pre_execute()` to clean the data.
WDYT?
--
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
Re: [I] SQLToGoogleSheetOperator unintuitive handling of nulls [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] closed issue #44628: SQLToGoogleSheetOperator unintuitive handling of nulls
URL: https://github.com/apache/airflow/issues/44628
--
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
Re: [I] SQLToGoogleSheetOperator unintuitive handling of nulls [airflow]
Posted by "boring-cyborg[bot] (via GitHub)" <gi...@apache.org>.
boring-cyborg[bot] commented on issue #44628:
URL: https://github.com/apache/airflow/issues/44628#issuecomment-2515844918
Thanks for opening your first issue here! Be sure to follow the issue template! If you are willing to raise PR to address this issue please do so, no need to wait for approval.
--
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
Re: [I] SQLToGoogleSheetOperator unintuitive handling of nulls [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #44628:
URL: https://github.com/apache/airflow/issues/44628#issuecomment-2641466103
This issue has been closed because it has not received response from the issue author.
--
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
Re: [I] SQLToGoogleSheetOperator unintuitive handling of nulls [airflow]
Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #44628:
URL: https://github.com/apache/airflow/issues/44628#issuecomment-2617240662
This issue has been automatically marked as stale because it has been open for 14 days with no response from the author. It will be closed in next 7 days if no further activity occurs from the issue author.
--
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