How to upload data to Google BigQuery
In this article, we consider options for uploading data to Google BigQuery cloud storage. We consider easy ways of loading data from CSV/JSON files and ways of uploading through an API or add-on.
Google BigQuery (GBQ) allows you to collect data from different sources and analyze it using SQL queries. Among the advantages of GBQ are its high speed of calculations – even with large volumes of data – and its low cost.
Why do you need to load data into one storage? If you want to use end-to-end analytics, use raw data for creating reports, and measure the efficiency of your marketing, then you should use Google BigQuery.
- Creating a dataset and table
- Data download with Google Sheets (OWOX BI Add-on)
- Downloading data with CSV Files
- Downloading data with JSON Files
- Downloading data from Google Cloud Storage
- Download data from other Google services such as Google Ad Manager and Google Ads
- Downloading data with the API
- Key Takeaways
- Useful links
If you need to analyze terabytes of data in seconds, Google BigQuery is the easiest and most affordable choice. You can learn more about this service by watching a short video on the Google Developers YouTube channel.
Creating a dataset and table
Before you upload any data, you need to create a dataset and table in Google BigQuery. To do this, on the BigQuery home page, select the resource in which you want to create a dataset.
In the Create dataset window, give your dataset an ID, select a data location, and set the default table expiration period.
Note: If you select “Never” for table expiration, the physical storage location will not be defined. For temporary tables, you can specify the number of days to store them.
Next, create a table in the dataset.
It’s ready! Now you can start loading data.
Find out the real value of campaigns
Automatically import cost data to Google Analytics from all your advertising services. Compare campaign costs, CPC, and ROAS in a single report.
Upload data with Google Sheets (OWOX BI BigQuery Reports Add-on)
If you need to upload data from Google Sheets to Google BigQuery, the easiest way to do that is to install the free OWOX BI BigQuery Reports Add-on.
You can install this add-on directly from Google Sheets or from the Chrome Web Store.
After installing it, a dialog box will appear with tips and permission requests.
Now it’s time to go back to Google Sheets. To upload data to BigQuery, just select Upload data to BigQuery from the Add-ons –> OWOX BI BigQuery Reports menu.
Specify the project, dataset, and name of the table to upload the data to. And that’s all :)
An undeniable advantage of the OWOX BI BigQuery Reports Add-on is its ease of use. You can also use the add-on to set up scheduled reports.
To build reports based on accurate raw data from all sources and automatically upload them to Google BigQuery repository, we recommend using the OWOX BI Pipeline service.
With Pipeline, you can set up automatic data collection from advertising services as well as call tracking and CRM systems. This allows you to quickly and easily get ready-made sets of complete data from the sources you select.
Just select data sources and grant access; leave the rest to OWOX BI.
With OWOX BI, you can build reports for every taste and need, from ROI, ROPO effect, and cohort analysis to LTV and RFM analysis.
Uploading data from CSV files
To upload data from a CSV file, in the Create table window, select a data source and use the Upload option.
Then select the file and file format.
Next, define the destination for the data, specifying the name of the project and the dataset.
Note: In Google BigQuery, you can select two types of tables: native and external.
Google BigQuery will automatically determine the table structure, but if you want to manually add fields, you can use either the text revision function or the + Add field button.
Note: if you want to change how Google BigQuery parses data from the CSV file, you can use the advanced options.
For more information on the CSV format, see this detailed documentation from the Internet Society.
Uploading data from JSON files
To upload data from JSON files, repeat all the steps create or select the dataset and table you’re working with – only select JSON as the file format.
You can upload a JSON file from your computer, Google Cloud Storage, or Google Drive disk.
Note: For more information about the JSON format, see the Google Cloud documentation.
Uploading data from Google Cloud Storage
Google Cloud Storage allows you to securely store and transfer data online.
Useful information about working with this service:
- Getting Started with Google Cloud Storage
- Cloud Storage documentation
- Choosing your storage and database on Google Cloud Platform
You can upload files in the following formats from Google Cloud Storage to Google BigQuery:
- JSON (newline delimited)
- Cloud Datastore
You can read more about using Cloud Storage with big data in the official documentation.
Upload data from other Google services such as Google Ads and Google Ad Manager
To upload data from various Google services, you first need to configure the BigQuery Data Transfer Service. Before you can use it, you must select or create a data project and, in most cases, enable billing for it. For example, billing is mandatory for these services:
- Campaign Manager
- Google Ad Manager
- Google Ads
- Google Play (beta)
- YouTube – Channel Reports
- YouTube – Content Owner Reports
Note: Read more about billing settings and changes in the Google Cloud help center.
To start the BigQuery Data Transfer Service, on the BigQuery home page, select Transfers from the left-hand menu.
Note: You'll need admin access to create a Transfer.
In the next window, all you have to do is select the data source you want.
Note: The BigQuery Data Transfer Service can be accessed not only from the platform console but also from:
- сlassic bq_ui
- bq command-line tool
- BigQuery Data Transfer Service API
Once configured, the service will automatically and regularly upload data to BigQuery. However, you cannot use it to download data from BigQuery.
Downloading data with the API
By means of Cloud Client Libraries, you can use your favorite programming language to work with the Google BigQuery API.
Note: You can find more details about downloading data using the API in the Google Cloud documentation.
To start, you need to create or select the project with which you’ll work. Then on the home page, go to the APIs section.
In the APIs overview window, you can enable APIs and services and select the API from the library.
In the library, you can use a field search or filter APIs by the category.
You can use a set of Python scripts from OWOX BI to automate the import of data into Google BigQuery.
You’ll find scripts to automate the import of data into Google BigQuery from the following sources:
You can download these Python scripts from GitHub.
Note: Learn how to use Python while working with the Google API in this video lesson on the Google Developers YouTube channel.
In our article, we considered the most popular ways of uploading data to Google BigQuery. From simply loading a data file to loading data through an API, any user can find a suitable option.
- BigQuery data structure in Google: How to get started with cloud storage
- Top 6 BigQuery Visualization Tools
- Automate reports in Google Sheets with data from Google BigQuery
- Standard SQL in Google BigQuery: Advantages and Examples of Use in Marketing
- Google Developers YouTube Channel
- Google Cloud Platform YouTube Channel