Joel Greijer

This Big Query script will fail create or replace table joel_dataset . demo_table (   int_field INT64 ,   int_field2 INT64 ,   changed_time TIMESTAMP , ) partition by range_bucket (             int_field ,             generate_array ( 0 , 10 , 1 )         ) ; create or replace table joel_dataset . demo_table (   int_field INT64 ,   int_field2 INT64 ,   changed_time TIMESTAMP , ) partition by range_bucket (             int_field ,             generate_array ( 0 , 10 , 1 )         ) cluster by int_field2 --adding clustering ; Invalid value: Incompatible table partitioning specification. Expected partitioning specification range(type:integer,field:int_field,start:0,end:10,interval:1) clustering(int_field2), but input partitioning specification is range(type:integer,field:int_field,...

  select     day as date_bk ,     day as date ,     cast ( day as datetime ) as date_time ,     cast ( day as timestamp ) as date_timestamp ,     format_date ( '%Y-%m-%d' , day ) as date_string ,     extract ( year from day ) AS year ,     extract ( quarter from day ) AS quarter ,     'Q' || extract ( quarter from day ) AS quarter_string ,     extract ( month from day ) AS month ,     format_date ( '%B' , day ) AS month_string ,     format_date ( '%b' , day ) AS month_abbr_string ,     extract ( week from day ) AS week_sun ,     cast ( format_date ( '%W' , day ) as integer ) AS week_mon ,     extract ( isoweek from day ) AS iso_week ,     'W' || format ( '%02d' , extract ( isoweek from day )) AS iso_week_string ,         extract ( day from day ) AS day_of_mon...

with  animal  as (    select   *   from        unnest ([ struct        ( 1   as  zoo_id,  'Horse'   AS  animal_name,  1   as  animal_count ) ,        ( 1 ,  'Cow' ,  25 ) ,        ( 2 ,  'Old World Swallowtail Butterfly' ,  1000   ) ,        ( 4 ,  'Fish'  , 5500 ) ,        ( 5 ,  'Beetle'  , 700000 )      ])   ) , zoo  as (    select   *   from        unnest ([ struct        ( 1   as  zoo_id,  'Skansen'   as  zoo_name,  100   as  city_id ) ,        ( 2 ,  'Fjärilshuset' ,  100   ) ,   ...

Converting iso dates with timezones to BigQuery timestamp can be a hassle if the date contains more than six decimals. select   cast ( '2023-03-21T06:49:19.1234567+00:00'   as   timestamp ) Invalid timestamp: '2023-03-21T06:49:19.1234567+00:00' One solution could be to truncate the fractional part like so create   or   replace   function  [your_dataset].truncate_iso_timestamp_string ( detailed_iso_timestamp  string ) /* big query crashes with more than 6 decimals on dates This function will truncate to 6 decimals and cast to timestamp. */ returns   timestamp as   (    cast (      concat (        left (          split ( detailed_iso_timestamp, '+' )[ offset ( 0 )] ,  26 ) ,          '+' ,    ...