Google BigQuery Aggregate functions

Google BigQuery has provided aggregate functions that are very useful when you are reading data from Google Big Table. The list of Aggregate functions includes Avg, Count, Max, Min and Sum which are very common. If you have used Ms-Excel spreadsheet or Google Spreadsheet or if you are familiar with any DBMS or RDBMS system, you may have used them at some point of time.

I have following Big Table with some sample rows into it. We will apply Aggregate functions on them. I have issued a SELECT query to see what data I have in this Big Table and its column.

Count: Count Function, as it name suggest will give count of total rows in the Big Table. So, I issued a COUNT(*) statement and it returned me a count 19.

Count(Distinct, field,[n]): Count(Distinct, field,[n]) function, will give count of total unique rows in the Big Table. In my Big Table Employee there are three unique departments, so I issued a COUNT (DISTINCT [DeptCode]) statement and it returned me a count 3.

GROUP_CONCAT('str'): GROUP_CONCAT('str') function concatenates the group values into one separating each value by a comma(,). I wanted to show each department and their concerned employees so, I issued a Group_CONCAT statement. They return each department and their employees. Do you remember how much code you had to write to achieve the same with SQL Server or Oracle or other RDBMS. I appreciate Google BigQuery has introduced this function.

STDDEV: This aggregate function returns Standard deviation of a particular column.

Variance: This function returns Variance in a particular column of Big Table.

SUM: As its name suggests it returns the sum total of a particular numeric column.

The list of Google Big Query Aggregrate function includes following functions

• Avg
• Count
• Count(Distinct, field,[n])
• GROUP_CONCAT('str')
• QUANTILES(expr[, buckets])
• STDDEV(numeric_expr)
• VARIANCE(numeric_expr)
• LAST(field)
• MAX(field)
• MIN(field)
• NTH(n, field)
• SUM(field)
• TOP(field, [max_records], [multiplier])

Related Google BigQuery Articles:

• What is Google BigQuery?
• How to create datasets and BigTables into Google BigQuery?
• How to read data from Google BigTables using GooglBigQuery?.
• How to apply JOINS on Google BigTables with Google BigQuery?
• How to read data from Google BigTables using .NET?

How to apply JOINS on Google BigTables with Google BigQuery?

This article is in continuation towards exploring Google BigQuery and Google BigTables. In last few post we learnt what Google BigQuery is and how to create Dataset and BigTables with Google BigQuery. We also learnt how to read data from BigTables. In this post we will learn how we can join two BigTables and read data from both tables.

Google BigQuery service allows to apply Join on BigTables. There are two type of JOIN that Google BigQuery services supports on BigTables.

• Inner Join
• Left Outer Join

Google BigQuery also expects that when we are joining two BigTables, one of the BigTable is relatively small in size. A Big Table is considered as small if the data size in the table is less than 7MB. If we try to join two BigTables with data size of more than 7 MB, the Join will fail.

We can apply row filter by using WHERE clause while joining two tables but it only supports AND condition. What it mean is we can apply multiple row filter criteria using AND condition but if we try to apply JOIN and put OR condition in the Where clause the Join will fail.

Let us create an example by joining two BigTables. We have following two CSV files with data of Employees and Departments.

I created two BigTables Employee and Dept by navigating to Google BigQuery webpage https://bigquery.cloud.google.com

The goal is to join these two BigTables and read Employee code, Employee name and Department name. To join these two tables, I have written following query.

SELECT [BigCompany.Employee.EmpCode], [BigCompany.Employee.EmpName], [BigCompany.Dept.DeptName] FROM [BigCompany.Employee] JOIN [BigCompany.Dept] ON [BigCompany.Employee.DeptCode] = [BigCompany.Dept.DeptCode]

I clicked on Run Query and results were expected. The two BigTables were successfully Join and resultset had data from both tables

So this is how we can apply Join on Big Tables.

How to read data from Google BigTables using Google BigQuery?

This post is in continuation towards our learning Google BigQuery. In last few posts we covered What is Google BigQuery and how to create dataset and BigTables. In this post we will learn how we can read data from Google BigTables using Google BigQuery?

With Google BigQuery we can write SQL-like statement to read data from Google BigTables. We can use the SELECT statement just like we use it to read data from SQL or Oracle tables. In last post we created a sample big table Employee_DS_BQ and stored employee data into it. To read this data using Google Browser Tool, we need to navigate to the Google BigQuery page. We need to click on Compose Query button.

To read data from employee table, we need to write query as following:

SELECT empName, empAge, empActive FROM [Employee_DS_BQ.myTable001].

If we want to limit our query resultset to n rows, we can use LIMIT clause. After writing query we need to click on RUN Query.

We can filter our query result set and apply condition using WHERE clause with Google BigQuery. In following example, I wanted to read data of employees where age is less than 25 so I applied WHERE clause in the query.

Google BigQuery provides Aggregrate functions, string functions, Bitwise functions, Comparison functions just like SQL Server or Oracle has provided with their products. We can JOIN two tables and apply Group by and Having clause into it. For Google BigQuery reference you can visit https://developers.google.com/bigquery/docs/query-reference.

How to create datasets and BigTables into Google BigQuery?

This article is in continuation to the last article What is Google BigQuery.

Once your access to Google BigQuery is enabled and your billing details are setup, you are all set to store data into Google cloud. The data stored in these BigTables can be accessed using three tools that Google has provided.

1. BigQuery Browser Tools
2. Bq Command-line tool
3. REST API

In this post we will examine BigQuery Browser Tool. To start with Browser Tool you need to navigate to Google BigQuery webpage https://bigquery.cloud.google.com/.

When you login into Google BigQuery, the default screen you see is following. Your projects are listed on the left side of panel. Google provides a sample dataset and few sample BigTables.

The first step towards creating BigTables is creating a Dataset. Dataset are containers for BigTables. To create Dataset you can click on your project and select Create new dataset. You will find the Create Dataset screen where you have to provide your Dataset a name.

For demonstration purpose, I created a dataset with the name Employee_DS_BQ.

Once I clicked on OK, data set Employee_DS_BQ got created and it got listed on the left side of panel.

Now we have created the dataset, it is time now to create BigTables. To create BigTables we need to click on the + sign next to dataset. This will launch the Create Tables screen.

In the Create Table screen, you have to enter your table name into Table ID field. In Schema field you need to specify your columns names. The columns are specified in the format [column name:datatype]. Google BigQuery currently supports following data types for columns:

- String
- Integer
- Boolean
- Float

You can click on OK button if you want or you can proceed to load data from Create Table screen. For demonstration purpose, I created one table EmpDetails and specified three columns empName, empAge and empActive.

The next thing I am doing is loading data into these tables. I have following data in a CSV file that I need to upload to the BigTable EmpDetails.

I clicked on Choose File button on Create Table screen and selected my CSV file. I clicked on Ok button.

A job was created by the Google BigQuery service and my BigTable was successfully created with the sample data.

So this is how we can create BigTable and load data inside it using Google BigQuery.

In next article we will learn to Query this data

What is Google BigQuery?

Big Data and Cloud – these two words are talk of IT Industry town in last few quarters. All the major players of IT word such as Amazon (AWS), Microsoft (Windows Azure, SQL Azure, Government Cloud and many more) , Google (Google App Engine, Google Compute Engine, Google Cloud Storage and Google BigQuery) etc. are offering cloud based tools and services and there a lot of successful stories and case study on their web sites about their clients who are using these services and tools. The introduction of Google BigQuery in November 2011 has added a power tool in the Google’s list of cloud servicing.

In a very simple term, Google BigQuery is platform to store data into Google cloud and analyze this data by writing SQL-like queries. With Google Big Query, you store the data into large datasets. These datasets contains one or more tables which are referred as BigTables. These BigTables can store billions of row or hundred terabytes of data and you can read these data in seconds. Data stored in these BigTables can be retried by writing SQL-Like queries just like you query SQL, Oracle or any other DBMS/RDBMS.

Among the many advantages that BigQuery offer speed is one of the significant advantages. The large datasets or BigTables data can be retrieved in few seconds from anywhere in the world. BigData is OLAP (Online Analytics Processing system) tool that let your analyze your terabytes of data quickly. BigData is not a Big Data tools such as Hadoop, Netezza, or Vertica but it gives you flexibility to store and analyze your humongous data quickly.

How to start with BigQuery?

To use BigQuery services to store your data into Google cloud and further analyze it, you need to have a Google account. You have to follow the two step process to activate your BigQuery and BigTable access with Google.

1. You have to enable BigQuery API access into Google API console
2. You have to enable Billing into Google API console

Google BigQuery services are free, if you are storing and reading your data within the Quota limit that Google have setup. You are charged if you are crossing the free quota limit.

Step 1: Enable BigQuery API into Google API console

Google API console is an online interface provided by Google to mange access, authorization and billing for the Google API uses. When you login into Google API console for the first time, you need to create a project.

By default the project is created with the name “API Project”. You need to go to Services and click on BigQuery API.

Your BigQuery API access will be enabled and on left side of panel BigQuery will be added as a label. BigQuery API will be shown into Active Services with a green ON button.

Step 2: Enable Billing

The second step involves enable billing. It is not that you will be charged every time you use Google Big Query services. It is just to ensure that if are crossing the Quota limit set by Google, you will be charged. To enable Billing, you need to click on Billing. By default Billing is not enabled. You need to click on the button just below Enable Billing.

You need to provide your credit card details. Google wallet accepts all Master card, Visa card, AMEX cards. If you are apprehensive about sharing your credit card details, may be you can try with a pre-paid credit card.

In my case, when I activated billing and created a sample BigTable, I was charged INR 1. This is to part of Google Validation of your credit cards.

Once you are done with the two steps process, your access to BigQuery is established. You can use the BigQuery services by logging into https://bigquery.cloud.google.com/ . You can create BigTables, store data into tables, write queries and play around with them.

That is all for this post. In next post we will see how we can create BigTables and store data into Google BigTables.

Related Google BigQuery Articles:

• What is Google BigQuery?
• How to create datasets and BigTables into Google BigQuery?
• How to read data from Google BigTables using GooglBigQuery?.
• How to apply JOINS on Google BigTables with Google BigQuery?
• How to read data from Google BigTables using .NET?