Page History
Google BigQuery provides SELECT statement to query data. Aqua Data Studio provides DML and DDL statements to manage data.
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 Query Reference to understand BigQuery's query 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. This DML is specific to Google BigQuery in Aqua Data Studio.
INSERT
INSERT INTO [projectId:datasetId.tableName] (field1, field2 …)
VALUES (value1, value2 …)
INSERT INTO [projectId:datasetId.tableName]
VALUES (json_document)
If projectId is not specified, it will default to the current project.
INSERT places data into a specified table. Data can be specified in JSON format or as values. When specifying a nested field, use the dot notation, e.g. name.first. When inserting data for repeated fields, use the JSON document format.
Examples
Code Block |
---|
INSERT INTO [sample-project-12345:mydataset2.names] (name, g, cnt)
VALUES ('Dilip','M', '600') |
Code Block |
---|
INSERT INTO [sample-project-12345:record_table.two_records] (record_1.c1, record_1.c2, record_2.r1, record_2.r2)
VALUES(0, 'acbd', 'pqrs', '2015-08-28 10:54:04') |
Code Block |
---|
INSERT INTO [dataset1.person_data] VALUES
(
{ "kind" : "person", "fullName" : "Mike Jones", "age" : 35, "gender" : "Male",
"phoneNumber" : { "areaCode" : "622", "number" : "1567845" },
"children" : [
{ "name" : "Rohit", "gender" : "Male", "age" : "10" },
{ "name" : "Sam", "gender" : "Male", "age" : "6" },
{ "name" : "Kit", "gender" : "Male", "age" : "8" }
],
"citiesLived" : [
{ "place" : "Los Angeles", "yearsLived" : [ "1989", "1993", "1998", "2002" ] },
{ "place" : "Washington DC", "yearsLived" : [ "1990", "1993", "1998", "2008" ] },
{ "place" : "Portland", "yearsLived" : [ "1993", "1998", "2003", "2005" ] },
{ "place" : "Austin", "yearsLived" : [ "1973", "1998", "2001", "2005" ] }
]
}
) |
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.
Valid field_type are: INTEGER, FLOAT, BOOLEAN, STRING, TIMESTAMP, RECORD, DATE, TIME, DATETIME and BYTES .
Valid field_mode are: REQUIRED, 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 REQUIRED "This is required",
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 |
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] |
USE Command
USE [projectId]
This command changes the current project to the specified project.
Example
Code Block |
---|
USE [sample-project-12345] |
Quote Identifiers
SELECT syntax
- If the project ID includes a dash ( - ) symbol, you must surround the entire table reference with brackets. For example, [my-project-123:dataset1.tableName].
DML and DDL syntax
- You must surround the entire name reference with brackets. For example, [my-project-123:dataset1.tableName].
- If the dataset, table, or view name starts with a number or is a SQL keyword, use the double quote ( " ) symbol to enclose the dataset, table, or view name.