How to Export to BigQuery
Once you have a report ready in Data Explorer, you are able to export it to BigQuery. This export option is included in the Enterprise 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:
- BigQuery Admin
- BigQuery Data Editor
- 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.
The user creating an export must have Write permissions to the dataset.
Set Up Your Initial Backfill and Rolling Updates
Initial backfill
You can backfill data for up to 2 years for most PMA connectors. In your first report in Data Explorer, set the date range to the entire range of data you want to export and click Set Range.
Click Save to save the report.
Under Exports, go to BigQuery and find your export. Click Run Export Now in the row of your export.
Rolling updates
In BigQuery, click Edit Export. Refresh period can be set to monthly, weekly, daily, or hourly.
Make sure your report's date range in Data Explorer is at least as long as the refresh period in BigQuery. Failing to do so may result in data missing from your backfill.
For example:
- 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.
- If your report's date range is one week and your export refreshes daily, your export will overwrite the past seven days, every day.
A range of 30 days with a daily or weekly refresh period can be an ideal choice for e-commerce to settle orders that are returned. However, excessively frequent backfills with large date ranges, such as a year's data refreshed every hour, can cause BigQuery to hang. Frequent large requests can also increase your BigQuery charges.
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 using connectors other than Facebook Ads or GA4, make sure the report you are trying to export contains fewer than 40,000 rows of data. 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. This limitation does not apply to Facebook Ads or GA4.
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 Explorer to use the first date range, then go to BigQuery and run your export.
If your export completes successfully:
- Return to Data Explorer.
- Edit your report and set Date Range to the second date range.
- 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.
- Reduce the size of the date range until the export runs successfully, indicating this range is within BigQuery's export limits.
- Run the export for each date range according to the instructions above.
Still having issues?
Send us a ticket and we will get back to you.
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. Export Limitations If you are using connectors other than ...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 MySQL
Our MySQL export feature enables flexible access to your data in the form of tables in a MySQL database hosted on the server of your choice. Once you've prepared a report in Data Explorer, you're ready to export your data to your MySQL server. With ...BigQuery Error: Code 403; Message: Access Denied
This article will assist you with the following errors: Code: 403; Message: Access Denied: BigQuery BigQuery: Streaming insert is not allowed in the free tier (accessDenied) Code: 403; Message: Access Denied: Dataset: Permission denied on dataset (or ...How to Create an FTP Export
FTP can be a valuable way for Enterprise users to export their Data Explorer reports. In this article, we will walk you through creating an FTP export, including selecting an FTP destination, scheduling data refreshes, and managing your exports to ...