Mismatch in schema causing error in create_bq_table

[james-d-f]

I hope if is ok to raise a support issue here.

I am getting a repeated issue setting this up for our clients due to a failure in create_bq_table.

I have tried starting the setup from the beginning and seem to run into the same issue.

My BigQuery dataset contains the tables, but not the views. The Reports table's Date field has type Date. The error seems to be due to REGEXREPLACE expecting this field to be a String.

Error:

[2020-05-13 22:22:40,139] {__init__.py:1631} ERROR - BigQuery job failed. Final error was: {'reason': 'invalidQuery', 'location': 'query', 'message': 'No matching signature for function REGEXP_REPLACE for argument types: DATE, STRING, STRING. Supported signatures: REGEXP_REPLACE(STRING, STRING, STRING); REGEXP_REPLACE(BYTES, BYTES, BYTES) at [199:19]'}. The job was: {'kind': 'bigquery#job', 'etag': '9mnWSoNkGPeMl52Mm/z42w==', 'id': 'funnel-data:EU.job_LVSfrWT08CXpzMBkCRAE8nJ0bd9r', 'selfLink': 'https://bigquery.googleapis.com/bigquery/v2/projects/funnel-data/jobs/job_LVSfrWT08CXpzMBkCRAE8nJ0bd9r?location=EU', 'user_email': '172399749176-compute@developer.gserviceaccount.com', 'configuration': {'query': {'query': '/*\n * Copyright 2020 Google LLC\n *\n * Licensed under the Apache License, Version 2.0 (the "License");\n * you may not use this file except in compliance with the License.\n * You may obtain a copy of the License at\n *\n *     https://www.apache.org/licenses/LICENSE-2.0\n *\n * Unless required by applicable law or agreed to in writing, software\n * distributed under the License is distributed on an "AS IS" BASIS,\n * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n * See the License for the specific language governing permissions and\n * limitations under the License.\n * \n */\n\n-- standardSQL\nWITH SDF AS (\n  SELECT\n  CAST(SDFLineItem.Line_Item_Id AS INT64) AS lineitem_id,\n  SDFLineItem.Type AS lineitem_type,\n  SDFLineItem.End_Date AS lineitem_end_date,\n  SDFLineItem.Pacing AS lineitem_pacing,\n  SDFLineItem.Pacing_Rate AS lineitem_pacing_rate,\n  SDFLineItem.Bid_Strategy_Value AS lineitem_bid_value,\n  SDFInsertionOrder.Pacing AS io_pacing,\n  SDFInsertionOrder.Pacing_Rate AS io_pacing_rate,\n  SDFInsertionOrder.Performance_Goal_Type AS io_performance_goal_type,\n  SDFCampaign.Campaign_Budget AS campaign_budget,\n  SAFE_CAST(SDFLineItem.Frequency_Enabled AS BOOL) AS frequency_enabled_lineitem,\n  SAFE_CAST(SDFInsertionOrder.Frequency_Enabled AS BOOL) AS frequency_enabled_io,\n  IF(CAST(SDFCampaign.Frequency_Enabled AS BOOL) IS NULL, FALSE, CAST(SDFCampaign.Frequency_Enabled AS BOOL)) AS frequency_enabled_campaign, \n  IF(SDFLineItem.TrueView_View_Frequency_Enabled = "False" OR SDFLineItem.TrueView_View_Frequency_Enabled IS NULL, FALSE, TRUE) AS frequency_enabled_trueview,\n  \n-- Brand controls\n  IF(SDFLineItem.Digital_Content_Labels_Exclude IS NULL, FALSE, TRUE) \n    AS bc_digital_content_label_exclusions,\n  IF(SDFLineItem.Brand_Safety_Custom_Settings IS NULL\n    AND SDFLineItem.TrueView_Category_Exclusions_Targeting IS NULL, FALSE, TRUE) \n    AS bc_sensitive_category_exclusions,\n  IF(SDFLineItem.App_Collection_Targeting_Exclude IS NULL \n    AND SDFLineItem.App_Targeting_Exclude IS NULL \n    AND SDFLineItem.Site_Targeting_Exclude IS NULL\n    AND SDFLineItem.Channel_Targeting_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Channels_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Videos_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_URLs_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_Apps_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_App_Collections_Exclude IS NULL, FALSE, TRUE)\n    AS bc_apps_url_channel_exclusions,\n  IF(SDFLineItem.Keyword_Targeting_Exclude IS NULL \n    AND SDFLineItem.Keyword_List_Targeting_Exclude IS NULL\n    AND SDFAdGroup.Keyword_Targeting_Exclude IS NULL, FALSE, TRUE) \n    AS bc_keyword_exclusions,\n  IF(SDFLineItem.Inventory_Source_Targeting_Authorized_Seller_Options = \'Authorized Direct Sellers\', TRUE, FALSE) AS bc_authorized_sellers_only,\n  SDFLineItem.Inventory_Source_Targeting_Authorized_Seller_Options AS raw_lineitem_inventory_source_auth_seller,\n  IF(SDFLineItem.Third_Party_Verification_Services = \'None\'\n    OR SDFLineItem.Third_Party_Verification_Services IS NULL, FALSE, TRUE) \n    AS bc_verification_services,\n  \n-- Targeting\n  IF(SDFAdGroup.Placement_Targeting_YouTube_Channels_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Videos_Exclude iS NULL, FALSE, TRUE)\n    AS exclude_youtube_channel_video,\n  IF(SDFLineItem.Bid_Multipliers IS NULL, FALSE, TRUE) AS bid_multipliers,\n  IF(SDFLineItem.App_Collection_Targeting_Include IS NULL \n    AND SDFLineItem.App_Targeting_Include IS NULL \n    AND SDFLineItem.Site_Targeting_Include IS NULL \n    AND SDFLineItem.Channel_Targeting_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Channels_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Videos_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_URLs_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_Apps_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_App_Collections_Include IS NULL, FALSE, TRUE) \n    AS include_apps_url_channel,\n  SDFLineItem.App_Collection_Targeting_Include AS raw_lineitem_app_collection_include,\n  SDFLineItem.App_Targeting_Include AS raw_lineitem_app_include,\n  SDFLineItem.Site_Targeting_Include AS raw_lineitem_site_include,\n  SDFLineItem.Channel_Targeting_Include AS raw_lineitem_channel_include,\n  SDFAdGroup.Placement_Targeting_YouTube_Channels_Include AS raw_adgroup_placement_yt_channel_include,\n  SDFAdGroup.Placement_Targeting_YouTube_Videos_Include AS raw_adgroup_placement_yt_video_include,\n  SDFAdGroup.Placement_Targeting_URLs_Include AS raw_adgroup_placement_urls_include,\n  SDFAdGroup.Placement_Targeting_Apps_Include AS raw_adgroup_placement_apps_include,\n  SDFAdGroup.Placement_Targeting_App_Collections_Include AS raw_adgroup_placement_app_col_include,\n\n  IF(SDFLineItem.Keyword_Targeting_Include IS NULL\n    AND SDFAdGroup.Keyword_Targeting_Include IS NULL, FALSE, TRUE)\n    AS include_keyword,\n  SDFLineItem.Keyword_Targeting_Include AS raw_lineitem_keyword_include,\n  SDFAdGroup.Keyword_Targeting_Include AS raw_adgroup_keyword_include,\n\n  IF(SDFLineItem.Category_Targeting_Include IS NULL\n    AND SDFLineItem.TrueView_Category_Exclusions_Targeting IS NULL\n    AND SDFAdGroup.Category_Targeting_Include IS NULL, FALSE, TRUE)\n    AS include_category,\n  SDFLineItem.Category_Targeting_Include AS raw_lineitem_category_include,\n  SDFLineItem.TrueView_Category_Exclusions_Targeting AS raw_lineitem_trueview_category_exclusions,\n  SDFAdGroup.Category_Targeting_Include AS raw_adgroup_category_include,\n\n  IF(SDFLineItem.Environment_Targeting IS NULL, FALSE, TRUE) AS include_environment,\n  SDFLineItem.Environment_Targeting AS raw_lineitem_environment_include,\n\n  IF(SDFLineItem.Language_Targeting_Include IS NULL, FALSE, TRUE) AS include_language,\n  SDFLineItem.Language_Targeting_Include AS raw_lineitem_language_include,\n\n  IF(SDFLineItem.Inventory_Source_Targeting_Include IS NULL, FALSE, TRUE) AS include_inventory,\n  SDFLineItem.Inventory_Source_Targeting_Include AS raw_lineitem_inventory_include,\n\n  IF(SDFLineItem.TrueView_Third_Party_Viewability_Vendor != \'None\' \n    AND SDFLineItem.TrueView_Third_Party_Viewability_Vendor IS NOT NULL \n    OR SDFLineItem.Viewability_Targeting_Active_View IS NOT NULL, TRUE, FALSE)\n    AS include_viewability,\n  SDFLineItem.TrueView_Third_Party_Viewability_Vendor AS raw_lineitem_trueview_3p_viewability_include,\n  SDFLineItem.Viewability_Targeting_Active_View AS raw_lineitem_viewability_active_view_include,\n\n  IF(SDFLineItem.Audience_Targeting_Include IS NULL\n    AND SDFAdGroup.Audience_Targeting_Include IS NULL, FALSE, TRUE)\n    AS include_audience,\n  SDFLineItem.Audience_Targeting_Include AS raw_lineitem_audience_include,\n  SDFAdGroup.Audience_Targeting_Include AS raw_adgroup_audience_include,\n\n  IF(SDFLineItem.Affinity_And_In_Market_Targeting_Include IS NULL\n    AND SDFAdGroup.Affinity_And_In_Market_Targeting_Include IS NULL, FALSE, TRUE)\n    AS include_affinity_inmarket,\n  SDFLineItem.Affinity_And_In_Market_Targeting_Include AS raw_lineitem_affinity_inmarket_include,\n  SDFAdGroup.Affinity_And_In_Market_Targeting_Include AS raw_adgroup_affinity_inmarket_include,\n\n  IF(SDFLineItem.Geography_Targeting_Include IS NULL, FALSE, TRUE) AS include_geography,\n  SDFLineItem.Geography_Targeting_Include AS raw_lineitem_geography_include,\n\n  IF(SDFLineItem.Daypart_Targeting IS NULL, FALSE, TRUE) AS include_daypart,\n  SDFLineItem.Daypart_Targeting AS raw_lineitem_daypart_include,\n\n  IF(SDFLineItem.Demographic_Targeting_Gender IS NULL \n    AND SDFLineItem.Demographic_Targeting_Age IS NULL \n    AND SDFLineItem.Demographic_Targeting_Household_Income IS NULL \n    AND SDFLineItem.Demographic_Targeting_Parental_Status IS NULL\n    AND SDFAdGroup.Demographic_Targeting_Gender IS NULL\n    AND SDFAdGroup.Demographic_Targeting_Age IS NULL\n    AND SDFAdGroup.Demographic_Targeting_Household_Income IS NULL\n    AND SDFAdGroup.Demographic_Targeting_Parental_Status IS NULL, FALSE, TRUE) \n    AS include_demographic,\n\n  SDFLineItem.Demographic_Targeting_Gender AS raw_lineitem_demo_gender,\n  SDFLineItem.Demographic_Targeting_Age AS raw_lineitem_demo_age,\n  SDFLineItem.Demographic_Targeting_Household_Income AS raw_lineitem_demo_income,\n  SDFLineItem.Demographic_Targeting_Parental_Status AS raw_lineitem_demo_parental_status,\n  SDFAdGroup.Demographic_Targeting_Gender AS raw_adgroup_demo_gender,\n  SDFAdGroup.Demographic_Targeting_Age AS raw_adgroup_demo_age,\n  SDFAdGroup.Demographic_Targeting_Household_Income AS raw_adgroup_demo_income,\n  SDFAdGroup.Demographic_Targeting_Parental_Status AS raw_adgroup_demo_parental_status,\n\n   IF(SDFLineItem.Position_Targeting_On_Screen IS NULL\n    AND SDFLineItem.Position_Targeting_Display_Position_In_Content IS NULL\n    AND SDFLineItem.Position_Targeting_Video_Position_In_Content IS NULL\n    AND SDFLineItem.Position_Targeting_Audio_Position_In_Content IS NULL, FALSE, TRUE)\n    AS include_position,\n  SDFLineItem.Position_Targeting_On_Screen AS raw_lineitem_position_onscreen,\n  SDFLineItem.Position_Targeting_Display_Position_In_Content AS raw_lineitem_display_position,\n  SDFLineItem.Position_Targeting_Video_Position_In_Content AS raw_lineitem_video_position,\n  SDFLineItem.Position_Targeting_Audio_Position_In_Content AS raw_lineitem_audio_position,\n\n  IF(SDFLineItem.Browser_Targeting_Include IS NULL, FALSE, TRUE) AS include_browser,\n  SDFLineItem.Browser_Targeting_Include AS raw_lineitem_browser_include,\n  IF(SDFLineItem.Device_Targeting_Include IS NULL, FALSE, TRUE) AS include_device,\n  SDFLineItem.Device_Targeting_Include AS raw_lineitem_device_include,\n  IF(SDFLineItem.Connection_Speed_Targeting IS NULL, FALSE, TRUE) AS include_connection_speed,\n  SDFLineItem.Connection_Speed_Targeting AS raw_lineitem_connection_speed_include,\n  IF(SDFLineItem.Carrier_Targeting_Include IS NULL, FALSE, TRUE) AS include_carrier,\n  SDFLineItem.Carrier_Targeting_Include AS raw_lineitem_carrier_include\n  \n  FROM `funnel-data.spindle_test_james_freeman.SDFLineItem` AS SDFLineItem\n  LEFT JOIN `funnel-data.spindle_test_james_freeman.SDFAdGroup` AS SDFAdGroup USING (Line_Item_Id)\n  LEFT JOIN `funnel-data.spindle_test_james_freeman.SDFInsertionOrder` AS SDFInsertionOrder USING (Io_Id)\n  LEFT JOIN `funnel-data.spindle_test_james_freeman.SDFCampaign` AS SDFCampaign USING (Campaign_Id)\n ),\n\nReport AS (\n  SELECT\n  SAFE_CAST(REGEXP_REPLACE(Report.Date, r"\\/", "-") AS Date) AS report_date,\n  SAFE_CAST(Report.Partner_ID AS INT64) AS partner_id,\n  Report.Partner AS partner_name,\n  SAFE_CAST(Report.Advertiser_ID AS INT64) AS advertiser_id,\n  Report.Advertiser AS advertiser_name,\n  SAFE_CAST(Report.Campaign_ID AS INT64) AS campaign_id,\n  Report.Campaign AS campaign_name,\n  SAFE_CAST(Report.Insertion_Order_ID AS INT64) AS io_id,\n  Report.Insertion_Order AS io_name,\n  Report.Line_Item AS lineitem_name,\n  SAFE_CAST(Report.Line_Item_ID AS INT64) AS lineitem_id,\n  -- Example naming convention check using regular expression. Checks LineItem name for given criteria\n  REGEXP_CONTAINS(Report.Line_Item, r"\\b(Display|Video|TrueView|display|video|trueview)\\b") AS lineitem_naming_convention,\n  SUM(SAFE_CAST(Report.Impressions AS INT64)) AS impressions,\n  SUM(SAFE_CAST(Report.Clicks AS INT64)) AS clicks,\n  ROUND(SUM(SAFE_CAST(Report.Revenue__USD_ AS FLOAT64)), 2) AS revenue_usd\n  \n  FROM `funnel-data.spindle_test_james_freeman.Reports` AS Report\n  WHERE SAFE_CAST(REGEXP_REPLACE(Report.Date, r"\\/", "-") AS Date) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)\n  GROUP BY 1,2,3,4,5,6,7,8,9,10,11\n),\n\nAggregations AS (\n  SELECT *,\n  \n-- Flag definitions\n  IF(frequency_enabled_lineitem = FALSE\n    AND frequency_enabled_trueview = FALSE \n    AND frequency_enabled_io = FALSE\n    AND frequency_enabled_campaign = FALSE, TRUE, FALSE)\n    AS flag_frequency_cap,\n  IF(bc_digital_content_label_exclusions = FALSE, TRUE, FALSE) AS flag_digital_content_label_exclusions,\n  IF(bc_sensitive_category_exclusions = FALSE, TRUE, FALSE) AS flag_sensitive_category_exclusions,\n  IF(bc_apps_url_channel_exclusions = FALSE, TRUE, FALSE) AS flag_apps_url_channel_exclusions,\n  IF(lineitem_type = \'TrueView\' AND exclude_youtube_channel_video = FALSE, TRUE, FALSE) AS flag_youtube_channel_video_exclusions\n  \n  FROM Report\n  INNER JOIN SDF USING (lineitem_id)\n)\n\nSELECT \n  *,\n  COUNTIF(flag_frequency_cap = TRUE)\n    + COUNTIF(flag_digital_content_label_exclusions = TRUE)\n    + COUNTIF(flag_sensitive_category_exclusions = TRUE)\n    + COUNTIF(flag_apps_url_channel_exclusions = TRUE)\n    + COUNTIF(flag_youtube_channel_video_exclusions = TRUE) AS total_flags\nFROM Aggregations\nGROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,\n26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,\n51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,\n76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100\n;', 'destinationTable': {'projectId': 'funnel-data', 'datasetId': 'spindle_test_james_freeman', 'tableId': 'spindle_lineitem_view'}, 'createDisposition': 'CREATE_IF_NEEDED', 'writeDisposition': 'WRITE_APPEND', 'priority': 'INTERACTIVE', 'allowLargeResults': True, 'useLegacySql': False, 'timePartitioning': {'type': 'DAY', 'expirationMs': '2592000000', 'field': 'report_date'}}, 'jobType': 'QUERY'}, 'jobReference': {'projectId': 'funnel-data', 'jobId': 'job_LVSfrWT08CXpzMBkCRAE8nJ0bd9r', 'location': 'EU'}, 'statistics': {'creationTime': '1589408559749', 'startTime': '1589408559841', 'endTime': '1589408559841'}, 'status': {'errorResult': {'reason': 'invalidQuery', 'location': 'query', 'message': 'No matching signature for function REGEXP_REPLACE for argument types: DATE, STRING, STRING. Supported signatures: REGEXP_REPLACE(STRING, STRING, STRING); REGEXP_REPLACE(BYTES, BYTES, BYTES) at [199:19]'}, 'errors': [{'reason': 'invalidQuery', 'location': 'query', 'message': 'No matching signature for function REGEXP_REPLACE for argument types: DATE, STRING, STRING. Supported signatures: REGEXP_REPLACE(STRING, STRING, STRING); REGEXP_REPLACE(BYTES, BYTES, BYTES) at [199:19]'}], 'state': 'DONE'}}
Traceback (most recent call last)
  File "/usr/local/lib/airflow/airflow/models/__init__.py", line 1491, in _run_raw_tas
    result = task_copy.execute(context=context
  File "/usr/local/lib/airflow/airflow/contrib/operators/bigquery_operator.py", line 209, in execut
    cluster_fields=self.cluster_fields
  File "/usr/local/lib/airflow/airflow/contrib/hooks/bigquery_hook.py", line 849, in run_quer
    return self.run_with_configuration(configuration
  File "/usr/local/lib/airflow/airflow/contrib/hooks/bigquery_hook.py", line 1252, in run_with_configuratio
    format(job['status']['errorResult'], job)
Exception: BigQuery job failed. Final error was: {'reason': 'invalidQuery', 'location': 'query', 'message': 'No matching signature for function REGEXP_REPLACE for argument types: DATE, STRING, STRING. Supported signatures: REGEXP_REPLACE(STRING, STRING, STRING); REGEXP_REPLACE(BYTES, BYTES, BYTES) at [199:19]'}. The job was: {'kind': 'bigquery#job', 'etag': '9mnWSoNkGPeMl52Mm/z42w==', 'id': 'funnel-data:EU.job_LVSfrWT08CXpzMBkCRAE8nJ0bd9r', 'selfLink': 'https://bigquery.googleapis.com/bigquery/v2/projects/funnel-data/jobs/job_LVSfrWT08CXpzMBkCRAE8nJ0bd9r?location=EU', 'user_email': '172399749176-compute@developer.gserviceaccount.com', 'configuration': {'query': {'query': '/*\n * Copyright 2020 Google LLC\n *\n * Licensed under the Apache License, Version 2.0 (the "License");\n * you may not use this file except in compliance with the License.\n * You may obtain a copy of the License at\n *\n *     https://www.apache.org/licenses/LICENSE-2.0\n *\n * Unless required by applicable law or agreed to in writing, software\n * distributed under the License is distributed on an "AS IS" BASIS,\n * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.\n * See the License for the specific language governing permissions and\n * limitations under the License.\n * \n */\n\n-- standardSQL\nWITH SDF AS (\n  SELECT\n  CAST(SDFLineItem.Line_Item_Id AS INT64) AS lineitem_id,\n  SDFLineItem.Type AS lineitem_type,\n  SDFLineItem.End_Date AS lineitem_end_date,\n  SDFLineItem.Pacing AS lineitem_pacing,\n  SDFLineItem.Pacing_Rate AS lineitem_pacing_rate,\n  SDFLineItem.Bid_Strategy_Value AS lineitem_bid_value,\n  SDFInsertionOrder.Pacing AS io_pacing,\n  SDFInsertionOrder.Pacing_Rate AS io_pacing_rate,\n  SDFInsertionOrder.Performance_Goal_Type AS io_performance_goal_type,\n  SDFCampaign.Campaign_Budget AS campaign_budget,\n  SAFE_CAST(SDFLineItem.Frequency_Enabled AS BOOL) AS frequency_enabled_lineitem,\n  SAFE_CAST(SDFInsertionOrder.Frequency_Enabled AS BOOL) AS frequency_enabled_io,\n  IF(CAST(SDFCampaign.Frequency_Enabled AS BOOL) IS NULL, FALSE, CAST(SDFCampaign.Frequency_Enabled AS BOOL)) AS frequency_enabled_campaign, \n  IF(SDFLineItem.TrueView_View_Frequency_Enabled = "False" OR SDFLineItem.TrueView_View_Frequency_Enabled IS NULL, FALSE, TRUE) AS frequency_enabled_trueview,\n  \n-- Brand controls\n  IF(SDFLineItem.Digital_Content_Labels_Exclude IS NULL, FALSE, TRUE) \n    AS bc_digital_content_label_exclusions,\n  IF(SDFLineItem.Brand_Safety_Custom_Settings IS NULL\n    AND SDFLineItem.TrueView_Category_Exclusions_Targeting IS NULL, FALSE, TRUE) \n    AS bc_sensitive_category_exclusions,\n  IF(SDFLineItem.App_Collection_Targeting_Exclude IS NULL \n    AND SDFLineItem.App_Targeting_Exclude IS NULL \n    AND SDFLineItem.Site_Targeting_Exclude IS NULL\n    AND SDFLineItem.Channel_Targeting_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Channels_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Videos_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_URLs_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_Apps_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_App_Collections_Exclude IS NULL, FALSE, TRUE)\n    AS bc_apps_url_channel_exclusions,\n  IF(SDFLineItem.Keyword_Targeting_Exclude IS NULL \n    AND SDFLineItem.Keyword_List_Targeting_Exclude IS NULL\n    AND SDFAdGroup.Keyword_Targeting_Exclude IS NULL, FALSE, TRUE) \n    AS bc_keyword_exclusions,\n  IF(SDFLineItem.Inventory_Source_Targeting_Authorized_Seller_Options = \'Authorized Direct Sellers\', TRUE, FALSE) AS bc_authorized_sellers_only,\n  SDFLineItem.Inventory_Source_Targeting_Authorized_Seller_Options AS raw_lineitem_inventory_source_auth_seller,\n  IF(SDFLineItem.Third_Party_Verification_Services = \'None\'\n    OR SDFLineItem.Third_Party_Verification_Services IS NULL, FALSE, TRUE) \n    AS bc_verification_services,\n  \n-- Targeting\n  IF(SDFAdGroup.Placement_Targeting_YouTube_Channels_Exclude IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Videos_Exclude iS NULL, FALSE, TRUE)\n    AS exclude_youtube_channel_video,\n  IF(SDFLineItem.Bid_Multipliers IS NULL, FALSE, TRUE) AS bid_multipliers,\n  IF(SDFLineItem.App_Collection_Targeting_Include IS NULL \n    AND SDFLineItem.App_Targeting_Include IS NULL \n    AND SDFLineItem.Site_Targeting_Include IS NULL \n    AND SDFLineItem.Channel_Targeting_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Channels_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_YouTube_Videos_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_URLs_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_Apps_Include IS NULL\n    AND SDFAdGroup.Placement_Targeting_App_Collections_Include IS NULL, FALSE, TRUE) \n    AS include_apps_url_channel,\n  SDFLineItem.App_Collection_Targeting_Include AS raw_lineitem_app_collection_include,\n  SDFLineItem.App_Targeting_Include AS raw_lineitem_app_include,\n  SDFLineItem.Site_Targeting_Include AS raw_lineitem_site_include,\n  SDFLineItem.Channel_Targeting_Include AS raw_lineitem_channel_include,\n  SDFAdGroup.Placement_Targeting_YouTube_Channels_Include AS raw_adgroup_placement_yt_channel_include,\n  SDFAdGroup.Placement_Targeting_YouTube_Videos_Include AS raw_adgroup_placement_yt_video_include,\n  SDFAdGroup.Placement_Targeting_URLs_Include AS raw_adgroup_placement_urls_include,\n  SDFAdGroup.Placement_Targeting_Apps_Include AS raw_adgroup_placement_apps_include,\n  SDFAdGroup.Placement_Targeting_App_Collections_Include AS raw_adgroup_placement_app_col_include,\n\n  IF(SDFLineItem.Keyword_Targeting_Include IS NULL\n    AND SDFAdGroup.Keyword_Targeting_Include IS NULL, FALSE, TRUE)\n    AS include_keyword,\n  SDFLineItem.Keyword_Targeting_Include AS raw_lineitem_keyword_include,\n  SDFAdGroup.Keyword_Targeting_Include AS raw_adgroup_keyword_include,\n\n  IF(SDFLineItem.Category_Targeting_Include IS NULL\n    AND SDFLineItem.TrueView_Category_Exclusions_Targeting IS NULL\n    AND SDFAdGroup.Category_Targeting_Include IS NULL, FALSE, TRUE)\n    AS include_category,\n  SDFLineItem.Category_Targeting_Include AS raw_lineitem_category_include,\n  SDFLineItem.TrueView_Category_Exclusions_Targeting AS raw_lineitem_trueview_category_exclusions,\n  SDFAdGroup.Category_Targeting_Include AS raw_adgroup_category_include,\n\n  IF(SDFLineItem.Environment_Targeting IS NULL, FALSE, TRUE) AS include_environment,\n  SDFLineItem.Environment_Targeting AS raw_lineitem_environment_include,\n\n  IF(SDFLineItem.Language_Targeting_Include IS NULL, FALSE, TRUE) AS include_language,\n  SDFLineItem.Language_Targeting_Include AS raw_lineitem_language_include,\n\n  IF(SDFLineItem.Inventory_Source_Targeting_Include IS NULL, FALSE, TRUE) AS include_inventory,\n  SDFLineItem.Inventory_Source_Targeting_Include AS raw_lineitem_inventory_include,\n\n  IF(SDFLineItem.TrueView_Third_Party_Viewability_Vendor != \'None\' \n    AND SDFLineItem.TrueView_Third_Party_Viewability_Vendor IS NOT NULL \n    OR SDFLineItem.Viewability_Targeting_Active_View IS NOT NULL, TRUE, FALSE)\n    AS include_viewability,\n  SDFLineItem.TrueView_Third_Party_Viewability_Vendor AS raw_lineitem_trueview_3p_viewability_include,\n  SDFLineItem.Viewability_Targeting_Active_View AS raw_lineitem_viewability_active_view_include,\n\n  IF(SDFLineItem.Audience_Targeting_Include IS NULL\n    AND SDFAdGroup.Audience_Targeting_Include IS NULL, FALSE, TRUE)\n    AS include_audience,\n  SDFLineItem.Audience_Targeting_Include AS raw_lineitem_audience_include,\n  SDFAdGroup.Audience_Targeting_Include AS raw_adgroup_audience_include,\n\n  IF(SDFLineItem.Affinity_And_In_Market_Targeting_Include IS NULL\n    AND SDFAdGroup.Affinity_And_In_Market_Targeting_Include IS NULL, FALSE, TRUE)\n    AS include_affinity_inmarket,\n  SDFLineItem.Affinity_And_In_Market_Targeting_Include AS raw_lineitem_affinity_inmarket_include,\n  SDFAdGroup.Affinity_And_In_Market_Targeting_Include AS raw_adgroup_affinity_inmarket_include,\n\n  IF(SDFLineItem.Geography_Targeting_Include IS NULL, FALSE, TRUE) AS include_geography,\n  SDFLineItem.Geography_Targeting_Include AS raw_lineitem_geography_include,\n\n  IF(SDFLineItem.Daypart_Targeting IS NULL, FALSE, TRUE) AS include_daypart,\n  SDFLineItem.Daypart_Targeting AS raw_lineitem_daypart_include,\n\n  IF(SDFLineItem.Demographic_Targeting_Gender IS NULL \n    AND SDFLineItem.Demographic_Targeting_Age IS NULL \n    AND SDFLineItem.Demographic_Targeting_Household_Income IS NULL \n    AND SDFLineItem.Demographic_Targeting_Parental_Status IS NULL\n    AND SDFAdGroup.Demographic_Targeting_Gender IS NULL\n    AND SDFAdGroup.Demographic_Targeting_Age IS NULL\n    AND SDFAdGroup.Demographic_Targeting_Household_Income IS NULL\n    AND SDFAdGroup.Demographic_Targeting_Parental_Status IS NULL, FALSE, TRUE) \n    AS include_demographic,\n\n  SDFLineItem.Demographic_Targeting_Gender AS raw_lineitem_demo_gender,\n  SDFLineItem.Demographic_Targeting_Age AS raw_lineitem_demo_age,\n  SDFLineItem.Demographic_Targeting_Household_Income AS raw_lineitem_demo_income,\n  SDFLineItem.Demographic_Targeting_Parental_Status AS raw_lineitem_demo_parental_status,\n  SDFAdGroup.Demographic_Targeting_Gender AS raw_adgroup_demo_gender,\n  SDFAdGroup.Demographic_Targeting_Age AS raw_adgroup_demo_age,\n  SDFAdGroup.Demographic_Targeting_Household_Income AS raw_adgroup_demo_income,\n  SDFAdGroup.Demographic_Targeting_Parental_Status AS raw_adgroup_demo_parental_status,\n\n   IF(SDFLineItem.Position_Targeting_On_Screen IS NULL\n    AND SDFLineItem.Position_Targeting_Display_Position_In_Content IS NULL\n    AND SDFLineItem.Position_Targeting_Video_Position_In_Content IS NULL\n    AND SDFLineItem.Position_Targeting_Audio_Position_In_Content IS NULL, FALSE, TRUE)\n    AS include_position,\n  SDFLineItem.Position_Targeting_On_Screen AS raw_lineitem_position_onscreen,\n  SDFLineItem.Position_Targeting_Display_Position_In_Content AS raw_lineitem_display_position,\n  SDFLineItem.Position_Targeting_Video_Position_In_Content AS raw_lineitem_video_position,\n  SDFLineItem.Position_Targeting_Audio_Position_In_Content AS raw_lineitem_audio_position,\n\n  IF(SDFLineItem.Browser_Targeting_Include IS NULL, FALSE, TRUE) AS include_browser,\n  SDFLineItem.Browser_Targeting_Include AS raw_lineitem_browser_include,\n  IF(SDFLineItem.Device_Targeting_Include IS NULL, FALSE, TRUE) AS include_device,\n  SDFLineItem.Device_Targeting_Include AS raw_lineitem_device_include,\n  IF(SDFLineItem.Connection_Speed_Targeting IS NULL, FALSE, TRUE) AS include_connection_speed,\n  SDFLineItem.Connection_Speed_Targeting AS raw_lineitem_connection_speed_include,\n  IF(SDFLineItem.Carrier_Targeting_Include IS NULL, FALSE, TRUE) AS include_carrier,\n  SDFLineItem.Carrier_Targeting_Include AS raw_lineitem_carrier_include\n  \n  FROM `funnel-data.spindle_test_james_freeman.SDFLineItem` AS SDFLineItem\n  LEFT JOIN `funnel-data.spindle_test_james_freeman.SDFAdGroup` AS SDFAdGroup USING (Line_Item_Id)\n  LEFT JOIN `funnel-data.spindle_test_james_freeman.SDFInsertionOrder` AS SDFInsertionOrder USING (Io_Id)\n  LEFT JOIN `funnel-data.spindle_test_james_freeman.SDFCampaign` AS SDFCampaign USING (Campaign_Id)\n ),\n\nReport AS (\n  SELECT\n  SAFE_CAST(REGEXP_REPLACE(Report.Date, r"\\/", "-") AS Date) AS report_date,\n  SAFE_CAST(Report.Partner_ID AS INT64) AS partner_id,\n  Report.Partner AS partner_name,\n  SAFE_CAST(Report.Advertiser_ID AS INT64) AS advertiser_id,\n  Report.Advertiser AS advertiser_name,\n  SAFE_CAST(Report.Campaign_ID AS INT64) AS campaign_id,\n  Report.Campaign AS campaign_name,\n  SAFE_CAST(Report.Insertion_Order_ID AS INT64) AS io_id,\n  Report.Insertion_Order AS io_name,\n  Report.Line_Item AS lineitem_name,\n  SAFE_CAST(Report.Line_Item_ID AS INT64) AS lineitem_id,\n  -- Example naming convention check using regular expression. Checks LineItem name for given criteria\n  REGEXP_CONTAINS(Report.Line_Item, r"\\b(Display|Video|TrueView|display|video|trueview)\\b") AS lineitem_naming_convention,\n  SUM(SAFE_CAST(Report.Impressions AS INT64)) AS impressions,\n  SUM(SAFE_CAST(Report.Clicks AS INT64)) AS clicks,\n  ROUND(SUM(SAFE_CAST(Report.Revenue__USD_ AS FLOAT64)), 2) AS revenue_usd\n  \n  FROM `funnel-data.spindle_test_james_freeman.Reports` AS Report\n  WHERE SAFE_CAST(REGEXP_REPLACE(Report.Date, r"\\/", "-") AS Date) = DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)\n  GROUP BY 1,2,3,4,5,6,7,8,9,10,11\n),\n\nAggregations AS (\n  SELECT *,\n  \n-- Flag definitions\n  IF(frequency_enabled_lineitem = FALSE\n    AND frequency_enabled_trueview = FALSE \n    AND frequency_enabled_io = FALSE\n    AND frequency_enabled_campaign = FALSE, TRUE, FALSE)\n    AS flag_frequency_cap,\n  IF(bc_digital_content_label_exclusions = FALSE, TRUE, FALSE) AS flag_digital_content_label_exclusions,\n  IF(bc_sensitive_category_exclusions = FALSE, TRUE, FALSE) AS flag_sensitive_category_exclusions,\n  IF(bc_apps_url_channel_exclusions = FALSE, TRUE, FALSE) AS flag_apps_url_channel_exclusions,\n  IF(lineitem_type = \'TrueView\' AND exclude_youtube_channel_video = FALSE, TRUE, FALSE) AS flag_youtube_channel_video_exclusions\n  \n  FROM Report\n  INNER JOIN SDF USING (lineitem_id)\n)\n\nSELECT \n  *,\n  COUNTIF(flag_frequency_cap = TRUE)\n    + COUNTIF(flag_digital_content_label_exclusions = TRUE)\n    + COUNTIF(flag_sensitive_category_exclusions = TRUE)\n    + COUNTIF(flag_apps_url_channel_exclusions = TRUE)\n    + COUNTIF(flag_youtube_channel_video_exclusions = TRUE) AS total_flags\nFROM Aggregations\nGROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,\n26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,\n51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,\n76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100\n;', 'destinationTable': {'projectId': 'funnel-data', 'datasetId': 'spindle_test_james_freeman', 'tableId': 'spindle_lineitem_view'}, 'createDisposition': 'CREATE_IF_NEEDED', 'writeDisposition': 'WRITE_APPEND', 'priority': 'INTERACTIVE', 'allowLargeResults': True, 'useLegacySql': False, 'timePartitioning': {'type': 'DAY', 'expirationMs': '2592000000', 'field': 'report_date'}}, 'jobType': 'QUERY'}, 'jobReference': {'projectId': 'funnel-data', 'jobId': 'job_LVSfrWT08CXpzMBkCRAE8nJ0bd9r', 'location': 'EU'}, 'statistics': {'creationTime': '1589408559749', 'startTime': '1589408559841', 'endTime': '1589408559841'}, 'status': {'errorResult': {'reason': 'invalidQuery', 'location': 'query', 'message': 'No matching signature for function REGEXP_REPLACE for argument types: DATE, STRING, STRING. Supported signatures: REGEXP_REPLACE(STRING, STRING, STRING); REGEXP_REPLACE(BYTES, BYTES, BYTES) at [199:19]'}, 'errors': [{'reason': 'invalidQuery', 'location': 'query', 'message': 'No matching signature for function REGEXP_REPLACE for argument types: DATE, STRING, STRING. Supported signatures: REGEXP_REPLACE(STRING, STRING, STRING); REGEXP_REPLACE(BYTES, BYTES, BYTES) at [199:19]'}], 'state': 'DONE'}

[james-d-f]

This seems to be resolved by reverting to commit: 77eae5b8582f8ea638f4f4c0b6b446015a620502

Suggesting that the error comes from the commit: Changed report columns to safe_cast

Unless it resolved itself for other uncorrelated reasons.

@mattlynam were you able to test a new setup since making that commit?

[Goropius]

I got the same issue, will now try to run with an older version of queries/create_table.sql as suggested by james

[mattlynam]

Thanks for the feedback. I wasn't able to replicate this issue on multiple clean setups, but obviously it is an issue, so I'll look into it more and try release a fix soon.

[mattlynam]

Hopefully this is now resolved with the latest commit. I fixed the Reports table schema, to guarantee field type, and swapped the REGEXP_REPLACE, with REPLACE, that was resulting in errors due to mismatched types

[Goropius]

Thanks mattlynam, the errors have disappeared after a new deploy.