Skip to content

let BigQueryGetData operator take a list of fields for the "order by" clause #39127

Closed
Closed
let BigQueryGetData operator take a list of fields for the "order by" clause#39127
Assignees
shahar1
Labels
area:providersgood first issuekind:featureFeature Requestsprovider:googleGoogle (including GCP) related issues

Description

@lopezvit
lopezvit
opened on Apr 19, 2024

Description

Sometimes you just need a the latest value of a field (e.g. updatedAt) so further operators downstream could use said value in their own query.
This can be done by SELECT MAX(updatedAt) [...] but that would required a lot of re-write, when simply adding a new param ordering_fields could solve the same issue, allowing to create a query similar to:
SELECT updatedAt FROM [...] LIMIT 1 ORDER BY updatedAt DESC

Example implementation (not tested):

def generate_query(self, hook: BigQueryHook) -> str:
    """Generate a SELECT query if for the given dataset and table ID."""
    query = "select "
    if self.selected_fields:
        query += self.selected_fields
    else:
        query += "*"
    query += (
        f" from `{self.table_project_id or hook.project_id}.{self.dataset_id}"
        f".{self.table_id}` limit {self.max_results}"
    )
    if self.ordering_fields:
        query += f" ORDER BY {self.ordering_fields}"
    return query

Use case/motivation

The operator BigQueryGetData should have 1 more params ordering_fields so the generated query would also include the ORDER BY clause.

Related issues

#24460

Are you willing to submit a PR?

  • Yes I am willing to submit a PR!

Code of Conduct

Activity

  1. lopezvit
    added
    kind:featureFeature Requests
    needs-triagelabel for new issues that we didn't triage yet
    on Apr 19, 2024
  2. jscheffl
    added
    provider:googleGoogle (including GCP) related issues
    and removed
    needs-triagelabel for new issues that we didn't triage yet
    on Apr 19, 2024

  3. shahar1 commented on Apr 20, 2024

    @shahar1
    shahar1
    on Apr 20, 2024
    Contributor

    @eladkal Could you please assign me?

  4. eladkal
    assigned
    shahar1
    on Apr 21, 2024
  5. eladkal
    added
    area:providers
    good first issue
    on Apr 21, 2024

  6. shahar1 commented on Apr 23, 2024

    @shahar1
    shahar1
    on Apr 23, 2024
    Contributor

    BigQueryGetData's generate_query() method is used for submitting deferred jobs, while normally, the operator utilizes the hook's method list_rows. The latter utilizes BigQuery's API directly, which does not support ordering clauses. Having the feature available only for queries the deferred operator makes is discouraged.

    There are two options left:

    1. Running the explicit query (including the ORDER BY clause) via BigQueryInsertJobOperator.
    2. Performing the ordering in Python as part of the operator logic, but I'm not sure there is a real advantage compared to the alternative.

    @eladkal What do you think?

  7. Lee-W commented on Apr 24, 2024

    @Lee-W
    Lee-W
    on Apr 24, 2024
    Member
    1. Running the explicit query (including the ORDER BY clause) via `BigQueryInsertJobOperator`.

    I might prefer this one. I think the performance would be better than 2

  8. lopezvit commented on Apr 25, 2024

    @lopezvit
    lopezvit
    on Apr 25, 2024
    Author

    But, how do you get the result when using BigQueryInsertJobOperator? As far as I understood it it returns the job_id, not the result 🤔

  9. shahar1 commented on Apr 26, 2024

    @shahar1
    shahar1
    on Apr 26, 2024
    Contributor

    But, how do you get the result when using BigQueryInsertJobOperator? As far as I understood it, it returns the job_id, not the result 🤔

    You got me on that one, as indeed - it only returns job_id.
    Using BigQueryInsertJobOperator, the only option is to write the results into a BigQuery table, so it might make sense to create an alternative for outputting the results into an XCOM instead.
    One possible implementation would be by adding a flag to the current operator, but then it will also have to wait until the job is actually completed.
    A better option, IMO, would be to create a dedicated operator that will return the results into the XCOM given a job_id, to keep operations atomic.

    @Lee-W, What are your thoughts about this one?

  10. Lee-W commented on Apr 29, 2024

    @Lee-W
    Lee-W
    on Apr 29, 2024
    Member

    A better option, IMO, would be to create a dedicated operator that will return the results into the XCOM given a job_id, to keep operations atomic.

    This sounds to be a better solution 👍

  11. shahar1 commented on Apr 29, 2024

    @shahar1
    shahar1
    on Apr 29, 2024
    Contributor

    A better option, IMO, would be to create a dedicated operator that will return the results into the XCOM given a job_id, to keep operations atomic.

    This sounds to be a better solution 👍

    Thanks :)
    I've been trying out different configurations, and after discussing with @eladkal we came to the conclusion that querying data by job_id could be merged with the existing BigQueryGetData operator, as logic is very similar (also in the deferrable mode).
    I'll create a PR and tag you when I'm done.

  12. eladkal commented on May 17, 2024

    @eladkal
    eladkal
    on May 17, 2024
    Contributor

    Fixed in #39315

  13. eladkal
    closed this as completedon May 17, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

area:providersgood first issuekind:featureFeature Requestsprovider:googleGoogle (including GCP) related issues

Type

No type

Projects

No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions