BigQuery's SUBSTR(value, position[, length]) function currently gets translated to the standard SUBSTRING(value FROM position[ FOR length]). BigQuery expects the arguments to be comma-separated rather than the use of the FROM and FOR keywords.
EXAMPLE: SUBSTR('hello', 2) would get translated to SUBSTRING('hello' FROM 2) which BigQuery does not recognize.
Hive addresses this issue by adding onto HiveSqlDialect#unparseCall method. I believe BigQuery should do something similar to avoid this incorrect translation. The docs for the SUBSTR (and alias SUBSTRING) function may be found here.
I will open a PR shortly that implements my suggestion and adds a couple of tests as well.