Skip to content

Commit 66904a6

Browse files
ebyhrebyhr
committed
Add view_definition system table for BigQuery view
1 parent 2b5f7ff commit 66904a6

File tree

3 files changed

+105
-0
lines changed

3 files changed

+105
-0
lines changed

‎docs/src/main/sphinx/connector/bigquery.rst‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -161,6 +161,13 @@ BigQuery Trino Notes
161161
``TIMESTAMP`` ``TIMESTAMP_WITH_TIME_ZONE`` Time zone is UTC
162162
============= ============================ =============================================================================================================
163163

164+
System tables
165+
-------------
166+
167+
For each Trino table which maps to BigQuery view there exists a system table which exposes BigQuery view definition.
168+
Given a BigQuery view ``customer_view`` you can send query
169+
``SELECT * customer_view$view_definition`` to see the SQL which defines view in BigQuery.
170+
164171
FAQ
165172
---
166173

‎plugin/trino-bigquery/src/main/java/io/trino/plugin/bigquery/BigQueryMetadata.java‎

Lines changed: 72 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -20,6 +20,7 @@
2020
import com.google.cloud.bigquery.TableDefinition;
2121
import com.google.cloud.bigquery.TableId;
2222
import com.google.cloud.bigquery.TableInfo;
23+
import com.google.cloud.bigquery.ViewDefinition;
2324
import com.google.common.collect.ImmutableList;
2425
import com.google.common.collect.ImmutableMap;
2526
import com.google.common.collect.ImmutableSet;
@@ -33,23 +34,30 @@
3334
import io.trino.spi.connector.ConnectorTableHandle;
3435
import io.trino.spi.connector.ConnectorTableMetadata;
3536
import io.trino.spi.connector.ConnectorTableProperties;
37+
import io.trino.spi.connector.ConnectorTransactionHandle;
3638
import io.trino.spi.connector.Constraint;
3739
import io.trino.spi.connector.ConstraintApplicationResult;
40+
import io.trino.spi.connector.InMemoryRecordSet;
3841
import io.trino.spi.connector.LimitApplicationResult;
3942
import io.trino.spi.connector.NotFoundException;
4043
import io.trino.spi.connector.ProjectionApplicationResult;
44+
import io.trino.spi.connector.RecordCursor;
4145
import io.trino.spi.connector.SchemaTableName;
4246
import io.trino.spi.connector.SchemaTablePrefix;
47+
import io.trino.spi.connector.SystemTable;
4348
import io.trino.spi.connector.TableNotFoundException;
4449
import io.trino.spi.expression.ConnectorExpression;
4550
import io.trino.spi.predicate.TupleDomain;
51+
import io.trino.spi.type.Type;
52+
import io.trino.spi.type.VarcharType;
4653

4754
import javax.inject.Inject;
4855

4956
import java.util.List;
5057
import java.util.Map;
5158
import java.util.Optional;
5259
import java.util.Set;
60+
import java.util.function.Function;
5361

5462
import static com.google.cloud.bigquery.TableDefinition.Type.TABLE;
5563
import static com.google.cloud.bigquery.TableDefinition.Type.VIEW;
@@ -66,6 +74,7 @@ public class BigQueryMetadata
6674
static final int NUMERIC_DATA_TYPE_PRECISION = 38;
6775
static final int NUMERIC_DATA_TYPE_SCALE = 9;
6876
static final String INFORMATION_SCHEMA = "information_schema";
77+
private static final String VIEW_DEFINITION_SYSTEM_TABLE_SUFFIX = "$view_definition";
6978

7079
private final BigQueryClient bigQueryClient;
7180
private final String projectId;
@@ -160,6 +169,32 @@ public ConnectorTableMetadata getTableMetadata(ConnectorSession session, Connect
160169
return new ConnectorTableMetadata(schemaTableName, columns);
161170
}
162171

172+
@Override
173+
public Optional<SystemTable> getSystemTable(ConnectorSession session, SchemaTableName tableName)
174+
{
175+
if (isViewDefinitionSystemTable(tableName)) {
176+
return getViewDefinitionSystemTable(tableName, getViewDefinitionSourceTableName(tableName));
177+
}
178+
return Optional.empty();
179+
}
180+
181+
private Optional<SystemTable> getViewDefinitionSystemTable(SchemaTableName viewDefinitionTableName, SchemaTableName sourceTableName)
182+
{
183+
TableInfo tableInfo = getBigQueryTable(sourceTableName);
184+
if (tableInfo == null || !(tableInfo.getDefinition() instanceof ViewDefinition)) {
185+
throw new TableNotFoundException(viewDefinitionTableName);
186+
}
187+
188+
List<ColumnMetadata> columns = ImmutableList.of(new ColumnMetadata("query", VarcharType.VARCHAR));
189+
List<Type> types = columns.stream()
190+
.map(ColumnMetadata::getType)
191+
.collect(toImmutableList());
192+
Optional<String> query = Optional.ofNullable(((ViewDefinition) tableInfo.getDefinition()).getQuery());
193+
Iterable<List<Object>> propertyValues = ImmutableList.of(ImmutableList.of(query.orElse("NULL")));
194+
195+
return Optional.of(createSystemTable(new ConnectorTableMetadata(sourceTableName, columns), constraint -> new InMemoryRecordSet(types, propertyValues).cursor()));
196+
}
197+
163198
@Override
164199
public Map<String, ColumnHandle> getColumnHandles(ConnectorSession session, ConnectorTableHandle tableHandle)
165200
{
@@ -311,4 +346,41 @@ private static boolean containSameElements(Iterable<? extends ColumnHandle> firs
311346
{
312347
return ImmutableSet.copyOf(first).equals(ImmutableSet.copyOf(second));
313348
}
349+
350+
private static boolean isViewDefinitionSystemTable(SchemaTableName table)
351+
{
352+
return table.getTableName().endsWith(VIEW_DEFINITION_SYSTEM_TABLE_SUFFIX) &&
353+
(table.getTableName().length() > VIEW_DEFINITION_SYSTEM_TABLE_SUFFIX.length());
354+
}
355+
356+
private static SchemaTableName getViewDefinitionSourceTableName(SchemaTableName table)
357+
{
358+
return new SchemaTableName(
359+
table.getSchemaName(),
360+
table.getTableName().substring(0, table.getTableName().length() - VIEW_DEFINITION_SYSTEM_TABLE_SUFFIX.length()));
361+
}
362+
363+
private static SystemTable createSystemTable(ConnectorTableMetadata metadata, Function<TupleDomain<Integer>, RecordCursor> cursor)
364+
{
365+
return new SystemTable()
366+
{
367+
@Override
368+
public Distribution getDistribution()
369+
{
370+
return Distribution.SINGLE_COORDINATOR;
371+
}
372+
373+
@Override
374+
public ConnectorTableMetadata getTableMetadata()
375+
{
376+
return metadata;
377+
}
378+
379+
@Override
380+
public RecordCursor cursor(ConnectorTransactionHandle transactionHandle, ConnectorSession session, TupleDomain<Integer> constraint)
381+
{
382+
return cursor.apply(constraint);
383+
}
384+
};
385+
}
314386
}

‎plugin/trino-bigquery/src/test/java/io/trino/plugin/bigquery/TestBigQueryIntegrationSmokeTest.java‎

Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -153,6 +153,32 @@ public void testRepeatCountAggregationView()
153153
executeBigQuerySql(client, "DROP VIEW " + viewName);
154154
}
155155

156+
@Test
157+
public void testViewDefinitionSystemTable()
158+
{
159+
BigQuery client = createBigQueryClient();
160+
161+
String schemaName = "test";
162+
String tableName = "views_system_table_base_" + randomTableSuffix();
163+
String viewName = "views_system_table_view_" + randomTableSuffix();
164+
165+
executeBigQuerySql(client, format("DROP TABLE IF EXISTS %s.%s", schemaName, tableName));
166+
executeBigQuerySql(client, format("DROP VIEW IF EXISTS %s.%s", schemaName, viewName));
167+
executeBigQuerySql(client, format("CREATE TABLE %s.%s (a INT64, b INT64, c INT64)", schemaName, tableName));
168+
executeBigQuerySql(client, format("CREATE VIEW %s.%s AS SELECT * FROM %s.%s", schemaName, viewName, schemaName, tableName));
169+
170+
assertEquals(
171+
computeScalar(format("SELECT * FROM %s.\"%s$view_definition\"", schemaName, viewName)),
172+
format("SELECT * FROM %s.%s", schemaName, tableName));
173+
174+
assertQueryFails(
175+
format("SELECT * FROM %s.\"%s$view_definition\"", schemaName, tableName),
176+
format("Table '%s.%s\\$view_definition' not found", schemaName, tableName));
177+
178+
executeBigQuerySql(client, format("DROP TABLE %s.%s", schemaName, tableName));
179+
executeBigQuerySql(client, format("DROP VIEW %s.%s", schemaName, viewName));
180+
}
181+
156182
private static void executeBigQuerySql(BigQuery bigquery, String query)
157183
{
158184
QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query)

0 commit comments

Comments
 (0)