Skip to content
Advertisement

Syntactical error when reading data from BigQuery using R Error: Encountered “ ”FROM“ ”FROM “” at line 1, column 10. Was expecting:

I have checked for answers however there are none available for “FROM” “FROM “” in particular. I am trying to pull data in from BigQuery using RStudio, which is running on a Virtual Machine via Google Compute Engine, packages readr and bigrquery are installed, and the session has been authenticated. the code is as follows

project <- "testconnectrtobigquery"
sql <- "SELECT * FROM 'testconnectrtobigquery.TestDataSetRtoBQ.TestTable' LIMIT 5"
query_exec(sql, project = project)

Here is the error

> source('~/.active-rstudio-document')
Error: Encountered " "FROM" "FROM "" at line 1, column 10.
Was expecting:
<EOF> 
 [invalidQuery] 

Please could someone explain how to correct this issue?

Advertisement

Answer

Generally, relational databases running SQL use single quotes for literal, string values and not for identifiers like table and column names. Specifically, according to Google BigQuery docs, GBQ follows two conventions to escape special characters, spaces, and keywords:

  • For Standard SQL, quoted identifiers must be enclosed by backticks.
  • For Legacy SQL, names should be enclosed in square brackets.

Therefore, consider replacing single quotes with backticks or brackets depending on your SQL mode:

-- BigQuery Legacy SQL
SELECT * FROM `testconnectrtobigquery.TestDataSetRtoBQ.TestTable` LIMIT 5

-- BigQuery Standard SQL
SELECT * FROM [testconnectrtobigquery.TestDataSetRtoBQ.TestTable] LIMIT 5

However, you do not have any special characters or spaces, so do not need escaping.

SELECT * FROM testconnectrtobigquery.TestDataSetRtoBQ.TestTable LIMIT 5
2 People found this is helpful
Advertisement