Joel Greijer

  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 ) ,          '+' ,    ...

If you ever exported a csv file from MsSql via bcp you probably got a csv file filled with NUL chars When using bq load you get this error  BigQuery error in load operation: Error processing job '[project]:[job_id]': Error while reading data, error message: Error detected while parsing row starting at position: 0. Error: Bad character (ASCII 0) encountered . File: gs://[bucket]/[path]/[file].csv Failure details: - Error while reading data, error message: CSV processing encountered One solution is to let bq load these characters using this flag --preserve_ascii_control_characters=true Note that the unicode general "unknown character"  �  symbol is inserted instead of null and you may want to clean this post bulk. But at least bq load does not crash.

  $ErrorActionPreference = "stop" $server = "your-server.database.windows.net" $database = "your-database" $query = "select 1 from dbo.yourtable" Connect-AzAccount $token = ( Get-AzAccessToken -ResourceUrl "https://database.windows.net" ).Token Invoke-Sqlcmd -server $server -Database $database -Query $query -as dataset -AccessToken $token write-output "Done!"

This is how to grant user from system assigned identity (e.g. automation account) to allow to connect and query azure sql database without any use of keys, secrets, vaults or hard coded passwords. The app does not have to be contributor in azure sql instance for this to work Enable System Assigned Identity Navigate to Automation Account / Identity Set System Assigned: On Copy Object (Principal) Id guid Navigate to Azure Active Directory Search for the guid in "Search your tenant" box Find the "enterprise application" popping up Copy the Name this is what you use below [App_Name] Grant access to Azure SQL Database CREATE USER [App_Name] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA = dbo ALTER ROLE db_datareader ADD MEMBER [App_Name] ALTER ROLE db_datawriter ADD MEMBER [App_Name] How to use from runbook (powershell) $server  =  "your-sql-server-name.database.windows.net" $database  =  "your-database-name" $query  =  "sel...