Load Google Play data into BigQuery

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

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

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

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

To retrieve data outside the refresh window, such as historical data, or to recover data from any transfer outages or gaps, you can initiate or schedule a backfill run.

Limitations

• The minimum frequency that you can schedule a data transfer for is once every 24 hours. By default, a transfer starts at the time that you create the transfer. However, you can configure the transfer start time when you set up your transfer.
• The BigQuery Data Transfer Service does not support incremental data transfers during a Google Play transfer. When you specify a date for a data transfer, all of 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 to enable the BigQuery Data Transfer Service.
• Create a BigQuery dataset to store the Google Play data.
• Find your Cloud Storage bucket:
  1. In the Google Play console, click file_download Download reports and select Reviews, Statistics, or Financial.
  2. To copy the ID for your Cloud Storage bucket, click content_copy Copy Cloud Storage URI. Your bucket ID begins with gs://. For example, for the reviews 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 between gs:// and /reviews:

     pubsite_prod_rev_01234567890987654321

  4. If you intend to set up transfer run notifications for Pub/Sub, you must have pubsub.topics.setIamPolicy permissions. Pub/Sub permissions are not required if you just set up email notifications. For more information, see BigQuery 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 the BigQuery Admin (roles/bigquery.admin) IAM role on your project. For more information about granting roles, see Manage 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 the Required permissions section:

Required permissions

The following permissions are required to create a BigQuery Data Transfer Service data transfer:

• BigQuery Data Transfer Service permissions:
  • bigquery.transfers.update
  • bigquery.transfers.get
• BigQuery permissions:
  • bigquery.datasets.get
  • bigquery.datasets.getIamPolicy
  • bigquery.datasets.update
  • bigquery.datasets.setIamPolicy
  • bigquery.jobs.create

You might also be able to get these permissions with custom roles or other predefined roles.

For more information, see Grant bigquery.admin access.

Required Google Play roles

Ensure that you have the following permissions in Google Play:

• You must have reporting access in the Google Play console.

  The Google Cloud team does NOT have the ability to generate or grant access to Google Play files on your behalf. See Contact Google Play support for 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 are described in Before you begin. Your Cloud Storage bucket begins with pubsite_prod_rev. For example: pubsite_prod_rev_01234567890987654321.
• Table suffix: A user-friendly name for all data sources loading into the same dataset. The suffix is used to prevent separate transfers from writing to the same tables. The table suffix must be unique across all transfers that load data into the same dataset, and the suffix should be short to minimize the length of the resulting table name.

To set up a Google Play data transfer:

bq mk \
--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

import com.google.api.gax.rpc.ApiException;
import com.google.cloud.bigquery.datatransfer.v1.CreateTransferConfigRequest;
import com.google.cloud.bigquery.datatransfer.v1.DataTransferServiceClient;
import com.google.cloud.bigquery.datatransfer.v1.ProjectName;
import com.google.cloud.bigquery.datatransfer.v1.TransferConfig;
import com.google.protobuf.Struct;
import com.google.protobuf.Value;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;

// Sample to create a play transfer config.
public class CreatePlayTransfer {

  public static void main(String[] args) throws IOException {
    // TODO(developer): Replace these variables before running the sample.
    final String projectId = "MY_PROJECT_ID";
    String datasetId = "MY_DATASET_ID";
    String bucket = "gs://cloud-sample-data";
    String tableSuffix = "_test";
    Map<String, Value> params = new HashMap<>();
    params.put("bucket", Value.newBuilder().setStringValue(bucket).build());
    params.put("table_suffix", Value.newBuilder().setStringValue(tableSuffix).build());
    TransferConfig transferConfig =
        TransferConfig.newBuilder()
            .setDestinationDatasetId(datasetId)
            .setDisplayName("Your Play Config Name")
            .setDataSourceId("play")
            .setParams(Struct.newBuilder().putAllFields(params).build())
            .build();
    createPlayTransfer(projectId, transferConfig);
  }

  public static void createPlayTransfer(String projectId, TransferConfig transferConfig)
      throws IOException {
    try (DataTransferServiceClient client = DataTransferServiceClient.create()) {
      ProjectName parent = ProjectName.of(projectId);
      CreateTransferConfigRequest request =
          CreateTransferConfigRequest.newBuilder()
              .setParent(parent.toString())
              .setTransferConfig(transferConfig)
              .build();
      TransferConfig config = client.createTransferConfig(request);
      System.out.println("play transfer created successfully :" + config.getName());
    } catch (ApiException ex) {
      System.out.print("play transfer was not created." + ex.toString());
    }
  }
}

Troubleshoot Google Play transfer set up

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

Query your data

When your data is transferred to BigQuery, the data is written to ingestion-time partitioned tables. For more information, see Introduction to partitioned tables.

If you query your tables directly instead of using the auto-generated views, you must use the _PARTITIONTIME pseudocolumn in your query. For more information, see Querying partitioned tables.

Pricing

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

Once data is transferred to BigQuery, standard BigQuery storage and query pricing applies.

What's next

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