500 server error when creating table using clustering

[charlielito]

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!

[plamut]

I can confirm the reported behavior, having a column explicitly specified as type GEOGRAPHY in the schema triggers the error on the backend.

@shollyman Any idea what the error 3144498 represents?
(also, fell free to increase the priority to P1 if no good workaround exists and user experience in considerably affected)

[shollyman]

This is a case where we need to improve our documentation.

The docstrings in the REST reference bear this constraint 'Only top-level, non-repeated, simple-type fields are supported'. But we're not doing a great job describing what that means.

You can see from the Standard SQL datatypes page that GEOGRAPHY types have problems with ordering and comparisons, which prevents the kinds of operations we need to do to cluster data.

I've filed internal issue 166457597 to improve this. Once we get better clarity in the reference, we can propagate that into the various client libraries.

[plamut]

OK thanks, that makes sense. I'll still keep this open for visibility until the docs are improved.

[charlielito]

@shollyman that is odd. Actually I wanted to try this because the documentation said that GEOGRAPHY columns supported clustering, which I thought was really cool. This section states the following:

• Clustering columns must be top-level, non-repeated columns of one of the following types:
  • DATE
  • BOOL
  • GEOGRAPHY
  • INT64
  • NUMERIC
  • STRING
  • TIMESTAMP

If you try the code sample I shared, it crashes also if you select as clustering column the GEOGRAPHY one

[shollyman]

It appears I've failed to read the details. Going to take a deeper look here, stay tuned.