Legacy SQL Query Reference (Revision #54)

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

Contents

Query Syntax DML Syntax INSERT DDL Syntax CREATE DATASET CREATE TABLE CREATE VIEW DROP DATASET DROP TABLE DROP VIEW

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 Query Reference to understand BigQuery's query 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. 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

INSERT INTO [sample-project-12345:mydataset2.names] (name, g, cnt)
VALUES ('Dilip','M', '600')

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')

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" ] }
    ]
  }
)

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: REQUIRED, 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 REQUIRED "This is required",
 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

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

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 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.

Back to top