Versions Compared

Old Version 2

changes.mady.by.user Nadine Hinojosa

Saved on

compared with

New Version Current

changes.mady.by.user Nadine Hinojosa

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

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

Anchor
backtotop
backtotop

Table of Contents
stylenone

Info

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

Code Block
SELECT * FROM `publicdata.samples.github_timeline` LIMIT 1000
Code Block
SELECT repository_url, repository_has_downloads, repository_has_issues 
FROM `publicdata.samples.github_timeline` LIMIT 1000
Code Block
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

Code Block
INSERT `sample-project-12345.Test.account` (`ID`, `Name`, `Salary` ) 
VALUES ('E001', 'John', 25000)
Code Block
INSERT `sample-project-12345.Test.account` (`ID`, `Name`, `Salary` ) 
VALUES ('E002', 'Lisa', 25000), ('E003', 'Tom', 22000)
Code Block
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

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

To delete all rows from a table:

Code Block
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

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

To update all rows in a table:

Code Block
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

Code Block
CREATE DATASET `sample-project-12345`.`datasetId`
Code Block
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

Code Block
CREATE TABLE `sample-project-12345`.`dataset1`.`table_without_record_type`
(field1 INTEGER NULLABLE,
 field2 STRING "this column has default nullable mode",
 field3 FLOAT)
Code Block
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

Code Block
CREATE VIEW `sample-project-12345`.`test`.`create_view_test` 
AS "SELECT * FROM `sample-project-12345.dataset1.github_timeline`"
Code Block
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

Code Block
CREATE PROCEDURE `TOM`.`proc1114` (OUT a INT64) 
BEGIN
SELECT * FROM TOM.Ctable_1;
END
Code Block
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

Code Block
DROP DATASET `datasetId`
Code Block
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

Code Block
DROP TABLE `dataset1`.`table_without_record_type`
Code Block
DROP TABLE `sample-project-12345`.`create_dataset`.`trigrams`

DROP VIEW

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

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

Example

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

Back to top

DROP PROCEDURE

Example

Code Block
DROP VIEW `TOM`.`proc1114`

USE Command

USE `projectId`

This command changes the current project to the specified project.

Example

Code Block
USE `sample-project-12345`

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