SQL Query Reference (Revision #42)

Contents

Query Syntax DML Syntax INSERT INSERT (WITHOUT RECORD FIELD) INSERT (WITH RECORD FIELD USING DOT NOTATION COLUMN NAMES) INSERT (WITH JSON OBJECT) DDL Syntax CREATE DATASET CREATE TABLE (WITHOUT RECORD TYPE COLUMN) CREATE TABLE (WITH RECORD TYPE COLUMN) CREATE VIEW DROP DATASET DROP TABLE DROP VIEW

USE QUOTE IDENTIFIERS

Keywords are not case-sensitive. Keywords such as SELECT are only capitalized to show as examples.

Query Syntax

The BigQuery Select Syntax is designed to allow the use of SQL-like statements to query the structured data documents of BigQuery. View the BigQuery's Select Query Reference  to understand the BigQuery Select SQL.

SELECT

BigQuery queries are written using a variation of the standard SQL SELECT statement.

 

Example

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 the Google BigQuery in Aqua Data Studio.

INSERT

INSERT (WITHOUT RECORD FIELD)

INSERT INTO [projectId:]datasetId.tableName (column1, column2, column3,...,column_N) VALUES (‘value1’, ‘value2’,’value3’,...,’valueN)

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

Example

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

Back to top

INSERT(With record field using dot notation column names)

INSERT INTO  [projectId:]datasetId.tableName       
(column1.childColumn1,
 column1.childColumn2,
 column1.childColumn3.column3Child1,
 column1.childColumn3.column3Child2,
 column1.childColumn4,…,
 column2,
 column3.childcoulmn1,
 column3.childcolumn2.child1,
 column3.childcolumn2.child2,
 column3.childColumn3
)      
VALUES
(   “cvfdvf for column1.childColumn1”,
   “ Value for column1.childColumn2”,
   “ Value for column1.childColumn3.childColumn3Child1”,
    “Value for column1.childColumn3.childColumn3Child2”,
                 “Value for column1.childColumn4”,...,
                 “Value for column2”,
                 “Value for column3.childColumn1”,
                 “Value for column3.childColumn2”.child1”,
                 “Value for column3.childColumn2”.child2”,
                 “Value for column3.childColumn3”
)

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

For inserting nested record use dot notation.

This command does not insert data in tables that contain repeated fields. Repeated fields must be inserted as JSON array.

Example

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

Back to top

INSERT (with JSON Object)

INSERT INTO  [projectId:]datasetId.tableName   
       VALUES ( JSON )

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

Example

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 the Google BigQuery in Aqua Data Studio.

CREATE DATASET

 CREATE DATASET [projectId:]datasetName
        DESCRIPTION “description”
        FRIENDLY_NAME “friendly_name”
        DEFAULT_TABLE_EXPIRATION_MS default_tables_expiration_time                 

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

Examples

CREATE DATASET [sample-project-12345:datasetName]
DESCRIPTION "this is description"
FRIENDLY_NAME "friendly name of the dataset"
DEFAULT_TABLE_EXPIRATION_MS 7200000

CREATE DATASET [sample-project-12345:data]
DEFAULT_TABLE_EXPIRATION_MS 7200000
DESCRIPTION "here is some description for the dataset"
FRIENDLY_NAME "My datatset test 1"

Back to top

CREATE TABLE WITHOUT RECORD TYPE COLUMN

CREATE TABLE [projectId:]datasetName.tableName
(column1 column_type column_mode “description” ,
column2 column_type column_mode “description”,
column3,…,column N column_type column_mode )
DESCRIPTION “description for table”
FRIENDLY_NAME “friendly_name”
EXPIRATION_TIME number              

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

Example

CREATE  TABLE    [sample-project-12345:create_dataset.table_without_record_type]
        (column1 INTEGER REQUIRED "This column is required" ,
        column2 STRING "this column has default nullable mode",
        column3 FLOAT)
    
        EXPIRATION_TIME 1439577000
        DESCRIPTION "This create table supports default column mode nullable and set description for columns"
        FRIENDLY_NAME "friendly name of the dataset"

Back to top

CREATE TABLE WITH RECORD TYPE COLUMN

Use the following command to create a table with nested fields:

Create with dot notation:

CREATE TABLE [projectId:]datasetName.tableName
(
column1 column_type column_mode “description”,

column2 record column_mode “description”,
column2.child1 column_type column_mode “description”,
column2.child2 column_type column_mode “description”,

column2.child3 record column_mode “description”,
column2.child3.child1 column_type column_mode “description”,
column2.child3.child2 column_type column_mode “description”,

column2.child3.child3 record column_mode “description”,
column2.child3.child3.child1 column_mode column_mode “description”,
column2.child3.child3 child2 column_mode “description”,

column3 column_type column_mode “description”,
column4 column_type column_mode “description”,

column5 record column_mode “description”,
column5.child1 column_type column_mode “description”,
column5.child2 column_type column_mode “description”,

column6 column_type column_mode “description”
)
DESCRIPTION “description for table”
FRIENDLY_NAME “friendly_name”
EXPIRATION_TIME number

Column mode and column description are optional.

Example

CREATE TABLE [sample-project-12345:create_dataset.trigrams]
(
ngram    STRING    NULLABLE    "Describe this field...",
first    STRING    NULLABLE    "Describe this field...",
second    STRING    NULLABLE    "Describe this field...",
third    STRING    NULLABLE    "Describe this field...",
fourth    STRING    NULLABLE    "Describe this field...",
fifth    STRING    NULLABLE    "Describe this field...",
cell    RECORD    REPEATED    "Describe this field...",
cell.value    STRING    REPEATED    "Describe this field...",
cell.volume_count    INTEGER    NULLABLE    "Describe this field...",
cell.volume_fraction    FLOAT    NULLABLE    "Describe this field...",
cell.page_count    INTEGER    NULLABLE    "Describe this field...",
cell.match_count    INTEGER    NULLABLE    "Describe this field...",
cell.sample    RECORD    REPEATED    "Describe this field...",
cell.sample.id    STRING    NULLABLE    "Describe this field...",
cell.sample.text    STRING    NULLABLE    "Describe this field...",
cell.sample.title    STRING    NULLABLE    "Describe this field...",
cell.sample.subtitle    STRING    NULLABLE    "Describe this field...",
cell.sample.authors    STRING    NULLABLE    "Describe this field...",
cell.sample.url    STRING    NULLABLE    "Describe this field..."

)
  DESCRIPTION "This is create command using dot notation syntax for columns "
  FRIENDLY_NAME "friendly name of the dataset"
  EXPIRATION_TIME 1439577000

Back to top

CREATE VIEW

CREATE VIEW [projectId:]datasetName.viewName AS “Query”
DESCRIPTION “description for table”
FRIENDLY_NAME “friendly_name”
EXPIRATION_TIME number                             

If projectId is not specified in the command, 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 * FROM publicdata:samples.github_timeline LIMIT 50000"
DESCRIPTION "  Expiration in milliseconds since epoch "
FRIENDLY_NAME "friendly name of the dataset"
EXPIRATION_TIME 14355996882

Back to top

DROP DATASET

DROP DATASET [projectId:]datasetName

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

Examples

DROP DATASET [dataset_without_friendlyName]

DROP DATASET [sample-project-12345:dataset_With_all_parameters_without_project_id]

Back to top

DROP TABLE

DROP TABLE [projectId:]datasetName.tableName

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

Examples

DROP TABLE [sample-project-12345:dataset_without_description.table_without_record_type]

DROP TABLE [dataset1.table_without_record_type]

Back to top

DROP VIEW

DROP VIEW [projectId:]datasetName.viewName

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

Example

DROP VIEW [sample-project-12345:test.create_view_test]

Back to top

USE

USE [PROJECT_ID]

This command changes the current project or throws an error if it does not find the project.

Example

USE [sample-project-12345]

Back to top

QUOTE IDENTIFIERS

• If the dataset, table, or view name starts with a number, then use double quotes ( " ) symbol to enclose dataset, table, or view name.
• For the select query, if project name includes a dash ( - ) symbol, you must surround entire table reference with brackets. For example, [my-project-123:dataset1.tableName].
• For all other queries (i.e. SHOW, INSERT, DROP, USE), you must surround entire table reference, dataset reference, or view reference with brackets. For example, [my-project-123:dataset1.tableName].

Back to top