Page History
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 | ||||
---|---|---|---|---|
|
Table of Contents | ||
---|---|---|
|
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 |
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.
...
- 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 |
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 |
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 |
...
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" |
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.
...
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 |
...
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 |
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` |
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` |
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 project_id, datatset_id, table_name with backticks.
- Use Dot (.) as separator.