Google BigQuery Create Table Command: 4 Easy Methods

on Data Warehouse, Google BigQuery, Tutorials • October 15th, 2020 • Write for Hevo

Since data acts as the new oil of businesses, it becomes crucial to collect, maintain, and analyze the data. Nowadays, data is growing exponentially, and it’s becoming a challenge to collect, maintain, load, and analyze data using traditional data warehouse technology. To tackle these challenges, BigQuery is playing a vital role by providing exceptional speed of data storage.

In this blog, you will go through Google BigQuery create table command, its usage, and examples. Furthermore, the blog will explain 4 easy methods using which you can create your own Tables in Google BigQuery. Also, the blog will explore how to query and load data in Google BigQuery easily. Read along to learn more about the Google BigQueryCreate Table command!

Table of Contents

Introduction to Google BigQuery

BigQuery Create Table: Google BigQuery
Google BigQuery

Google BigQuery is a highly Scalable Data Warehouse solution to store and query the data in a matter of seconds. BigQuery stores data in columnar format. It is a serverless Cloud-based Data Warehouse that allows users to perform the ETL process on data with the help of some SQL queries. BigQuery supports massive data loading in real-time.

Unlike its counterparts, BigQuery is serverless and therefore dynamic, which means users don’t need to provision or manage hardware. BigQuery is built in a way that is best suited for complex queries and analytics.
The peculiarity of this Data Warehouse is that it is linked to other Google services like Spreadsheets, Google Drive, etc., making BIGquery a very attractive and maintenance-free option for the companies and available in all places where Google Cloud is present.

To learn more bout Google BigQuery, visit here.

Introduction to Google BigQuery Tables

The BigQuery table contains records, organized in rows and each row has fields (also known as columns). Every table has a schema that describes the table details, such as column name, their data type, nullability, column description, clustering columns, partition column, and other details. You can specify table schema at the time of table creation, or you can create a table using another table when it gets populated.

BigQuery table types are described below:

  • Native tables: Table data stored in BigQuery native storage.
  • External tables: It is not a physical table. It points to the files present at GCS (Google Cloud Storage), Google Drive, or any other GCP (Google Cloud Platform) storage options.
  • Views: It is a virtual table. It is created using a query on top of BigQuery native tables.

To learn more about Google Bigquery Tables, visit here.

Hevo Data: Load your Data in Google BigQuery Conveniently

Hevo Data is a No-code Data Pipeline. It supports pre-built data integrations from 100+ data sources. Hevo offers a fully managed solution for your data migration process. With Hevo, you can transform and enrich your data in minutes. It will automate your data flow without writing any line of code. Its fault-tolerant architecture makes sure that your data is secure and consistent. Hevo provides you with a truly efficient and fully automated solution to manage data in real-time and always have analysis-ready data in your desired destination.

Get Started with Hevo for Free

Let’s look at some salient features of Hevo:

  • Fully Managed: It requires no management and maintenance as Hevo is a fully automated platform.
  • Data Transformation: It provides a simple interface to perfect, modify, and enrich the data you want to transfer. 
  • Real-Time: Hevo offers real-time data migration. So, your data is always ready for analysis.
  • Schema Management: Hevo can automatically detect the schema of the incoming data and maps it to the destination schema.
  • Live Monitoring: Advanced monitoring gives you a one-stop view to watch all the activities that occur within pipelines.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-Day Free Trial!

BigQuery Create Table Process

BigQuery Table
Image Source

Before diving into the BigQuery Create Table process, you need to create a Dataset, which contains both Tables and Views. In this section, you will go through the following:

Table Naming Guidelines 

Before creating a table in Google BigQuery you must understand the proper naming convention used for the Tables. The following guidelines must be followed while naming a Google BigQuery Table:

  • The name of the Table must be unique in the Dataset but can be the same in different Datasets.
  • Table name length can be 1024 characters long and can have a lower case, upper case letter, number, and underscore.

Methods to Implement the BigQuery Create Table Command

Below are the permissions required to execute the BigQuery Create Table command:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery. admin

Particularly in this article, you will explore the command-line tool to Create, Load, and View the BigQuery Table data.

To use the BigQuery Create Table command, you can use any of the following methods:

Method 1: BigQuery Create Table Using bq mk Command

The bq command-line tool is based on Python Programming Language and can be used to implement BigQuery Create Table Command. The bq command-line tool works with the following format:

bq COMMAND [FLAGS] [ARGUMENTS]

The above syntax allows 2 kinds of flags:

  • Global flags: These flags can easily be used with all bq commands.
  • Command-specific flags: they are allowed with some specific bq commands only.

Steps to create a table in BigQuery using the bq mk command are listed below:

Step 1: Create a Dataset, if required.

bq mk test_dataset

Step 2:  Create a Table in the test_dataset Dataset.

bq mk --table --expiration 36000 --description "test table" bigquery_project_id:test_dataset.test_table sr_no:INT64,name:STRING,DOB:DATE

Method 2: BigQuery Create Table Using YAML Definition File

The steps to create a table in BigQuery using the YAML definition file are listed below. Please refer to Google documentation for more details, here.

  • Create a YAML file, refer to the below example and upload it into Google Cloud shell.
resources:
- name: TestTableYAML
  type:  bigquery.v2.table
  properties:
   datasetId: test_dataset
   tableReference:
     projectId: testgcpproject
     tableId: TestTableYAML
   description: TestTableYAML
   labels:
     apigateway: not-applicable
     build_date:  2458647
     bus_contact: lahu
     businessregion: us
     cloudzone: google-us
     company: lahu
     costcenter: finance
     country: india
     dataclassification: dc2
     department: product-development     
     eol_date: not-applicable
     group: application-delivery
     lifecycle: development
     organization: lahu
     prj_code: data_analytics
     productversion: 1
     project: gcp-cloud-data-lake
     resourcename: location-table
     service: gcp-cloud-datawarehouse
     sla: silver
     status: development
     support_contact: lahu
     tech_contact: lahu
     tier: bigquery
   schema:
     fields:
       - mode: nullable 
         name: ID 
         type: string
       - mode: nullable 
         name: UserName 
         type: string
       - mode: nullable 
         name: FullName 
         type: string
       - mode: nullable 
         name: UserGroupID 
         type: string
       - mode: nullable 
         name: create_date_time 
         type: datetime
       - mode: nullable 
         name: update_date_time 
         type: datetime
  • Execute the below command to create a table using the YAML file:
gcloud deployment-manager deployments create testtableyaml --config TestTableYAML.yaml

Method 3: BigQuery Create Table Command Using API

If you want to implement the BigQuery Create Table command using the BigQuery API, you will need to send a JSON-formatted configuration string to the API of your choice. The jobs.insert API call can be used to insert a new Table in your Database. This method involves the following terminology:

  • query: It is the actual SQL query written in the proper syntax which will create the desired Table.
  • destinationTable: It is a collection space that contains multiple sub-fields to instruct the API about the location, where the query results must be saved.
  • projectId: It is the unique id of the project to which the data will be exported.
  • datasetId: It is the name of the dataset to which data will be export.
  • tableId: It is the name of the table to which data will be exported. This Can either represent an existing table or a new one.
  • createDisposition: It determines how the API will create the new Table.
  • writeDisposition: It determines how the API writes new data to the table.
  • WRITE_APPEND: It indicates that new data will be appended to the already existing data.

The below image shows the API options provided by Google BigQuery.

BigQuery Table using API
Image Source

Method 4: BigQuery Create Table Command Using WebUI

Using BigQuery WebUI to execute Create Table command makes it very easy to specify a destination table for your query result. The following steps are required to implement this method:

  • Step 1: You must ensure that the Project and Dataset that you wish to treat as the destination already exist.
  • Step 2: Write a Query in the normal syntax, but before executing it using the Run Query button, click on the Show Options button.
  • Step 3: You will view the Destination Table section. In that, click Select Table and then a popup will ask you to choose the Project, Dataset, and specify the Table Name to use as your destination.
  • Step 4: Click on Run Query and once the query is executed, the results will be appended to the table that you have specified in the previous steps.

The Google BigQuery classic WebUI is shown in the below image.

BigQuery Classic WebUI
Image source

Commands to Show Table Schema in BigQuery

To show table schema in BigQuery, you need to execute the following command:

Syntax:

bq show --schema --format=prettyjson gcp_project_id:dataset_name.table_name

Where

  • gcp_project_id is your project ID.
  • dataset_name is the name of the dataset.
  • table_name is the name of the table.

Example:

bq show --schema --format=prettyjson bigquerylascoot:test_dataset.test_table

The below image show an instance of table schema in BigQuery.

Table Schema in BigQuery
Image Source

Commands to Load the Data to BigQuery Table

You can load a variety of data to BigQuery tables, such as CSV, Parquet, JSON, ORC, AVRO, etc. Here, you will see how to load CSV data to the table using the command-line tool.

bq --location=data_center_location load --source_format=source_data_format dataset_name.table_name path_to_source_file table_schema

Where:

  • location: It specifies the data center location.
  • format: It specifies the source data format.
  • dataset_name: It specifies the name of the dataset.
  • table_name: It specifies the table name in which you want to load data.
  • path_to_source_file: It specifies the path of the data file, for example, Google Cloud storage bucket URI (fully qualified) or your local file system file path.
  • table_schema: It specifies the schema of the target table. Here, you can specify –autodetect schema option to automatically detect the schema of the target table.

Example: Sample data file contains below data “mydata.csv”.

1,ABC,1995-05-102,DEF,1996-10-103,PQR,1997-06-084,XYZ,1998-05-125,ABCD,1998-07-10

The bq load command to load data in BigQuery is as follows:

bq load --source_format=CSV test_dataset.test_table mydata.csv sr_no:INT64,name:STRING,DOB:DATE

The following image shows the process of Loading Data into BigQuery Table.

Loading Data into BgQuery Table
Image Source

Commands to Interact with the Table in BigQuery

There are many options to interact with tables. Here, you are going to explore the command line options, i.e., bq query:

Creates a query job for the provided SQL query statement.

For example: Select the count of the table using the bq query command-line tool.

bq query --use_legacy_sql=false "select count(1) from test_dataset.test_table"

Select the data from the table.

bq query --use_legacy_sql=false "select *  from test_dataset.test_table"

The below image shows the process of extracting data from BigQuery Table using SQL query.

Selecting Data from Table
Image Source

Conclusion

In this blog, you have learned about the Google BigQuery create table command, its usage, and examples. You also learned about how to query a table or load data in BigQuery. If you are looking for a data pipeline that automatically loads data in BigQuery, then try Hevo.

Visit our Website to Explore Hevo

Hevo Data is a No-code Data Pipeline. It supports pre-built integration from 100+ data sources at a reasonable price. Load your data in Google BigQuery within minutes with Hevo.

Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand.

Share your experience of using the BigQuery Create Table command in the comment section below.

No-code Data Pipeline for Google BigQuery