CODEWITHSUNDEEP
google-bigquery
Yerachmiel Elimelech Weiss
Yerachmiel Elimelech Weiss

Reputation: 220

Using period "." in Standard SQL in BigQuery

BigQuery Standard SQL does not seems to allow period "." in the select statement. Even a simple query (see below) seems to fail. This is a big problem for datasets with field names that contain "." Is there an easy way to avoid this issue?

select id, time_ts as time.ts from `bigquery-public-data.hacker_news.comments` LIMIT 10

Returns error... Error: Syntax error: Unexpected "." at [1:27]

This also fails... select * except(detected_circle.center_x ) from [bigquery-public-data:eclipse_megamovie.photos_v_0_2] LIMIT 10

Upvotes: 4

Views: 1917

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173036

Elliot's answer great and addresses first part of your question, so let me address second part of it (as it is quite different)

First, wanted to mention that select modifiers like SELECT * EXCEPT are supported for BigQuery Standard SQL so, instead of 

SELECT * EXCEPT(detected_circle.center_x )
FROM [bigquery-public-data:eclipse_megamovie.photos_v_0_2] 
LIMIT 10

you should rather tried 

#standardSQL
SELECT * EXCEPT(detected_circle.center_x )
FROM `bigquery-public-data.eclipse_megamovie.photos_v_0_2` 
LIMIT 10

and of course now we are back to issue with `using period in standard sql

So, above code can only be interpreted as you try to eliminate center_x field from detected_circle STRUCT (nullable record). Technically speaking, this makes sense and can be done using below code 

SELECT * 
  REPLACE(STRUCT(detected_circle.radius, detected_circle.center_y ) AS detected_circle)
FROM `bigquery-public-data.eclipse_megamovie.photos_v_0_2` 
LIMIT 10

... still not clear to me how to use your recommendation to remove the entire detected_circle.* 

SELECT * EXCEPT(detected_circle)
FROM `bigquery-public-data.eclipse_megamovie.photos_v_0_2` 
LIMIT 10

Upvotes: 3

Elliott Brossard
Elliott Brossard

Reputation: 33745

It depends on what you are trying to accomplish. One interpretation is that you want to return a STRUCT named time with a single field named ts inside of it. If that's the case, you can use the STRUCT operator to build the result:

SELECT
  id,
  STRUCT(time_ts AS ts) AS time
FROM `bigquery-public-data.hacker_news.comments` 
LIMIT 10;

In the BigQuery UI, it will display the result as id and time.ts, where the latter indicates that ts is inside a STRUCT named time.

BigQuery disallows columns in the result whose names include periods, so you'll get an error if you run the following query:

SELECT
  id,
  time_ts AS `time.ts`
FROM `bigquery-public-data.hacker_news.comments` 
LIMIT 10;

Invalid field name "time.ts". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 128 characters long.

Upvotes: 3

Related Questions