Getting Started with SQL in BigQuery

I hope you have read all the past posts and now are ready to move on to writing some SQL.  As you might remember, SQL is short for Structured Query Language. This is the language used to interact with the data contained in most of the databases including BigQuery.

(Note: If you want to expedite your learning then check out BigQuery for Marketing Analytics course)

In this post, I will get you started with writing SQL and show you the most common SQL commands that you will end up using.  This command will be the basis for a lot of advanced commands and SQL queries that we will cover in the future. Also, the skills that you will build are also transferable to other database systems.

Let’s take a simple user case to start learning SQL and BigQuery user interface for writing SQL or SQL Queries.  

Let’s say you want to look at all the data that’s contained in the latest events table.

In order to do that:

1. Go to your project

2. Expand the dataset that contains the tables

3. Click on the three vertical dots next to the events_(##) table. ## is a number that indicates the number of days of the data contained in the events table.

4. In the menu that opens up, click on “Query”

5. This will open up the query interface in your right-hand panel. This is where you will write the query.

By following the above steps the basic structure of the SQL query is also prewritten for you. You can use the same technique for writing queries for other tables as well.

Here is what the query interface looks like:

The query that’s prewritten for you is

SELECT FROM optizent-test-project.analytics_219941990.events_20221204 LIMIT 1000

SELECT is the most common SQL command that you will use. This allows you to select or view that data from a given table or set of tables.

To select the data from various columns of the table you will type in the names of the columns separated by a comma between SELECT and FROM, that’s where your cursor is initially placed.

Let’s say you want to see the event_date and event_name in your result set then you will use the following statement

SELECT event_date, event_name FROM optizent-test-project.analytics_219941990.events_20221204 LIMIT 1000

Let’s deconstruct this statement before proceeding.

First is the SQL command “SELECT”, this tells the database (BigQuery in this case), what to do.

Next is the name of the columns that it wants to use for that command, in this case we provided event_date and event_name

Then the FROM statement which tells where those columns are followed by the name of the table that contains the columns, in this case, the table name events_20221204. However you have to provide the complete path in the name, so you have to start with the project name, followed by a dot, then the data set name, again followed by a dot, and then the table name. That’s why you see optizent-test-project.analytics_219941990.events_20221204. 

Note: The name of the table has to be enclosed in single quotes.

Finally, there is a LIMIT statement followed by a number.  This LIMIT statement limits the number of rows of data that will be shown in your result set.  In our example, we limit the result set to only show 1000 rows.  This statement is optional.  If you remove the LIMIT statement then you will get the following statement, which will pull all the records from that table.

SELECT event_date, event_name FROM optizent-test-project.analytics_219941990.events_20221204

Once you are done writing the list of columns, click on the “Run” button, right on top of the SQL statement.  This will run (execute) the query and show you the result set. If there are any errors in your SQL query then you will see them on your screen as well. Go ahead and fix those errors and then “Run”

Here is how your screen will look like:

What if you want to select all the columns?

If you want to select the values for all the columns instead of just a few then instead of typing the name of each column, you can use a star (*). means all the columns.  Here is the SQL query using

SELECT * FROM optizent-test-project.analytics_219941990.events_20221204 LIMIT 1000

Go ahead and run this query and see the result set.

Congratulations! You have successfully written your first SQL statement. Get ready for more SQL commands and more fun with this statement. For now, practice this and stay tuned for the next post.

Please make sure to leave a comment if you have any questions.

If you like this newsletter, you should also check out Digital Analytics Insights Newsletter.

Ready to dive deeper into BigQuery? Check out the BigQuery for Marketing Analytics course.