Repository: sql-formatter-org/sql-formatter
Pull request #319 (2022-07-15 08:13:09)

Support bigquery DDL optional args
Listed all possible create/alter/drop commands, and keywords for bigquery. https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language

Will handle the other dialects later in a separate PR.

Also fixes the `AS` issue in https://github.com/sql-formatter-org/sql-formatter/issues/309.


Patch:
diff --git a/src/languages/bigquery/bigquery.formatter.ts b/src/languages/bigquery/bigquery.formatter.ts
index b230a62f..4ce098ab 100644
--- a/src/languages/bigquery/bigquery.formatter.ts
+++ b/src/languages/bigquery/bigquery.formatter.ts
@@ -33,57 +33,113 @@ const reservedCommands = [
   // DDL, https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language
   'SET SCHEMA', // added
   'CREATE SCHEMA',
+  'CREATE SCHEMA IF NOT EXISTS',
+  'DEFAULT COLLATE',
   'CREATE TABLE',
   'CREATE TABLE IF NOT EXISTS',
   'CREATE TEMP TABLE',
   'CREATE TEMP TABLE IF NOT EXISTS',
   'CREATE TEMPORARY TABLE',
   'CREATE TEMPORARY TABLE IF NOT EXISTS',
+  'CLUSTER BY',
   'CREATE OR REPLACE TABLE',
   'CREATE OR REPLACE TEMP TABLE',
   'CREATE OR REPLACE TEMPORARY TABLE',
-  'CREATE TABLE LIKE',
-  'CREATE TABLE COPY',
+  'FOR SYSTEM_TIME AS OF', // CREATE SNAPSHOT TABLE
   'CREATE SNAPSHOT TABLE',
-  'CREATE TABLE CLONE',
+  'CREATE SNAPSHOT TABLE IF NOT EXISTS',
   'CREATE VIEW',
   'CREATE VIEW IF NOT EXISTS',
   'CREATE OR REPLACE VIEW',
   'CREATE MATERIALIZED VIEW',
+  'CREATE OR REPLACE MATERIALIZED VIEW',
+  'CREATE MATERIALIZED VIEW IF NOT EXISTS',
   'CREATE EXTERNAL TABLE',
+  'CREATE OR REPLACE EXTERNAL TABLE',
+  'CREATE EXTERNAL TABLE IF NOT EXISTS',
+  'WITH CONNECTION',
+  'WITH PARTITION COLUMNS',
   'CREATE FUNCTION',
+  'CREATE OR REPLACE FUNCTION',
+  'CREATE FUNCTION IF NOT EXISTS',
+  'CREATE TEMP FUNCTION',
+  'CREATE OR REPLACE TEMP FUNCTION',
+  'CREATE TEMP FUNCTION IF NOT EXISTS',
+  'CREATE TEMPORARY FUNCTION',
+  'CREATE OR REPLACE TEMPORARY FUNCTION',
+  'CREATE TEMPORARY FUNCTION IF NOT EXISTS',
+  'REMOTE WITH CONNECTION',
   'CREATE TABLE FUNCTION',
+  'CREATE OR REPLACE TABLE FUNCTION',
+  'CREATE TABLE FUNCTION IF NOT EXISTS',
+  'RETURNS TABLE',
   'CREATE PROCEDURE',
+  'CREATE OR REPLACE PROCEDURE',
+  'CREATE PROCEDURE IF NOT EXISTS',
   'CREATE ROW ACCESS POLICY',
-  'ALTER SCHEMA SET OPTIONS',
-  'ALTER TABLE SET OPTIONS',
-  'ALTER TABLE ADD COLUMN',
-  'ALTER TABLE RENAME TO',
-  'ALTER TABLE DROP COLUMN',
-  'ALTER COLUMN SET OPTIONS',
-  'ALTER COLUMN DROP NOT NULL',
-  'ALTER COLUMN SET DATA TYPE',
-  'ALTER VIEW SET OPTIONS',
-  'ALTER MATERIALIZED VIEW SET OPTIONS',
+  'CREATE OR REPLACE ROW ACCESS POLICY',
+  'CREATE ROW ACCESS POLICY IF NOT EXISTS',
+  'GRANT TO',
+  'FILTER USING',
+  'CREATE CAPACITY',
+  'AS JSON',
+  'CREATE RESERVATION',
+  'CREATE ASSIGNMENT',
+  'CREATE SEARCH INDEX',
+  'CREATE SEARCH INDEX IF NOT EXISTS',
+  'ALTER SCHEMA',
+  'ALTER SCHEMA IF EXISTS',
+  'SET DEFAULT COLLATE',
+  'SET OPTIONS',
+  'ALTER TABLE',
+  'ALTER TABLE IF EXISTS',
+  'ADD COLUMN',
+  'ADD COLUMN IF NOT EXISTS',
+  'RENAME TO',
+  'DROP COLUMN',
+  'DROP COLUMN IF EXISTS',
+  'ALTER COLUMN',
+  'ALTER COLUMN IF EXISTS',
+  'DROP NOT NULL',
+  'SET DATA TYPE',
+  'ALTER VIEW',
+  'ALTER VIEW IF EXISTS',
+  'ALTER MATERIALIZED VIEW',
+  'ALTER MATERIALIZED VIEW IF EXISTS',
+  'ALTER BI_CAPACITY',
   'DROP SCHEMA',
+  'DROP SCHEMA IF EXISTS',
   'DROP TABLE',
+  'DROP TABLE IF EXISTS',
   'DROP SNAPSHOT TABLE',
+  'DROP SNAPSHOT TABLE IF EXISTS',
   'DROP EXTERNAL TABLE',
+  'DROP EXTERNAL TABLE IF EXISTS',
   'DROP VIEW',
+  'DROP VIEW IF EXISTS',
   'DROP MATERIALIZED VIEW',
+  'DROP MATERIALIZED VIEW IF EXISTS',
   'DROP FUNCTION',
+  'DROP FUNCTION IF EXISTS',
   'DROP TABLE FUNCTION',
+  'DROP TABLE FUNCTION IF EXISTS',
   'DROP PROCEDURE',
+  'DROP PROCEDURE IF EXISTS',
   'DROP ROW ACCESS POLICY',
-  // DCL, https://cloud.google.com/bigquery/docs/reference/standard-sql/data-control-language
-  'GRANT',
-  'REVOKE',
-  'CREATE CAPACITY',
-  'CREATE RESERVATION',
-  'CREATE ASSIGNMENT',
+  'DROP ALL ROW ACCESS POLICIES',
   'DROP CAPACITY',
+  'DROP CAPACITY IF EXISTS',
   'DROP RESERVATION',
+  'DROP RESERVATION IF EXISTS',
   'DROP ASSIGNMENT',
+  'DROP ASSIGNMENT IF EXISTS',
+  'DROP SEARCH INDEX',
+  'DROP SEARCH INDEX IF EXISTS',
+  'DROP',
+  'DROP IF EXISTS',
+  // DCL, https://cloud.google.com/bigquery/docs/reference/standard-sql/data-control-language
+  'GRANT',
+  'REVOKE',
   // Script, https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting
   'DECLARE',
   'SET',
diff --git a/src/languages/bigquery/bigquery.functions.ts b/src/languages/bigquery/bigquery.functions.ts
index 43158b86..369ef761 100644
--- a/src/languages/bigquery/bigquery.functions.ts
+++ b/src/languages/bigquery/bigquery.functions.ts
@@ -536,6 +536,7 @@ export const functions = flatKeywordList({
     'TO_BASE64',
   ],
   other: ['BQ.JOBS.CANCEL', 'BQ.REFRESH_MATERIALIZED_VIEW'],
+  ddl: ['OPTIONS'],
   pivot: ['PIVOT', 'UNPIVOT'],
   // Data types with parameters like VARCHAR(100)
   // https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#parameterized_data_types
diff --git a/src/languages/bigquery/bigquery.keywords.ts b/src/languages/bigquery/bigquery.keywords.ts
index d7bc39d0..824f0d93 100644
--- a/src/languages/bigquery/bigquery.keywords.ts
+++ b/src/languages/bigquery/bigquery.keywords.ts
@@ -127,4 +127,21 @@ export const keywords = flatKeywordList({
   // https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_functions#formatting_syntax
   stringFormat: ['HEX', 'BASEX', 'BASE64M', 'ASCII', 'UTF-8', 'UTF8'],
   misc: ['SAFE'],
+  // https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language
+  ddl: [
+    // 'LIKE', // CREATE TABLE LIKE
+    'COPY', // CREATE TABLE COPY
+    'CLONE', // CREATE TABLE CLONE
+    // 'IN',
+    'OUT',
+    'INOUT',
+    'RETURNS',
+    'LANGUAGE',
+    'ANY TYPE',
+    'ALL COLUMNS',
+    'CASCADE',
+    'RESTRICT',
+    'DETERMINISTIC',
+    'NOT DETERMINISTIC',
+  ],
 });


Test patch:
diff --git a/test/behavesLikeSqlFormatter.ts b/test/behavesLikeSqlFormatter.ts
index a9a45f2a..c758bf49 100644
--- a/test/behavesLikeSqlFormatter.ts
+++ b/test/behavesLikeSqlFormatter.ts
@@ -250,10 +250,10 @@ export default function behavesLikeSqlFormatter(format: FormatFn) {
   });
 
   it('formats simple DROP query', () => {
-    const result = format('DROP TABLE IF EXISTS admin_role;');
+    const result = format('DROP TABLE admin_role;');
     expect(result).toBe(dedent`
       DROP TABLE
-        IF EXISTS admin_role;
+        admin_role;
     `);
   });
 
diff --git a/test/bigquery.test.ts b/test/bigquery.test.ts
index 9405a3c2..6c373eba 100644
--- a/test/bigquery.test.ts
+++ b/test/bigquery.test.ts
@@ -2,6 +2,7 @@ import dedent from 'dedent-js';
 
 import { format as originalFormat, FormatFn } from 'src/sqlFormatter';
 import BigQueryFormatter from 'src/languages/bigquery/bigquery.formatter';
+import { flatKeywordList } from 'src/utils';
 import behavesLikeSqlFormatter from './behavesLikeSqlFormatter';
 
 import supportsCreateTable from './features/createTable';
@@ -202,57 +203,6 @@ describe('BigQueryFormatter', () => {
     `);
   });
 
-  it('supports create view optional arguments', () => {
-    const createViewVariations = [
-      'CREATE VIEW',
-      'CREATE OR REPLACE VIEW',
-      'CREATE VIEW IF NOT EXISTS',
-    ];
-
-    createViewVariations.forEach((createViewVariation: string) => {
-      expect(
-        format(`
-        ${createViewVariation} my_dataset.my_view AS (
-          SELECT t1.col1, t1.col2 FROM my_dataset.my_table)`)
-      ).toBe(dedent`
-        ${createViewVariation}
-          my_dataset.my_view AS (
-            SELECT
-              t1.col1,
-              t1.col2
-            FROM
-              my_dataset.my_table
-          )
-      `);
-    });
-  });
-
-  it('supports create table optional arguments', () => {
-    const createTableVariations = [
-      'CREATE TABLE',
-      'CREATE TABLE IF NOT EXISTS',
-      'CREATE TEMP TABLE',
-      'CREATE TEMP TABLE IF NOT EXISTS',
-      'CREATE TEMPORARY TABLE',
-      'CREATE TEMPORARY TABLE IF NOT EXISTS',
-      'CREATE OR REPLACE TABLE',
-      'CREATE OR REPLACE TEMP TABLE',
-      'CREATE OR REPLACE TEMPORARY TABLE',
-    ];
-
-    createTableVariations.forEach((createTableVariation: string) => {
-      expect(
-        format(`
-        ${createTableVariation} mydataset.newtable (
-          a INT64 NOT NULL
-        )`)
-      ).toBe(dedent`
-        ${createTableVariation}
-          mydataset.newtable (a INT64 NOT NULL)
-      `);
-    });
-  });
-
   // Issue #279
   describe('supports FROM clause operators:', () => {
     it('UNNEST operator', () => {
@@ -301,4 +251,640 @@ describe('BigQueryFormatter', () => {
       `);
     });
   });
+
+  describe('BigQuery DDL Create Statements', () => {
+    const createCmds = {
+      schema: ['CREATE SCHEMA', 'CREATE SCHEMA IF NOT EXISTS'],
+      table: ['CREATE TABLE', 'CREATE TABLE IF NOT EXISTS', 'CREATE OR REPLACE TABLE'],
+      tempTable: [
+        'CREATE TEMP TABLE',
+        'CREATE TEMP TABLE IF NOT EXISTS',
+        'CREATE TEMPORARY TABLE',
+        'CREATE TEMPORARY TABLE IF NOT EXISTS',
+        'CREATE OR REPLACE TEMP TABLE',
+        'CREATE OR REPLACE TEMPORARY TABLE',
+      ],
+      snapshotTable: ['CREATE SNAPSHOT TABLE', 'CREATE SNAPSHOT TABLE IF NOT EXISTS'],
+      view: ['CREATE VIEW', 'CREATE OR REPLACE VIEW', 'CREATE VIEW IF NOT EXISTS'],
+      materializedView: [
+        'CREATE MATERIALIZED VIEW',
+        'CREATE OR REPLACE MATERIALIZED VIEW',
+        'CREATE MATERIALIZED VIEW IF NOT EXISTS',
+      ],
+      externalTable: [
+        'CREATE EXTERNAL TABLE',
+        'CREATE OR REPLACE EXTERNAL TABLE',
+        'CREATE EXTERNAL TABLE IF NOT EXISTS',
+      ],
+      function: ['CREATE FUNCTION', 'CREATE OR REPLACE FUNCTION', 'CREATE FUNCTION IF NOT EXISTS'],
+      tempFunction: [
+        'CREATE TEMP FUNCTION',
+        'CREATE OR REPLACE TEMP FUNCTION',
+        'CREATE TEMP FUNCTION IF NOT EXISTS',
+        'CREATE TEMPORARY FUNCTION',
+        'CREATE OR REPLACE TEMPORARY FUNCTION',
+        'CREATE TEMPORARY FUNCTION IF NOT EXISTS',
+      ],
+      tableFunction: [
+        'CREATE TABLE FUNCTION',
+        'CREATE OR REPLACE TABLE FUNCTION',
+        'CREATE TABLE FUNCTION IF NOT EXISTS',
+      ],
+      procedure: [
+        'CREATE PROCEDURE',
+        'CREATE OR REPLACE PROCEDURE',
+        'CREATE PROCEDURE IF NOT EXISTS',
+      ],
+      rowAccessPolicy: [
+        'CREATE ROW ACCESS POLICY',
+        'CREATE ROW ACCESS POLICY IF NOT EXISTS',
+        'CREATE OR REPLACE ROW ACCESS POLICY',
+      ],
+      searchIndex: ['CREATE SEARCH INDEX', 'CREATE SEARCH INDEX IF NOT EXISTS'],
+    };
+
+    createCmds.schema.forEach(createSchema => {
+      it(`Supports ${createSchema}`, () => {
+        const input = `
+          ${createSchema} mydataset
+            DEFAULT COLLATE 'und:ci'
+            OPTIONS(
+              location="us", labels=[("label1","value1"),("label2","value2")])`;
+        const expected = dedent`
+          ${createSchema}
+            mydataset
+          DEFAULT COLLATE
+            'und:ci' OPTIONS(
+              location = "us",
+              labels = [("label1", "value1"), ("label2", "value2")]
+            )
+        `;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    createCmds.table.concat(createCmds.tempTable).forEach((createTable: string) => {
+      it(`Supports ${createTable}`, () => {
+        const input = `
+          ${createTable} mydataset.newtable
+          (
+            x INT64 OPTIONS(description="desc1"),
+            y STRUCT<
+              a ARRAY<STRING> OPTIONS(description="desc2"),
+              b BOOL
+            >
+          )
+          PARTITION BY _PARTITIONDATE
+          OPTIONS(
+            expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC",
+            partition_expiration_days=1
+          )`;
+
+        // TODO: need to fix the formatting of y STRUCT<a ARRAY<STRING>OPTIONS(description ="desc2"), b BOOL>
+        const expected = dedent`
+          ${createTable}
+            mydataset.newtable (
+              x INT64 OPTIONS(description = "desc1"),
+              y STRUCT<a ARRAY<STRING>OPTIONS(description ="desc2"), b BOOL>
+            )
+          PARTITION BY
+            _PARTITIONDATE OPTIONS(
+              expiration_timestamp = TIMESTAMP "2025-01-01 00:00:00 UTC",
+              partition_expiration_days = 1
+            )`;
+
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    it(`Supports CREATE TABLE LIKE`, () => {
+      const input = `
+        CREATE TABLE mydataset.newtable
+        LIKE mydataset.sourcetable
+        AS (SELECT * FROM mydataset.myothertable)`;
+      const expected = dedent`
+        CREATE TABLE
+          mydataset.newtable LIKE mydataset.sourcetable AS (
+            SELECT
+              *
+            FROM
+              mydataset.myothertable
+          )`;
+
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports CREATE TABLE COPY`, () => {
+      const input = `
+        CREATE TABLE mydataset.newtable
+        COPY mydataset.sourcetable`;
+      const expected = dedent`
+        CREATE TABLE
+          mydataset.newtable COPY mydataset.sourcetable`;
+
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports CREATE TABLE CLONE`, () => {
+      const input = `
+        CREATE TABLE mydataset.newtable
+        CLONE mydataset.sourcetable`;
+      const expected = dedent`
+        CREATE TABLE
+          mydataset.newtable CLONE mydataset.sourcetable`;
+
+      expect(format(input)).toBe(expected);
+    });
+
+    createCmds.snapshotTable.forEach(createSnapshotTable => {
+      it(`Supports ${createSnapshotTable}`, () => {
+        const input = `
+          ${createSnapshotTable} mydataset.mytablesnapshot
+          CLONE mydataset.mytable`;
+        const expected = dedent`
+          ${createSnapshotTable}
+            mydataset.mytablesnapshot CLONE mydataset.mytable`;
+
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    createCmds.view.concat(createCmds.materializedView).forEach(createView => {
+      it(`Supports ${createView}`, () => {
+        const input = `
+          ${createView} my_dataset.my_view AS (
+          SELECT t1.col1, t1.col2 FROM my_dataset.my_table)`;
+        const expected = dedent`
+          ${createView}
+            my_dataset.my_view AS (
+              SELECT
+                t1.col1,
+                t1.col2
+              FROM
+                my_dataset.my_table
+            )`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    createCmds.externalTable.forEach(createExternalTable => {
+      it(`Supports ${createExternalTable}`, () => {
+        const input = `
+          ${createExternalTable} dataset.CsvTable
+          WITH PARTITION COLUMNS (
+            field_1 STRING,
+            field_2 INT64
+          )
+          OPTIONS(
+            format = 'CSV',
+            uris = ['gs://bucket/path1.csv']
+          )`;
+        const expected = dedent`
+          ${createExternalTable}
+            dataset.CsvTable
+          WITH PARTITION COLUMNS
+            (field_1 STRING, field_2 INT64) OPTIONS(format = 'CSV', uris = ['gs://bucket/path1.csv'])`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    createCmds.function.concat(createCmds.tempFunction).forEach(createFunction => {
+      it(`Supports ${createFunction}`, () => {
+        const input = `
+          ${createFunction} mydataset.myFunc(x FLOAT64, y FLOAT64)
+          RETURNS FLOAT64
+          AS (x * y);`;
+        const expected = dedent`
+          ${createFunction}
+            mydataset.myFunc (x FLOAT64, y FLOAT64) RETURNS FLOAT64 AS (x * y);`;
+        expect(format(input)).toBe(expected);
+
+        // Regression test for issue #309
+        expect(format(input, { aliasAs: 'always' })).toBe(expected);
+      });
+    });
+
+    it(`Supports CREATE FUNCTION - LANGUAGE js`, () => {
+      const input = dedent`
+        CREATE FUNCTION myFunc(x FLOAT64, y FLOAT64)
+        RETURNS FLOAT64
+        LANGUAGE js
+        AS r"""
+            return x*y;
+          """;`;
+      const expected = dedent`
+        CREATE FUNCTION
+          myFunc (x FLOAT64, y FLOAT64) RETURNS FLOAT64 LANGUAGE js AS r"""
+            return x*y;
+          """;`;
+      expect(format(input)).toBe(expected);
+    });
+
+    createCmds.tableFunction.forEach(createTableFunc => {
+      it(`Supports ${createTableFunc}`, () => {
+        const input = `
+          ${createTableFunc} mydataset.names_by_year(y INT64)
+          RETURNS TABLE<name STRING, year INT64>
+          AS (
+            SELECT year, name
+            FROM mydataset.mytable
+            WHERE year = y
+          )`;
+
+        // TODO: formatting for <name STRING, year INT64> can be improved
+        const expected = dedent`
+          ${createTableFunc}
+            mydataset.names_by_year (y INT64)
+          RETURNS TABLE
+            < name STRING,
+            year INT64 > AS (
+              SELECT
+                year,
+                name
+              FROM
+                mydataset.mytable
+              WHERE
+                year = y
+            )`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    // not correctly supported yet
+    createCmds.procedure.forEach(createProcedure => {
+      it(`Supports ${createProcedure}`, () => {
+        const input = `
+          ${createProcedure} myDataset.QueryTable()
+          BEGIN
+            SELECT * FROM anotherDataset.myTable;
+          END;`;
+        const expected = dedent`
+          ${createProcedure}
+            myDataset.QueryTable ()
+          BEGIN
+          SELECT
+            *
+          FROM
+            anotherDataset.myTable;
+
+          END;`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    createCmds.rowAccessPolicy.forEach(createRowAccessPolicy => {
+      it(`Supports ${createRowAccessPolicy}`, () => {
+        const input = `
+          ${createRowAccessPolicy} us_filter
+          ON mydataset.table1
+          GRANT TO ("group:abc@example.com", "user:hello@example.com")
+          FILTER USING (Region="US")`;
+        const expected = dedent`
+          ${createRowAccessPolicy}
+            us_filter ON mydataset.table1
+          GRANT TO
+            ("group:abc@example.com", "user:hello@example.com")
+          FILTER USING
+            (Region = "US")`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    it(`Supports CREATE CAPACITY`, () => {
+      const input = dedent`
+        CREATE CAPACITY admin_project.region-us.my-commitment
+        AS JSON """{
+            "slot_count": 100,
+            "plan": "FLEX"
+          }"""`;
+      const expected = dedent`
+        CREATE CAPACITY
+          admin_project.region-us.my-commitment
+        AS JSON
+          """{
+            "slot_count": 100,
+            "plan": "FLEX"
+          }"""`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports CREATE RESERVATION`, () => {
+      const input = dedent`
+        CREATE RESERVATION admin_project.region-us.prod
+        AS JSON """{
+            "slot_capacity": 100
+          }"""`;
+      const expected = dedent`
+        CREATE RESERVATION
+          admin_project.region-us.prod
+        AS JSON
+          """{
+            "slot_capacity": 100
+          }"""`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports CREATE ASSIGNMENT`, () => {
+      const input = dedent`
+        CREATE ASSIGNMENT admin_project.region-us.prod.my_assignment
+        AS JSON """{
+            "assignee": "projects/my_project",
+            "job_type": "QUERY"
+          }"""`;
+      const expected = dedent`
+        CREATE ASSIGNMENT
+          admin_project.region-us.prod.my_assignment
+        AS JSON
+          """{
+            "assignee": "projects/my_project",
+            "job_type": "QUERY"
+          }"""`;
+      expect(format(input)).toBe(expected);
+    });
+
+    createCmds.searchIndex.forEach(createSearchIndex => {
+      it(`Supports ${createSearchIndex}`, () => {
+        const input = `
+          ${createSearchIndex} my_index
+          ON dataset.my_table(ALL COLUMNS);`;
+        const expected = dedent`
+          ${createSearchIndex}
+            my_index ON dataset.my_table (ALL COLUMNS);`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+  });
+
+  describe('BigQuery DDL Alter Statements', () => {
+    const alterCmds = {
+      schema: ['ALTER SCHEMA', 'ALTER SCHEMA IF EXISTS'],
+      table: ['ALTER TABLE', 'ALTER TABLE IF EXISTS'],
+      column: ['ALTER COLUMN', 'ALTER COLUMN IF EXISTS'],
+      view: ['ALTER VIEW', 'ALTER VIEW IF EXISTS'],
+      materializedView: ['ALTER MATERIALIZED VIEW', 'ALTER MATERIALIZED VIEW IF EXISTS'],
+    };
+
+    alterCmds.schema.forEach(alterSchema => {
+      it(`Supports ${alterSchema} - SET DEFAULT COLLATE`, () => {
+        const input = `
+          ${alterSchema} mydataset
+          SET DEFAULT COLLATE 'und:ci'`;
+        const expected = dedent`
+          ${alterSchema}
+            mydataset
+          SET DEFAULT COLLATE
+            'und:ci'`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    it(`Supports ALTER SCHEMA - SET OPTIONS`, () => {
+      const input = `
+        ALTER SCHEMA mydataset
+        SET OPTIONS(
+          default_table_expiration_days=3.75
+          )`;
+      const expected = dedent`
+        ALTER SCHEMA
+          mydataset
+        SET OPTIONS
+          (default_table_expiration_days = 3.75)`;
+      expect(format(input)).toBe(expected);
+    });
+
+    alterCmds.table.forEach(alterTable => {
+      it(`Supports ${alterTable} - SET OPTIONS`, () => {
+        const input = `
+          ${alterTable} mydataset.mytable
+          SET OPTIONS(
+            expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
+          )`;
+        const expected = dedent`
+          ${alterTable}
+            mydataset.mytable
+          SET OPTIONS
+            (
+              expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
+            )`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    it(`Supports ALTER TABLE - ADD COLUMN`, () => {
+      const input = `
+        ALTER TABLE mydataset.mytable
+        ADD COLUMN col1 STRING,
+        ADD COLUMN IF NOT EXISTS col2 GEOGRAPHY`;
+      const expected = dedent`
+        ALTER TABLE
+          mydataset.mytable
+        ADD COLUMN
+          col1 STRING,
+        ADD COLUMN IF NOT EXISTS
+          col2 GEOGRAPHY`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports ALTER TABLE - RENAME TO`, () => {
+      const input = `
+        ALTER TABLE mydataset.mytable RENAME TO mynewtable`;
+      const expected = dedent`
+        ALTER TABLE
+          mydataset.mytable
+        RENAME TO
+          mynewtable`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports ALTER TABLE - DROP COLUMN`, () => {
+      const input = `
+        ALTER TABLE mydataset.mytable
+        DROP COLUMN col1,
+        DROP COLUMN IF EXISTS col2`;
+      const expected = dedent`
+        ALTER TABLE
+          mydataset.mytable
+        DROP COLUMN
+          col1,
+        DROP COLUMN IF EXISTS
+          col2`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports ALTER TABLE - SET DEFAULT COLLATE`, () => {
+      const input = `
+        ALTER TABLE mydataset.mytable
+        SET DEFAULT COLLATE 'und:ci'`;
+      const expected = dedent`
+        ALTER TABLE
+          mydataset.mytable
+        SET DEFAULT COLLATE
+          'und:ci'`;
+      expect(format(input)).toBe(expected);
+    });
+
+    alterCmds.column.forEach(alterColumn => {
+      it(`Supports ${alterColumn} - SET OPTIONS`, () => {
+        const input = `
+          ALTER TABLE mydataset.mytable
+          ${alterColumn} price
+          SET OPTIONS (
+            description="Price per unit"
+          )`;
+        const expected = dedent`
+          ALTER TABLE
+            mydataset.mytable
+          ${alterColumn}
+            price
+          SET OPTIONS
+            (description = "Price per unit")`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    it(`Supports ALTER COLUMN - DROP NOT NULL`, () => {
+      const input = `
+        ALTER TABLE mydataset.mytable
+        ALTER COLUMN price
+        DROP NOT NULL`;
+      const expected = dedent`
+        ALTER TABLE
+          mydataset.mytable
+        ALTER COLUMN
+          price
+        DROP NOT NULL`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports ALTER COLUMN - SET DATA TYPE`, () => {
+      const input = `
+        ALTER TABLE mydataset.mytable
+        ALTER COLUMN price
+        SET DATA TYPE NUMERIC`;
+      const expected = dedent`
+        ALTER TABLE
+          mydataset.mytable
+        ALTER COLUMN
+          price
+        SET DATA TYPE
+          NUMERIC`;
+      expect(format(input)).toBe(expected);
+    });
+
+    alterCmds.view.concat(alterCmds.materializedView).forEach(alterView => {
+      it(`Supports ${alterView} - SET OPTIONS`, () => {
+        const input = `
+          ${alterView} mydataset.myview
+          SET OPTIONS (
+            expiration_timestamp=TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
+          )`;
+        const expected = dedent`
+          ${alterView}
+            mydataset.myview
+          SET OPTIONS
+            (
+              expiration_timestamp = TIMESTAMP_ADD(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
+            )`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    it(`Supports ALTER BI_CAPACITY - SET OPTIONS`, () => {
+      const input = `
+        ALTER BI_CAPACITY my-project.region-us.default
+        SET OPTIONS(
+          size_gb = 250
+        )`;
+      const expected = dedent`
+        ALTER BI_CAPACITY
+          my-project.region-us.default
+        SET OPTIONS
+          (size_gb = 250)`;
+      expect(format(input)).toBe(expected);
+    });
+  });
+
+  describe('BigQuery DDL Drop Statements', () => {
+    const dropCmds = {
+      schema: ['DROP SCHEMA', 'DROP SCHEMA IF EXISTS'],
+      table: ['DROP TABLE', 'DROP TABLE IF EXISTS'],
+      snapshotTable: ['DROP SNAPSHOT TABLE', 'DROP SNAPSHOT TABLE IF EXISTS'],
+      externalTable: ['DROP EXTERNAL TABLE', 'DROP EXTERNAL TABLE IF EXISTS'],
+      view: ['DROP VIEW', 'DROP VIEW IF EXISTS'],
+      materializedViews: ['DROP MATERIALIZED VIEW', 'DROP MATERIALIZED VIEW IF EXISTS'],
+      function: ['DROP FUNCTION', 'DROP FUNCTION IF EXISTS'],
+      tableFunction: ['DROP TABLE FUNCTION', 'DROP TABLE FUNCTION IF EXISTS'],
+      procedure: ['DROP PROCEDURE', 'DROP PROCEDURE IF EXISTS'],
+      reservation: ['DROP RESERVATION', 'DROP RESERVATION IF EXISTS'],
+      assignment: ['DROP ASSIGNMENT', 'DROP ASSIGNMENT IF EXISTS'],
+    };
+
+    flatKeywordList(dropCmds).forEach(drop => {
+      it(`Supports ${drop}`, () => {
+        const input = `
+          ${drop} mydataset.name`;
+        const expected = dedent`
+          ${drop}
+            mydataset.name`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    it(`Supports DROP SCHEMA - CASCADE`, () => {
+      const input = `
+        DROP SCHEMA mydataset CASCADE`;
+      const expected = dedent`
+        DROP SCHEMA
+          mydataset CASCADE`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports DROP SCHEMA - RESTRICT`, () => {
+      const input = `
+        DROP SCHEMA mydataset RESTRICT`;
+      const expected = dedent`
+        DROP SCHEMA
+          mydataset RESTRICT`;
+      expect(format(input)).toBe(expected);
+    });
+
+    const dropSearchIndices = ['DROP SEARCH INDEX', 'DROP SEARCH INDEX IF EXISTS'];
+    dropSearchIndices.forEach(dropSearchIndex => {
+      it(`Supports ${dropSearchIndex}`, () => {
+        const input = `
+          ${dropSearchIndex} index2 ON mydataset.mytable`;
+        const expected = dedent`
+          ${dropSearchIndex}
+            index2 ON mydataset.mytable`;
+        expect(format(input)).toBe(expected);
+      });
+    });
+
+    it(`Supports DROP ROW ACCESS POLICY`, () => {
+      const input = `
+        DROP mypolicy ON mydataset.mytable`;
+      const expected = dedent`
+        DROP
+          mypolicy ON mydataset.mytable`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports DROP ROW ACCESS POLICY IF EXISTS`, () => {
+      const input = `
+        DROP IF EXISTS mypolicy ON mydataset.mytable`;
+      const expected = dedent`
+        DROP IF EXISTS
+          mypolicy ON mydataset.mytable`;
+      expect(format(input)).toBe(expected);
+    });
+
+    it(`Supports DROP ALL ROW ACCESS POLICIES`, () => {
+      const input = `
+        DROP ALL ROW ACCESS POLICIES ON table_name`;
+      const expected = dedent`
+        DROP ALL ROW ACCESS POLICIES
+          ON table_name`;
+      expect(format(input)).toBe(expected);
+    });
+  });
 });