.. DO NOT EDIT.
.. THIS FILE WAS AUTOMATICALLY GENERATED BY SPHINX-GALLERY.
.. TO MAKE CHANGES, EDIT THE SOURCE PYTHON FILE:
.. "auto/integrations/gcp/bigquery/bigquery.py"
.. LINE NUMBERS ARE GIVEN BELOW.

.. only:: html

    .. note::
        :class: sphx-glr-download-link-note

        Click :ref:`here <sphx_glr_download_auto_integrations_gcp_bigquery_bigquery.py>`
        to download the full example code

.. rst-class:: sphx-glr-example-title

.. _sphx_glr_auto_integrations_gcp_bigquery_bigquery.py:


BigQuery Query
############

This example shows how to use a Flyte BigQueryTask to execute a query.

.. GENERATED FROM PYTHON SOURCE LINES 7-16

.. code-block:: default

    try:
        from typing import Annotated
    except ImportError:
        from typing_extensions import Annotated

    import pandas as pd
    from flytekit import StructuredDataset, kwtypes, task, workflow
    from flytekitplugins.bigquery import BigQueryConfig, BigQueryTask


.. GENERATED FROM PYTHON SOURCE LINES 17-19

This is the world's simplest query. Note that in order for registration to work properly, you'll need to give your
BigQuery task a name that's unique across your project/domain for your Flyte installation.

.. GENERATED FROM PYTHON SOURCE LINES 19-32

.. code-block:: default

    bigquery_task_no_io = BigQueryTask(
        name="sql.bigquery.no_io",
        inputs={},
        query_template="SELECT 1",
        task_config=BigQueryConfig(ProjectID="flyte", Location="us-west1-b"),
    )


    @workflow
    def no_io_wf():
        return bigquery_task_no_io()



.. GENERATED FROM PYTHON SOURCE LINES 33-39

Of course, in real world applications we are usually more interested in using BigQuery to query a dataset.
In this case we use crypto_dogecoin data which is public dataset in BigQuery.
`here <https://console.cloud.google.com/bigquery?project=bigquery-public-data&page=table&d=crypto_dogecoin&p=bigquery-public-data&t=transactions>`__

Let's look out how we can parameterize our query to filter results for a specific transaction version, provided as a user input
specifying a version.

.. GENERATED FROM PYTHON SOURCE LINES 39-54

.. code-block:: default


    DogeCoinDataset = Annotated[
        StructuredDataset, kwtypes(hash=str, size=int, block_number=int)
    ]

    bigquery_task_templatized_query = BigQueryTask(
        name="sql.bigquery.w_io",
        # Define inputs as well as their types that can be used to customize the query.
        inputs=kwtypes(version=int),
        output_structured_dataset_type=DogeCoinDataset,
        task_config=BigQueryConfig(ProjectID="flyte", Location="us-west1-b"),
        query_template="SELECT * FROM `bigquery-public-data.crypto_dogecoin.transactions` WHERE @version = 1 LIMIT 10;",
    )



.. GENERATED FROM PYTHON SOURCE LINES 55-57

StructuredDataset transformer can convert query result to pandas dataframe here.
We can also change "pandas.dataframe" to "pyarrow.Table", and convert result to Arrow table.

.. GENERATED FROM PYTHON SOURCE LINES 57-68

.. code-block:: default

    @task
    def convert_bq_table_to_pandas_dataframe(sd: DogeCoinDataset) -> pd.DataFrame:
        return sd.open(pd.DataFrame).all()


    @workflow
    def full_bigquery_wf(version: int) -> pd.DataFrame:
        sd = bigquery_task_templatized_query(version=version)
        return convert_bq_table_to_pandas_dataframe(sd=sd)



.. GENERATED FROM PYTHON SOURCE LINES 69-70

Check query result on bigquery console: ``https://console.cloud.google.com/bigquery``


.. rst-class:: sphx-glr-timing

   **Total running time of the script:** ( 0 minutes  0.000 seconds)


.. _sphx_glr_download_auto_integrations_gcp_bigquery_bigquery.py:

.. only:: html

  .. container:: sphx-glr-footer sphx-glr-footer-example


    .. container:: sphx-glr-download sphx-glr-download-python

      :download:`Download Python source code: bigquery.py <bigquery.py>`

    .. container:: sphx-glr-download sphx-glr-download-jupyter

      :download:`Download Jupyter notebook: bigquery.ipynb <bigquery.ipynb>`


.. only:: html

 .. rst-class:: sphx-glr-signature

    `Gallery generated by Sphinx-Gallery <https://sphinx-gallery.github.io>`_