Install Unravel for GCP BigQuery - VM Authentication method

Unravel can be installed on a GCP instance and configured to monitor jobs, datasets, tables, and data with a service account located outside the Google Cloud environment. This section outlines the steps for installing Unravel for GCP BigQuery using the VM authentication method.

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
  TLS
  TCP
  4443
  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
TLS	TCP	4443

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:
General
This section covers general information about your Unravel install. You are prompted for the following:
```
-- General information
Which data platform are you installing for?
   1- Hadoop
   2- EMR
   3- HDI
   4- Databricks
   5- Dataproc
   6- BigQuery

Select one of the above [Hadoop]: 

## You can choose a number corresponding to the platform. 
```
Select 6- BigQuery.
BigQuery
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 one of the following methods to access data from BigQuery.
-- BigQuery how do you want Unravel to access data from BigQuery? 1- API Pull 2- API Push 3- Information schema Select one of the above []:
API Pull method
Tip
Unravel recommends the API pull method, which is more secure.
Enter 1 to use this API pull method. Even if you leave it empty, the API pull method is used by default.
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 API pull method, there is some delay for the BigQuery jobs to be displayed on the Unravel UI compared to the API push method, where the BigQuery jobs are displayed in real-time.
API Push method
Enter 2 to use this API push method.
If you select the API 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 endpoint supports HTTPS.
Note
Also, refer to 6. Configure an HTTPS Load Balancer for Unravel Endpoint
-- BigQuery Log receiver fully qualified hostname [None]: <lr-endpoint> Log receiver port (integer) [4443]: <lr-port>
INFORMATION-SCHEMA method
Enter 3 to use INFORMATION-SCHEMA method. You are further prompted to specify the following:
how do you want Unravel to access data from BigQuery? 1- API pull 2- API push 3- Information schema Select one of the above []: 3 Selected: Information schema Lookback (days) (integer) [30]: Schema polling interval (seconds) (integer) [1800]: Schema polling threads (integer) [1]: Locations (comma separated) [US]
Schema polling interval
Specify the interval in seconds to poll data from BigQuery. The default is 1800 seconds.
Lookback: The time frame in days to process metadata. The default is 30 days. You can specify in the range of 1 to 180 days.
Schema polling threads
Specify a count for the polling threads. The default is 1.
Locations
Specify the BigQuery locations in a comma-separated format. Refer BigQuery locations.
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 Mode: pull Polling: Default
Database configuration
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]:
License
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 #####
Extra packages
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
```
Trust chain certificates
This check allows you to add root and intermediate certificates to validate the trust chain to establish the connection using TLS. Currently, only pem files are supported.
```
-- TLS certificate trustchain
  Add trusted certificates? (y/n) [No]:  
  ##If you answer “No”, certificates will not be added.
  ##If you answer “Yes”, All the certificates found at the specified location are imported. Wildcards can be used.
```
Unravel HTTPS
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
Unravel UI port
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.
Unravel directories
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.
```
Email configuration
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
```
Full precheck
This option allows you to run the full precheck using some of the provided information. Additional tests like user limits, CPU, and memory are run this way.
```
-- Full precheck
```
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. 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 be 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 project 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 based on your chosen polling method, select any of the following permissions, 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.
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.

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.
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.
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.

Warning

If you have chosen the INFORMATION-SCHEMA polling method during installation, then you can skip this step.

When queries are run, logs are generated in BigQuery. The logs can be pushed to Unravel via Pub/Sub topics. To route logs to Unravel, a Pub/Sub topic must be created.

On the GCP console, go to thePub/Sub topics page.
Note
The logs are pushed to Unravel via Pub/Sub topics.
Click Create topic.
In the Topic ID field, enter an ID for your topic and click Create Topic. Preferably specify unravel-bigquery as the topic ID. The topic is now listed in the list of topics.

Warning

If you have chosen the INFORMATION-SCHEMA polling method during installation, then you can skip this step.

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.

Warning

If you have chosen the INFORMATION-SCHEMA polling method during installation, then you can skip this step.

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/tree/release/4.8.2.0/bigquery

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:
For API Pull/Push method
<Unravel installation directory>/unravel/manager config bigquery add <project-id> <subscription-id>
For INFORMATION-SCHEMA method
<Unravel installation directory>/unravel/manager config bigquery add <project-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 --need-integration
For example: /unravel/manager config bigquery add bigqadminprj1 --is-admin --need-integration
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 --need-integration
For example: /unravel/manager config bigquery add --batch /tmp/proj-id.txt --is-admin --need-integration
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 --need-integration
For example: /unravel/manager config bigquery add bigqadminprj1 --is-admin --no-monitoring --need-integration
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 --need-integration
For example: /unravel/manager config bigquery add --batch /tmp/proj-id.txt --is-admin --no-monitoring --need-integration
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 a single billing account is supported.

You must separately configure the billing data. For this, you must enable GCP cloud billing export to a Bigquery 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.
The 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
Based on your chosen method of resource creation (Manual, external terraform, Unravel managed resources), do the following. Refer to the BigQuery installation document based on the polling and authentication method you have selected for more details.
- Manual creation of resources
  If you have manually created resources, you should modify the IAM role by adding bigquery.tables.getData permissions to the role.
- Creation of resources using external terraform
  For the resources created using an external terraform, you should add the project IDs to the input.tfvars file as elements in a list under the billing_project_id keyword and run the terraform apply command.
- Unravel managed resources
  For Unravel managed resources, run the integrate command. When you run this command, the bigquery.tables.getData permissions and other required permissions are correctly provisioned for the IAM role.
```
<Unravel installation directory>/unravel/manager config bigquery integrate
```
  For example: /opt/unravel/manager config bigquery integrate
  Note
  An error may be shown when running Integrate or terraform apply command with external Terraform scripts. Refer to Error shown when running Integrate or terraform apply command with external Terraform scripts for the solution.

Unset Billing data

Stop Unravel.

<Unravel installation directory>/unravel/manager stop

Run the following command from the Unravel installation directory.

<Unravel installation directory>/unravel/manager config bigquery unset-billing-data

Based on your chosen method of resource creation (Manual, external terraform, Unravel managed resources), do the following:
- Manual creation of resources
  If you have manually created resources, you should modify the IAM role by deleting the bigquery.tables.getData permissions from the role.
- Creation of resources using external terraform
  For the resources created using an external terraform, you should remove the project IDs added to the input.tfvars file as elements in a list under the billing_project_id keyword and run the terraform apply command.
- Unravel managed resources
  For Unravel managed resources, run the integrate command. The bigquery.tables.getData permission, which is required to set the billing data, is removed from the existing permission list.
```
<Unravel installation directory>/unravel/manager config bigquery integrate
```
  /opt/unravel/manager config bigquery integrate
  Note
  An error may be shown when running Integrate or terraform apply command with external Terraform scripts. Refer to Error shown when running Integrate or terraform apply command with external Terraform scripts for the solution.

Apply the changes.

<Unravel installation directory>/unravel/manager config apply

Start Unravel.

<Unravel installation directory>/unravel/manager start

Enable Data page for BigQuery

To enable the Data page, include the BigQuery projects you wish to monitor from the Data page. The Data page on Unravel UI can show data for up to 100 BigQuery projects. You can add single projects as well as multiple projects to the Data page. Data (BigQuery)

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
Based on your chosen method of resource creation, (Manual, external terraform, Unravel managed resources), do the following:
- Manual creation of resources
  If you have manually created resources, you should modify the IAM role by adding bigquery.tables.getData permissions to the role.
- Creation of resources using external terraform
  For the resources created using an external terraform, you should add the project IDs to the input.tfvars file as elements in a list under the datapage_project_ids keyword and run the terraform apply command.
- Unravel managed resources
  For Unravel managed resources, run the integrate command. The bigquery.tables.getData permission, which is required to enable the Data page, is added to the existing permission list.
```
<Unravel installation directory>/unravel/manager config bigquery integrate
```
  /opt/unravel/manager config bigquery integrate
  Note
  An error may be shown when running Integrate or terraform apply command with external Terraform scripts. Refer to Error shown when running Integrate or terraform apply command with external Terraform scripts for the solution.

Run <Unravel installation directory>/unravel/manager config bigquery show to verify if the project IDs are enabled for the Data page. The following sample output is shown:

API Pull/Push polling method

/opt/unravel/manager config bigquery show
-- Running: config bigquery show
BigQuery support: Enabled

Data access:
Mode: pull
Polling (seconds): Default

Billing data location:
Project id: unravel-flat-rate-test
Dataset id: all_billing_data
Table name: gcp_billing_export_resource_v1_016A85_0733E3_979331

Authentication:
Mode: multi
Project: bq-test-project
Terraform integration: Enabled

Projects:
test-admin:
Is admin: True
Is monitoring: False
Last modified: 2023-11-10 12:21:49
test-mon1:
Subscription: unravel-bigquery-sub
Is admin: False
Is monitoring: True
Last modified: 2023-11-10 12:22:05
test-mon2:
Subscription: unravel-bigquery-sub
Is admin: False
Is monitoring: True
Last modified: 2023-11-10 12:22:12
test-res:
Subscription: unravel-bigquery-sub
Is admin: True
Is monitoring: True
Last modified: 2023-11-10 12:21:57
unravel-flat-rate-test:
Subscription: mallik-test
Is admin: False
Is monitoring: True
Last modified: 2023-11-10 12:22:30

Datapage projects: 1 out of 100.
-- OK

INFORMATION_SCHEMA based polling method

`/opt/unravel/manager config bigquery show
-- Running: config bigquery show
BigQuery support: Enabled

Data access:
Mode: schema
Polling (seconds): 300
Polling threads: 5
Lookback days: 90
Max delay (seconds): 1800
Locations: US

Billing data location:
Project id: unravel-flat-rate-test
Dataset id: all_billing_data
Table name: gcp_billing_export_resource_v1_016A85_0733E3_979331

Authentication:
Mode: vm
Project: bq-test-project
Terraform integration: Enabled

Projects:
test-admin:
Is admin: True
Is monitoring: False
Last modified: 2023-11-10 10:31:56
test-mon1:
Is admin: False
Is monitoring: True
Last modified: 2023-11-10 10:32:14
test-mon2:
Is admin: False
Is monitoring: True
Last modified: 2023-11-10 10:32:22
test-res:
Is admin: True
Is monitoring: True
Last modified: 2023-11-10 10:32:07
unravel-flat-rate-test:
Is admin: False
Is monitoring: True
Last modified: 2023-11-10 10:32:27

Datapage projects: 1 out of 100.
-- OK

Disable BigQuery projects from the Data page

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 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
Based on your chosen method of resource creation (Manual, external terraform, Unravel managed resources), do the following:
- Manual creation of resources
  If you have manually created resources, you should modify the IAM role by deleting the bigquery.tables.getData permissions from the role.
- Creation of resources using external terraform
  For the resources created using an external terraform, you should remove the project IDs added to the input.tfvars file as elements in a list under the datapage_project_ids keyword and run the terraform apply command.
- Unravel managed resources
  For Unravel managed resources, run the integrate command. The bigquery.tables.getData permission, which is required to enable the Data page, is removed from the existing permission list.
```
<Unravel installation directory>/unravel/manager config bigquery integrate
```
  /opt/unravel/manager config bigquery integrate
  Note
  An error may be shown when running Integrate or terraform apply command with external Terraform scripts. Refer to Error shown when running Integrate or terraform apply command with external Terraform scripts for the solution.

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

Warning

Note

Warning

Warning

Note

Note

Note

Tip

Note

Note

Note

Note

Notice

Note

Exporting billing data

Note

Note

Note

Enable Data page for BigQuery

Note

Note

Note

Remove BigQuery projects from Unravel

Note

Notice

Note

Search results