Google BigQuery (GBQ) projects can use direct connect to reverse engineer entire schemas in seconds. Users can obtain the DDL from GBQ for manual upload or use the direct connect option.

Direct Connect using a private key file

Users can connect securely and directly to their GBQ account from SqlDBM to obtain the DDL for selected schemas. To do so, you will need to generate a private key file from the Google Cloud Console (GCC) using the instructions provided here.

Obtain a private key file

Use the steps below to generate a private key file from your GCC.

1. Select your GBQ project and head to the "Credentials" area of your GCC using the menu or the following link to begin the process.

https://console.cloud.google.com/apis/credentials

2. Click Create Credentials and select Service account (or go to step 4 if a service account already exists)

3. In your Service account details, add two new roles: BigQuery Data Viewer and BigQuery Job User. The latter is needed to run a query job to obtain table metadata from the INFORMATION_SCHEMA. BigQuery Admin role will also work but this may be a security concern.

Click "Done" to finish creating the Service Account

4. Click on the Service Account to open it and click on the "KEYS" tab.

4.1.

4.2 Click, "Create new key"

5. Select "JSON" and click "Create"

Save the generated file in a secure location.

Direct Connect

Once you have a private key file created, you can direct connect to pull DDL from your associated GBQ project. Direct connection parameters can be obtained by uploading the key file directly (a) or entering them separately into the UI (b).

a. Key File upload

Click "Drop your file" and select your private key file. You will be authenticated and taken to the list of datasets.

Skip to the "Import datasets" section below to select the datasets whose DDL you wish to import.

b. Provide key file parameters

Instead of uploading the key file, you can enter the necessary parameters directly into the UI. These include:

Client email – this can be found in the "Details" tab of your GCC Service Account or in the key file as the value for "client_email".
Private key – The full value contained in double quotes (including "-----BEGIN") in the key file for "private_key".
Project id – the project id from the GBQ console or the key file in the "project_id" parameter.

Click the "Connect to BigQuery" button to authenticate and import your datasets.

Import datasets

Select the desired datasets and press "Apply" to retrieve their DDL details.

The following options are also available:

Select all – highlight all datasets in your project
Only selected – filter and display only selected (checked) datasets

Hit the "Apply" button to retrieve the DDL.

Upload and review the DDL and import it into the project following the general "Reverse Engineering" instructions (related article link at the bottom).

Upload DDL from GBQ

DDL is obtained from GBQ by querying the INFORMATION_SCHEMA directly. Unfortunately, GBQ export settings for query results insert quotation marks around strings that may complicate DDL parsing. For this reason, we ask that you use the following query to generate a JSON file which you can upload straight into SqlDBM.

Clean DDL without GBQ-generated quotation marks can be uploaded or pasted into the reverse engineering screen as usual (see the "Reverse engineering" link at the end of this article).

Query the DDL

Please run the following query in GBQ to obtain the required JSON string. The query only includes the statements for supported object types. More information schema tables will be added to this code as new GBQ object support is unveiled.

Replace <DATASET_NAME> with the name of your dataset before executing.

SELECT STRING_AGG(statement, '\n') FROM (
  SELECT ddl AS statement, 1 AS sort_order FROM INFORMATION_SCHEMA.SCHEMATA WHERE schema_name = '<DATASET_NAME>'
  UNION ALL
  SELECT ddl AS statement, 2 AS sort_order FROM <DATASET_NAME>.INFORMATION_SCHEMA.TABLES
  UNION ALL
  SELECT ddl || ";" as statement, 3 as sort_order FROM <DATASET_NAME>.INFORMATION_SCHEMA.SEARCH_INDEXES  
  ORDER BY sort_order 
);

Export to JSON and upload to SqlDBM

Once you successfully run the query to obtain the DDL string, export it to a JSON file using the corresponding option in GBQ.

Once the file is saved, upload it into SqlDBM using the reverse engineering screen (more details at the link at the end of this article).

The end-to-end process can be seen in the following video: