Using period "." in standard SQL in BigQuery

Question

BigQuery Standard SQL does not seem to allow the use of the period "." in the select clause. 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 problem?

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

It returns an error ... Error: Syntax error: Unexpected "." at [1:27]

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

Answer 1

It depends on what you are trying to accomplish. One interpretation is that you want to return STRUCTwith a name timewith one field with a name tsinside it. In this case, you can use the operator STRUCTto build the result:

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

BigQuery id time.ts, , ts STRUCT time.

BigQuery , , , :

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

"time.ts". , , 128 .

Answer 2

, ( )

 

-, , BigQuery Standard SQL , SELECT * EXCEPT,

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

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

, , ` sql

, center_x detected_circle STRUCT ( ).

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   

... , _circle. *

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