Google BigQuery

BigQuery is a fully managed enterprise data warehouse that helps you manage and analyze your data with built-in features like machine learning, geospatial analysis, and business intelligence. Google BigQuery's serverless architecture lets you use SQL queries.

DeZign for Databases has been adapted to enable data modeling for Google BigQuery projects/databases. Our software connects to your BigQuery project and reads out the structure of the database. After the connection with the BigQuery database has been set up, tables, views, primary key constraints, foreign key constraints, indexes, procedures and functions are read out. DeZign for Databases automatically creates a data model from this that you can adjust. DeZign for Databases supports the use of specific BigQuery data types such as nested complex data types (STRUCT). After you have adjusted the data model, you can compare your data model within the tool with your original BigQuery project in the cloud. The tool can then generate intelligent BigQuery specific alteration code to modify your database in the cloud.

In summary:

Reverse engineering: Connect to a BigQuery project and import your database structure (a diagram is automatically created for each dataset).
Forward engineering: Generate a complete (new) Google BigQuery database.
Bi-directional synchronization: Intelligently generate BigQuery specific code to modify your database in the cloud. Connect to the BigQuery database to keep your data model up to date if it has changed outside of the software.

Connect to a BigQuery project

To access a Google BigQuery project, DeZign for Databases supports access through the Simba ODBC Driver in combination with a Google Service Account associated with your Google Cloud project, or with a custom ODBC connection string. When connecting with a Google Service Account, the credentials are loaded from a file created in the Google IAM & Admin console by your administrator.

During creation (or modification) of your Google service account you need to specify roles which will allow DeZign for Databases to reverse engineer BigQuery databases. DeZign for Databases uses the INFORMATION_SCHEMA views to read out the database structure. Minimum permissions that your service account needs to have are:

BigQuery Metadadata Viewer - to be able to read out metadata.
BigQuery Job User - to be able to run queries that reads meatadata.

Connection details

Connection method: Select one of the following options:

Google service account - Simba ODBC Driver
Use this connection method when you have installed the Simba BigQuery ODBC driver and want to connect using a Google Service Account.
ODBC connection string
Use this connection method when you want to connect using a DSN (data source name), a File DSN, or a DSN-less connection using a connection string.

Key file path: Link to the Service Account key file (.json) generated in the Google IAM & Admin console.

Project id: Enter the ID of your BigQuery project.

Dataset id: Enter the name/id of the project's dataset you wish to load. If you leave this field empty then all datasets in the project will be loaded (a diagram is created for each individual dataset).

User name: Fill out this field when you have used the place holder %username% in your connection string.

Password: Fill out this field when you have used the place holder %password% in your connection string.

Connection string: If you want to use a connection string for connecting to a database then you must specify either a DSN (data source name), a File DSN, or a DSN-less connection in the string. The difference is whether you use the DSN=, FILEDSN=, or the DRIVER= keyword in the connection string, as described in the ODBC specification.

A DSN or FILEDSN connection string tells the driver where to find the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the data source.

The DSN connection string has the following form: DSN=data_source_name[;attribute=value[;attribute=value]...]

The FILEDSN connection string has the following form: FILEDSN=filename.dsn[;attribute=value[;attribute=value]...]

The DSN-less connection string specifies a driver instead of a data source. All connection information must be entered in the connection string. The information is not stored in a data source.

The DSN-less connection string has the following form: DRIVER=[{]driver_name[}][;attribute=value[;attribute=value]...]

The connection string is stored in the project. If you don't want to store the user name and/or password in the connection string then you can use the place holders %username% and/or %password% in the connection string. In that case fill out the user name and password in the entry fields.

Example: DRIVER=MyDriver;UID=%username%;PWD=%password%;HOST=myserver;PORT=27017;DB=db1;

Specifications

Metadata

DeZign for Databases reads the following metadata from your Google BigQuery database:

Tables
- Table name
- Table comments (filtered out of table options)
- Table options (description is deleted from the table options and added to the description property)
- Columns
  - Column name
  - Data type (including STRUCT/Record)
  - Nullability
  - Character set
  - Collation
  - Column comment (filtered out of column options)
  - Nested columns
- Primary key constraints
  - Primary key constraint name
  - Constraint columns
- Foreign key constraints
  - Foreign key constraint name
  - Constraint columns
- Indexes
  - Index name
  - Index type (VECTOR, SEARCH)
  - Index columns
Views
- View name
- Code/script
- View comments (filtered out of view options)
Procedure/Functions
- Procedure/function name
- Code/script
- Procedure/function comments (filtered out of options)

Supported features

Reverse engineering with a database connection	Yes
Reverse engineering by parsing a SQL DDL file	Yes
Forward engeneering (generating database)	Yes
Update model after comparing the model to the database	Yes
Update database (generate alter database scripts) after comparing the model to the database	Yes