Environment details

I'm using pandas with google-cloud-python

Steps to reproduce

1. I have a dataframe has datetime format, ex: "2019-07-08 08:00:00" and my schema has created column with DATETIME type.
2. I tried covert it to use pq.to_datetime()
3. Then I used load_table_from_dataframe() to insert data.

Code example

my_df = get_sessions()  # this return a dataframe has a column name is created which is datetime[ns] type ex :"2020-01-08 08:00:00"
my_df['created'] = pd.to_datetime(my_df['created'], format='%Y-%m-%d %H:%M:%S').astype('datetime64[ns]')
res = bigquery_client.client.load_table_from_dataframe(my_df, table_id)
res.result()

# exp: my value "2020-01-08 08:00:00" is being changed as INVALID or this value "0013-03-01T03:05:00" or other wrong value @plamut please help

I just updated my problem . Here Thanks!

bug googleapis/google-cloud-python#9965 is still happening in v1.24.0 and six v1.14.0

`File "/root/.local/share/virtualenvs/code-788z9T0p/lib/python3.6/site-packages/google/cloud/bigquery/schema.py", line 17, in

from six.moves import collections_abc ImportError: cannot import name 'collections_abc' `

why did you close the googleapis/google-cloud-python#9965 issue if it still reproduces for many people?

I know BigQuery jobs are asynchronous by default. However, I am struggling to make my datapipeline async end-to-end.

Looking at this JS example, I thought it would be the most Pythonic to make a BigQuery job awaitable. However, I can't get that to work in Python i.e. errors when await client.query(query). Looking at the source code, I don't see which method returns an awaitable object.

I have little experience in writing async Python code and found this example that wraps jobs in a async def coroutine.

class BQApi(object):                                                                                                 
    def __init__(self):                                                                                              
        self.api = bigquery.Client.from_service_account_json(BQ_CONFIG["credentials"])                               

    async def exec_query(self, query, **kwargs) -> bigquery.table.RowIterator:                                       
        job = self.api.query(query, **kwargs)                                                                        
        task = asyncio.create_task(self.coroutine_job(job))                                                          
        return await task                                                                                            

    @staticmethod                                                                                                    
    async def coroutine_job(job):                                                                                    
        return job.result()   

The google.api_core.operation.Operation shows how to use add_done_callback to asynchronously wait for long-running operations. I have tried that, but the following yields AttributeError: 'QueryJob' object has no attribute '_condition' :

from concurrent.futures import ThreadPoolExecutor, as_completed
query1 = 'SELECT 1'
query2 = 'SELECT 2'

def my_callback(future):
    result = future.result()

operations = [bq.query(query1), bq.query(query2)]
[operation.add_done_callback(my_callback) for operation in operations]
results2 = []
for future in as_completed(operations):
  results2.append(list(future.result()))

Given that jobs are already asynchronous, would it make sense to add a method that returns an awaitable?

Or am I missing something and is there an Pythonic way to use the BigQuery client with the async/await pattern?

The support for python Bigquery API indicates that arrays are possible, however, when passing from a pandas dataframe to bigquery there is a pyarrow struct issue.

The only way round it seems its to drop columns then use JSON Normalise for a separate table.

from google.cloud import bigquery

project = 'lake'
client = bigquery.Client(credentials=credentials, project=project)
dataset_ref = client.dataset('XXX')
table_ref = dataset_ref.table('RAW_XXX')
job_config = bigquery.LoadJobConfig()
job_config.autodetect = True
job_config.write_disposition = 'WRITE_TRUNCATE'

client.load_table_from_dataframe(appended_data, table_ref,job_config=job_config).result()

This is the error recieved. NotImplementedError: struct

The reason I wanted to use this API as it indicates Nested Array support, which is perfect for our data lake in BQ but I assume this doesn't work?

Environment details

• OS type and version: Ubuntu20 PopOs
• Python version: 3.7.8
• pip version: 20.1.1
• google-cloud-bigquery version: 1.27.2

Steps to reproduce

I'm creating a table with some columns, one of them is of type GEOGRAHPY. When I try to create the table with a sample data, if I choose to use clustering, I got the 500 error. I can create the table only if no clustering is made. Also I can create the table with clustering if I don't include the column of type GEOGRAHPY. Code with a toy example to reproduce it:

Code example

import time
import pandas as pd
from google.cloud import bigquery
from shapely.geometry import Point

client = bigquery.Client()
PROJECT_ID = ""
table_id = f"{PROJECT_ID}.data_capture.toy"

df = pd.DataFrame(
    dict(
        lat=[6.208969] * 100,
        lon=[-75.571696] * 100,
        logged_at=[int(time.time() * 1000) for _ in range(100)],
    )
)
df["point"] = df.apply(lambda row: Point(row["lon"], row["lat"]).wkb_hex, axis=1)

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("lon", "FLOAT64", "REQUIRED"),
        bigquery.SchemaField("lat", "FLOAT64", "REQUIRED"),
        bigquery.SchemaField("point", "GEOGRAPHY", "REQUIRED"),
        bigquery.SchemaField("logged_at", "TIMESTAMP", "REQUIRED"),
    ],
    write_disposition="WRITE_TRUNCATE",
    time_partitioning=bigquery.TimePartitioning(
        type_=bigquery.TimePartitioningType.DAY, field="logged_at",
    ),
    clustering_fields=["logged_at"],
)

job = client.load_table_from_dataframe(
    df, table_id, job_config=job_config
)  # Make an API request.
job.result()  # Wait for the job to complete.

Stack trace

Traceback (most recent call last):
  File "test.py", line 108, in <module>
    job.result()  # Wait for the job to complete.
  File "/home/charlie/data/kiwi/data-upload/.venv/lib/python3.7/site-packages/google/cloud/bigquery/job.py", line 812, in result
    return super(_AsyncJob, self).result(timeout=timeout)
  File "/home/charlie/data/kiwi/data-upload/.venv/lib/python3.7/site-packages/google/api_core/future/polling.py", line 130, in result
    raise self._exception
google.api_core.exceptions.InternalServerError: 500 An internal error occurred and the request could not be completed. Error: 3144498

Thank you in advance!

This error occurs when I run a query using %%bigquery magics in GCP-hosted notebook and the query fails.

"ImportError: cannot import name bigquery_storage_v1beta1 from google.cloud (unknown location)"

Environment details

• OS type and version:
• Python version: 3.7
• pip version: 20.1.1
• google-cloud-bigquery version: 1.26.0 and 1.26.1; not an issue with 1.25.0

Steps to reproduce

1. Install current version of google-cloud-bigquery
2. Query

Code example

%pip install --upgrade google-cloud-bigquery[bqstorage,pandas]
%load_ext google.cloud.bigquery
import google.cloud.bigquery.magics

%%bigquery stackoverflow --use_bqstorage_api
SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10

Stack trace

---------------------------------------------------------------------------
ImportError                               Traceback (most recent call last)
<ipython-input-2-29432a7a9e7c> in <module>
----> 1 get_ipython().run_cell_magic('bigquery', 'stackoverflow --use_bqstorage_api', "SELECT\n  CONCAT(\n    'https://stackoverflow.com/questions/',\n    CAST(id as STRING)) as url,\n  view_count\nFROM `bigquery-public-data.stackoverflow.posts_questions`\nWHERE tags like '%google-bigquery%'\nORDER BY view_count DESC\nLIMIT 10\n")

/opt/conda/lib/python3.7/site-packages/IPython/core/interactiveshell.py in run_cell_magic(self, magic_name, line, cell)
   2369             with self.builtin_trap:
   2370                 args = (magic_arg_s, cell)
-> 2371                 result = fn(*args, **kwargs)
   2372             return result
   2373 

/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/magics.py in _cell_magic(line, query)
    589             )
    590         else:
--> 591             result = query_job.to_dataframe(bqstorage_client=bqstorage_client)
    592 
    593         if args.destination_var:

/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/job.py in to_dataframe(self, bqstorage_client, dtypes, progress_bar_type, create_bqstorage_client, date_as_object)
   3381             progress_bar_type=progress_bar_type,
   3382             create_bqstorage_client=create_bqstorage_client,
-> 3383             date_as_object=date_as_object,
   3384         )
   3385 

/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/table.py in to_dataframe(self, bqstorage_client, dtypes, progress_bar_type, create_bqstorage_client, date_as_object)
   1725                 progress_bar_type=progress_bar_type,
   1726                 bqstorage_client=bqstorage_client,
-> 1727                 create_bqstorage_client=create_bqstorage_client,
   1728             )
   1729             df = record_batch.to_pandas(date_as_object=date_as_object)

/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/table.py in to_arrow(self, progress_bar_type, bqstorage_client, create_bqstorage_client)
   1543             record_batches = []
   1544             for record_batch in self._to_arrow_iterable(
-> 1545                 bqstorage_client=bqstorage_client
   1546             ):
   1547                 record_batches.append(record_batch)

/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/table.py in _to_page_iterable(self, bqstorage_download, tabledata_list_download, bqstorage_client)
   1432     ):
   1433         if bqstorage_client is not None:
-> 1434             for item in bqstorage_download():
   1435                 yield item
   1436             return

/opt/conda/lib/python3.7/site-packages/google/cloud/bigquery/_pandas_helpers.py in _download_table_bqstorage(project_id, table, bqstorage_client, preserve_order, selected_fields, page_to_item)
    626     # is available and can be imported.
    627     from google.cloud import bigquery_storage_v1
--> 628     from google.cloud import bigquery_storage_v1beta1
    629 
    630     if "$" in table.table_id:

ImportError: cannot import name 'bigquery_storage_v1beta1' from 'google.cloud' (unknown location)

Currently TIME columns are just exposed as string objects. This would be a better experience and align with better with the expectations for working with timeseries in pandas https://pandas.pydata.org/pandas-docs/stable/user_guide/timeseries.html

Presumably one could combine a date column with a time column to create a datetime by adding them.

Closes #661.

For load jobs and external tables config.

PR checklist:

• [x] Make sure to open an issue as a bug/issue before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
• [x] Ensure the tests and linter pass
• [x] Code coverage does not decrease (if any source code was changed)
• [x] Appropriate docs were updated (if necessary)

This would technically be a breaking change, but it might make sense to do while we are changing default dtypes in https://github.com/googleapis/python-bigquery/pull/786 for https://issuetracker.google.com/144712110

If the GeoPandas library is installed (meaning, GeoPandas should be considered an optional "extra"), it may make sense to use the extension dtypes provided by GeoPandas by default on GEOGRAPHY columns.

Hi,

What is the purpose of using timeout while fetching job information since the bigquery.Client says time to wait for before retrying. Is retry attempt should be made by the user or the client will handle that

But I am getting an exception raised

host='bigquery.googleapis.com', port=443): Read timed out. (read timeout=5)"}

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:

• [x] Make sure to open an issue as a bug/issue before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
• [x] Ensure the tests and linter pass
• [x] Code coverage does not decrease (if any source code was changed)
• [x] Appropriate docs were updated (if necessary)

Fixes #19 🦕

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:

• [ ] Make sure to open an issue as a bug/issue before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
• [ ] Ensure the tests and linter pass
• [ ] Code coverage does not decrease (if any source code was changed)
• [ ] Appropriate docs were updated (if necessary)

Towards #790 🦕

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:

• [ ] Make sure to open an issue as a bug/issue before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
• [ ] Ensure the tests and linter pass
• [ ] Code coverage does not decrease (if any source code was changed)
• [ ] Appropriate docs were updated (if necessary)

Towards #790 🦕

in public docs we less sample python code for setting iam on bigquery table. https://cloud.google.com/bigquery/docs/control-access-to-resources-iam#grant_access_to_a_table_or_view

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:

• [ ] Make sure to open an issue as a bug/issue
• [ ] before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
• [ ] Ensure the tests and linter pass
• [ ] Code coverage does not decrease (if any source code was changed)
• [ ] Appropriate docs were updated (if necessary)

Fixes #<1449> 🦕

Thanks for stopping by to let us know something could be better!

PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.

Is your feature request related to a problem? Please describe. in https://cloud.google.com/bigquery/docs/control-access-to-resources-iam#grant_access_to_a_table_or_view, there has no sample code for python to grant access on table or view Describe the solution you'd like give the grant access on table or view python simple code Describe alternatives you've considered customer need do coding by complex documentation. Additional context n/a

It is currently not possible to escape json parameters in a query like this:

job_config = bigquery.QueryJobConfig(
    config.query_parameters=[
        bigquery.JsonQueryParameter("data", {"foo": "bar"})
    ]
)
stmt = 'UPDATE FROM my_table SET dat[email protected]';
query_job = client.query(stmt, job_config=job_config)

It seem quite important to avoid SQL injections.

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:

• [ ] Make sure to open an issue as a bug/issue before writing your code! That way we can discuss the change, evaluate designs, and agree on the general idea
• [ ] Ensure the tests and linter pass
• [ ] Code coverage does not decrease (if any source code was changed)
• [ ] Appropriate docs were updated (if necessary)

Towards #790 🦕