How to upload data to Google BigQuery

0
71
Downloads
959.24 Kb

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.

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.

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.

Combine your data with OWOX BI!

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:

You can upload files in the following formats from Google Cloud Storage to Google BigQuery:

  • CSV
  • JSON (newline delimited)
  • Avro
  • Parquet
  • ORC
  • Cloud Datastore

You can read more about using Cloud Storage with big data in the official documentation. 

You can also find out about data download limits and Cloud Storage permissions in the Google Cloud help center.

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.

A great bonus for our readers! 

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:

  • amoCRM
  • FTP
  • FTPS
  • HTTP(S)
  • Intercom
  • ExpertSender
  • MySQL
  • SFTP

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.

Key takeaways

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.

Useful links

Used tools

You might also like