bug(bigquery): different name of column in sql code and table.columns about ibis

[cpcloud]

We probably need to see the ibis expression. Column names that look like that should effectively never make it into bigquery.

Can you give a reproducible example that only uses Ibis? We definitely need to see what Ibis APIs you're using to produce this error.

[jitingxu1]

Rewrite the problem description with an easy example

[jitingxu1]

Updated the error message*******

400 POST https://bigquery.googleapis.com/upload/bigquery/v2/projects/voltrondata-demo/jobs?uploadType=multipart: Invalid field name "Cast(y, int64)_ef9a46". Fields must contain the allowed characters, and be at most 300 characters long. For allowed characters, please refer to https://cloud.google.com/bigquery/docs/schemas#column_names

seems like Cast(y, int64)_ef9a46 is not a valid name for bigquery, because have letters after number.

[gforsyth]

are you running against main? That looks like the bigquery issues I fixed yesterday in #9141 and #9149

[jitingxu1]

oh, no. Let me pull it from main

are you running against main? That looks like the bigquery issues I fixed yesterday in #9141 and #9149

[jitingxu1]

we convert an intermediate table into a memtable in bis-ml, see link

The intermediate information in the expression tree got lost, unfortunately, the new name "Cast(y, float64)" is not a valid bigquery column name.

I think it is not an ibis issue, we could fix this in ibisml.

Here is the simplified code to reproduce the error:

import ibis
ibis.options.interactive = True

con = ibis.bigquery.connect(project_id="xx", dataset_id="xx")

t = ibis.memtable({
    "x": ["a", "b"],
    "y": ["0", "1"]
})
con.create_table(
    "t", t.to_pyarrow(), overwrite=True
)

t = con.table("t")

base_table = t.drop("y")
target_table = t.y.cast(dt.float64)


y_m = ibis.memtable(target_table.as_table().to_pyarrow())
y_m = y_m.mutate(x=ibis.literal("a"))

base_table.join(y_m, "x")

And the new name "Cast(y, float64)" is not a valid name in bigquery.

ibis.to_sql(y_m)

sql
SELECT
  "t0"."Cast(y, float64)",
  'a' AS "x"
FROM "ibis_pyarrow_memtable_cimh6vdzlvhyxlddji3g6jvugq" AS "t0"