TVF not found!

[technical2304]

I have created a Table Valued Function in BigQuery. It is in a dataset set for the location EU. When i try to call the function, it gives an error saying ‘function not found’

The way I am calling the function is:

SELECT  *, column1 FROM table1, UNNEST(my_tvf(column2)) AS column1;

I am not sure if I am not calling the TVF properly or if there is anything else. 

Any help is appreciated, thank you!

[NorieRam]

Hi @technical2304,

Welcome to Google Cloud Community!

In BigQuery, a ‘function not found’ error for a Table Valued Function (TVF) means you're trying to use a TVF that BigQuery can't locate, you might want to see this breakdown and how to fix it:

• Ensure you're using the exact same capitalization for the function name in your query.
• Double-check that both table1 and my_tvf exist in the same dataset. If they're in different datasets, you'll need to either move the TVF or query the data from the correct dataset.
• Make sure that both the dataset containing my_tvf and the dataset containing table1 are within the same BigQuery project.
• Make sure the my_tvf function takes the expected argument type (e.g., column2). Also, A TVF should return an array of values, which can be unnested using UNNEST.
• Verify that your user account or service account has the necessary permissions.
• Try refreshing the cache by running a simple SELECT statement that directly references the TVF.

Also, now that you’ve mentioned the location, it is less common but still worth considering. Double-check that your function and queries are in the same region (in this case, the EU). If you are using different regions, you might need to move your function or query to the correct location or explicitly set your query's region using the location clause.

Here are some helpful documentation regarding TVF: Table functions — A hidden gem in Google’s BigQuery

I hope the above information is helpful.