Schema In a Google BigQuery Table

What Is Schema ?

A database schema defines a logical configuration of a table and data type of a column. By schema of a table we can understand the table in a better way and we can easily relate our data to other tables.

Defining Schema In a Google BigQuery Table

While adding data into bigquery we have to define the schema of the table . There are three ways to define schema in BigQuery :-

By Auto detect

We can tick on auto detect schema by selecting Schema and input parameters . After selecting autodect , Bigquery automatically sets the schema of the table columns.

By Edit as text

We can also set schema by editing it as text in JSON format like this -

[
{
"description": "[DESCRIPTION]",
"name": "[NAME]",
"type": "[TYPE]",
"mode": "[MODE]"
},
{
"description": "[DESCRIPTION]",
"name": "[NAME]",
"type": "[TYPE]",
"mode": "[MODE]"
}
]

Here NAME and TYPE is compulsory .

By adding manually field

We can also add the table columns as fields by clicking on Add Field and entering name , type and mode.

Data Types In Google BigQuery

There are thirteen types of data types in bigquery. We can define a column schema from these data types -:

• STRING : STRING data type is for variable-length character (Unicode) data
• BYTES : BYTES data type is for variable-length binary data
• INTEGER :INTEGER data type is for numeric values without fractional components
• FLOAT :FLOAT data type is for approximate numeric values with fractional components
• NUMERIC :NUMERIC data type is for exact numeric values with fractional components
• BIGNUMERIC : BIGNUMERIC data type is for exact numeric values with fractional components
• BOOLEAN : BOOLEAN data type is for TRUE or FALSE (case insensitive)
• TIMESTAMP : TIMESTAMP data type is for absolute point in time, with microsecond precision
• DATE : DATE data type is for a logical calendar date
• DATETIME : DATETIME data type is for a year, month, day, hour, minute, second, and subsecond
• TIME : TIME data type is for a time, independent of a specific date
• GEOGRAPHY : GEOGRAPHY data type is for a pointset on the Earth's surface (a set of points, lines and polygons on the WGS84 reference spheroid, with geodesic edges)
• RECORD : RECORD data type is for container of ordered fields each with a type (required) and field name (optional)