Unnecessary .list.item nesting in BigQuery schemas from pyarrow upload dataframe with lists as columns. enable_list_inference partly works?

user_7814994 0
Viewed 268

So I am uploading this table to BigQuery with

job_config = bigquery.LoadJobConfig()
job_config.source_format = bigquery.SourceFormat.PARQUET
job_config.write_disposition = "WRITE_TRUNCATE"
pq_opt = ParquetOptions()
pq_opt.enable_list_inference = True
job_config.parquet_options = pq_opt  
job = self.client.load_table_from_file(source_file, table_ref, job_config=job_config)

where say,I have a pa.schema with entries of the type:

("image_id", pa.list_(pa.string())),

And as suggested in this question I use enable_list_inference. Before using enable_list_inference the schema in BQ looks like:

Schema without list inference

And after I use it I get:

Schema with list inference

So what's the reason I lose the list part but not the item part? I am passing a normal data frame with lists in some rows. How can I ditch the .item part and just have a column of REPEATED entries?

parquet pyarrow pandas google-bigquery dataframe
1 Answers

This became too long for a comment but there are two potential things going on here (not sure if fixing either would help but it is worth trying).

  • Pyarrow by default assumes columns are nullable. You can try making the top level non-nullable (and/or the inner string element non-nullable), if they in fact cannot have nulls, this should change schema inference. BQ needs to maintain intermediate records (similar to parquet) to handle repeated elements that might also be null.

  • The second thing to try is to set use_compliant_nested_types=True when writing the pyarrow table. this will change the inner element from "item" to "element" which is the correct name according to the parquet specification. This might also affect things. (BigQuery should support either so I think the first option is the more likely to work).

user_109943630
Related