GoogleCloudPlatform / bigquery-utils

Useful scripts, udfs, views, and other utilities for migration and data warehouse operations in BigQuery.
https://cloud.google.com/bigquery/
Apache License 2.0
1.11k stars 277 forks source link

Add a view that emulates the SYS_CALENDAR.CALENDAR table from Teradata #331

Closed Hackerpilot closed 1 year ago

Hackerpilot commented 1 year ago

This will help people migrating code from Teradata to BigQuery. Placing this in views/migration/teradata to mimic the structure of the UDF directories. So far there seems to be no test infrastructure for views, so let me know what, if anything, needs to be done to test this. So far this has just been manually tested.

Hackerpilot commented 1 year ago

Pushed a new version that fixes a few cases. Tested with this:

select * from 
(select 4, weekday_of_month,
  4, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-10-24')
union all
(select 3, weekday_of_month,
  3, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-10-18')
union all
(select 5, weekday_of_month,
  4, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-10-29')
union all
(select 1,  weekday_of_month,
  0, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-10-1')
union all
(select 1, weekday_of_month,
  1, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-5-1')
  union all
(select 1, weekday_of_month,
  0, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-9-1')
  union all
(select 1, weekday_of_month,
  0, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-9-2')
  union all
(select 1, weekday_of_month,
  0, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-9-3')
    union all
(select 2, weekday_of_month,
  1, week_of_month, calendar_date
  from dummy.CALENDAR
  where calendar_date = '2022-9-9');