How to Export to BigQuery

How to Export to BigQuery

Info
Once you have a report ready in Data Builder, you are able to export it to BigQuery. This export option is included in the Custom plan. Follow the instructions in this article to get started.

Grant Required Roles and Permissions

A successful connection to BigQuery requires the user to have one of the following roles:
  1. BigQuery Admin
  2. BigQuery Data Editor
  3. BigQuery Owner
The BigQuery Job User role must be added to Data Editor and Owner roles.

Add the Job User Role

In the Google account used for exports, go to IAM and Admin from the main menu in Google Cloud Console. Select Permissions and view by Principals.



Find the account used for exports and click the pencil icon to Edit principal.



Under Role, select BigQuery, then BigQuery Job User.



Create a Dataset

A dataset must be available before proceeding to create an export. Follow the instructions in Google's guide to create a dataset. You will need to select this dataset when setting up your export to BigQuery.
Alert
The user creating an export must have Write permissions to the dataset.

Step 1: Configure Your Data Builder Report for Initial Backfill

Alert
Note: Data Builder reports exported to BigQuery must include at least one date field. Without a date field, the export will fail to complete successfully.
  1. In your PMA hub, go to Reports > Data Builder and find your report. Click the three-dots action menu and select Edit. 



  2. Go to Available Data Range and set it to the entire range of data you want to export (up to 2 years for most PMA connectors). To backfill from a specific start date up to yesterday, choose Custom start to date and enter a start date.



  3. Click Save Changes to save the report.


Step 2: Connect to BigQuery

  1. Go to Exports > BigQuery in the left sidebar.
  2. Click + Create Export.



  3. Under Destination, select the connected Google account associated with your BigQuery dataset.
    1. If you haven't yet connected your Google account, make sure you are logged into the account associated with your BigQuery dataset, then click the blue plus button to proceed with granting BigQuery permissions to Power My Analytics.



  4. For Dataset, select the BigQuery dataset you'd like to use.



  5. Enter the name of the BigQuery dataset table name to use, then click Next.


Step 3: Choose Your Report

  1. Under Report or Report Group, select either Single report or Group of reports.



  2. Choose the specific report or report group from the dropdown. Click Next.



  3. For now, select Monthly under Schedule and choose any day and hour (we'll adjust this later). Click Add to create your export, then click Confirm.


Step 4: Run the Initial Backfill

  1. On the Exports to BigQuery page, find your new export. Click the play icon to run the export manually.



  2. The export will begin running, as indicated by the three dots icon. Wait for the three dots icon to change back to a play button, indicating the export has finished.
  3. Click the paper icon to view the logs and confirm the export was successful.


Step 5: Set Up Rolling Updates

Configure your Data Builder report

  1. Go back to your report in Data Builder. Click the three-dots action menu and select Edit.
  2. Set the Available Data Range to a rolling window (e.g., last 30 days for e-commerce to account for returns).


  3. Then click Save Changes.


Configure your BigQuery export

  1. Return to Exports > BigQuery. Find your export and click the pencil icon to Edit Export.



  2. Under Schedule, choose your desired frequency: monthly, weekly, daily, or hourly. Select the appropriate day/time for the export to run.
  3. Click Update.


Alert
Make sure your report's date range in Data Builder is at least as long as the refresh period in your export. Failing to do so may result in data missing from your backfill. For example:
  1. If your report's date range is one week but your export refreshes monthly, your export will fail to include data from before the last week of the month.
  2. If your report's date range is one week and your export refreshes daily, your export will overwrite the past seven days, every day.
Your export will now run automatically at the scheduled time, keeping your BigQuery dataset up-to-date with the latest data from your PMA reports.

Date Partitioning

How PMA exports to BigQuery

When your export runs, we will first delete any of your export data in BigQuery that falls within the date range of your export. We will then create new tables based on the date range of your export and populate these tables with the export data.

Exports ranging less than 60 days

Our exports to BigQuery are partitioned by date. Exports that span less than 60 days will be partitioned by day. In Google Cloud console, the daily partitions are visible under the Filter dropdown next to the table name.




Exports ranging 60 days or more

Exports spanning 60 days or more will be partitioned by month. In Google Cloud console, monthly partitions are visible under the Filter dropdown next to the table name.





Export Limitations

If you are backfilling a large amount of your historical data into BigQuery, you may need to take additional steps to make sure your exports are under BigQuery's limits for streaming inserts.

Your export should run in under 10 minutes. If the export runs for 30 minutes or more, it has hung and needs to be cancelled. To stop the hung export, click Edit Export.



Do not make any changes to the export. Click Update.



The hung export is now stopped.

How to accommodate large datasets

If your data has caused the export to hang, try segmenting the overall date range into two smaller date ranges. Edit the report in Data Builder to use the first date range, then go to BigQuery and run your export.

If your export completes successfully:

  1. Return to Data Builder.
  2. Edit your report and set Available Data Range to the second date range.
  3. Go to BigQuery and run your export again.

If your export is unsuccessful:

You may need to segment the overall date range into four or more date ranges.

  1. Reduce the size of the date range until the export runs successfully, indicating this range is within BigQuery's export limits.
  2. Run the export for each date range according to the instructions above.

    • Related Articles

    • BigQuery Export Limitations

      Your data exports to BigQuery may encounter errors in certain situations such as large export sizes. This article will show you how to identify and correct errors with your BigQuery exports. Date Field Requirement Data Builder reports exported to ...

    • Troubleshooting BigQuery

      When troubleshooting BigQuery, you may need to view your BigQuery export logs. This article will show you how to check the logs and see details about any errors. Viewing Export Logs Your BigQuery export logs show a timestamped record of your exports ...

    • How to Export to PostgreSQL

      Our PostgreSQL export feature enables flexible access to your data in the form of tables in a PostgreSQL database hosted on the server of your choice. Once you've prepared a report in Data Builder, you're ready to export your data to your PostgreSQL ...

    • BigQuery Error: Billing Has Not Been Enabled for This Project

      This article explains how to resolve a BigQuery billing error that can occur when attempting to run your data exports. Error When attempting to run a BigQuery export of your Power My Analytics report, you may see the following error in the View Logs ...

    • Error: Missing BigQuery Scopes

      When setting up BigQuery exports in Power My Analytics, you may encounter an error message stating "Missing BigQuery Scopes." This guide will help you understand the cause of this error and provide step-by-step instructions to resolve it. Error ...