Standard SQL Query Reference

New in 18.0 Google BigQuery provides SELECT statement to query data and DML statements to manage data. Aqua Data Studio provides DDL statements to define data schema.

Contents

Query Syntax DML Syntax INSERT DELETE UPDATE DDL Syntax CREATE DATASET CREATE TABLE CREATE VIEW CREATE TABLE CREATE PROCEDURE DROP DATASET DROP TABLE DROP VIEW DROP VIEW DROP PROCEDURE

USE Command Quote Identifiers

Keywords are not case-sensitive. In this document, keywords such as SELECT are capitalized for illustration purposes.

Query Syntax

Google BigQuery provides native support for SELECT query. View BigQuery’s Standard SQL Query Reference to understand the BigQuery’s standard SQL syntax and functions.

Examples

SELECT * FROM `publicdata.samples.github_timeline` LIMIT 1000

SELECT repository_url, repository_has_downloads, repository_has_issues 
FROM `publicdata.samples.github_timeline` LIMIT 1000

SELECT repository.url, repository.created_at, repository.homepage, repository.description 
FROM `publicdata.samples.github_nested` LIMIT 1000

Back to top

DML Syntax

Data manipulation language (DML) is a family of syntax elements used to insert, delete and, update data in a database. Google BigQuery provides native support for INSERT, DELETE and UPDATE. View BigQuery’s Data Manipulation Language Syntax.

INSERT

Use INSERT statement to add rows to a table. INSERT statement must follow the following rules.

• Column names must be specified.
• Duplicate names are not allowed in the list of target columns.
• Values must be specified in the same order as the columns specified.
• Number of values must match with the number of columns.
• Value must have compatible type with target column.
   

INSERT [INTO] `projectId.datasetId.tableName` (`Field1`. `Field2`,...)
VALUES (`Value1`. `Value2`,...)

INSERT [INTO] `projectId.datasetId.tableName` (`Field1`. `Field2`,...)
VALUES (`Value1`. `Value2`,...), (`Value1`. `Value2`,...), …

Examples

INSERT `sample-project-12345.Test.account` (`ID`, `Name`, `Salary` ) 
VALUES ('E001', 'John', 25000)

INSERT `sample-project-12345.Test.account` (`ID`, `Name`, `Salary` ) 
VALUES ('E002', 'Lisa', 25000), ('E003', 'Tom', 22000)

INSERT `sample-project-12345.dataset1.test_table` (`c1`, `c2`,`c3`,`c4` ) 
SELECT c1, c2, c3, c4 FROM `sample-project-12345.1.copyTable` LIMIT 1000

Back to top

DELETE

Use DELETE statement to delete rows from a table. DELETE statement must follow the following rules.

• DELETE statement must specify a WHERE clause.
• To delete all rows from a table, set the WHERE condition to "true".

DELETE [FROM] table_name
WHERE condition

DELETE [FROM] target_name [alias]
WHERE condition

Examples

DELETE FROM `sample-project-12345.dataset1.test_table` WHERE c1 = 0

To delete all rows from a table:

DELETE FROM `sample-project-12345.dataset1.test_table` WHERE true

Back to top

UPDATE

Use UPDATE statement to update data in a table. UPDATE statement must follow the following rules.

• Each UPDATE statement must specify a WHERE clause.
• To update all rows in a table, set the WHERE condition to "true".

UPDATE target_name [alias]
SET update_item [, update_item]* [FROM from_clause]
WHERE condition

Examples

UPDATE `sample-project-12345.dataset1.person`  
SET age = age + 2 
WHERE phoneNumber.areaCode = 206

To update all rows in a table:

UPDATE `sample-project-12345.dataset1.Sheet1`  
SET Online_Order_Flag = true 
WHERE true

Back to top

DDL Syntax

Data Definition Language (DDL) is a syntax used to define data structures, especially database schemas. The following DDL is specific to Google BigQuery in Aqua Data Studio.

CREATE DATASET

CREATE DATASET `projectId`.datasetId`
       DESCRIPTION "description"
       FRIENDLY_NAME "friendly_name"
       DEFAULT_TABLE_EXPIRATION_MS default_tables_expiration_time

If projectId is not specified, it will default to the current project.

Examples

CREATE DATASET `sample-project-12345`.`datasetId`

CREATE DATASET `sample-project-12345`.`data`
DEFAULT_TABLE_EXPIRATION_MS 7200000
DESCRIPTION "this dataset expires in 7200000 milliseconds"
FRIENDLY_NAME "My datatset test 1"

Back to top

CREATE TABLE

CREATE TABLE `projectId`.`datasetId`.`tableName`
(field1 field_type field_mode "description",
 field2 field_type field_mode "description",
 …)
DESCRIPTION "description for table"
FRIENDLY_NAME "friendly_name"
EXPIRATION_TIME number

If projectId is not specified, it will default to the current project.

Valid field_type are: INTEGER, FLOAT, BOOLEAN, STRING, TIMESTAMP, RECORD, DATE, TIME, DATETIME and BYTES.

Valid field_mode are: NULLABLE, and REPEATED. Default field_mode is NULLABLE.

Use the dot notation to specify nested fields.

Examples

CREATE TABLE `sample-project-12345`.`dataset1`.`table_without_record_type`
(field1 INTEGER NULLABLE,
 field2 STRING "this column has default nullable mode",
 field3 FLOAT)

CREATE TABLE `sample-project-12345`.`create_dataset`.`trigrams`
(
 ngram STRING,
 cell RECORD REPEATED,
 cell.value STRING REPEATED,
 cell.volume_count INTEGER,
 cell.volume_fraction FLOAT,
 cell.page_count INTEGER,
 cell.match_count INTEGER,
 cell.sample RECORD REPEATED,
 cell.sample.id STRING,
 cell.sample.text STRING,
 cell.sample.title STRING,
 cell.sample.subtitle STRING,
 cell.sample.authors STRING,
 cell.sample.url STRING
)
DESCRIPTION "This is create command using dot notation syntax for nested fields"
FRIENDLY_NAME "friendly name for this table"
EXPIRATION_TIME 1439577000

Back to top

CREATE VIEW

CREATE VIEW `projectId`.`datasetId`.`viewName` AS "query"
DESCRIPTION "description for table"
FRIENDLY_NAME "friendly_name"
EXPIRATION_TIME number

If projectId is not specified, it will default to the current project.

Examples

CREATE VIEW `sample-project-12345`.`test`.`create_view_test` 
AS "SELECT * FROM `sample-project-12345.dataset1.github_timeline`"

CREATE VIEW `sample-project-12345`.`test`.`Test_create_view` 
AS "SELECT repository_url FROM `publicdata.samples.github_timeline` LIMIT 50000"
DESCRIPTION "expiration in milliseconds since epoch"
FRIENDLY_NAME "friendly name of the view"
EXPIRATION_TIME 14355996882

Back to top

CREATE PROCEDURE

Examples

CREATE PROCEDURE `TOM`.`proc1114` (OUT a INT64) 
BEGIN
SELECT * FROM TOM.Ctable_1;
END

CREATE PROCEDURE `TOM`.`proc` (a INT64, OUT b INT64, INOUT n INT64)
BEGIN
SELECT * FROM TOM.Entity1;
END

 

DROP DATASET

DROP DATASET `projectId`.`datasetId`

If projectId is not specified, it will default to the current project.

Examples

DROP DATASET `datasetId`

DROP DATASET `sample-project-12345`.`data`

Back to top

DROP TABLE

DROP TABLE `projectId`.`datasetId`.`tableName`

If projectId is not specified, it will default to the current project.

Examples

DROP TABLE `dataset1`.`table_without_record_type`

DROP TABLE `sample-project-12345`.`create_dataset`.`trigrams`

Back to top

DROP VIEW

DROP VIEW `projectId`.`datasetId`.`viewName`

If projectId is not specified, it will default to the current project.

Example

DROP VIEW `sample-project-12345`.`test`.`create_view_test`

Back to top

DROP PROCEDURE

Example

DROP VIEW `TOM`.`proc1114`

 

USE Command

USE `projectId`

This command changes the current project to the specified project.

Example

USE `sample-project-12345`

Back to top

Quote Identifiers

SELECT and DML syntax

• Surround entire table reference with backticks. For example, `my-project-123.dataset1.tableName`

DDL syntax

• Surround project_id, datatset_id, table_name with backticks.
• Use Dot (.) as separator.

Back to top