Python pandas_gbq.read_gbq() Examples

def get_definitions(self):
        '''get the definitions from the master source

        Returns:
            df (pandas dataframe): dataframe

        '''
        projectid = self.config["definitions"]['project']

        if 'query' in self.config["definitions"]:
            q = self.config["definitions"]['query']
        else:
            dataset = self.config["definitions"]['dataset']
            table = self.config["definitions"]['table']
            q = """SELECT * FROM `%s.%s`""" % (dataset, table)

        return pandas_gbq.read_gbq(q, project_id=projectid) 

def main(project_id):
    # [START bigquery_pandas_gbq_read_gbq_simple]
    import pandas_gbq

    # TODO: Set project_id to your Google Cloud Platform project ID.
    # project_id = "my-project"

    sql = """
    SELECT country_name, alpha_2_code
    FROM `bigquery-public-data.utility_us.country_code_iso`
    WHERE alpha_2_code LIKE 'A%'
    """
    df = pandas_gbq.read_gbq(sql, project_id=project_id)
    # [END bigquery_pandas_gbq_read_gbq_simple]
    print(df)
    return df 

def main(project_id):
    # [START bigquery_pandas_gbq_read_gbq_legacy]
    sql = """
    SELECT country_name, alpha_2_code
    FROM [bigquery-public-data:utility_us.country_code_iso]
    WHERE alpha_2_code LIKE 'Z%'
    """
    df = pandas_gbq.read_gbq(
        sql,
        project_id=project_id,
        # Set the dialect to "legacy" to use legacy SQL syntax. As of
        # pandas-gbq version 0.10.0, the default dialect is "standard".
        dialect="legacy",
    )
    # [END bigquery_pandas_gbq_read_gbq_legacy]
    print(df)
    return df 

def test_read_gbq_should_use_dialect(mock_bigquery_client):
    import pandas_gbq

    assert pandas_gbq.context.dialect is None
    pandas_gbq.context.dialect = "legacy"
    pandas_gbq.read_gbq("SELECT 1")

    _, kwargs = mock_bigquery_client.query.call_args
    assert kwargs["job_config"].use_legacy_sql

    pandas_gbq.context.dialect = "standard"
    pandas_gbq.read_gbq("SELECT 1")

    _, kwargs = mock_bigquery_client.query.call_args
    assert not kwargs["job_config"].use_legacy_sql
    pandas_gbq.context.dialect = None  # Reset the global state. 

def main(project_id):
    # [START bigquery_pandas_gbq_read_gbq_legacy]
    sql = """
    SELECT country_name, alpha_2_code
    FROM [bigquery-public-data:utility_us.country_code_iso]
    WHERE alpha_2_code LIKE 'Z%'
    """
    df = pandas_gbq.read_gbq(
        sql,
        project_id=project_id,
        # Set the dialect to "legacy" to use legacy SQL syntax. As of
        # pandas-gbq version 0.10.0, the default dialect is "standard".
        dialect="legacy",
    )
    # [END bigquery_pandas_gbq_read_gbq_legacy]
    print(df)
    return df 

def get_pandas_df(
        self, sql: str, parameters: Optional[Union[Iterable, Mapping]] = None, dialect: Optional[str] = None
    ) -> DataFrame:
        """
        Returns a Pandas DataFrame for the results produced by a BigQuery
        query. The DbApiHook method must be overridden because Pandas
        doesn't support PEP 249 connections, except for SQLite. See:

        https://github.com/pydata/pandas/blob/master/pandas/io/sql.py#L447
        https://github.com/pydata/pandas/issues/6900

        :param sql: The BigQuery SQL to execute.
        :type sql: str
        :param parameters: The parameters to render the SQL query with (not
            used, leave to override superclass method)
        :type parameters: mapping or iterable
        :param dialect: Dialect of BigQuery SQL – legacy SQL or standard SQL
            defaults to use `self.use_legacy_sql` if not specified
        :type dialect: str in {'legacy', 'standard'}
        """
        if dialect is None:
            dialect = 'legacy' if self.use_legacy_sql else 'standard'

        credentials, project_id = self._get_credentials_and_project_id()

        return read_gbq(sql,
                        project_id=project_id,
                        dialect=dialect,
                        verbose=False,
                        credentials=credentials) 

def test_read_gbq_should_save_credentials(mock_get_credentials):
    import pandas_gbq

    assert pandas_gbq.context.credentials is None
    assert pandas_gbq.context.project is None

    pandas_gbq.read_gbq("SELECT 1", dialect="standard")

    assert mock_get_credentials.call_count == 1
    mock_get_credentials.reset_mock()
    assert pandas_gbq.context.credentials is not None
    assert pandas_gbq.context.project is not None

    pandas_gbq.read_gbq("SELECT 1", dialect="standard")
    mock_get_credentials.assert_not_called() 

def method_under_test(credentials):
    import pandas_gbq

    return functools.partial(pandas_gbq.read_gbq, credentials=credentials)