Basic Concepts of BigQuery

Create and Use Tables

There are many ways to create a table in BigQuery. Let's check them out.

• By using CREATE TABLE.
• By defining a table that makes use of a third-party data source.
• By calling tables.insert API method.
• By loading data.
• By using the client library.
• By using query results.

Create an empty table with a schema definition

There are four ways by which you can create an empty table with a schema definition:

• Using the Google Cloud Console.
• Schema inline using BQ command line tool.
• Submit a JSON schema file by the bq command-line tool.
• Using table resources.

We can also create an empty table without schema definition. Let's see how to create one.

Create an empty table without a schema definition

To create an empty table without a schema definition, we have to write code in Java Console:

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;


public class CreateTableWithoutSchema {


  public static void main(String[] args) {
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    createTableWithoutSchema(datasetName, tableName);
  }


  public static void createTableWithoutSchema(String datasetName, String tableName) {
    try {
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();


      TableId tableId = TableId.of(datasetName, tableName);
      TableDefinition tableDefinition = StandardTableDefinition.of(Schema.of());
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();


      bigquery.create(tableInfo);
      System.out.println("Table created successfully");
    } catch (BigQueryException e) {
      System.out.println("Table was not created. \n" + e.toString());
    }
  }
}

You can also try this code with Online Java Compiler

Run Code

Create a table using query result

The public bikeshare_trips table's data is used to create the trips table in the example that comes using the CREATE TABLE statement:

1. Go to the BigQuery page in the Google Cloud Console.
    
2. Give the statement in the query editor.
   
   CREATE TABLE mydataset.trips AS (
     SELECT
       bikeid,
       start_time,
       duration_minutes
     FROM
       bigquery-public-data.austin_bikeshare.bikeshare_trips
   );
    
3. Click on Run.

Partitioned Tables

A table that is divided into sections is known as Partition Tables. This is a special table that helps you manage and query your data. The sections in these tables offer improved performance and cost control by using fewer bytes.

There are three types of Partitioning in tables:

• Time Unit Column.
• Ingestion Time.
• Integer range.

Loading CSV data 

There are two things that may happen while you load your CSV data from Cloud Storage:

• Create a new table or partition.
• Append to or overwrite an existing table.

Loading data into a table

You can use a lot of methods to load data into the table. Choose the process from the list given below.

• Console.
• SQL
• BQ
• API
• C#
• Go
• Java
• Node.js
• PHP
• Python
• Ruby

Here, we will discuss loading data into a table using SQL. The below example will load a CSV file into the mytable.

1. Go to the BigQuery page in the Google Cloud Console.
    
2. Enter the given query in the query editor.
   
   LOAD DATA OVERWRITE mydataset.mytable
   FROM FILES (
     format = 'CSV',
     uris = ['gs://bucket/path/file.csv']);
    
3. Click on Run.

Column-based time partitioning

We will now learn the Column based time partitioning using Go. For this, first, we have to know the BigQuery quickstart using client libraries. Let's learn this using an example.

import (
        "context"
        "fmt"
        "time"


        "cloud.google.com/go/bigquery"
)


// importPartitionedTable demonstrates specifying time partitioning for a BigQuery table when loading
// CSV data from Cloud Storage.
func importPartitionedTable(projectID, destDatasetID, destTableID string) error {
        // projectID := "my-project-id"
        // datasetID := "mydataset"
        // tableID := "mytable"
        ctx := context.Background()
        client, err := bigquery.NewClient(ctx, projectID)
        if err != nil {
                return fmt.Errorf("bigquery.NewClient: %v", err)
        }
        defer client.Close()


        gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv")
        gcsRef.SkipLeadingRows = 1
        gcsRef.Schema = bigquery.Schema{
                {Name: "name", Type: bigquery.StringFieldType},
                {Name: "post_abbr", Type: bigquery.StringFieldType},
                {Name: "date", Type: bigquery.DateFieldType},
        }
        loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
        loader.TimePartitioning = &bigquery.TimePartitioning{
                Field:      "date",
                Expiration: 90 * 24 * time.Hour,
        }
        loader.WriteDisposition = bigquery.WriteEmpty


        job, err := loader.Run(ctx)
        if err != nil {
                return err
        }
        status, err := job.Wait(ctx)
        if err != nil {
                return err
        }


        if status.Err() != nil {
                return fmt.Errorf("job completed with error: %v", status.Err())
        }
        return nil
}

You can also try this code with Online Java Compiler

Run Code

Frequently Asked Questions

What is BigQuery used for?

BigQuery is a fully managed firm data warehouse that offers inbuilt technologies like ML, geospatial analysis, and business intelligence to assist you in collecting and analyzing your data.

Does BigQuery use SQL?

Although a legacy SQL dialect is also offered, BigQuery supports the Google Standard SQL dialect. Google Standard SQL provides the widest breadth of capability. And it is the best choice if you're new to BigQuery. E.g., Google Standard SQL is the only database engine that supports capabilities like DDL and DML statements.

Is BigQuery an ETL tool?

Google BigQuery ETL is based on a cloud data warehouse that serves as an ETL solution through the help of SQL queries. Also, users can gain analytical insights from the serverless product with its inbuilt ML.

Conclusion

We have discussed the topic of Basic concepts of BigQuery. We have learned how to Load and query data, create and use tables, Partitioned tables, and load CSV data.

We hope this blog has helped you enhance your knowledge of the Basic concepts of BigQuery. If you want to learn more, check out our similar articles on: 

• BigQuery
• Advanced concepts of BigQuery

And many more on our platform Coding Ninjas Studio.

Readers also prefer to read the blogs shown below.

• Data Catalog.
• Cloud Life Science.
• Cloud Interface API.

But suppose you have just started your learning process and are looking for questions from tech giants like Amazon, Microsoft, Uber, etc. In that case, you must look at the problems, interview experiences, and interview bundles for placement trials.

However, you may consider our paid courses to give your career an edge over others!

Happy Learning!