Google BigQuery Support
Go Up to Supported Database Platforms
ER/Studio's Google BigQuery data warehouse tool support allows you to reverse engineer a live database, import BigQuery SQL files, and generate both SQL DDL code for new databases and also ALTER scripts through the Compare/Merge tool. This functionality is similar to the MongoDB and JSON file support, where ER/Studio transforms between relational Logical models to hierarchical Physical models, repeatably handling nested objects to take advantage of BigQuery performance boosts through denormalization.
Contents
Using nested field structures
ERDA supports the nested field structure of Google BigQuery objects by offering:
- a Nested Object option in the Objects drop-down list
- Table Objects (Tables) and Fields (Columns) containers in the tree view
Using the Table Editor
ERDA leverages existing capabilities for generation from and compare/merge of hierarchical structures with Logical models. Use the Table Editor to easily create and manage your nested objects.
If you attempt to create a new object with the same name as an existing object, ERDA allows you to re-use the existing object, create a new object, or skip creating a nested object. Note that when you create a nested object and click Save, the object is saved temporarily. You must save the changes to the entire table by clicking OK before the new nested object is created.
Working with Data Types and Mappings
ERDA includes Data Types and Mapping options specific to Google BigQuery. In addition, an optional Array check box is available in the Data Type properties area. For more information about this feature, see Customizing Datatype Mappings.
Using the Physical Platforms list
Google BigQuery is available for the following flows using the Physical Platforms list:
- Reverse engineering using Simba ODBC DSN
- Comparing against an SQL file
- Adding a New Physical Model
- Generating a Physical Model
- Changing the Database Platform
- Importing a Database
Using the Table Editor
The Table Editor supports Google BigQuery in the following areas:
- the Clustering Keys option in the Compare Options tab
- the optional Array check box in the Data Type properties area
- the Storage tab, which supports partitioning and allows users to select Partition By Ingestion Time, further specifying by day, hour, month, or year
- the Clustering tab, which allows users to select up to four columns to display
Using the View Editor
In the View Editor, the Google BigQuery CREATE VIEW statement supports the OPTIONS clause. The Options tab allows you to set view options such as a label and an expiration date and time.
Use the following formats, noting that VALUE is a constant expression containing only literals, query parameters, and scalar functions:
| Name | Value | Example |
|---|---|---|
| expiration_timestamp | TIMESTAMP | expiration_timestamp=TIMESTAMP "2025-01-01 00:00:00 UTC" |
| friendly_name | STRING | friendly_name="my_view" |
| description | STRING | description="a view that expires in 2025" |
| labels | ARRAY<STRUCT<STRING,STRING>> | labels=[("org_unit","development")] |
Using the COM API
The COM API allows macro access to support Google BigQuery settings.
| Property | Type | Example |
|---|---|---|
| Clustering Keys | String | Get() - ent.ClusterByColumns() gives the list of selected columns for clustering.
Set() - ent.ClusterByColumns() = "list_of_columns" |
| Expiration Timestamp | STRING | Get() - ent.GoogleBigqueryExpirationTimestamp gives the value.
Set() - ent.GoogleBigqueryExpirationTimestamp = "new_value" sets the value. |
| Partition Expiration Days | INTEGER | Get() - ent.GoogleBigqueryExpirationTimestamp gives the value.
Set() - ent.GoogleBigqueryExpirationTimestamp= new_number sets the value. |
| Kms Key Name | STRING | Get() - ent.GoogleBigqueryKmsKeyName gives the value.
Set() - ent.GoogleBigqueryKmsKeyName = "new_value" sets the value. |
| Labels | STRING | Get() - ent.GoogleBigqueryLabels gives the value.
Set() - ent.GoogleBigqueryLabels = "new_value" sets the value. |
| Partition | STRING | It has three high-level classifications: NO_PARTITION, PARTITION_BY_INGESTION_TIME, and PARTITION_BY_FIELD.
For Partition by field, it also contains the column field. For example: PARTITION_BY_FIELD,dummy_date. |
| Partition Filter | STRING | It has two values. "Y" for boolean True and " " for boolean False.
Get() - ent.GoogleBigqueryPartitionFilter gives the value. Set() - ent.GoogleBigqueryPartitionFilter = "new_value" sets the value. |
| Partition Type | STRING | It has four values. "BY_DAY", "BY_HOUR", "BY_MONTH", and "BY_YEAR"
Get() - ent.GoogleBigqueryPartitionType gives the value. Set() - ent.GoogleBigqueryPartitionType = "new_value" sets the value. |
| Range Fields | STRING | All three fields are passed through a string. In this example: "2,4,1", the number 2 is rangeStart, 4 is rangeEnd, and 1 is rangeInterval
Get() - ent.GoogleBigqueryRangeFields gives the value. Set() - ent.GoogleBigqueryRangeFields = "new_value" sets the value. |
Using Google BigQuery in wizards
The following wizards support Google BigQuery:
The Materialized Views wizard and object type support Google BigQuery. The Materialized Views object is available in the Data Model Explorer for you to view and manage options reflected in generated DDL. Materialized views help improve response time and performance by periodically caching query results and reading only the delta changes from the base tables.
The procedure, function, and schema objects support Google BigQuery physical models. Users can forward and reverse-engineer, import and generate SQL for, and add/edit/delete procedures in Google BigQuery physical models using the added procedures, functions, and schema object support.
Table inheritance supports Google BigQuery. Google BigQuery supports table inheritance and the Generate Physical Model wizard includes two options for resolving Subtype relationships using the Relationship Type area of the Properties tab:
- Identifying Relationship
- Inheritance Relationship
Note: The inheriting table cannot introduce new columns and remains identical to the inherited table's columns. In addition, Google BigQuery does not allow inheritance of multiple tables nor self-inheritance.
An additional property exists on the physical relationship line of type Inherits that allows you to specify whether it is a LIKE or COPY relationship. If a table is related to another table via an Inherits relationship, then the Column list is ignored during DL generation. In a COPY-type relation, clustering and partitioning options are disabled in the Table Editor.