Clever Geek Handbook

Bigquery: can't change name

We are changing the naming convention in UpperCamelCase. Then we realized that if it is a one-word name, BQ will simply ignore changes in any case. For example, when we ran the following query, all the field names changed as we expected, except that "speed", "ignition", "latitude" and "longitude" remained the same as in lowercase ... Interesting, is it a bug or a special consideration? This gave us a huge problem when we were migrating our big data into thousands of tables to the new naming convention

     SELECT  
            file_date_time AS FileLastModifiedTime  
            ,driver_id AS DriverId  
            ,date_time AS DateTime  
            ,latitude AS Latitude  
            ,longitude AS Longitude  
            ,gps_valid AS  GpsValid  
            ,ignition AS Ignition  
            ,speed AS SPEED  
            ,gps_reason AS GpsReason  
            ,zip_code AS ZipCode  
      FROM MyTable
+3


source to share


3 answers


This is mistake. Field names in BigQuery are not case sensitive, and you should be able to change the case of a field using aliases. I internally filed an error.



A workaround is to do a no-op typecast, which will force us to apply an exact corpus alias.
For example, if the speed is int:SELECT ... INTEGER(speed) AS SPEED, ...

+1


source


This is a quirk in BQ schema generation for the reasons given in your other answer.

One way is to use a subquery, which renames the fields to an intermediate name:



SELECT LatitudeTmp AS Latitude FROM
  (SELECT latitude AS LatitudeTmp FROM MyTable)
+2


source


Ok, I am assuming BQ is not case sensitive with field names when doing queries. But when flying data, the field name is case sensitive. Since records named "Ignition" will not be successfully transferred to table with "ignition"

0


source






More articles:


All Articles