Export recommendations to BigQuery

Overview

With BigQuery export, you can view daily snapshots of recommendationsfor your organization. This is done using the BigQuery Data Transfer Service. SeeRecommenders to see which recommenders areincluded in BigQuery export today.

BigQuery export supports the following:

  • Exporting recommendations and insights for all projects, folders, and billingaccounts for an organization (including organization-level recommendationsand insights)

  • Exporting negotiated pricing instead of standard pricing for cost savingsrecommendations.

Before you begin

Complete the following steps before you create a data transfer for recommendations:

  • Allow the BigQuery Data Transfer Service permission to manage your data transfer. If you use the BigQuery web UI to create the transfer, you must allow pop-ups fromconsole.cloud.google.com on your browser to be able to view the permissions. For more details, see enable a BigQuery Data Transfer Service.
  • Create a BigQuery dataset to store data.
    • The data transfer uses the same region where the dataset is created. The location is immutable once the dataset and transfer are created.
    • The dataset will contain insights and recommendations from all regions across the globe. Thus this operation would aggregate all those data into a global region during the process. Please refer toGoogle Cloud Customer Care if there are any data residency concerns.
    • If the dataset location is newly launched, there may be a delay in initial export data availability.

Pricing

Exporting recommendations to BigQuery is available to allRecommender customers based on theirRecommender pricing tier.

Required permissions

While setting up the data transfer, you require the following permissions at theproject level where you create a data transfer:

  • bigquery.transfers.update - Allows you to create the transfer
  • bigquery.datasets.update - Allows you to update actions on the target dataset
  • resourcemanager.projects.update - Allows you to select a project where you'd like the export data to be stored
  • pubsub.topics.list - Allows you to select a Pub/Sub topic in order toreceive notifications about your export

The following permission is required at the organization level. This organizationcorresponds to the one that the export is being set up for.

  • recommender.resources.export - Allows you to export recommendations to BigQuery

The following permissions are required to export negotiated prices for cost savingsrecommendations:

  • billing.resourceCosts.get at project level - Allows exporting negotiated prices for project level recommendations
  • billing.accounts.getSpendingInformation at billing account level - Allows exporting negotiated prices for billing account level recommendations

Without these permissions, cost savings recommendations will be exported withstandard prices instead of negotiated prices.

Note: The latest available permissions are used to determine whether negotiated pricesare exported. If you have recently added or removed permissions, there may be a fewdays delay before prices are updated in the export.Note: Service accounts are currently not supported via the UI - see instructionsbelowon using the API/CLI if you want to set up an export using a service account.

Grant permissions

The following roles have to be granted on the project where you create thedata transfer:

    To allow you to create a transfer and update actions on the target dataset, you must grant the following role:

  • BigQuery admin role -roles/bigquery.admin

    • There are multiple roles that contain permissions to select a project for storing your export data and for selecting a Pub/Sub topic to receive notifications. To have both these permissions available, you can grant the following role:

  • Project owner role -roles/owner

    • There are multiple roles that contain the permission billing.resourceCosts.get to export negotiated prices for cost savings project level recommendations - you can grant any one of them:

  • Project Owner role -roles/owner
  • Project Viewer role -roles/viewer
  • Project Editor role -roles/editor

    • There are multiple roles that contain the permission billing.accounts.getSpendingInformation to export negotiated prices for cost savings billing account level recommendations - you can grant any one of them:

  • Billing Account Administrator role -roles/billing.admin
  • Billing Account Costs Manager role -roles/billing.costsManager
  • Billing Account Viewer role -roles/billing.viewer

You must grant the following role at the organization level:

  • Recommendations Exporter (roles/recommender.exporter) role on the Google Cloud console.
Note: If you do not have this permission granted, the dailyrun history status shows that the permission ismissing. Upon granting this permission, the future exports will run successfully. You also havethe option to retry the export for the runs that didn't have the permission granted.

You can also createCustomroles containing therequired permissions.

Create a data transfer for recommendations

  1. Go to the BigQuery export form for Active Assist.

    Go to BigQuery export form

  2. Select aDestination Project to store the recommendation data.

    Form in the console for selecting a destination project to store recommendation data, with an error message indicating the Destination project ID is required.Form in the console for selecting a destination project to store recommendation data, with an error message indicating the Destination project I.D. is required.

    Note: If you can't find the project, then you either don't have the necessarypermissions,or there aren't any projects available.

  3. ClickNext.

  4. ClickEnable APIs to enable the BigQuery APIs for the export.This can take a several seconds to complete. Once done, clickContinue.

    Note: If the APIs are already enabled for this project, this step is skipped.

  5. On theConfigure Transfer form, provide the following details:

  6. ClickCreate to create the transfer.

  7. ClickAllow on the consent pop-up.

    Authorization dialog box for Recommender, listing the permissions it requires and providing links to learn about the risks and how Recommender handles data.Authorization dialog box for Recommender, listing the permissions it requires and providing links to learn about the risks and how Recommender handles data.

    Note: By allowing consent, this permits the usage of scopes BigQuery and Google Cloud Platform APIs in your data transfer.These APIs are used to view and manage your data inhttps://www.googleapis.com/auth/bigqueryandhttps://www.googleapis.com/auth/cloud-platform.These APIs are only used for projects and organizations that you have access to based on thepermissions set.

  8. Once the transfer is created, you are directed back to the Active Assist.You can click the link to access the transfer configuration details.Alternatively, you can access the transfers by doing the following:

    • Go to the BigQuery page in the Google Cloud console.

      Go to the BigQuery page

    • ClickData Transfers. You can view all the available data transfers.

View the run history for a transfer

To view the run history for a transfer, do the following:

  1. Go to the BigQuery page in the Google Cloud console.

    Go to the BigQuery page

  2. ClickData Transfers. You can view all the available data transfers.

    Note: A transfer is complete when the status appears green.

  3. Click the appropriate transfer in the list.

  4. In the list of run transfers displayed under theRUN HISTORY tab, select the transfer you want to view the details for.

  5. TheRun details panel is displayed for the individual run transfer you selected.Some of the possible run details displayed are:

When does your data get exported?

When you create a data transfer, the first export occurs in two days.After the first export, the export jobs run at the cadence you have requested at set up time. The following conditions apply:

Common status messages on an export

Learn about common status messages you can see exporting recommendations to BigQuery.

User does not have required permission

The following message occurs when user does not have required permissionrecommender.resources.export. You will see the following message:

User does not have required permission "recommender.resources.export". Please, obtain the required permissions for the datasource and try again by triggering a backfill for this date

To resolve this issue grant the IAM roleroles/recommender.exporter touser/service account setting up the export at organizational level for the organization for which the export was set up for. It can be given through the gcloud commands below:

Transfer deferred due to source data not being available

The following message occurs when the transfer is rescheduled because the source data is not yet available. This is not an error. It means the export pipelines have not completed yet for the day. The transfer will re-run at the new scheduled time and will succeed once the export pipelines have completed. You will see the following message:

Transfer deferred due to source data not being available

Source data not found

The following message occurs when the export pipelines have completed, but norecommendations or insights were found for the organization that the export was set up for. You will see the following message:

Source data not found for 'recommendations_export$<date>'insights_export$<date>

This message occurs due to following reasons:

View tables for a transfer

When you export recommendations to BigQuery, the dataset containstwo tables that arepartitioned by date:

  • recommendations_export
  • insight_export

For more details on tables and schema, seeCreating and using tables andSpecifying a schema.

To view the tables for a data transfer, do the following:

  1. Go to the BigQuery page in the Google Cloud console.Go to the BigQuery page

  2. ClickData Transfers. You can view all the available data transfers.

  3. Click the appropriate transfer in the list.

  4. Click theCONFIGURATION tab and click the dataset.

  5. In theExplorer panel, expand your project and select a dataset.The description and details appear in the details panel. The tables for a dataset are listed with the dataset name in the Explorer panel.

Schedule a backfill

Recommendations for a date in the past (this date being later than the date when the organizationwas opted in for the export) can be exported by scheduling a backfill.To schedule a backfill, do the following:

  1. Go to the BigQuery page in the Google Cloud console.

    Go to the BigQuery page

  2. ClickData Transfers.

  3. On theTransfers page, click on an appropriate transfer in the list.

    1. ClickSchedule backfill.

    2. In theSchedule backfill dialog, choose yourStart date andEnd date.

      Form in the console for scheduling a backfill run, showing the required fields for Start date and End date, and a note that the start date is inclusive and the end date is exclusive.Form in the console for scheduling a backfill run, showing the required fields for Start date and End date, and a note that the start date is inclusive and the end date is exclusive.

For more information on working with transfers, seeWorking with transfers.

Export schema

Recommendations Export table:

schema:fields:-name:cloud_entity_typetype:STRINGdescription:|Representswhatcloudentitytypetherecommendationwasgeneratedfor-eg:projectnumber,billingaccount-name:cloud_entity_idtype:STRINGdescription:|Valueoftheprojectnumberorbillingaccountid-name:nametype:STRINGdescription:|Nameofrecommendation.Aprojectrecommendationisrepresentedasprojects/[PROJECT_NUMBER]/locations/[LOCATION]/recommenders/[RECOMMENDER_ID]/recommendations/[RECOMMENDATION_ID]-name:locationtype:STRINGdescription:|Locationforwhichthisrecommendationisgenerated-name:recommendertype:STRINGdescription:|RecommenderIDoftherecommenderthathasproducedthisrecommendation-name:recommender_subtypetype:STRINGdescription:|Containsanidentifierforasubtypeofrecommendationsproducedforthesamerecommender.Subtypeisafunctionofcontentandimpact,meaninganewsubtypewillbeaddedwheneithercontentorprimaryimpactcategorychanges.Examples:Forrecommender="google.iam.policy.Recommender",recommender_subtypecanbeoneof"REMOVE_ROLE"/"REPLACE_ROLE"-name:target_resourcestype:STRINGmode:REPEATEDdescription:|Containsthefullyqualifiedresourcenamesforresourceschangedbytheoperationsinthisrecommendation.Thisfieldisalwayspopulated.ex:[//cloudresourcemanager.googleapis.com/projects/foo].-name:descriptiontype:STRINGdescription:|Required.Free-formhumanreadablesummaryinEnglish.Themaximumlengthis500characters.-name:last_refresh_timetype:TIMESTAMPdescription:|Outputonly.Lasttimethisrecommendationwasrefreshedbythesystemthatcreateditinthefirstplace.-name:primary_impacttype:RECORDdescription:|Required.Theprimaryimpactthatthisrecommendationcanhavewhiletryingtooptimizeforonecategory.schema:fields:-name:categorytype:STRINGdescription:|Categorythatisbeingtargeted.Valuescanbethefollowing:CATEGORY_UNSPECIFIED:Defaultunspecifiedcategory.Donotusedirectly.COST:Indicatesapotentialincreaseordecreaseincost.SECURITY:Indicatesapotentialincreaseordecreaseinsecurity.PERFORMANCE:Indicatesapotentialincreaseordecreaseinperformance.RELIABILITY:Indicatesapotentialincreaseordecreaseinreliability.-name:cost_projectiontype:RECORDdescription:Optional.UsewithCategoryType.COSTschema:fields:-name:costtype:RECORDdescription:|Anapproximateprojectiononamountsavedoramountincurred.Negativecostunitsindicatecostsavingsandpositivecostunitsindicateincrease.Seegoogle.type.Moneydocumentationforpositive/negativeunits.schema:fields:-name:currency_codetype:STRINGdescription:The3-lettercurrencycodedefinedinISO4217.-name:unitstype:INTEGERdescription:|Thewholeunitsoftheamount.Forexampleif`currencyCode`is`"USD"`,then1unitisoneUSdollar.-name:nanostype:INTEGERdescription:|Numberofnano(10^-9)unitsoftheamount.Thevaluemustbebetween-999,999,999and+999,999,999inclusive.If`units`ispositive,`nanos`mustbepositiveorzero.If`units`iszero,`nanos`canbepositive,zero,ornegative.If`units`isnegative,`nanos`mustbenegativeorzero.Forexample$-1.75isrepresentedas`units`=-1and`nanos`=-750,000,000.-name:cost_in_local_currencytype:RECORDdescription:|Anapproximateprojectiononamountsavedoramountincurredinthelocalcurrency.Negativecostunitsindicatecostsavingsandpositivecostunitsindicateincrease.Seegoogle.type.Moneydocumentationforpositive/negativeunits.schema:fields:-name:currency_codetype:STRINGdescription:The3-lettercurrencycodedefinedinISO4217.-name:unitstype:INTEGERdescription:|Thewholeunitsoftheamount.Forexampleif`currencyCode`is`"USD"`,then1unitisoneUSdollar.-name:nanostype:INTEGERdescription:|Numberofnano(10^-9)unitsoftheamount.Thevaluemustbebetween-999,999,999and+999,999,999inclusive.If`units`ispositive,`nanos`mustbepositiveorzero.If`units`iszero,`nanos`canbepositive,zero,ornegative.If`units`isnegative,`nanos`mustbenegativeorzero.Forexample$-1.75isrepresentedas`units`=-1and`nanos`=-750,000,000.-name:durationtype:RECORDdescription:Durationforwhichthiscostapplies.schema:fields:-name:secondstype:INTEGERdescription:|Signedsecondsofthespanoftime.Mustbefrom-315,576,000,000to+315,576,000,000inclusive.Note:theseboundsarecomputedfrom:60sec/min*60min/hr*24hr/day*365.25days/year*10000years-name:nanostype:INTEGERdescription:|Signedfractionsofasecondatnanosecondresolutionofthespanoftime.Durationslessthanonesecondarerepresentedwitha0`seconds`fieldandapositiveornegative`nanos`field.Fordurationsofonesecondormore,anon-zerovalueforthe`nanos`fieldmustbeofthesamesignasthe`seconds`field.Mustbefrom-999,999,999to+999,999,999inclusive.-name:pricing_type_nametype:STRINGdescription:|ApricingtypecaneitherbebasedonthepricelistedonGCP(LIST)oracustompricebasedonpastusage(CUSTOM).-name:reliability_projectiontype:RECORDdescription:Optional.UsewithCategoryType.RELIABILITYschema:fields:-name:risk_typestype:STRINGmode:REPEATEDdescription:|Theriskassociatedwiththereliabilityissue.RISK_TYPE_UNSPECIFIED:Defaultunspecifiedrisk.Donotusedirectly.SERVICE_DISRUPTION:Potentialservicedowntime.DATA_LOSS:Potentialdataloss.ACCESS_DENY:Potentialaccessdenial.Theserviceisstillupbutsomeorallclientscannotaccessit.-name:details_jsontype:STRINGdescription:|AdditionalreliabilityimpactdetailsthatisprovidedbytherecommenderinJSONformat.-name:statetype:STRINGdescription:|Outputonly.Thestateoftherecommendation:STATE_UNSPECIFIED:Defaultstate.Donotusedirectly.ACTIVE:Recommendationisactiveandcanbeapplied.RecommendationscontentcanbeupdatedbyGoogle.ACTIVErecommendationscanbemarkedasCLAIMED,SUCCEEDED,orFAILED.CLAIMED:Recommendationisinclaimedstate.RecommendationscontentisimmutableandcannotbeupdatedbyGoogle.CLAIMEDrecommendationscanbemarkedasCLAIMED,SUCCEEDED,orFAILED.SUCCEEDED:Recommendationisinsucceededstate.RecommendationscontentisimmutableandcannotbeupdatedbyGoogle.SUCCEEDEDrecommendationscanbemarkedasSUCCEEDED,orFAILED.FAILED:Recommendationisinfailedstate.RecommendationscontentisimmutableandcannotbeupdatedbyGoogle.FAILEDrecommendationscanbemarkedasSUCCEEDED,orFAILED.DISMISSED:Recommendationisindismissedstate.DISMISSEDrecommendationscanbemarkedasACTIVE.-name:ancestorstype:RECORDdescription:|Ancestryfortherecommendationentityschema:fields:-name:organization_idtype:STRINGdescription:|Organizationtowhichtherecommendationproject-name:folder_idstype:STRINGmode:REPEATEDdescription:|Upto5levelsofparentfoldersfortherecommendationproject-name:associated_insightstype:STRINGmode:REPEATEDdescription:|Insightsassociatedwiththisrecommendation.Aprojectinsightisrepresentedasprojects/[PROJECT_NUMBER]/locations/[LOCATION]/insightTypes/[INSIGHT_TYPE_ID]/insights/[insight_id]-name:recommendation_detailstype:STRINGdescription:|AdditionaldetailsabouttherecommendationinJSONformat.schema:-name:overviewtype:RECORDdescription:OverviewoftherecommendationinJSONformat-name:operation_groupstype:OperationGroupmode:REPEATEDdescription:OperationstooneormoreGoogleCloudresourcesgroupedinsuchawaythat,alloperationswithinonegroupareexpectedtobeperformedatomicallyandinanorder.Morehere:https://cloud.google.com/recommender/docs/key-concepts#operation_groups-name:operationstype:Operationdescription:AnOperationistheindividualactionthatmustbeperformedasoneoftheatomicstepsinasuggestedrecommendation.Morehere:https://cloud.google.com/recommender/docs/key-concepts?#operation-name:state_metadatatype:mapwithkey:STRING,value:STRINGdescription:AmapofSTRINGkey,STRINGvalueofmetadataforthestate,providedbyuserorautomationssystems.-name:additional_impacttype:Impactmode:REPEATEDdescription:Optionalsetofadditionalimpactthatthisrecommendationmayhavewhentryingtooptimizefortheprimarycategory.Thesemaybepositiveornegative.Morehere:https://cloud.google.com/recommender/docs/key-concepts?#recommender_impact-name:prioritytype:STRINGdescription:|Priorityoftherecommendation:PRIORITY_UNSPECIFIED:Defaultunspecifiedpriority.Donotusedirectly.P4:Lowestpriority.P3:Secondlowestpriority.P2:Secondhighestpriority.P1:Highestpriority.

Insights Export table:

schema:-fields:-name:cloud_entity_typetype:STRINGdescription:|Representswhatcloudentitytypetherecommendationwasgeneratedfor-eg:projectnumber,billingaccount-name:cloud_entity_idtype:STRINGdescription:|Valueoftheprojectnumberorbillingaccountid-name:nametype:STRINGdescription:|Nameofrecommendation.Aprojectrecommendationisrepresentedasprojects/[PROJECT_NUMBER]/locations/[LOCATION]/recommenders/[RECOMMENDER_ID]/recommendations/[RECOMMENDATION_ID]-name:locationtype:STRINGdescription:|Locationforwhichthisrecommendationisgenerated-name:insight_typetype:STRINGdescription:|RecommenderIDoftherecommenderthathasproducedthisrecommendation-name:insight_subtypetype:STRINGdescription:|Containsanidentifierforasubtypeofrecommendationsproducedforthesamerecommender.Subtypeisafunctionofcontentandimpact,meaninganewsubtypewillbeaddedwheneithercontentorprimaryimpactcategorychanges.Examples:Forrecommender="google.iam.policy.Recommender",recommender_subtypecanbeoneof"REMOVE_ROLE"/"REPLACE_ROLE"-name:target_resourcestype:STRINGmode:REPEATEDdescription:|Containsthefullyqualifiedresourcenamesforresourceschangedbytheoperationsinthisrecommendation.Thisfieldisalwayspopulated.ex:[//cloudresourcemanager.googleapis.com/projects/foo].-name:descriptiontype:STRINGdescription:|Required.Free-formhumanreadablesummaryinEnglish.Themaximumlengthis500characters.-name:last_refresh_timetype:TIMESTAMPdescription:|Outputonly.Lasttimethisrecommendationwasrefreshedbythesystemthatcreateditinthefirstplace.-name:categorytype:STRINGdescription:|Categorybeingtargetedbytheinsight.Canbeoneof:Unspecifiedcategory.CATEGORY_UNSPECIFIED=Unspecifiedcategory.COST=Theinsightisrelatedtocost.SECURITY=Theinsightisrelatedtosecurity.PERFORMANCE=Theinsightisrelatedtoperformance.MANAGEABILITY=Theinsightisrelatedtomanageability.RELIABILITY=Theinsightisrelatedtoreliability.;-name:statetype:STRINGdescription:|Outputonly.Thestateoftherecommendation:STATE_UNSPECIFIED:Defaultstate.Donotusedirectly.ACTIVE:Recommendationisactiveandcanbeapplied.RecommendationscontentcanbeupdatedbyGoogle.ACTIVErecommendationscanbemarkedasCLAIMED,SUCCEEDED,orFAILED.CLAIMED:Recommendationisinclaimedstate.RecommendationscontentisimmutableandcannotbeupdatedbyGoogle.CLAIMEDrecommendationscanbemarkedasCLAIMED,SUCCEEDED,orFAILED.SUCCEEDED:Recommendationisinsucceededstate.RecommendationscontentisimmutableandcannotbeupdatedbyGoogle.SUCCEEDEDrecommendationscanbemarkedasSUCCEEDED,orFAILED.FAILED:Recommendationisinfailedstate.RecommendationscontentisimmutableandcannotbeupdatedbyGoogle.FAILEDrecommendationscanbemarkedasSUCCEEDED,orFAILED.DISMISSED:Recommendationisindismissedstate.DISMISSEDrecommendationscanbemarkedasACTIVE.-name:ancestorstype:RECORDdescription:|Ancestryfortherecommendationentityschema:fields:-name:organization_idtype:STRINGdescription:|Organizationtowhichtherecommendationproject-name:folder_idstype:STRINGmode:REPEATEDdescription:|Upto5levelsofparentfoldersfortherecommendationproject-name:associated_recommendationstype:STRINGmode:REPEATEDdescription:|Insightsassociatedwiththisrecommendation.Aprojectinsightisrepresentedasprojects/[PROJECT_NUMBER]/locations/[LOCATION]/insightTypes/[INSIGHT_TYPE_ID]/insights/[insight_id]-name:insight_detailstype:STRINGdescription:|AdditionaldetailsabouttheinsightinJSONformatschema:fields:-name:contenttype:STRINGdescription:|Astructofcustomfieldstoexplaintheinsight.Example:"grantedPermissionsCount":"1000"-name:observation_periodtype:TIMESTAMPdescription:|Observationperiodthatledtotheinsight.Thesourcedatausedtogeneratetheinsightendsatlast_refresh_timeandbeginsat(last_refresh_time-observation_period).-name:state_metadatatype:STRINGdescription:|Amapofmetadataforthestate,providedbyuserorautomationssystems.-name:severitytype:STRINGdescription:|Severityoftheinsight:SEVERITY_UNSPECIFIED:Defaultunspecifiedseverity.Donotusedirectly.LOW:Lowestseverity.MEDIUM:Secondlowestseverity.HIGH:Secondhighestseverity.CRITICAL:Highestseverity.

Example queries

You can use the following sample queries to analyze your exported data.

Viewing cost savings for recommendations where the recommendation duration isdisplayed in days

SELECTname,recommender,target_resources,caseprimary_impact.cost_projection.cost.unitsisnullwhentruethenround(primary_impact.cost_projection.cost.nanos*power(10,-9),2)elseround(primary_impact.cost_projection.cost.units+(primary_impact.cost_projection.cost.nanos*power(10,-9)),2)endasdollar_amt,primary_impact.cost_projection.duration.seconds/(60*60*24)asduration_in_daysFROM`<project>.<dataset>.recommendations_export`WHEREDATE(_PARTITIONTIME)="<date>"andprimary_impact.category="COST"

Viewing the list of unused IAM roles

SELECT*FROM`<project>.<dataset>.recommendations_export`WHEREDATE(_PARTITIONTIME)="<date>"andrecommender="google.iam.policy.Recommender"andrecommender_subtype="REMOVE_ROLE"

Viewing a list of granted roles that must be replaced by smaller roles

SELECT*FROM`<project>.<dataset>.recommendations_export`WHEREDATE(_PARTITIONTIME)="<date>"andrecommender="google.iam.policy.Recommender"andrecommender_subtype="REPLACE_ROLE"

Viewing insights for a recommendation

SELECTrecommendations.nameasrecommendation_name,insights.nameasinsight_name,recommendations.cloud_entity_id,recommendations.cloud_entity_type,recommendations.recommender,recommendations.recommender_subtype,recommendations.description,recommendations.target_resources,recommendations.recommendation_details,recommendations.state,recommendations.last_refresh_timeasrecommendation_last_refresh_time,insights.insight_type,insights.insight_subtype,insights.category,insights.description,insights.insight_details,insights.state,insights.last_refresh_timeasinsight_last_refresh_timeFROM`<project>.<dataset>.recommendations_export`asrecommendations,`<project>.<dataset>.insights_export`asinsightsWHEREDATE(recommendations._PARTITIONTIME)="<date>"andDATE(insights._PARTITIONTIME)="<date>"andinsights.nameinunnest(recommendations.associated_insights)

Viewing recommendations for projects belonging to a specific folder

This query returns parent folders up to five levels from the project.

SELECT*FROM`<project>.<dataset>.recommendations_export`WHEREDATE(_PARTITIONTIME)="<date>"and"<folder_id>"inunnest(ancestors.folder_ids)

Viewing recommendations for the latest available date exported so far

DECLAREmax_dateTIMESTAMP;SETmax_date=(SELECTMAX(_PARTITIONTIME)FROM`<project>.<dataset>.recommendations_export`);SELECT*FROM`<project>.<dataset>.recommendations_export`WHERE_PARTITIONTIME=max_date

Use Sheets to explore BigQuery data

As an alternative to executing queries on BigQuery, you can access,analyze, visualize, and share billions of rows of BigQuery data fromyour spreadsheet with Connected Sheets, the new BigQuery data connector.For more information, refer toGet started with BigQuery data in Google Sheets.

Set up the Export Using BigQuery Command Line & REST API

  • Get required permissions:

    You can get the required Identity and Access Management permissions via theGoogle Cloud console or command line.

    For example, to use Command Line to get organization level recommender.resources.export permission for the service account:

    gcloud organizations add-iam-policy-binding *<organization_id>* --member=serviceAccount:*<service_acct_name>*' --role='roles/recommender.exporter'

  • Create dataset & enable BigQuery API

  • Enroll project in BigQuery data source

    Datasource to use: 6063d10f-0000-2c12-a706-f403045e6250

  • Create the export:

    bqmk\--transfer_config\--project_id=project_id\--target_dataset=dataset_id\--display_name=name\--params='parameters'\--data_source=data_source\--service_account_name=service_account_name

    Where:

    • project_id is your project ID.
    • dataset is the target dataset id for the transfer configuration.
    • name is the display name for the transfer configuration. Thetransfer name can be any value that allows you to easily identify thetransfer if you need to modify it later.
    • parameters contains the parameters for the created transferconfiguration in JSON format. For Recommendations and Insights BigQuery Export,you must supply the organization_id for which recommendations and insightsneed to be exported.Parameters format: '{"organization_id":"<org id>"}'
    • data_source Datasource to use: '6063d10f-0000-2c12-a706-f403045e6250'
    • service_account_name is the service account name used forauthenticating your export. The service account shouldbe owned by the sameproject_id used for creating the transfer and itshould have all therequired permissions listedabove.

  • Manage an existing export via UI or BigQuery Command Line:

  • Note - the export runs as the user that set up the account, irrespective of who updates the export configuration in future. For example, if the export is set up using a service account, and later a human user updates the export configuration via the BigQuery Data Transfer Service UI, the export will continue to run as the service account. The permission check for 'recommender.resources.export' in this case is done for the service account every time the export runs.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-18 UTC.