Preparing for Deployment of Hyper-Q on Google BigQuery

Before you deploy Hyper-Q, verify that the Google Cloud environment meets the following requirements.

Hyper-Q Software and License

Datometry makes available the following items necessary for installation. These files need to be copied to the Hyper-Q VM for installation.

Table 1. Hyper-Q Software and License

Hyper-Q Filename	Description
`hyperq-version-cloud_database.tar.gz`	RPM packages with which to install the Hyper-Q software.
`dtm.lic`	License file specific to your subscription.

Google Cloud BigQuery Project

Ensure you have an active Google Cloud project, and have available the project name, project ID, and project number. To learn more, see Projects in the Google Cloud documentation.

Google Cloud Credentials and Privileges

You must have the following privileges within the Google Cloud project and credentials on the BigQuery instance to which you are connecting Hyper-Q. You need these credentials to install, not to operate, the system. The installation automatically creates an account with which to operate Hyper-Q.

Note: Avoid using highly privileged user IDs such as an overall Google Cloud administrative account. Password requirements are as recommended by the customer.

Google Cloud Permissions within the Project

The person performing the installation must have an account in the Google Cloud project with permissions to provision virtual machines using Compute Engine within the project. To learn more, see the Google Cloud Compute Engine documentation.

BigQuery Permissions

You must create several database objects in the BigQuery project. Therefore, you must have sufficient permissions to perform the following operations in the BigQuery project that you are connecting to Hyper-Q:

Create a schema
Create a user
Create a role
Add user to the newly created role

Create the Metadata Store Schema

Metadata Store contains annotations, a type of metadata for name resolution that identifies complex SQL constructs that Hyper-Q emulates. Hyper-Q stores this metadata in a separate schema named __DTM_MDSTORE. The __DTM_MDSTORE schema contains information such as if a column is case sensitive or not, and the date format to use with a given column among many other characteristics of the SQL being emulated. Hyper-Q references this metadata when emulating queries from Teradata clients for the use with Google BigQuery.

Using the Google Cloud Console, create the following objects in the BigQuery project. The Cloud Console provides a graphical interface that you can use to create and manage BigQuery resources and run SQL queries. You issue this SQL statement to BigQuery from the Cloud Console which routes the statement through the Google Cloud load balancer and then through a Hyper-Q VM.

Note

Be aware that schema and table names in BigQuery are quoted identifiers and must be enclosed by backtick (`) characters, not single quotes.

Important

You must grant read access to every user of MDSTOR_TBL_

CREATE SCHEMA `__DTM_MDSTORE`;
CREATE TABLE `__DTM_MDSTORE`.`MDSTORE_TBL` (
schemaname string,
objectname string,
colname string,
propname string,
propvalue string,
seqno int64
);

Create a Dedicated User to Access Metadata Store

Metadata Store is maintained and accessed using a specific user ID. The user ID to be used for this purpose is set using the "mdstore".user property in the dtm.ini configuration file. To learn more, see Example Hyper-Q Configuration File for Google BigQuery.

In order to set up a dedicated user ID for accessing Metadata Store you must create a login with a valid password. Hyper-Q uses Google Identity and Access Management (IAM) to authenticate client applications connecting to BigQuery.

The steps to create a Hyper-Q user account for use with BigQuery are:

Create an OAuth 2.0 client ID.
Generate a refresh token.
Create client credentials.
Ensure the user you create has the BigQuery IAM role roles/bigquery.user. To learn more, see BigQuery permissions and predefined IAM roles in the Google documentation.

Create an OAuth 2.0 Client ID

To use OAuth 2.0 in your application, you need create an OAuth 2.0 client ID, which your application uses when requesting an OAuth 2.0 access token.

To learn more about OAuth 2.0 and configuring it for use with your Google BigQuery project, see Setting up OAuth 2.0 in the Google documentation.

Open a web browser and go to the Google Cloud Platform Console.
From the projects list, select a project or create a new one.
If the APIs & Services page is not already open, open the console left-side menu and select APIs & Services.
Click Credentials from the APIs & Services page.
Click + Create Credentials, then select OAuth client ID.
Select the appropriate application type for your project from the Application type drop-down menu.
- (Recommended) If you are using the Datometry dtm-bq-auth utility to create a refresh token, select Desktop app. To learn about the dtm-bq-auth utility, see Generate a Refresh Token.
- (Alternative) If your organization uses a web application to create credentials using an OAuth client ID, select Web application.
  Important
  If your organization uses a web application to create credentials using an OAuth client ID, ensure you understand how to use such an application in conjunction with Hyper-Q.
If this is your first time creating a client ID, you can also configure your consent screen by clicking Consent Screen.
You must configure an OAuth consent screen before using an OAuth 2.0 client ID. The following steps provide an overview of configuring the consent screen for use with your Hyper-Q deployment. Refer to the Google documentation for specific instructions, as the details for your Google BigQuery project may differ. To learn more, see Setting up your OAuth consent screen in the Google documentation.
To set up your project's consent screen and request verification:
1. Go to the Google API Console OAuth consent screen page.
2. Add required information such as the user type, product name, and support email address.
3. Click Add Scope.
  On the dialog that appears, select the scopes to use for the project. Scopes define the type of user data you'll ask to access on behalf of the user in the consent screen.
4. When you're finished adding information to the OAuth consent screen, click Submit for verification.
5. A Verification required window displays.
6. Add the justification for the scopes you selected for use with the project, a contact email address, and any additional information that can help with verification, then click Submit.
7. When a project goes through verification, its current status displays under Verification status.
  To learn about the verification process, see the Google Support article OAuth API verification FAQs.
Click Create client ID.
Save the OAuth2 client ID and client secret displayed for the application.

You use the OAuth2 client ID is subsequent steps in the installation procedure, so make note of it for future reference.

Generate a Refresh Token

OAuth2 access expires after a limited time. An OAuth2 refresh token allows your application to automatically obtain new access tokens. To generate a refresh token, you use the Datometry dtm-bq-auth utility.

Download the dtm-bq-auth utility.
You can download the dtm-bq-auth utility for both Linux and Windows at the following URLs:
- https://downloads.datometry.com/tools/dtm-bq-auth/linux/
- https://downloads.datometry.com/tools/dtm-bq-auth/windows/
You can run the dtm-bq-auth utility from any environment, including those separate from the Hyper-Q environment on Google Cloud. You can use either the Linux or Microsoft Windows version of the utility.

Execute the dtm-bq-auth utility from the command line, and provide the OAuth client ID and OAuth2 client secret which you obtained in the previous procedure.

./dtm-bq-auth
Please type the value for the setting 'ClientID' [? for help] oauth2_client_id
Please type the value for the setting 'ClientSecret' [? for help] oauth2_client_secret
Please type the value for the setting 'Filepath' ./refresh_token_file

where:

Parameter	Description
oauth2_client_id	The OAuth client ID.
oauth2_client_secret	The OAuth2 client secret. In this context, the client secret is obviously not treated as a secret.
filepath	The directory in which to output the newly generated refresh token.

The following example executes the dtm-bq-auth utility and provides the OAuth client ID and client secret in response to the prompts.

./dtm-bq-auth
Please type the value for the setting 'ClientSecret' [? for help] 126559720376-eqc0ncv50fg38i061kf5kh3b58m865dk.apps.googleusercontent.com
Please type the value for the setting 'ClientID' [? for help] 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
? Please type the value for the setting 'Filepath' ./refreshToken

Save the value of the refresh token you receive in the response.
Secure the refresh token according to your organization’s policy on storing and maintaining authentication credentials. For example, using a password manger.

Create Client Credentials

You create credentials to use in the client applications using:

A username, which is the OAuth2 Client ID. For example:

1234567890121234567890abcdef1234567890abcdef.apps.googleusercontent.com

A password, which is the OAuth2 client secret in combination with the refresh token separated by four vertical bars. For example:
```
'client_secret||||refresh_token'
```

You can now use the username and password information you created in client applications to log into BigQuery. You will also use this information in future procedures to update the configuration properties "mdstore".user and "mdstore".password in the /opt/datometry/config/dtm.ini configuration file.

Verify the Metadata Store User Has the BigQuery IAM Role

Ensure the user you create has the BigQuery IAM role roles/bigquery.user. To learn more, see BigQuery permissions and predefined IAM roles in the Google documentation.

Datometry Documentation