CODEWITHSUNDEEP
google-bigquerygoogle-cloud-datalab
jaycode
jaycode

Reputation: 2958

Using Google Datalab, how to create table from a complex query with UDF in Google BigQuery?

I have seen many similar questions, but they are not specific to Google Cloud Datalab AND using UDF at the same time, e.g. query execution shows Unknown TVS error and creating table from query result via python API.

I managed to create the table when UDF was not used, but when it was, it returned error "Unknown TVF: myFunc".

Edit Here is the code I'm using:

%%bigquery udf --module transform_field
...udf function...

Then I used udf function above in an sql query:

%%sql --module querymodule
...complex SELECT query FROM transform_field(table)...

Then I'd like to use that query to create another table as shown below:

%%bigquery execute --target project:dataset.tablename --query querymodule

But it kept showing this error instead:

Unknown TVF: TRANSFORM_FIELD

Upvotes: 1

Views: 654

Answers (1)

jaycode
jaycode

Reputation: 2958

Alright, I found it. Turns out you need to pass the query through a python cell before using it in a %%bigquery execute cell:

bq_query = bq.Query(querymodule, udf=transform_field)

Thus the entire process should go as follows:

%%bigquery udf --module transform_field
...udf function...

Then I used udf function above in an sql query:

%%sql --module querymodule
...complex SELECT query FROM transform_field(table)...

Then use the query and udf function above to create a bq.Query object.

bq_query = bq.Query(querymodule, udf=transform_field)

Then use bq_query in table creation.

%%bigquery execute --target project:dataset.tablename --query bq_query

I keep being amazed at what good night sleeps do.

Upvotes: 2

Related Questions