When creating external partitioned table in BigQuery, it says: Specifying a schema is disallowed for STORAGE_FORMAT_AVRO

0
votes

I'm following this guide from google cloud documentation to create external partitioned table, which is:

Create table from: Google Cloud Storage
Select file from GCS bucket: gs://my_bucket/data/ymd=20200703/*
File format: Avro
Source Data Partitioning, URI Prefix: gs://my_bucket/data/
Table Type: External

But when I click create table it says:

Specifying a schema is disallowed for STORAGE_FORMAT_AVRO

If I use native tables instead of external, it just works. I also tried gs://my_bucket/data/, gs://my_bucket/data/, gs://my_bucket/data/**/, gs://my_bucket/data/ymd=20200703/*, gs://my_bucket/data/ymd=20200703/file-blabla, ... for Select file from GCS bucket but no difference.

Any ideas how I can create external partitioned tables in BigQuery? not native?

1
google-bigquery
Are you trying to create an empty table? Are you defining the database schema? - Samuel Romero
@SamuelRomero no, the data is already there. so it's not an empty table, and the schema is auto detected. It's just that if I enable partitioning it fails to do so. - hkoosha
Not sure if the data layout you are using is the right one, but you can try something like described at this link cloud.google.com/bigquery/docs/… - Samuel Romero

1 Answers

0
votes

This was a bug with google big query, reported this and they are working on it now.