Load Google Play data into BigQuery

You can load data from Google Play to BigQuery using theBigQuery Data Transfer Service for Google Play connector. With theBigQuery Data Transfer Service, you can schedule recurring transfer jobs thatadd your latest data from Google Play toBigQuery.

Connector overview

The BigQuery Data Transfer Service for the Google Play connector supports the following options for your data transfer.

Data ingestion from Google Play transfers

Refresh windows

Arefresh window is the number of days that a data transfer retrieves datawhen a data transfer occurs. For example, if the refresh window is three daysand a daily transfer occurs, the BigQuery Data Transfer Service retrieves all data fromyour source table from the past three days. In thisexample, when a daily transfer occurs, the BigQuery Data Transfer Service creates a newBigQuery destination table partition with a copy of your source table datafrom the current day, then automatically triggers backfill runs to update theBigQuery destination table partitions with your source table data from thepast two days. The automatically triggered backfill runs will either overwriteor incrementally update your BigQuery destination table,depending on whether or not incremental updates are supported in theBigQuery Data Transfer Service connector.

When you run a data transfer for the first time, the data transfer retrieves allsource data available within the refresh window. For example, if the refreshwindow is three days and you run the data transfer for the first time, theBigQuery Data Transfer Service retrieves all source data within three days.

To retrieve data outside the refresh window, such as historical data, or torecover data from any transfer outages or gaps, you can initiate or schedule abackfill run.

Limitations

• The minimum frequency that you can schedule a data transfer for is once every24 hours. By default, a transfer starts at the time that you create thetransfer. However, you can configure the transfer start time when youset up your transfer.
• The BigQuery Data Transfer Service does not support incremental data transfers during aGoogle Play transfer. When you specify a date for a data transfer, allof the data that is available for that date is transferred.

Before you begin

Before you create a Google Play data transfer:

• Verify that you have completed all actions required toenable the BigQuery Data Transfer Service.
• Create a BigQuery datasetto store the Google Play data.
• Find your Cloud Storage bucket:
  1. In theGoogle Play console,clickfile_downloadDownload reports and selectReviews,Statistics, orFinancial.
  2. To copy the ID for your Cloud Storage bucket, clickcontent_copyCopy Cloud Storage URI.Your bucket ID begins withgs://. For example, for thereviews report, your ID is similar to the following:

     gs://pubsite_prod_rev_01234567890987654321/reviews

  3. For the Google Play data transfer, you need to copy only the unique ID that comes betweengs:// and/reviews:

     pubsite_prod_rev_01234567890987654321

• If you intend to setup transfer run notifications for Pub/Sub, youmust havepubsub.topics.setIamPolicy permissions. Pub/Subpermissions are not required if you just set up email notifications. For moreinformation, seeBigQuery Data Transfer Service run notifications.

Required permissions

Ensure that you have granted the following permissions.

Required BigQuery roles

To get the permissions that you need to create a BigQuery Data Transfer Service data transfer, ask your administrator to grant you theBigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to create a BigQuery Data Transfer Service data transfer. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

For more information, seeGrantbigquery.admin access.

Required Google Play roles

Ensure that you have the following permissions in Google Play:

• You must have reporting access in theGoogle Play console.

  The Google Cloud team doesNOT have theability to generate or grant access to Google Play files on your behalf. SeeContact Google Play supportfor help accessing Google Play files.

Set up a Google Play transfer

Setting up a Google Play data transfer requires a:

• Cloud Storage bucket. Steps for locating your Cloud Storage bucket aredescribed inBefore you begin.Your Cloud Storage bucket begins withpubsite_prod_rev. Forexample:pubsite_prod_rev_01234567890987654321.
• Table suffix: A user-friendly name for all data sources loading into thesame dataset. The suffix is used to prevent separate transfers from writingto the same tables. The table suffix must be unique across all transfers thatload data into the same dataset, and the suffix should be short to minimizethe length of the resulting table name.

To set up a Google Play data transfer:

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

bq mk \--transfer_config \--target_dataset=mydataset \--display_name='My Transfer' \--params='{"bucket":"pubsite_prod_rev_01234567890987654321","table_suffix":"MT"}' \--data_source=play

importcom.google.api.gax.rpc.ApiException;importcom.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;importcom.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;importcom.google.cloud.bigquery.datatransfer.v1.ProjectName;importcom.google.cloud.bigquery.datatransfer.v1.TransferConfig;importcom.google.protobuf.Struct;importcom.google.protobuf.Value;importjava.io.IOException;importjava.util.HashMap;importjava.util.Map;// Sample to create a play transfer config.publicclassCreatePlayTransfer{publicstaticvoidmain(String[]args)throwsIOException{// TODO(developer): Replace these variables before running the sample.finalStringprojectId="MY_PROJECT_ID";StringdatasetId="MY_DATASET_ID";Stringbucket="gs://cloud-sample-data";StringtableSuffix="_test";Map<String,Value>params=newHashMap<>();params.put("bucket",Value.newBuilder().setStringValue(bucket).build());params.put("table_suffix",Value.newBuilder().setStringValue(tableSuffix).build());TransferConfigtransferConfig=TransferConfig.newBuilder().setDestinationDatasetId(datasetId).setDisplayName("Your Play Config Name").setDataSourceId("play").setParams(Struct.newBuilder().putAllFields(params).build()).build();createPlayTransfer(projectId,transferConfig);}publicstaticvoidcreatePlayTransfer(StringprojectId,TransferConfigtransferConfig)throwsIOException{try(DataTransferServiceClientclient=DataTransferServiceClient.create()){ProjectNameparent=ProjectName.of(projectId);CreateTransferConfigRequestrequest=CreateTransferConfigRequest.newBuilder().setParent(parent.toString()).setTransferConfig(transferConfig).build();TransferConfigconfig=client.createTransferConfig(request);System.out.println("play transfer created successfully :"+config.getName());}catch(ApiExceptionex){System.out.print("play transfer was not created."+ex.toString());}}}

If you are having issues setting up your data transfer, seeTroubleshooting BigQuery Data Transfer Service transfer setup.

Query your data

When your data is transferred to BigQuery, the data iswritten to ingestion-time partitioned tables. For more information, seeIntroduction to partitioned tables.

If you query your tables directly instead of using the auto-generated views, youmust use the_PARTITIONTIME pseudocolumn in your query. For more information,seeQuerying partitioned tables.

Pricing

For information on Google Play data transfer pricing, see thePricing page.

Once data is transferred to BigQuery, standardBigQuerystorage andquery pricing applies.

What's next

• To see how your Google Play reports are transferred to BigQuery,seeGoogle Play report transformations.
• For an overview of BigQuery Data Transfer Service, seeIntroduction to BigQuery Data Transfer Service.
• For information on using transfers including getting information about atransfer configuration, listing transfer configurations, and viewing atransfer's run history, seeWorking with transfers.