UDF returns "internal error"

[GoogleCodeExporter]

I'm receiving the following error:

Query Failed
Error: An internal error occurred and the request could not be completed.
Job ID: realself-main:bquijob_9032737_153aa231452

There are no joins, and the query is:

select 
  device.browser,
  device.browser_version,
  device.device_category,
  device.flash_version,
  device.is_mobile,
  device.java_enabled,
  device.language,
  device.mobile_device_branding,
  device.operating_system,
  device.operating_system_version,
  device.screen_colors,
  device.screen_resolution,
  device.user_agent,
  ga_session_id,
  geo.continent,
  geo.country,
  geo.metro,
  geo.region,
  geo.rs_location.id,
  geo.rs_location.name,
  geo.sub_continent,
  hits.author_user_id,
  hits.author_user_name,
  hits.content.id,
  hits.content.type,
  hits.custom_is_secure,
  hits.datetime_utc,
  hits.days_until_surgery_date,
  hits.doctor.name,
  hits.doctor.id,
  hits.doctor_advertisement,
  hits.doctor_advertisement.doctor.id,
  hits.doctor_advertisement.doctor.name,
  hits.doctor_advertisement.is_click,
  hits.doctor_advertisement.is_impression,
  hits.doctor_advertisement.sku,
  hits.doctor_advertisement.topic,
  hits.doctor_location.name,
  hits.doctor_location.id,
  hits.events.action,
  hits.events.category,
  hits.events.label,
  hits.events.value,
  hits.first_pageview_hit_number,
  hits.hit_number,
  hits.hours_since_most_recent_answer,
  hits.hours_since_most_recent_review_update,
  hits.is_interaction,
  hits.last_pageview_hit_number,
  hits.local_time,
  hits.metrics.answer_creates,
  hits.metrics.answer_votes,
  hits.metrics.click_to_call_contacts,
  hits.metrics.consumer_media_votes,
  hits.metrics.discussion_comments,
  hits.metrics.discussion_creates,
  hits.metrics.discussion_follows,
  hits.metrics.discussion_unfollows,
  hits.metrics.doctor_follows,
  hits.metrics.doctor_photo_votes,
  hits.metrics.doctor_profile_claims,
  hits.metrics.doctor_unfollows,
  hits.metrics.email_contacts,
  hits.metrics.estimated_doctor_contacts,
  hits.metrics.get_direction_clicks,
  hits.metrics.guide_comments,
  hits.metrics.guide_creates,
  hits.metrics.guide_follows,
  hits.metrics.guide_unfollows,
  hits.metrics.hits,
  hits.metrics.pageviews,
  hits.metrics.private_messages,
  hits.metrics.question_creates,
  hits.metrics.question_follows,
  hits.metrics.question_unfollows,
  hits.metrics.review_comments,
  hits.metrics.review_creates,
  hits.metrics.review_entry_votes,
  hits.metrics.review_follows,
  hits.metrics.review_unfollows,
  hits.metrics.review_updates,
  hits.metrics.review_votes,
  hits.metrics.sign_ins,
  hits.metrics.sign_ups,
  hits.metrics.video_comments,
  hits.metrics.video_follows,
  hits.metrics.video_unfollows,
  hits.metrics.video_votes,
  hits.metrics.virtual_pageviews,
  hits.metrics.website_clicks,
  hits.number_of_answers,
  hits.number_of_comments,
  hits.number_of_review_updates,
  hits.page.clean_uri,
  hits.page.engaged_10s,
  hits.page.engaged_30s,
  hits.page.engaged_60s,
  hits.page.hostname,
  hits.page.is_virtual,
  hits.page.title,
  hits.page.uri,
  hits.parent_content_id,
  hits.photo_count,
  hits.reported_cost_in_review,
  hits.review_word_count,
  hits.rs_user_id,
  hits.rs_user_id_mod_100,
  hits.time,
  hits.topic.id,
  hits.topic.name,
  hits.type,
  hits.video_count,
  hits.worth_it_rating_of_review,
  logged_in_as_consumer,
  logged_in_as_doctor,
  logged_in_as_paying_doctor,
  logged_in_as_realfriend,
  logged_in_as_rs_employee,
  optimizely.experiment,
  optimizely.variant,
  session_number,
  session_start_timestamp_utc,
  traffic_source.keyword,
  traffic_source.medium,
  traffic_source.referrer,
  traffic_source.referrer_hostname,
  traffic_source.source,
  user.ga_user_id,
  user.is_rs_employee,
  number_of_comments,
  number_of_review_updates,
  hours_since_most_recent_review_update,
  review_word_count,
  photo_count,
  worth_it_rating_of_review,
  reported_cost_in_review,
  traffic_source.campaign,
  traffic_source.referralPath,
  traffic_source.adContent,
  traffic_source.adwordsClickInfo.campaignId,
  traffic_source.adwordsClickInfo.adGroupId,
  traffic_source.adwordsClickInfo.creativeId,
  traffic_source.adwordsClickInfo.criteriaId,
  traffic_source.adwordsClickInfo.page,
  traffic_source.adwordsClickInfo.slot,
  traffic_source.adwordsClickInfo.criteriaParameters,
  traffic_source.adwordsClickInfo.gclId,
  traffic_source.adwordsClickInfo.customerId,
  traffic_source.adwordsClickInfo.adNetworkType,
  traffic_source.adwordsClickInfo.isVideoAd,
  traffic_source.adwordsClickInfo.targetingCriteria.boomUserlistId,
  hits.answer_id,
  hits.answer_title,
  hits.lead_decision_stage,
  hits.special_offers_selected,
  hits.sort_order_position,
  hits.page_location_id,
  hits.result_count,
  hits.search_terms,
  hits.filter_values,
  hits.adzerk_advertiser_id,
  hits.adzerk_advertiser_name,
  hits.adzerk_creative_id,
  hits.metrics.video_play_value
from 
  temp_ga_processing(
    select 
      customDimensions.index,
      customDimensions.value,
      date,
      device.browser,
      device.browserVersion,
      device.deviceCategory,
      device.flashVersion,
      device.isMobile,
      device.javaEnabled,
      device.language,
      device.mobileDeviceBranding,
      device.operatingSystem,
      device.operatingSystemVersion,
      device.screenColors,
      device.screenResolution,
      fullVisitorId,
      geoNetwork.continent,
      geoNetwork.country,
      geoNetwork.metro,
      geoNetwork.region,
      geoNetwork.subContinent,
      hits.appInfo.exitScreenName,
      hits.appInfo.landingScreenName,
      hits.appInfo.screenDepth,
      hits.appInfo.screenName,
      hits.customDimensions.index,
      hits.customDimensions.value,
      hits.customMetrics.index,
      hits.customMetrics.value,
      hits.customVariables,
      hits.eCommerceAction.action_type,
      hits.eCommerceAction.step,
      hits.eventInfo.eventAction,
      hits.eventInfo.eventCategory,
      hits.eventInfo.eventLabel,
      hits.eventInfo.eventValue,
      hits.exceptionInfo.isFatal,
      hits.experiment,
      hits.hitNumber,
      hits.hour,
      hits.isEntrance,
      hits.isExit,
      hits.isInteraction,
      hits.minute,
      hits.page.hostname,
      hits.page.pagePath,
      hits.page.pageTitle,
      hits.page.searchCategory,
      hits.page.searchKeyword,
      hits.product.customDimensions,
      hits.product.customMetrics,
      hits.product.isImpression,
      hits.product.localProductPrice,
      hits.product.productBrand,
      hits.product.productPrice,
      hits.product.productSKU,
      hits.product.v2ProductCategory,
      hits.product.v2ProductName,
      hits.promotion,
      hits.referer,
      hits.time,
      hits.type,
      totals.bounces,
      totals.hits,
      totals.newVisits,
      totals.pageviews,
      totals.timeOnSite,
      totals.visits,
      trafficSource.adContent,
      trafficSource.campaign,
      trafficSource.keyword,
      trafficSource.medium,
      trafficSource.referralPath,
      trafficSource.source,
      visitId,
      visitNumber,
      visitStartTime
    from 
      [79689075.ga_sessions_20160201] where totals.hits < 10 limit 1)

Original issue reported on code.google.com by gray...@realself.com on 24 Mar 2016 at 7:46

[GoogleCodeExporter]

Original comment by thomasp...@google.com on 24 Mar 2016 at 8:33

• Changed state: Accepted

[GoogleCodeExporter]

Not sure if this is related:
http://stackoverflow.com/questions/36274752/bigquery-udf-internal-error

Original comment by pli...@datalicious.com on 29 Mar 2016 at 3:51

[GoogleCodeExporter]

Re the original poster's issue, this should now be resolved.  Please retry and 
let me know if you run into any issues.

Pliang@ - I am looking into your query now, will reply on SO.

Original comment by thomasp...@google.com on 29 Mar 2016 at 4:16

• Changed state: Fixed

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

thomaspark@ - I tried again and am still getting the same "internal error" 
message, the new job ID is realself-main:job_ck4OapaFQAoRmUMytrri9h1XGZk

Original comment by gray...@realself.com on 29 Mar 2016 at 10:44

[GoogleCodeExporter]

[deleted comment]

[GoogleCodeExporter]

Got the code - it looks like the problem is that your output schema provided 
two fields with the same name (local_time).  I'll open a bug on our side to 
detect this case and provide a more helpful error message.

There are a couple of other issues in the query; there are some non-leaf fields 
in the inner select, and there are some output columns that are defined as 
"integer" but are having non-integer values written to them 
(worth_it_rating_of_review, reported_cost_in_review, number_of_review_updates, 
photo_count, maybe others)

If I update these, I can run the query on at least 100k rows.  I'll need to 
size up my test tree to test more rows; this should be enough to get you 
unblocked for the time being though I hope?

Original comment by thomasp...@google.com on 29 Mar 2016 at 11:36

• Changed state: Done