Install Unravel for GCP BigQuery - VM Authentication method

You can install Unravel on a GCP instance. Unravel is then set up to monitor the jobs, datasets, tables, and data using a service account outside the Google Cloud environment. This section provides instructions to install Unravel for GCP BigQuery using the VM authentication method.

Before setting up Unravel for BigQuery, you must complete the prerequisites.

Follow the instructions to install and set up Unravel to receive BigQuery data.

Installing Unravel on the GCP Bigquery VM instance

Do the following to set up Unravel on the GCP VM.

1. Creating and configuring the GCP BigQuery VM instance

From your GCP console, go to the Compute Engine dashboard and click Create Instance.
Select the following options based on Unravel's instance requirements:
- Base OS
- Instance type and size
- Ports
- Networking
  The instance must be HTTPS and publicly accessible.
- Firewall rules or policies
  Sample inbound rule
  Type
  Protocol
  Port range
  Source
  All traffic
  All
  All
  For example, 10.10.0.0/16
  SSH
  TCP
  22
  0.0.0.0/0 or trusted public IP for SSH access
  Custom TCP Rule
  TCP
  3000
  Custom TCP Rule
  TCP
  4043
  Sample outbound rule
  Type
  Protocol
  Port range
  Source
  All traffic
  All
  All
  0.0.0.0/0
Note
The GCP VM should have all TCP access to the BigQuery cluster (server/parent or worker) nodes. You can grant access by inserting adding firewall rules of the BigQuery server/parent and worker with all TCP, all port ranges.
While creating the GCP VM, add the Firewall properties, Enable the HTTP and HTTPS traffic Go to Network tab, and add Network tags. (This is the firewall rule that is already created.)

Type	Protocol	Port range	Source
All traffic	All	All	For example, 10.10.0.0/16
SSH	TCP	22	0.0.0.0/0 or trusted public IP for SSH access
Custom TCP Rule	TCP	3000
Custom TCP Rule	TCP	4043

Type	Protocol	Port range	Source
All traffic	All	All	0.0.0.0/0

Configuring the GCE instance

Disable selinux.
```
sudo setenforce Permissive
```
Edit /etc/selinux/config to ensure the setting persists after reboot and ensure SELINUX=permissive.
```
sudo vi /etc/selinux/config
```

Install libaio.x86_64, lzop.x86_64, and ntp.x86_64.

sudo yum install -y libaio.x86_64
sudo yum install -y lzop.x86_64
sudo yum install -y ntp.x86_64

Start ntpd and check the system time.
```
sudo service ntpd start
sudo ntpq -p
```
Create a new Unravel user named unravel.
```
sudo useradd unravel
```

2. Downloading Unravel

Download Unravel onto the VM instance that you have created.

Important

Before downloading Unravel for your platform, ensure to get the username and password from Unravel Support.

Download the Unravel package (RPM or TAR):

You can refer to Download section for the complete list of Unravel product downloads.

For example:

Tar

curl -v https://preview.unraveldata.com/unravel/RPM/4.x.x/unravel-4.x.x.x-cloud.tar.gz -u username:password

RPM

curl -v https://preview.unraveldata.com/unravel/RPM/4.x.x/unravel-4.x.x.x.rpm -o unravel-4.x.x.x.rpm -u username:password

3. Deploying Unravel

Deploy Unravel on the GCP instance that you have created.

Unravel binaries are available as a tar file or RPM package. You can deploy the Unravel binaries in any directory on the server. However, the user who installs Unravel must have the write permissions to the directory where the Unravel binaries are deployed.

After deploying the Unravel binaries, the directory layout for the Tar and RPM will be unravel/versions/<Directories and files>. The binaries are deployed to <Unravel_installation_directory>, and Unravel will be available in <Unravel_installation_directory/unravel.

The following steps to deploy Unravel from a tar file should be performed by a user who will run Unravel.

Create an Installation directory.
```
mkdir /path/to/installation/directory
```
For example: mkdir /opt/
Extract the Unravel tar file to the installation directory, which you have created in the first step. After you extract the contents of the tar file, the unravel directory is created within the installation directory.
```
tar zxf unravel-<version>tar.gz -C /path/to/installation/directory
```
For example: tar zxf unravel-4.x.x.x.tar.gz -C /opt
The unravel directory will be available within /opt.
Grant ownership of the directory to a user who will run Unravel.
```
chown -R username:groupname /path/to/installation/directory
```
For example: chown -R hadoop:hadoop /opt/unravel/

Important

A root user should perform the following steps to deploy Unravel from an RPM package. After the RPM package is deployed, the remaining installation procedures should be completed by the Unravel user.

Create an installation directory.
```
mkdir /usr/local/unravel
```
Run the following command:
```
rpm -i unravel-<version>.rpm
```
For example: rpm -i unravel-4.x.x.x.rpm
The unravel directory will be available in /usr/local.
If you want to provide a different location, use the --prefix command. For example:
```
mkdir /opt/unravel
rpm -i unravel-4.x.x.x.rpm --prefix /opt
```
The unravel directory will be available in /opt.
Grant ownership of the directory to a user who will run Unravel. This user executes all the processes involved in the Unravel installation.
```
chown -R username:groupname /usr/local/unravel
```
For example: chown -R hadoop:hadoop /usr/local/unravel
Continue with the installation procedures as unravel user.

4. Installing Unravel

You can also manually install Unravel; refer to Run setup

The Interactive Precheck utility is run to validate the required configurations before installing Unravel. When you run the Interactive Precheck utility, various checks are prompted for gathering configuration information. The responses you provide for these checks generate a bootstrap configuration file. This file, which contains the configuration information, is then used to install Unravel.

Do the following to install and configure Unravel with Interactive Precheck.

After you download and deploy the Unravel, run the precheck.sh script from unravel/versions/X.Y.Z/healthcheck/.
For example:
/opt/unravel/versions/X.Y.Z/healthcheck/precheck.sh

Enter the necessary details when you are prompted for the following configuration information:

To complete the BigQuery checks, do the following:

Choose 3- vm attached service account (VM) for the following prompt to integrate BigQuery with Unravel using one of the following authentication modes. This mode integrates BigQuery with Unravel using a service account attached to a project, which is attached to the VM where Unravel is installed.
```
-- BigQuery
Authentication mode:
1- Dedicated key per project (multi)
2- Shared key per project (single)
3- vm attached service account (VM) 

Select one of the above. 
```
If you choose this option, you must provide the project ID of the VM where Unravel is installed.
```
-- BigQuery
Project ID: <Provide project ID where you want to create the key> 
```
Select a method that can be used to get data from BigQuery. You can choose either the Pull method or the Push method.
Tip
Unravel recommends the Pull method, which is more secure.
- Pull method
  Type yes to use the Pull method. In case you leave it empty also, the pull method is used by default.
```
-- BigQuery
Do you want Unravel to pull data from BigQuery? (yes=pull, no=push) (y/n) [Yes]: yes
```
  The polling time to pull data from BigQuery is set to 300 seconds (5 mins) by default. You can change this time separately after installation by running the manager config set-polling command:
  For example:
```
/opt/unravel/manager config bigquery set-polling 100 
```
  Refer to Set polling period for BigQuery pull method for detailed instructions.
  Caution
  When you use the Pull method, there is some delay for the BigQuery jobs to be displayed on the Unravel UI compared to the Push method, where the BigQuery jobs are displayed in real-time.
- Push method
```
-- BigQuery
Do you want Unravel to pull data from BigQuery? (yes=pull, no=push) (y/n)
```
  Type no, to use the Push method. If you select the push method, you are prompted to enter the LR endpoint and the LR port where data can be pushed from BigQuery. 4443 is the default LR port, which can be changed. Ensure that the LR end point supports HTTPS.
  Note
  Also, refer to 6. Configure an HTTPS Load Balancer for Unravel Endpoint
```
-- BigQuery
Do you want Unravel to pull data from BigQuery? (yes=pull, no=push) (y/n) [Yes]: no
Log receiver fully qualified hostname [None]: <lr-endpoint>
Log receiver port (integer) [4443]: <lr-port>
```

Note

After you have installed Unravel for BigQuery, run the following command to verify the method you have set to get data from BigQuery.

<Unravel installation directory>/unravel/manager config bigquery show

The following sample output is shown:

-- Running: config bigquery show
BigQuery support: Enabled
LR endpoint: Default
Mode: pull
Polling: Default

This check allows you to configure database-related information and an external database for Unravel.

-- Database configuration
 Configure an external database? (y/n) [No]:

If you answer No, an Unravel-managed database is used for the installation.

If you answer Yes, you are further prompted for the type of external database that you want to configure.

-- Database configuration
   
   Configure an external database? (y/n) [No]: y

   Type
   1- PostgresQL
   2- MySQL
   3- MariaDB

If you choose a specific type of external database, you are prompted for the following database information and test connectivity to that database. Refer to Integrating Database for more details. For example:

-- Database configuration
  
   Configure an external database? (y/n) [No]: y

   Type
   1- PostgresQL
   2- MySQL
   3- MariaDB

   Select one of the above []: 1
   Selected: PostgresQL

   Database hostname [None]:

   Database port (integer) [None]:

   Database schema [None]:

   Does the database use TLS (y/n) [No]: 

   Database username [None]:

   Database password [None] (no echo):

   Do you wish to test connecting to the external database? (y/n) [Yes]:-- Database configuration
 Configure an external database? (y/n) [No]:

If you choose MySQL or MariaDB database you are further prompted for extra packages. If you answer Yes, the Extra packages section searches for the required JDBC drivers.
```
-- Database configuration
 Will Unravel connect to a MySQL or MariaDB database (ex: hive metastore) ? (y/n) [No]: 
```

The license option prompts you to set the Unravel license. Specify the license file location.

--License
Do you want to set the unravel license now? (y/n) [Yes]:
License file location (ex: /path/to/unravel.license) [/home/unravel/valid.lic]:
License: Ok

If the license file is valid, the OK message is displayed.
If the license file is expired, the license expired error is displayed with the license expiry date and timestamp.
If the license file content is incorrect, the invalid license error is displayed with the appropriate error message. You can fix the error in the license file and then set the license.
To resolve the errors, see Licensing error messages and troubleshooting.

Note

If the filename is not provided, the command prompts for the license information. You can provide either a path to the license file or the content of the license file.

Sample content of the license file:

##### BEGIN UNRAVEL LICENSE 
Licensee     : ACME Disintegrating Pistol Manufacturing
Valid from    :  2022-12-16 00:00:00 UTC 
Expire after  :  2023-10-16 23:59:00 UTC
License type : Enterprise
Licensed number of nodes : 1000000
Signature    : c2Uvb2JqLnRhcmdldC92OF9pbml0aWFsaXplcnMvZ2VuL3RvcnF1ZS
Revision     : 1
##### END UNRAVEL LICENSE #####

The Extra packages check shows if you use Unravel-managed MySQL/MariaDB or need JDBC drivers. Else, this check is automatically skipped.

-- Extra package location

*** JDBC drivers are required for Unravel managed MySQL or MariaDB.
*** Database software package is required for Unravel managed MySQL or MariaDB.

External package location [None]: /<my-extra-packages>
##This is the path to the directory where the required packages are located.

If the required packages are located, then the following message is shown:

The following packages will be installed:
   Database server: /my-extra-packages/mysql-5.7.27-linux-glibc2.12-x86_64.tar.gz
   JDBC driver:
     - /my-extra-packages/mysql-connector-java-5.1.48.tar.gz

 External package: Ok

If the required packages are not found, then the following error message is showing:

External package: ERROR
 - ERROR: Couldn't find jdbc drivers in /my-extra-packages
 - ERROR: Looked for:
   mysql-connector-java-*.tar.gz
   mysql-connector-java-*.jar
   mariadb-java-client-*.jar
 - ERROR: Couldn't find database server package in /my-extra-packages
 - ERROR: Looked for:
   mysql-*-linux-glibc2.12-x86_64.tar.gz
   mariadb-*-linux-x86_64.tar.gz

This check allows you to configure and test HTTPS for the unravel UI. This check prompts you for the certificate, key, password, and hostname details used to access Unravel.

Use HTTPS to access unravel? (y/n) [Yes]:  
##If you answer “Yes”, you are prompted for the path to the certificate and key. Unravel uses this information to configure TLS during installation. 
##If you answer “No”, you are shown a warning message for confirmation.

The information provided is verified for the following:

If the Key and Certificate match
If the certificate is valid
If the certificate applies for the provided hostname

This check allows you to set the Unravel UI port and verify the connectivity.

-- Unravel default port
  
  Port number (integer) [3000]: 

  Do you want to test if the port is accessible? (y/n) [Yes]: 
    
  This will open port 3000 and listen for connection for 120 seconds.
  Use your browser to test if the Unravel UI will be accessible on that port.
    
  We have detected the following hostnames:
  - some.host.example
    
 Browse to: http://some.host.example:3000
    
 ATTENTION: This address is an example. You should test with the URL that will be used to access Unravel.

A connection on port 3000 is tried and established. If the connection is successful, Unravel Port Test: OK is shown on the browser, and Unravel port: Reached is shown on the server.

This check allows you to set a custom data directory and verify the access if the directories exist. You will always find the software location where you deploy the Unravel binaries. In this check, only the space and access are tested. That data location that you have configured will be used.

-- Unravel directories

  Software [/opt/unravel]: 

  Data [/opt/unravel/data]: 

  Directories: ERROR
  - OK: 33 GB of free disk space for software.
  - ERROR: SYSH0026: Space for data 33 GB is low, recommended minimum is 100 GB.

This check allows you to configure and test email. You are prompted for host and credentials, and the following items are tested:

Connectivity
Authentication, only if provided.
Optional: Send test mail.

Following is a sample:

-- Mail server (SMTP) configuration

Unravel can send notification and alert emails.
   This will allow you to configure and test connection to a SMTP server.
   Optionally, it can also send a test email.

   You will have to provide:
   - Protocol, hostname and port
   - Credentials if required



   Configure a SMTP server? (y/n) [No]: y

   SMTP hostname [None]: smtphostname.gmail.com

   SMTP port (usually 25 for clear text, 465 for SSL, 587 for STARTLS) (integer) [None]: 587

   Security protocol
   1- None
   2- SSL
   3- StartTLS

   Select one of the above [None]: 3
   Selected: StartTLS

   Authentication required? (y/n) [Yes]: y

   Username [None]: daemon@unraveldata.com

   Password [None] (no echo):

   From [None]: daemon@unraveldata.com

   To [None]: user@unraveldata.com

   Send test email (y/n) [No]: y

Note

For more information, refer to Using the Interactive Precheck utility.

The responses that you have provided for the configuration information are used to generate a configuration file. You can use this configuration file when you run the setup command to install and configure Unravel.

After you have completed the responses, you are prompted to confirm if you want to generate the bootstrap configuration file. Press ENTER if you want to generate the bootstrap configuration file.
```
-- Unravel bootstrap configuration

   Generate a unravel bootstrap configuration file? (y/n) [Yes]:
```
The bootstrap configuration file is generated and located at $HOME/unravel-interactive-precheck/unravel-bootstrap.yaml.

Install Unravel with the bootstrap configuration file.

<unravel_installation_directory>/unravel/versions/<Unravel version>/setup --bootstrap $HOME/unravel-interactive-precheck/unravel-bootstrap.yaml

Apply the changes.

<Unravel installation directory>/unravel/manager config apply

Start all the services.

<unravel_installation_directory>/unravel/manager start

Check the status of services.
```
<unravel_installation_directory>/unravel/manager report 
```
The following service statuses are reported:
- OK: Service is up and running.
- Not Monitored: Service is not running. (Has stopped or has failed to start)
- Initializing: Services are starting up.
- Does not exist: The process unexpectedly disappeared. A restart will be attempted ten times.
You can also get the status and information for a specific service. Run the manager report command as follows:
```
<unravel_installation_directory>/unravel/manager report <service> 
```
For example: /opt/unravel/manager report auto_action

5. Enable Transport Layer Security (TLS) for Unravel UI

Refer to Enabling Transport Layer Security (TLS) for Unravel UI.

6. Configure an HTTPS Load Balancer for Unravel Endpoint

Note

The HTTPS load balancer for Unravel endpoint must be configured only when using the Push model.

Unravel LR endpoint should be available over a publically accessible HTTPS endpoint to receive messages from BigQuery PubSub. The Load Balancer is an easier and more secure method to push the log messages between the Google Cloud Platform (GCP) and Unravel. Use the following instructions to configure an HTTPS load balancer for Unravel with public endpoint and SSL termination.

You must have the following information handy before you configure the Load Balancer:

Region and Zone where the Unravel VM is running.
Network and Subnet-network where the Unravel VM is running.
A valid SSL certificate in GCP.

Do the following to create a Load Balancer

Create an instance group. Refer to Create a managed instance group for detailed instructions.
- In the New unmanaged instance group page, ensure to keep the following items the same as that of Unravel VM.
  - Location > Region
  - Location > Zone
  - Network and Instances > Network
  - Network and Instances > SubNetwork
- Under Port Mapping, enter the following:
  - Port Name: http4043
  - Port Number: 4043
Set up an HTTPS Load Balancer. Refer to Set up an HTTPS Load Balancer for detailed instructions. Ensure to do the following:
- Under Name, update the name as unravel-loadbalancer.
- In Backends > New Backend > Instance groups, select the Unravel instance group that you had created in Step 1.
- Under Health check, do the following:
  - Select Create a health check, and then add the name as unravel-4043-hc
  - Update the Protocol as HTTP and Port as 4043.
  - Update the Request Path as /lr/status.
- Ensure that Port is set to 443 to allow HTTPS traffic.
After the Load Balancer is created, find the public IP address of the Load Balancer that is mentioned under Frontend section of the Load Balancer. Add the IP address of the Load Balancer to a valid DNS name.

Setting Unravel to receive BigQuery data

Unravel can be set up to automatically create and configure resources in more than 100 projects at a time. Based on the authentication method you selected while installing Unravel, you can add projects either with customer-supplied credentials or with Unravel-generated credentials for Unravel monitoring. These can be single projects or multiple projects.

To integrate the BigQuery projects in Unravel, you must create a service account in only the project in which Unravel is hosted. This service account must is attached to the Unravel VM. Custom roles are created for the monitoring and administrator projects with required permissions. IAM binding of the role is done with the principal, which is the service account, in each of the projects.

Unravel ships the following resources, which are required to automatically set up Unravel to receive BigQuery data.

Terraform
This is an open-source software for infrastructure provisioning. The Terraform creates resources on the GCP account and facilitates the smooth integration of Unravel with your cloud platform.
You can choose to either use the Terraform, which is bundled with Unravel installer or edit and use the external Terraform, which is provided separately, independent of the installer.
gcloud CLI
Set of tools to create and manage Google Cloud resources.

BigQuery projects can be set by any one of the following. Ensure to complete the prerequisites before you set up BigQuery projects.

Customer-supplied credentials: For the customer-supplied credentials, the customer must create and provide all the resources. You can either do this manually from the GCP console, or you can use the external Terraform.
Unravel-generated credentials: For projects added with Unravel-generated credentials, all the resources are created and handled by Unravel.

Configuring BigQuery projects with customer-supplied credentials

You can configure the BigQuery projects using one of the following methods:

Manually from the GCP. Refer to Add BigQuery projects manually from the GCP console
Using external Terraform. Refer to Add BigQuery projects using external Terraform. All the steps that you perform manually to configure BigQuery projects from the GCP console are automatically handled by the external Terraform.

Stop Unravel.

<Unravel installation directory>/unravel/manager stop

Configure the BigQuery projects.

You can use one of the following methods to configure the BigQuery projects manually:

A project ID is a unique string used to differentiate your project from all others in Google Cloud. You cannot edit a Project ID after it is generated.

You can either create a project on the GCP account and get the Project ID or get the Project ID of an existing project and keep it handy. The Project ID must be set in Unravel later using the manager utility.

There are three types of projects IDs that are required for the integration of BigQuery with Unravel

Unravel project ID: Project ID of the VM where Unravel is installed. Refer to the Project ID mentioned in the diagram.
Monitoring project ID: Project ID of the projects in which jobs (query, load, extract, copy) are executed.
Administrator project ID: Project ID of the projects in which reservation, commitment, slot assignment data is stored.

Create a role for an Unravel project with the required permission for Unravel monitoring. This role is assigned to the Unravel service account that is used to authenticate Unravel applications.

On the GCP Console, go to the Roles page.
Using the drop-down list at the top of the page, select the Unravel project in which you want to create a role.
Click Create Role and enter a Name, Title, and Descriptionfor the role. Preferably keep the role name as unravel for easy identification. The description is optional, which can be maintained as Unravel monitoring.
Select Role launch stage as General Availability.
Caution
The role name cannot be changed after the role is created.
Click Add Permissions.

In the Add Permissions dialog box, filter and select the following permissions for the role, and then click Add.

For monitoring projects

Permission	Description
bigquery.jobs.listAll	Gets all the jobs list.
bigquery.reservationAssignments.search	Retrieves the reservation assignments data for the assigned project.
bigquery.transfers.get	Retrieves information about all transfer configs owned by a project in the specified location. (Scheduled jobs list)
recommender.bigqueryCapacityCommitmentsInsights.get	Fetches the insights and recommendations generated by the Capacity Commitments Recommender for BigQuery.
recommender.bigqueryCapacityCommitmentsInsights.list	Retrieves the list of insights and recommendations that the Capacity Commitments Recommender has generated for BigQuery's capacity commitments.
recommender.bigqueryCapacityCommitmentsRecommendations.get	Fetches a specific recommendation provided by the Capacity Commitments Recommender for BigQuery's capacity commitments.
recommender.bigqueryCapacityCommitmentsRecommendations.list	Retrieves a list of recommendations that the Capacity Commitments Recommender has generated for BigQuery's capacity commitments.
recommender.bigqueryPartitionClusterRecommendations.get	Fetches a specific recommendation provided by the Partition Cluster Recommender for optimizing BigQuery table partitioning and clustering.
recommender.bigqueryPartitionClusterRecommendations.list	Retrieves a list of recommendations that the Partition Cluster Recommender has generated for BigQuery tables' partitioning and clustering.
serviceusage.services.use	Grants the capability to enable or use specific services with their respective service IDs in a GCP project.
bigquery.datasets.get bigquery.routines.get bigquery.routines.list bigquery.tables.getData bigquery.tables.list	Permission to get the tables and partitions metadata. These permissions are required for the Data page. Note The bigquery.tables.getData permission is activated only if you choose to enable the Data Page or set Billing Data in your Unravel environment.Configuring Bigquery Data Page
bigquery.jobs.create	Permission to execute queries on BigQuery to fetch the metadata about the tables and partitions. These permissions are required for the Data page.
bigquery.jobs.get	Gets the job details.
bigquery.tables.get	Gets the table details.
pubsub.subscriptions.consume	Consumes the message from google pub-sub topic.
resourcemanager.projects.get	Gets the project details. For this permission, you must enable the Resource Manager API.

For administrator projects

Permissions	description
bigquery.reservations.list	Retrieves the reservations list for the admin project.
bigquery.capacityCommitments.list	Retrieves the capacity commitments list for the admin project.
bigquery.jobs.create	Creates a job on bigquery information schema to retrieve the reservations and commitments data.

Enable the BigQuery Data Transfer API.
Click Create. The role is created.

A service account can be attached to a VM so that applications running on that VM can authenticate as the service account.

To set up Unravel for BigQuery monitoring, you must create a service account in the project of the VM where Unravel is installed and assign the role created in Step 3 to this service account.

On the GCP Console, go to the Create service account page.
Select the project ID of the VM where Unravel is installed.
Under Service Account Details, enter a service account name to display on the GCP Console. Preferably keep the service account name as unravel-service-account.
Note
A service account ID is generated based on this name. Edit the ID if required. You cannot change the ID later.
Optional: Enter a description of the service account. Preferably keep the description as Unravel monitoring.
Click CREATE AND CONTINUE.
Click Done. The service account is created.

Sinks control how Cloud Logging routes log. Using sinks, you can route the logs to supported destinations.

On the GCP console, go to the Logs Router page.
Click Create Sink and provide a name and description for the sink. Preferably name the Sink as unravel-sink.
Under Sink Destination, from the Sink Service dropdown, select the Cloud Pub/Sub topic.
From Select a Cloud Pub/Sub topic, select the Cloud Pub/Sub topic that you had created for Unravel project in Step 5.

Under Choose logs to include in the Sink, add a filter to determine the logs that must be included in the log routing sink. For example

 (resource.type="bigquery_resource" AND ((protoPayload.methodName="jobservice.insert" AND  protoPayload.serviceData.jobInsertResponse.resource.jobName.jobId :*) OR (protoPayload.methodName="jobservice.jobcompleted" AND
  protoPayload.serviceData.jobCompletedEvent.job.jobName.jobId :*))) OR (resource.type="bigquery_dts_config" AND (labels.run_id :* AND resource.labels.config_id :*))

In this example:

bigquery_resource indicates the BigQuery logs.
jobservice.insert indicates the jobs of running type.
jobservice.jobcompleted indicates the jobs of completed type.

Click Create Sink.

A subscription is created to subscribe to associated topics and set the process to send the logs to unravel. Do the following to create a subscription.

On the GCP console, go to the Pub/Sub topics page.
Select the topic that was created in Step 5.
Scroll down to the Subscriptions tab and click Create Subscription > Create Subscription.
In the Subscription ID text box, provide a name for the subscription. Preferably keep the Subscription ID as unravel-bigquery-sub.
Under the Delivery type, select Push or Pull.
If you have selected Push, then in the Endpoint URL text box, provide the Log Receiver (LR) endpoint URL for PUSH messages. LR server receives the logs from Google Pub/Sub and is the entry point to unravel. The criterion for the LR endpoint URLs is as follows:
- Has to be a POST verb.
- Has to be an HTTPS endpoint. Refer to Enabling Transport Layer Security (TLS) for Unravel UI
- Should serve a valid self-signed certificate.
- The endpoint should be publicly accessible.
You must specify the endpoint URL in the following format:
```
https://<unravel-hostname>:4443/logs/bigquery/<gcp-project-id>/bigquery/bigquery
```
For example:
```
https://playground-bq-4770.unraveldata.com:4443/logs/bigquery/unravelsaas-329506/bigquery/bigquery
```
Provide the expiry time for the subscription.
Under Message ordering, select the Order messages with an ordering key option. This will allow the messages tagged with the same ordering key to be received in the published order.
Note
This option can be selected only once when you are configuring Pub/Sub service for the first time.
Optionally, you can filter the messages via Dead lettering and Retry Policy options.
Click Create.

You must give the IAM access to the service account by adding the principal and by assigning the Unravel role that you have created with the required permissions.

Open the IAM page in the Google Cloud console.
Select a project and click Grant Access.
In the Grant access to <Project> dialog box, in Add principals section, select a principal. The principal is the service account that you have created in the project where Unravel is installed.
In the Assign roles, select the role that you have created with the set of permissions.
Click Save.

With the external Terraform, all the steps that are performed manually to configure BigQuery projects from the GCP console are automatically handled. To use the external Terraform, do the following:

Go to Unravel Cloud Platform Integration scripts in GitHub.

Based on the instructions, clone unravel-terraform-scripts.

git clone https://github.com/unraveldata-org/unravel-terraform-scripts.git

Go into the bigquery folder and follow the instructions in the README file.

Also, see Disintegrating GCP projects with Unravel.

Add projects.
Add projects
The following types of projects can be added.
Single BigQuery monitoring project (customer-supplied credentials)
Note
In case you have configured the Push method, ensure to configure the target endpoint of the Pub/Sub subscription ID to the Unravel LR HTTPS endpoint.
To add a single BigQuery project for Unravel monitoring, run the following command:
<Unravel installation directory>/unravel/manager config bigquery add <project-id> <subscription-id>
Note
In case you have used the external Terraform to configure, ensure to add the same subscription ID here that you have provided in the Terraform scripts.
For example:
/opt/unravel/manager config bigquery add my-project subscription-12345
Tip
You can use the same command to add more projects one at a time. If you use the same project ID, the existing project gets updated.
Multiple BigQuery monitoring projects (customer-supplied credentials)
You can configure multiple projects with customer-supplied credentials. To implement this, you must create a text file containing the list of project IDs and provide the path of this file in the following command. Each line in the text file must carry a single Project ID. Along with the project ID, you must also provide the subscription ID and the path to the customer-supplied credentials file. During the configuration, the credentials in the file are copied and encrypted.
Run the following command to add multiple BigQuery projects for Unravel monitoring with customer-supplied credentials.
For API Pull/Push method
<Unravel installation directory>/unravel/manager config bigquery add --batch </path/to/project-id-file> [--subscription-id <sub>]
For INFORMATION-SCHEMA method
<Unravel installation directory>/unravel/manager config bigquery add --batch </path/to/project-id-file> [--subscription-id <sub>]
--subscription-id : This is an optional field when you add projects with Unravel-generated credentials. If you do not provide any value, the default unravel-bigquery-sub is considered.
Note
The --subscription-id is a mandatory field when you add projects with customer-supplied credentials since there is a different subscription ID.
In case you have used the external Terraform to configure, ensure to add the same subscription ID here that you have provided in the Terraform scripts.
In case of INFORMATION-SCHEMA method, you need not add the subscription ID.
</path/to/project-id-file>: The full path to the directory containing the project IDs.
For example, you have the project-id-file with the following project IDs:
first-project second-project
Administrator projects (customer-supplied credentials)
Adminstrator projects store the data of reservation, commitment, and slot assignment. The administrator projects are integrated into Unravel to retrieve reservation data. To integrate the administrator project, you must create a custom role with specific permissions, give the IAM access to the service account, and then assign the custom role to the service account.
Create a custom role and add administrator permissions to this role. Refer to Create a role in the project topic for instructions. Add only the Step 2.4.
Add the administrator project using the following command:
Add administrator project with monitoring. These are admin projects where BigQuery jobs are also run and require monitoring.
Adding single administrator project for monitoring
Unravel installation directory>/unravel/manager config bigquery add <project ID of the administrator project> --is-admin
For example: /unravel/manager config bigquery add bigqadminprj1 --is-admin
Adding multiple administrator projects for monitoring
Refer to Project ID text file.
Unravel installation directory>/unravel/manager config bigquery add --batch </path/to/project-id-file> --is-admin
For example: /unravel/manager config bigquery add --batch /tmp/proj-id.txt --is-admin
Add administrator project without monitoring. These are admin projects that store reservation data and do not run BigQuery jobs. Therefore may not require monitoring.
Adding single administrator project
Unravel installation directory>/unravel/manager config bigquery add <project ID of the administrator project> --is-admin --no-monitoring
For example: /unravel/manager config bigquery add bigqadminprj1 --is-admin --no-monitoring
Adding multiple administrator projects
Refer to Project ID text file.
Unravel installation directory>/unravel/manager config bigquery add --batch </path/to/project-id-file> --is-admin --no-monitoring
For example: /unravel/manager config bigquery add --batch /tmp/proj-id.txt --is-admin --no-monitoring
Note
To remove projects, you can refer to Remove BigQuery projects from Unravel.
Attach the service account to Unravel VM manually. To execute this step, you must shut down the VM and restart the VM after attaching the service account to that VM.
1. On the GCP, go to VM instances and select the VM instance where Unravel is installed.
2. From More actions, click Stop and confirm. The VM is stopped.
3. Click the same VM link and then click Edit. The Edit <VM-pagename> page is displayed.
4. Scroll down to Service accounts section and select the service account that you want to attach to the VM.
5. Click Save.
6. From the VM instances page, restart the VM.

Run <Unravel installation directory>/unravel/manager config bigquery show to verify. The following output is shown:

/opt/unravel/manager config bigquery show
-- Running: config bigquery show
BigQuery support: Enabled
LR endpoint: Default
Mode: pull
Polling: Default

Billing data location: Not configured

Authentication mode: vm
   Project: unravel-prj-4810
   Terraform integration: Enabled

Project: prj3-394305
  Subscription: unravel-bigquery-sub-final
  Is admin: False
  Is monitoring: True
  Integration: True
Project: prj4
  Subscription: unravel-bigquery-sub-final
  Is admin: False
  Is monitoring: True
  Integration: True
Project: prj5-394305
  Subscription: unravel-bigquery-sub-final
  Is admin: False
  Is monitoring: True
  Integration: True

Apply the changes.

<Unravel installation directory>/unravel/manager config apply

Start Unravel.

<Unravel installation directory>/unravel/manager start

Verify BigQuery integration
1. On the GCP console, run test queries from the project integrated with Unravel.
2. Using a supported web browser, navigate to Unravel URL (For example, https://<unravel-host>:3000) and log onto Unravel UI using the credentials.
3. Navigate to Jobs tab and click All in the left panel. The details of the queries run from the GCP console will be listed.
  To verify the integration of administrator projects, check the Reservation column from the Projects tab.

Configuring BigQuery projects with Unravel-generated credentials

Stop Unravel.

<Unravel installation directory>/unravel/manager stop

Configure the BigQuery projects.
Single BigQuery monitoring project (Unravel-generated credentials)
Unravel can automatically create the appropriate resources and generate the credentials required to monitor BigQuery projects. To add a single BigQuery project for Unravel monitoring with Unravel-managed credentials, do the following:
Using a terminal, run the following command from the Unravel installation directory:
```
<Unravel installation directory>/unravel/manager config bigquery add <project-id> <subscription-id> --need-integration
```
Note
The subscription ID is optional. In case you have chosen the INFORMATION-SCHEMA method for polling data, you need not add the subscription ID.
For example:
/opt/unravel/manager config bigquery add my-project unravel-bigquery-sub --need-integration
Multiple BigQuery monitoring projects (Unravel-generated credentials)
Unravel can automatically create resources on the GCP side and generate the credentials required to monitor BigQuery projects.
Run the following command to add multiple BigQuery monitoring projects with Unravel-generated credentials:
<Unravel installation directory>/unravel/manager config bigquery add --batch </path/to/project-id-file> [--subscription-id <sub>] --need-integration
Note
</path/to/project-id-file>: The full path to the directory containing the project IDs.
--subscription-id : This is an optional field. If you do not provide any value, the default unravel-bigquery-sub is considered.
In case you have chosen the INFORMATION-SCHEMA method for polling data, you need not add the subscription ID.
For example, you have a list of projects in project-id-file with a subscription ID as a-sub-id. Run the following command to add multiple projects with Unravel-generated credentials:
manager config bigquery add --batch /opt/unravel/project-id-file --subscription-id a-sub-id --need-integration
Administrator projects (Unravel-generated credentials)
Adminstrator projects store the data of reservation, commitment, and slot assignment. The administrator projects are integrated into Unravel to retrieve reservation data. To integrate the administrator project, you must create a custom role with specific permissions, give the IAM access to the service account, and then assign the custom role to the service account.
Create a custom role and add administrator permissions to this role. Refer to Create a role in the project topic for instructions. Add only the Step 2.4.
Add the administrator project using the following command:
Add administrator project with monitoring. These are admin projects where BigQuery jobs are also run and require monitoring.
Adding single administrator project for monitoring
Unravel installation directory>/unravel/manager config bigquery add <project ID of the administrator project> --is-admin --create-credentials
For example: /unravel/manager config bigquery add bigqadminprj1 --is-admin --create-credentials
Adding multiple administrator projects for monitoring
Refer to Project ID text file.
Unravel installation directory>/unravel/manager config bigquery add --batch </path/to/project-id-file> --is-admin --create-credentials
For example: /unravel/manager config bigquery add --batch /tmp/proj-id.txt --is-admin --create-credentials
Add administrator project without monitoring. These are admin projects that store reservation data and do not run BigQuery jobs. Therefore may not require monitoring.
Adding single administrator project
Unravel installation directory>/unravel/manager config bigquery add <project ID of the administrator project> --is-admin --no-monitoring --create-credentials
For example: /unravel/manager config bigquery add bigqadminprj1 --is-admin --no-monitoring --create-credentials
Adding multiple administrator projects
Refer to Project ID text file.
Unravel installation directory>/unravel/manager config bigquery add --batch </path/to/project-id-file> --is-admin --no-monitoring --create-credentials
For example: /unravel/manager config bigquery add --batch /tmp/proj-id.txt --is-admin --no-monitoring --create-credentials
Integrate BigQuery projects for Unravel monitoring. This is a mandatory step for Unravel-generated credentials. The following command configures all the projects added Unravel.
Notice
Do not run the following command if you have used external Terraform to automatically create resources.
```
<Unravel installation directory>/unravel/manager config bigquery integrate
```
A URL will be provided in the output.
Note
If you want to skip the interactive gcloud authentication by Unravel and handle the gcloud authentication on your own, then run the command as follows:
```
<Unravel installation directory>/unravel/manager config bigquery integrate --skip-authorization
```
Authenticate gcloud CLI.
1. On a Google Chrome browser, copy the URL provided in the output, and in the sign-in dialog box, click Allow. Ensure to sign in to the gcloud CLI from the account that is authenticated with the required permissions.
2. From the Sign in to the glcoud CLI box, click Copy button to copy the authorization code.
3. Go back to the terminal and paste the authorization code in the Enter authorization code field, and press ENTER. This will run the following actions in the background:
  - Authenticate the user with Google Cloud.
  - Configure the required resources on the GCP.
  - Encrypt the credentials (service account keys) and then integrate them with Unravel.
  - Integrate all the added BigQuery projects with Unravel.
  - Securely sign out the end user from the gcloud session.
Attach the service account to Unravel VM manually. To execute this step, you must shut down the VM and restart the VM after attaching the service account to that VM.
1. On the GCP, go to VM instances and select the VM instance where Unravel is installed.
2. From More actions, click Stop and confirm. The VM is stopped.
3. Click the same VM link and then click Edit. The Edit <VM-pagename> page is displayed.
4. Scroll down to Service accounts section and select the service account that you want to attach to the VM.
5. Click Save.
6. From the VM instances page, restart the VM.
Start Unravel. Unravel is stopped as a part of VM restart.
```
<Unravel installation directory>/unravel/manager start
```

Run <Unravel installation directory>/unravel/manager config bigquery show to verify. The following output is shown:

/opt/unravel/manager config bigquery show
-- Running: config bigquery show
BigQuery support: Enabled
LR endpoint: Default
Mode: pull
Polling: Default

Billing data location: Not configured

Authentication mode: vm
   Project: unravel-prj-4810
   Terraform integration: Enabled

Project: prj3-394305
  Subscription: unravel-bigquery-sub-final
  Is admin: False
  Is monitoring: True
  Integration: True
Project: prj4
  Subscription: unravel-bigquery-sub-final
  Is admin: False
  Is monitoring: True
  Integration: True
Project: prj5-394305
  Subscription: unravel-bigquery-sub-final
  Is admin: False
  Is monitoring: True
  Integration: True

Apply the changes.

<Unravel installation directory>/unravel/manager config apply

Start Unravel.

<Unravel installation directory>/unravel/manager start

Verify BigQuery integration
1. On the GCP console, run test queries from the project integrated with Unravel.
2. Using a supported web browser, navigate to Unravel URL (For example, https://<unravel-host>:3000) and log onto Unravel UI using the credentials.
3. Navigate to Jobs tab and click All in the left panel. The details of the queries run from the GCP console will be listed.
  To verify the integration of administrator projects, check the Reservation column from the Projects tab.

Exporting billing data

Note

Only single billing account is supported.

You must separately configure the billing data. For this, you must export the billing data to a BigQuery table and integrate Unravel with that table for Unravel to query the table.

The following information is required for integrating the billing data:

Project ID in which billing data is exported. This project ID should be integrated with Unravel for monitoring.
Dataset in which the billing data is exported.
Table in which billing data is exported.

To export billing data for BigQuery monitoring, do the following:

Export Billing Data to a BigQuery DataSet. Refer to Set up Cloud Billing data export to BigQuery for detailed instructions.

Stop Unravel.

<Unravel installation directory>/unravel/manager stop

Run the following command and set the billing info.
```
<Unravel installation directory>/unravel/manager config bigquery set-billing-data
```
You are prompted to enter the following:
- Project ID where billing export is enabled
- Dataset ID where data is getting exported
- Name of the table where data is getting exported
Note
To unset the properties that you have set for exporting billing data, run the following command:
```
<Unravel installation directory>/unravel/manager config bigquery unset-billing-data
```

Adding BigQuery projects to the Data page

You must separately configure the BigQuery projects that you want to track from the Data page. The Data page on Unravel UI can show data for only up to 100 BigQuery projects.Data

Also, refer to Remove BigQuery projects from the Data page on Unravel UI.

Add projects to the Data page.
- To add single projects to the Data page, run the following:
```
<Unravel installation directory>/unravel/manager config bigquery enable-datapage <project-id>
```
  For example: /opt/unravel/manager config bigquery enable-datapage myproject
- To add multiple projects to the Data page, run the following:
```
<Unravel installation directory>/unravel/manager config bigquery enable-datapage --batch </path/to/project-id-file> 
```
  For example: /opt/unravel/manager config bigquery enable-datapage --batch /opt/unravel/project-id-file
Run <Unravel installation directory>/unravel/manager config bigquery show to verify if the project IDs are enabled for the Data page.

Remove BigQuery projects from the Data page on Unravel UI

Note

When you remove a BigQuery project from the Data page, then the associated data also gets deleted from OpenSearch.

Stop Unravel.

<Unravel installation directory>/unravel/manager stop

Run the following command from the Unravel installation directory.
- For single project
```
<Unravel installation directory>/unravel/manager config bigquery delete-datapage <project-ID>
```
  For example: /opt/unravel/manager config bigquery delete-datapage --batch my-project
- For multiple projects
```
<Unravel installation directory>/unravel/manager config bigquery delete-datapage --batch </path/to/project-id-file>
```
  For example: /opt/unravel/manager config bigquery delete-datapage --batch /opt/unravel/my-projects.txt

Apply the changes.

<Unravel installation directory>/unravel/manager config apply

Start Unravel.

<Unravel installation directory>/unravel/manager start

Remove BigQuery projects from Unravel

Note

When you remove a BigQuery project from Unravel, then the associated data also gets deleted from OpenSearch.

You can perform the following steps to remove BigQuery projects from Unravel. In case you have integrated BigQuery projects using Terraform, then refer Disintegrating GCP projects with Unravel

Stop Unravel.

<Unravel installation directory>/unravel/manager stop

Run the following command from the Unravel installation directory.
- For single project
```
<Unravel installation directory>/unravel/manager config bigquery remove <project-ID>
```
  For example: /opt/unravel/manager config bigquery remove my-project
- For multiple projects
```
<Unravel installation directory>/unravel/manager config bigquery remove --batch </path/to/project-id-file>
```
  For example: /opt/unravel/manager config bigquery remove --batch /opt/unravel/my-projects.txt
Integrate BigQuery projects for Unravel monitoring. This is a mandatory step for Unravel-generated credentials. The following command configures all the projects added for Unravel monitoring at once i.e., projects with customer-supplied credentials as well as with Unravel-managed credentials.
Notice
Do not run the following command if you have used external Terraform to automatically create resources.
```
<Unravel installation directory>/unravel/manager config bigquery integrate
```
A URL will be provided in the output.
Note
If you want to skip the interactive gcloud authentication by Unravel and handle the gcloud authentication on your own, then run the command as follows:
```
<Unravel installation directory>/unravel/manager config bigquery integrate --skip-authorization
```
Authenticate gcloud CLI.
1. On a Google Chrome browser, copy the URL provided in the output, and in the sign-in dialog box, click Allow. Ensure to sign in to the gcloud CLI from the account that is authenticated with the required permissions.
2. From the Sign in to the glcoud CLI box, click Copy button to copy the authorization code.
3. Go back to the terminal and paste the authorization code in the Enter authorization code field, and press ENTER. This will run the following actions in the background:
  - Authenticate the user with Google Cloud.
  - Configure the required resources on the GCP.
  - Encrypt the credentials (service account keys) and then integrate them with Unravel.
  - Integrate all the added BigQuery projects with Unravel.
  - Securely sign out the end user from the gcloud session.

Apply the changes.

<Unravel installation directory>/unravel/manager config apply

Start Unravel.

<Unravel installation directory>/unravel/manager start

In this section:

Home

Install Unravel for GCP BigQuery - VM Authentication method

Installing Unravel on the GCP Bigquery VM instance

Note

Important

Important

Tip

Caution

Note

Note

Note

Note

Note

Setting Unravel to receive BigQuery data

Caution

Note

Note

Note

Note

Note

Note

Tip

Note

Note

Note

Note

Notice

Note

Exporting billing data

Note

Note

Adding BigQuery projects to the Data page

Note

Remove BigQuery projects from Unravel

Note

Notice

Note

Search results