My Bigquery SQL wont use functions such as YEAR or CREATE or DROP

Question

I am new to Big Query and I am trying to understand why my script doesn't work. Script is below

DROP TEMP TABLE top1;

CREATE TEMP TABLE topl
(Language STRING,
NoofRepos INTEGER);

INSERT INTO topl
(SELECT  l.language.name Language, COUNT(*) AS NoOfRepos, 
  FROM [bigquery-public-data:github_repos.sample_repos] sr
  INNER JOIN [bigquery-public-data:github_repos.languages] l on sr.repo_name = l.repo_name
  WHERE l.language.name is NOT null
  GROUP BY language
  ORDER BY 3 desc
  LIMIT 10 );


SELECT YEAR(c.committer.date), COUNT(*)
FROM [bigquery-public-data:github_repos.sample_commits] c
INNER JOIN [bigquery-public-data:github_repos.sample_repos] sr on c.repo_name = sr.Repo_name
INNER JOIN [bigquery-public-data:github_repos.languages] l on sr.repo_name = l.repo_name
GROUP BY YEAR(c.committer.date)
WHERE l.language.name in (SELECT Language FROM topl)

It is coming up with errors for the functions 'YEAR' 'CREATE' 'DROP' and I am unsure why as I know it supports these functions. Any help would be great

Answer 1

You are on an interface where there is still legacySQL.

Check your GUI or add #standardSQL before your queries, also tables names must be escaped like

`table`

Answer 2

You are probably using the legacy SQL GUI. According to the documentation, LegacySQL does not a DROP function. You have two options for changing to Standard SQL, first it is via command line within the console using #standardSQL in the beginning of your code. The second one is using the console, as follows:

First step:

Second step:

In addition, I encourage you to have a look in all available built in function within StandardSQL here.

Nevertheless, when you get an error on BigQuery you can consult this documentation for further clarification, it contains the most common errors when using the console.

I hope it helps.