SQL Query Reference (Revision #4)

Contents

DML Syntax 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

SELECT USE

Keywords are not case-sensitive. In this document, keywords such as SELECT are capitalized for illustration purposes.

DML Syntax

This DML is specific to the Google BigQuery in Aqua Data Studio.

INSERT (WITHOUT RECORD FIELD)

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

                                            OR

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

                                            OR

Use following syntax if dataset or table name starts with number:

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

projectId: Unique project id(If project id is not specified in the command it will default to current project).

datasetId: Dataset Id which contains the table to insert data.

tableName: Name of the table to insert data.

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

projectId: Unique project id (If project id is not specified in the command it will default to current project).

datasetId: Dataset Id which contains the table to insert data.

tableName: Name of the table to insert data.

For inserting nested record use dot notation.

This command does not insert for 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 )

                                OR

If the dataset or the table name starts with a number then use the following syntax:

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

projectId: Unique project id ((If project id is not specified in the command it will default to current project).

datasetId: Dataset Id which contains the table to insert data.

tableName: Name of the table to insert data.

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

                                   OR

If dataset name starts with a number then use the following syntax:

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

projectId : Unique project id of google bigquery project (If project id is not specified in the command it will default to current project).

dataset Name : Name of dataset to be created.

description : A user-friendly description of the dataset [Optional].

friendlyName : A descriptive name for the dataset [Optional].

defaultTableExpirationMs :The default lifetime of all tables in the dataset, in milliseconds. The minimum value is 3600000 milliseconds (one hour). Once this property is set, all newly-created tables in the dataset will have an expirationTime property set to the creation time plus the value in this property, and changing the value will only affect new tables, not existing ones. When the expirationTime for a given table is reached, that table will be deleted automatically. If a table's expirationTime is modified or removed before the table expires, or if you provide an explicit expirationTime when creating a table, that value takes precedence over the default expiration time indicated by this property [Optional].

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

                         OR

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

                         OR

If the dataset or the table name starts with a number then use the following syntax:

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

project Id : Unique project id of google bigquery project (If project id is not specified in the command it will default to current project).

dataset Name :  Name of dataset in which table will be created.

table Name:  Name of the table to be created.

description : A user-friendly description of the table [Optional].

friendlyName : A descriptive name for the table [Optional].

expiration_time: The time when this table expires, in milliseconds since the epoch. If not present, the table will persist indefinitely. Expired tables will be deleted and their storage reclaimed[Optional] .

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

                                OR

If the dataset or view name starts with a number then use the following syntax:

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

project Id : Unique project id of google bigquery project (If project id is not specified in the command it will default to current project).

dataset Name :  Name of dataset in which view will be created.

view Name:  Name of the view to be created.

Query: Query to create a view.

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]

                              OR

If the dataset name starts with a number then use the following syntax:

DROP DATASET [projectId:”datasetName”]

projectId: Unique project id of google bigquery project  (If project id is not specified in the command it will default to current project).

dataset Name :  Name of dataset to be deleted.

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]

                                   OR

If the dataset name or table starts with a number then use the following syntax:

DROP TABLE [projectId:”datasetName”.”tableName”]

projectId: Unique project id of google bigquery project (If project id is not specified in the command it will default to current project).

dataset Name : Name of dataset which contains the table.

tableName: Name of the table to delete.

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]

                               OR

If the dataset or view name starts with a number then use the following syntax:

DROP VIEW [projectId:”datasetName”.”viewName”]

projectId: Unique project id of google bigquery project (If project id is not specified in the command it will default to current project).

dataset Name : Name of dataset which contains the view.

tableName: Name of the view to delete.

Example

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

Back to top

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

USE

USE [PROJECT_ID]

projectId: Unique project id .

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

Example

USE [sample-project-12345]

Back to top