Overview

The server models the Google BigQuery data defined within your Google Cloud organization. It can be further refined to retrieve data from a specified project and dataset.

Once connected, the server mimics the hierarchy in Google BigQuery by modeling each project in Google BigQuery as its own catalog. Within a catalog, the datasets in the corresponding project are modeled as individual schemas. The tables and views within a dataset are modeled as tables and views within the respective schema. Additionally, the server includes a static 'CData' catalog, containing a static 'Google BigQuery' schema, which contains information found outside the Google BigQuery hierarchy.

Catalogs

Each project in Google BigQuery is modeled as a catalog. For instance, if you have two projects in Google Cloud which have the IDs 'test-project' and 'cloud-data', then the Google BigQuery server will show two catalogs, one named 'test-project' and the other named 'cloud-data'. The catalog may be specified in either the ProjectId connection property or the fully qualified table name.

Additionally, the data model contains a single static 'CData' catalog, which contains data on client-side views. Details on how to use it will be discussed further in the next section.

Schemas

Each dataset in a project is modeled as a schema in the project's corresponding catalog. For example, if the project 'test-project' has two datasets, 'DatasetTest' and 'BusinessData', then the 'test-project' catalog will have the schemas 'DatasetTest' and 'BusinessData'. The schema may be specified in either the DatasetId connection property or the fully qualified table name.

The 'CData' catalog contains one static 'Google BigQuery' schema. This schema contains client-side views such as 'PartitionsList' and 'PartitionsValues'. These client-side views can be accessed by setting catalog to 'CData' and schema to 'Google BigQuery'. For instance:

SELECT * FROM [CData].[Google BigQuery].PartitionsList

Tables

Tables are retrieved dynamically from Google BigQuery. For instance, if the table 'Accounts' is in the project 'test-project' and dataset 'BusinessData', then it can be queried as follows:

SELECT * FROM [test-project].[BusinessData].Accounts

By setting the ProjectId and DatasetId properties, a connection can be configured to retrieve data from a specific project and dataset so these do not need to be included in the query. For instance, if ProjectId is set to 'test-project' and DatasetId is set to 'BusinessData', then the query only needs to contain the table name, as shown below.

SELECT * FROM Accounts

Views

Views are client-side tables that cannot be modified. The server uses these to report metadata about the Google BigQuery projects and datsets it is connected to. The following views are included with the server:

Table	Description
Datasets	Lists all the accessible datasets for a given project.
PartitionsList	Lists the partitioning definitions for tables.
PartitionsValues	Lists the partitioning ranges for tables.
Projects	Lists all the projects for the authorized user.

The server also supports server-side views defined within Google BigQuery. These views can be used in SELECT statements the same way as tables. However, view schemas can easily become out of date and the server must refresh them. See RefreshViewSchemas for details.

Stored Procedures

Stored Procedures are actions that are invoked via SQL queries. The server uses these to manage Google BigQuery tables and jobs and to perform OAuth operations.

In addition to the client-side stored procedures offered by the server, support is also provided for server-side stored procedures defined in Google BigQuery. The server supports both CALL and EXEC using the procedure's parameter names.

Note: The server only supports IN parameters and resultset return values.

CALL `psychic-valve-137816`.Northwind.MostPopularProduct()
CALL `psychic-valve-137816`.Northwind.GetStockedValue(24, 0.75)

EXEC `psychic-valve-137816`.Northwind.MostPopularProduct
EXEC `psychic-valve-137816`.Northwind.GetStockedValue productId = 24, discountRate = 0.75