FROM

Translation specification for the FROM clause in BigQuery to Snowflake

Description

This clause indicates the table or tables from which to retrieve rows, and specifies how to join those rows together to produce a single stream of rows for processing in the rest of the query (BigQuery FROM clause documentation)

FROM from_clause[, ...]

from_clause:
  from_item
  [ { pivot_operator | unpivot_operator } ]
  [ tablesample_operator ]

from_item:
  {
    table_name [ as_alias ] [ FOR SYSTEM_TIME AS OF timestamp_expression ] 
    | { join_operation | ( join_operation ) }
    | ( query_expr ) [ as_alias ]
    | field_path
    | unnest_operator
    | cte_name [ as_alias ]
  }

as_alias:
  [ AS ] alias

Sample Source Patterns

Setup data

BigQuery

Query

CREATE TABLE test.exampleTable
(
	col1 INTEGER,
	col2 STRING(10),
);

INSERT INTO test.exampleTable VALUES (11, 'A');
INSERT INTO test.exampleTable VALUES (4, 'C');
INSERT INTO test.exampleTable VALUES (6, 'B');
INSERT INTO test.exampleTable VALUES (15, 'D');
INSERT INTO test.exampleTable VALUES (8, 'D');
INSERT INTO test.exampleTable VALUES (9, 'A');
INSERT INTO test.exampleTable VALUES (10, 'B');
INSERT INTO test.exampleTable VALUES (3, 'B');

Snowflake

Query

CREATE TABLE test.exampleTable
(
	col1 INTEGER,
	col2 VARCHAR(10),
);

INSERT INTO test.exampleTable VALUES (11, 'A');
INSERT INTO test.exampleTable VALUES (4, 'C');
INSERT INTO test.exampleTable VALUES (6, 'B');
INSERT INTO test.exampleTable VALUES (15, 'D');
INSERT INTO test.exampleTable VALUES (8, 'D');
INSERT INTO test.exampleTable VALUES (9, 'A');
INSERT INTO test.exampleTable VALUES (10, 'B');
INSERT INTO test.exampleTable VALUES (3, 'B');

TABLESAMPLE

The results of this clause are not deterministic but functionally equivalent in both languages

BigQuery

Query

SELECT * FROM test.exampleTable TABLESAMPLE SYSTEM (50 PERCENT);

Result

The results of this query are not deterministic

Snowflake

Query

SELECT * FROM test.exampleTable TABLESAMPLE SYSTEM (50);

Result

The results of this query are not deterministic

FOR SYSTEM_TIME

BigQuery

Query

INSERT INTO test.exampleTable VALUES (12, 'F');

SELECT * FROM test.exampleTable FOR SYSTEM_TIME AS OF TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 MINUTE) ORDER BY 1;

Result

| COL1 | COL2 |
+------+------+
| 3    | B    |
+------+------+
| 4    | C    |
+------+------+
| 6    | B    |
+------+------+
| 8    | D    |
+------+------+
| 9    | A    |
+------+------+
| 10   | B    |
+------+------+
| 11   | A    |
+------+------+
| 15   | D    |
+------+------+

Snowflake

Query

INSERT INTO test.exampleTable VALUES (12, 'F');

SELECT * FROM exampleTable AT (TIMESTAMP => CURRENT_TIMESTAMP() - INTERVAL '5 MINUTE') ORDER BY 1;

Result

| COL1 | COL2 |
+------+------+
| 3    | B    |
+------+------+
| 4    | C    |
+------+------+
| 6    | B    |
+------+------+
| 8    | D    |
+------+------+
| 9    | A    |
+------+------+
| 10   | B    |
+------+------+
| 11   | A    |
+------+------+
| 15   | D    |
+------+------+

Known Issues

No known issues.

Related EWIs

No related EWIs.