BigQuery Tutorial – A Comprehensive Guide

Share

Tweet

Share

Share

Email

Comments

In this article, we will be covering a beginner’s guide to understanding Google’s BigQuery. From getting to what is bigQuery, how to use it, understanding the setup, to export queries. This BigQuery tutorial will help you understand bigquery in depth.

What is Google BigQuery?

It’s the enterprise data warehouse for analytics on the Google Cloud Platform. With features like exabyte-scale storage and petabyte-scale SQL queries, Google BigQuery excels at analyzing large amounts of data and swiftly satisfies your Big Data processing needs. It’s a serverless Software as a Service (SaaS) application with ANSI SQL querying and machine learning capabilities.

Why you should use BigQuery

The basic purpose of BigQuery is to do analytical queries. You can use BigQuery to perform complex analytical queries on big data sets. Queries are data requests that might include calculations, changes, merges, and other data manipulations. BigQuery is a high-throughput database designed for making analytical queries that go beyond ordinary CRUD operations.

BigQuery Setup Guide

There is no need to install any program Google takes care of the infrastructure, so all you have to do is set up BigQuery, which is simple. Google Cloud Platform will be the starting point for your trip.

• If this is your first visit, you must first choose a nation and accept the Terms of Service.
• Then go to BigQuery, which you can discover using the search bar or manually in the left menu.
• To spin the prop, click the Create Project button. Click Create after giving your project a name and, if necessary, an organization.

• Your BigQuery Setup is now ready

How to use Google BigQuery

Create data set in BigQuery

Step 1. Select the project you wish to work on and then click Create Dataset.

Step 2. You can use letters and numbers to create a Dataset ID. You can choose the Data location as well as the table expiration (up to 60 days) and encryption if necessary. After that, select Create dataset from the drop-down menu.

Step 3. Now we’re going to make a fresh dataset. Click the Expand node button next to your project name to find it.

Step 4. The dataset’s next step is to generate a table. You have a couple of choices here:

• Make an empty table and manually fill it.
• Using one of the available formats, upload a table from your device
• Import a table from Google Drive or Google Cloud Storage
• Using the CLI, import a table from Google Cloud Bigtable.

Upload CSV data to BigQuery

After you’ve clicked the Create table button, you’ll need to do the following:

• Choose a source – Upload a file
• Select a file from your device by clicking Browse and selecting the CSV file.
• Choose CSV as the file format, however, the system will generally detect it for you.
• Table name – type the name of the table here.
• Select the Auto-detect option.
• Create a table by clicking the button.

Import data from Google Sheets to BigQuery manually

To import data from google sheets click the Create table button and:

• Choose source – Drive
• Select Drive URI – insert Google Sheets spreadsheet URL
• File format – Google Sheets
• Sheet range – specify the sheet and data range to import.
• Table name – enter the table name
• Mark the Auto detect checkbox
• Click Create table

Query tables in BigQuery

The standard SQL dialect can be used to query the tables in your database. Although the non-standard or legacy SQL dialect can be used, BigQuery recommends that you use the standard SQL dialect.

Query data in BigQuery + syntax

Click on Query Table button

The query boilerplate will look something like this:

SELECT FROM `test-project-310714.test.pipedrive-deals` LIMIT 1000

This is the most simple querying example. To make the query appear like this, add * after the SELECT method:

SELECT * FROM `test-project-310714.test.pipedrive-deals` LIMIT 1000

This query returns all accessible columns from the provided table with a maximum of 1,000 rows. Click Run, and you’re done!

As a result, rather than using *, we must specify the field names we require. The field names can be found in the Schema tab or in your previous query.

Replace the default query’s LIMIT method with ORDER BY, which will allow you to sort data by a specific column. Add DESC at the end of the query to sort the data in descending order. This is how it appears:

SELECT

      string_field_4,

      string_field_13,

      string_field_19

FROM `test-project-310714.test.pipedrive-deals`

ORDER BY string_field_19 DESC

This is how you can save Queries

You may save your inquiries and come back to them later. To do so, go to File => Save Query.

Name your query and choose its visibility in the following window:

1. personal – only you will be able to edit the query
2. project – only project members will be able to edit the query
3. public – the query will be available publicly for edit

After choosing an option-click Save the file.

How you can schedule queries

A Schedule button is located next to the Save button, and it allows you to run scheduled queries.

You’ll get a notification after clicking the Schedule button that you need to enable the BigQuery Data Transfer API first.

Wait a few moments after clicking Enable API. When you click the Schedule button after that, you’ll be able to generate scheduled queries.

Click Create a new scheduled query and set the parameters as follows:

1. Name for scheduled query
2. Schedule options
3. Repeats
4. Start date and run time
5. End date
6. Destination
7. Table name
8. Write preference (overwrite or append)

When you’re finished with the configuration, click Schedule.

For more in-depth information, you can refer to Hevo Data’s BigQuery tutorial to understand in what more ways BigQuery can be used.

Export queries from BigQuery manually

Most customers will need to export their query results outside of BigQuery. Spreadsheet apps like Google Sheets and Excel, visualization and dashboarding tools like Google Data Studio and Tableau, and other software are typical destinations. You may also use Power BI to connect to BigQuery.

BigQuery exporting limit 

Click the Save Results button and choose one of the following options to export your query results:

• CSV file (download in devices up to 16K rows or Google Drive up to 1GB)
• JSON file (download in devices up to 16K rows or Google Drive up to 1GB)
• BigQuery table
• Google Sheets
• Copy to clipboard

Bonus: How BigQuery stores data

BigQuery stores data column by column, unlike standard relational databases, which store data row by row. This means that each column is stored in its own file block. BigQuery can reach a very high throughput using this columnar structure, known as Capacitor, which is critical for live analytical processing.

To transfer data from BigQuery you need ETL, Hevo is a no-code data pipeline that helps in transferring your data in the data warehouse without doing it manually. It is completely automated and transfers data very quickly without writing any code.

Conclusion 

This blog presented you with a complete BigQuery Tutorial, as well as in-depth knowledge of the concepts underlying each stage to assist you in understanding and effectively implementing them.